There was a question in the Community forum about creating tables in an external databases that would match the schema of F&O data entities.
One of the possible approaches is generating SQL code for CREATE TABLE statement. The information about field names, types and such things would be taken from F&O metadata and we already have a nice API for this purpose.
Here is code for simple console application getting some of the necessary data:
using System; using System.Collections.Generic; using System.Linq; using Microsoft.Dynamics.AX.Metadata.MetaModel; using Microsoft.Dynamics.AX.Metadata.Storage; using Microsoft.Dynamics.AX.Metadata.Storage.Runtime; namespace DataEntityFields { public class Demo { public static void Main(string[] args) { var environment = Microsoft.Dynamics.ApplicationPlatform.Environment.EnvironmentFactory.GetApplicationEnvironment(); var runtimeConfiguration = new RuntimeProviderConfiguration(environment.Aos.PackageDirectory); var metadataProvider = new MetadataProviderFactory().CreateRuntimeProviderWithExtensions(runtimeConfiguration); AxDataEntityView entity = metadataProvider.DataEntityViews.Read("VendVendorV2Entity"); foreach (AxDataEntityViewMappedField entityField in entity.Fields.Where(f => f is AxDataEntityViewMappedField)) { var datasource = FindDataSource(entity.ViewMetadata.DataSources, entityField.DataSource); if (datasource == null) { continue; } AxTable table = metadataProvider.Tables.Read(datasource.Table); AxTableField field = table.Fields[entityField.DataField]; AxTableFieldString stringField = field as AxTableFieldString; if (stringField != null) { AxEdtString edt = null; if (stringField.ExtendedDataType != "") { edt = metadataProvider.Edts.Read(stringField.ExtendedDataType) as AxEdtString; } int stringSize = edt?.StringSize ?? stringField.StringSize; Console.WriteLine($"{entityField.Name} (String {stringSize})"); } } Console.ReadLine(); } private static AxQuerySimpleDataSource FindDataSource(IEnumerable<AxQuerySimpleDatasource> dataSources, string dataSourceName) { foreach (AxQuerySimpleDataSource ds in dataSources) { if (ds.Name == dataSourceName) { return ds; } return FindDataSource(ds.DataSources, dataSourceName); } return null; } } }
Note that it requires references to a few assemblies:
- Microsoft.Dynamics.AX.Metadata.dll
- Microsoft.Dynamics.AX.Metadata.Core.dll
- Microsoft.Dynamics.AX.Metadata.Storage.dll
- Microsoft.Dynamics.ApplicationPlatform.Environment.dll