Important NOTE: in example below you’ll see a creation of entity for export of data from AX, you’ll not be able to import data with these kind of entity.
For some scenarios you may need to create a DataEntity that don’t have a natural key (like InventTrans), or worse – tables, which don’t have a unique index at all (like CustTrans). Also, for such types of Entities, you may have a requirement – support of Incremental Push (which is also an issue if your table for DataEntity doesn’t have a natural key);
In order to satisfy these requirements, you need to:
1. Create a Query (CustTransQ) object and as a root data source – you need to specify your table (CustTrans);
2. Create a View object (CustTransV) and into Query property – you should specify CustTransQ object, created one step earlier;
3. Normally, by using wizard for creation of DataEntity, you need to select a table, which has a natural key, but for this scenario, you can select ANY table that has natural key (it’s just for the purpose of creation the DataEntity object in AOT – let’s call it MyCustTransEntity). For instance – you can select InventDim table.
4. Make sure, that on this step you uncheck all checkboxes (this means that our newly created DataEntity will not contains any field from InventDim table).
5. Delete InventDim table from the DataSource of MyCustTransEntity;
6. Drug CustTransV into DataSource of MyCustTransEntity;
7. Select those fields from CustTransV that you need to be exported via your DataEntity and drug and drop them into Fields node of MyCustTransEntity. The CustTransV.RecId field must also be placed in Fields node of MyCustTransEntity;
8. When you drug and drop CustTransView.RecId field into Fields node, you need to rename it: let’s say from RecId1 to something like CustTransRecId;
9. Add CustTransRecId field into Entity Index node;
After these operations, you need to build you model with synchronization.
So, at this moment your DataEntity can be user for data Export. In order to use this DataEntity for incremental data export, you need to create a class, which will handle some events of DMFEntityBase class (the DMFEntityBase class is responsible for analysis of DataEntities). So, below you’ll see class, which allows to:
1. Use a Views as a dataSource for your DataEntity
2. Check whether change tracking (CT) is enabled for your DataEntity->View->Query-Table
a. So it will enable/disable CT if you’ll add/delete your DataEntityl;
/// <remarks>
/// Fixing of MS wrong logic if DataSource for DataEntity is View (sourced by Query)
/// </remarks >
using DataManagementSerialization = Microsoft.Dynamics.AX.Framework.Tools.DataManagement.Serialization;
using Microsoft.Dynamics.BusinessPlatform.SharedTypes;
using Microsoft.Dynamics.ApplicationFoundation.DIXF.Instrumentation;
class DMFEntityBase_YourModel_ClassEH
{
const static str qtyOfTablesCTEnabledArgName = ‘qtyOfTablesCTEnabled’;
const static str isCTEnabledArgName = ‘isCTEnabled’;
/// <summary>
///
/// </summary>
/// <param name=”args”></param>
/// <remarks>
/// Fixing of MS wrong logic if DataSource for DataEntity is View (sourced by Query)
/// </remarks >
[PreHandlerFor(classStr(DMFEntityBase), staticMethodStr(DMFEntityBase, DisableCTForEntity))]
public static void DMFEntityBase_Pre_DisableCTForEntity(XppPrePostArgs _args)
{
DMFEntity entityloc = _args.getArg(‘entityloc’);
boolean isCTEnabledForTable = false;
int qtyOfTablesCTEnabled = 0;
DictDataEntity dictDataEntity ;
AifSqlChangeTrackingEnabledTables aifSqlChangeTrackingEnabledTables;
dictDataEntity = new DictDataEntity(tableName2Id(entityloc.TargetEntity));
TableName rootTableName = DMFEntityBase::getRootTableName(dictDataEntity);
if(rootTableName && entityloc.TargetEntity != “”)
{
isCTEnabledForTable = AifSqlChangeTrackingEnabledTables::find(rootTableName, entityloc.TargetEntity).RecId != 0;
if (isCTEnabledForTable)
{
select count(RecId) from aifSqlChangeTrackingEnabledTables
where aifSqlChangeTrackingEnabledTables.TableName == rootTableName;
qtyOfTablesCTEnabled = aifSqlChangeTrackingEnabledTables.RecId;
}
}
_args.addArg(DMFEntityBase_YourModel_ClassEH::qtyOfTablesCTEnabledArgName, aifSqlChangeTrackingEnabledTables.RecId);
_args.addArg(DMFEntityBase_YourModel_ClassEH::isCTEnabledArgName, isCTEnabledForTable);
}
/// <summary>
///
/// </summary>
/// <param name=”args”></param>
/// <remarks>
/// Fixing of MS wrong logic if DataSource for DataEntity is View (sourced by Query)
/// </remarks >
[PostHandlerFor(classStr(DMFEntityBase), staticMethodStr(DMFEntityBase, DisableCTForEntity))]
public static void DMFEntityBase_Post_DisableCTForEntity(XppPrePostArgs _args)
{
DMFEntity entityloc = _args.getArg(‘entityloc’);
boolean isCTEnabledForTable = _args.getArg(DMFEntityBase_YourModel_ClassEH::isCTEnabledArgName);
int qtyOfTablesCTEnabled = _args.getArg(DMFEntityBase_YourModel_ClassEH::qtyOfTablesCTEnabledArgName);
DictDataEntity dictDataEntity ;
AifSqlChangeTrackingEnabledTables aifSqlChangeTrackingEnabledTables;
TableName rootTableName;
boolean isRecFromAifCTTbleDeleted = false;
AifChangeTrackingQuery query;
AifChangeTrackingDataSource dataSource;
int dataSourceIndex, dataSourceCount;
TableName tableName;
AifSqlChangeTrackingEnabledTables enabledTablesDelete;
AIFSQLCDCENABLEDTABLES ctEnabledTables;
boolean isEntityEnabled, rootCTDisabled;
boolean trigerCTDisabled = false;
AifChangeTrackingScope scope;
int64 ctEnabled = -1;
if (isCTEnabledForTable && qtyOfTablesCTEnabled > 0)
{
dictDataEntity = new DictDataEntity(tableName2Id(entityloc.TargetEntity));
rootTableName = DMFEntityBase::getRootTableName(dictDataEntity);
select count(RecId) from aifSqlChangeTrackingEnabledTables
where aifSqlChangeTrackingEnabledTables.TableName == rootTableName;
isRecFromAifCTTbleDeleted = qtyOfTablesCTEnabled != aifSqlChangeTrackingEnabledTables.RecId
&& qtyOfTablesCTEnabled > aifSqlChangeTrackingEnabledTables.RecId;
}
if (!isRecFromAifCTTbleDeleted && isCTEnabledForTable && qtyOfTablesCTEnabled > 0)
{
scope = entityloc.TargetEntity +”@DMF:DMFExport”;
select count(RecId) from ctEnabledTables where ctEnabledTables.Scope == scope;
isEntityEnabled = ctEnabledTables.RecId > 1 ? true:false;
if(dictDataEntity && AifSqlChangeTrackingEnabledTables::find(rootTableName, entityloc.TargetEntity).RecId)
{
query = AifChangeTrackingQuery::constructFromDataEntity(dictDataEntity,isEntityEnabled);
ttsbegin;
dataSourceCount = query.dataSourceCount();
for (dataSourceIndex = 1; dataSourceIndex <= dataSourceCount; dataSourceIndex++)
{
dataSource = query.dataSourceNo(dataSourceIndex);
tableName = tableId2name(dataSource.table());
new AifChangeTrackingPermission().assert();
ctEnabled = DMFEntityBase_YourModel_ClassEH::checkCT(tableName);
if (ctEnabled == 1 && qtyOfTablesCTEnabled == 1)
{
DMFEntityBase::DisableCT(tableName);
}
AifChangeTrackingConfiguration::disableChangeTracking(scope);
if (tableName == rootTableName)
{
rootCTDisabled = true;
}
CodeAccessPermission::revertAssert();
if (rootCTDisabled)
{
//If root table CT is disabled, delete record without disbaling CT for it
enabledTablesDelete = AifSqlChangeTrackingEnabledTables::find(tableName, entityloc.TargetEntity, true);
if(enabledTablesDelete && enabledTablesDelete.validateDelete())
{
enabledTablesDelete.delete();
}
}
}
ttscommit;
info(strFmt(“@DMF:DMFCTDisableSuccess”, entityloc.EntityName));
}
}
}
/// <summary>
///
/// </summary>
/// <param name=”args”></param>
/// <remarks>
/// Fixing of MS wrong logic if DataSource for DataEntity is View (sourced by Query)
/// </remarks >
[PostHandlerFor(classStr(DMFEntityBase), staticMethodStr(DMFEntityBase, getRootTableName))]
public static void DMFEntityBase_Post_getRootTableName(XppPrePostArgs _args)
{
#AOT
#Properties
#TreeNodeSysNodeType
SysDictTable dictTable;
TreeNode viewsNode;
TreeNode viewNode;
str viewQueryName;
Query viewQuery;
QueryBuildDataSource viewRootDataSource;
AifSqlChangeTrackingEnabledTables aifSqlChangeTrackingEnabledTables;
TableName rootTableName = _args.getReturnValue();
Query query;
QueryBuildDataSource qbds;
DictDataEntity dictDataEntity = _args.getArg(‘dictDataEntity’);
DMFEntityName entityName;
int dataSourceIndex;
int dataSourceCount;
boolean isCTEnabledForTable = false;
if (dictDataEntity != null)
{
entityName = dictDataEntity.name();
isCTEnabledForTable = AifSqlChangeTrackingEnabledTables::find(rootTableName, entityName).RecId != 0;
if (!isCTEnabledForTable)
{
// Code access security to ensure API is only called on the server
new AifChangeTrackingPermission().demand();
query = dictDataEntity.query();
qbds = query.dataSourceNo(1);
dictTable = new SysDictTable(qbds.table());
if (dictTable.isView())
{
// Get view node
viewsNode = TreeNode::findNode(#ViewsPath);
viewNode = viewsNode.AOTfindChild(dictTable.name());
// Get view query
if (viewNode != null)
{
viewQueryName = viewNode.AOTgetProperty(#PropertyQuery);
if (viewQueryName != “”)
{
viewQuery = new Query(viewQueryName);
if (viewQuery != null)
{
viewRootDataSource = viewQuery.dataSourceNo(1);
rootTableName = tableId2Name(viewRootDataSource.table());
}
}
isCTEnabledForTable = AifSqlChangeTrackingEnabledTables::find(rootTableName, entityName).RecId != 0;
if (isCTEnabledForTable)
{
_args.setReturnValue(rootTableName);
}
}
}
}
}
}
/// <summary>
/// Disbles change tracking for the provided table.
/// </summary>
/// <param name = “tableName”>Table name</param>
/// <returns>true or false</returns>
/// <remarks>
/// Fixing of MS wrong logic if DataSource for DataEntity is View (sourced by Query)
/// </remarks >
public static int64 checkCT(TableName tableName)
{
InteropPermission permission;
str errorString;
CLRObject CLRexception;
System.Data.SqlClient.SqlDataReader sqlDataReader;
str sql,connectionStr, physicalTableName;
System.Data.SqlClient.SqlConnection sqlConn;
System.Data.SqlClient.SqlCommand sqlComm;
SysDictTable dictTable;
int64 rv = -1;
AifSqlCtChangeTracking act = new AifSqlCtChangeTracking();
permission = new InteropPermission(InteropKind::ClrInterop);
permission.assert();
try
{
dictTable = new SysDictTable(tableName2id(tableName));
if (dictTable && !dictTable.isView())
{
physicalTableName = AifChangeTracking::getHierarchyRootTablePhysicalName(dictTable.id());
if(act.isChangeTrackingEnabledForTable(tableName2id(physicalTableName)))
{
connectionStr = SqlSystem::managedConnectionString();
sql = strfmt(‘SELECT COUNT(*) FROM sys.change_tracking_tables WHERE object_id = OBJECT_ID(‘%1′)’, physicalTableName);
sqlConn = new System.Data.SqlClient.SqlConnection(connectionStr);
sqlConn.Open();
sqlComm = sqlConn.CreateCommand();
sqlComm.set_CommandText(sql);
sqlDataReader = sqlComm.ExecuteReader();
if (sqlDataReader && sqlDataReader.Read())
{
rv = sqlDataReader.GetInt32(0);
}
sqlComm.Dispose();
sqlDataReader.Dispose();
sqlConn.Close();
}
}
return rv;
}
catch (Exception::Error)
{
error(strFmt(“@DMF:DMFDisableCTError”, physicalTableName));
return false;
}
catch( Exception::CLRError )
{
//Cannot inititalize connection to the source system
error(“@SYS134042″);
//BP Deviation documented
CLRexception = CLRInterop::getLastException();
while( CLRexception )
{
//BP Deviation documented
errorString += CLRInterop::getAnyTypeForObject( CLRexception.get_Message() );
CLRexception = CLRexception.get_InnerException();
}
throw error(errorString);
}
CodeAccessPermission::revertAssert();
}
/// <summary>
///
/// </summary>
/// <param name=”args”></param>
/// <remarks>
/// Fixing of MS wrong logic if DataSource for DataEntity is View (sourced by Query)
/// </remarks >
[PreHandlerFor(classStr(DMFEntityBase), staticMethodStr(DMFEntityBase, DisableCT))]
public static void DMFEntityBase_Pre_DisableCT(XppPrePostArgs _args)
{
const str ctIsNotEnabledForThisTable = ‘NotExistTable’;
const str tableNameArgName = ‘tableName’;
TableName tableName = _args.getArg(‘tableName’);
int64 rv = -1;
rv = DMFEntityBase_YourModel_ClassEH::checkCT(tableName);
if (rv <= 0)
{
_args.setArg(tableNameArgName, ctIsNotEnabledForThisTable);
}
}
/// <remarks>
///Fixing of MS wrong logic if DataSource for DataEntity is View (sourced by Query)
/// </remarks >
public static boolean isCTEnabledForAllTablesInView(DMFEntity _entity)
{
TableName rootTableName;
AifChangeTrackingQuery query;
AifChangeTrackingDataSource dataSource;
int dataSourceIndex, dataSourceCount;
TableName tableName;
AIFSQLCDCENABLEDTABLES ctEnabledTables;
boolean isEntityEnabled;
boolean ret = true;
AifChangeTrackingScope scope;
int64 ctEnabled;
Query q;
QueryBuildDataSource qbds;
SysDictTable dictTable;
DictDataEntity dictDataEntity;
dictDataEntity = new DictDataEntity(tableName2Id(_entity.TargetEntity));
q = dictDataEntity.query();
qbds = q.dataSourceNo(1);
dictTable = new SysDictTable(qbds.table());
if (dictTable && !dictTable.isView())
{
return checkFailed(strFmt(“For DataEntity %1, for DataSource, not a View is used”, dictDataEntity.name()));
}
if (_entity == null)
{
return checkFailed(strFmt(“DataEntity %1 is not exists”, dictDataEntity.name()));
}
rootTableName = DMFEntityBase::getRootTableName(dictDataEntity);
scope = _entity.TargetEntity +”@DMF:DMFExport”;
select count(RecId) from ctEnabledTables where ctEnabledTables.Scope == scope;
isEntityEnabled = ctEnabledTables.RecId >= 1 ? true:false;
if(isEntityEnabled && AifSqlChangeTrackingEnabledTables::find(rootTableName, _entity.TargetEntity).RecId)
{
query = AifChangeTrackingQuery::constructFromDataEntity(dictDataEntity,isEntityEnabled);
dataSourceCount = query.dataSourceCount();
for (dataSourceIndex = 1; dataSourceIndex <= dataSourceCount; dataSourceIndex++)
{
dataSource = query.dataSourceNo(dataSourceIndex);
tableName = tableId2name(dataSource.table());
new AifChangeTrackingPermission().assert();
ctEnabled = -1;
ctEnabled = DMFEntityBase_YourModel_ClassEH::checkCT(tableName);
if (ctEnabled <= 0)
{
info(strFmt(“For table %1, in View %2, in DataEnitity %3 change tracking is not enabled (in sys.change_tracking_tables table)”,
tableName,
dictTable.name(),
dictDataEntity.name()));
ret = false;
}
CodeAccessPermission::revertAssert();
}
}
return ret;
}
}
Comments
Post a Comment