SysDa API
"Da" is short for Data access. It is a set of new APIs exposing the object graph that the compiler otherwise produces from select statements.
Problem:
On the extensibility journey in D365 FO, X++ select statements are not extensible. If developer needs to add a new range, a join, a field in the field list – it is not possible.
Pros:
1. It has the same performance characteristics as select statements
2. It is extensible for futher enhancement
3. It is available in from PU22
"Da" is short for Data access. It is a set of new APIs exposing the object graph that the compiler otherwise produces from select statements.
Problem:
On the extensibility journey in D365 FO, X++ select statements are not extensible. If developer needs to add a new range, a join, a field in the field list – it is not possible.
Pros:
1. It has the same performance characteristics as select statements
2. It is extensible for futher enhancement
3. It is available in from PU22
List:
- Select: SysDaQueryObject, SysDaSearchObject, and SysDaSearchStatement
- Update: SysDaUpdateObject and SysDaUpdateStatement
- Insert: SysDaInsertObject and SysDaInsertStatement
- Delete: SysDaQueryObject, SysDaDeleteObject, and SysDaDeleteStatement
CRUD Operation:
Select:
//Selection - On SYSDA API
VendTable vendTab;
var qe = new SysDaQueryObject(vendTab);
qe.projection()
.add(fieldStr(VendTable, AccountNum))
.add(fieldStr(VendTable, VendGroup));
qe.WhereClause(new SysDaEqualsExpression(
new SysDaFieldExpression(vendTab, fieldStr(VendTable, VendGroup)),
new SysDaValueExpression(10)));
//below statement to get less than or equal to field values
//qe.WhereClause(new SysDaLessThanOrEqualsExpression(
// new SysDaFieldExpression(vendTab, fieldStr(VendTable, VendGroup)),
// new SysDaValueExpression(10)));
//Default order by field is ASC
qe.OrderByClause().addDescending(fieldStr(VendTable, AccountNum)); //for desc
//Type 1
var so = new SysDaSearchObject(qe);
var ss = new SysDaSearchStatement();
while (ss.nextRecord(so))
{
info(vendTab.AccountNum);
}
//Type 2
var fo = new SysDaFindObject(qe);
new SysDaFindStatement().execute(fo);
//Type 3 Using join
PurchLine purchLine;
InventDim Invdim;
var qepl = new SysDaQueryObject(purchLine);
var qedim = new SysDaQueryObject(Invdim);
qepl.joinClause(SysDaJoinKind::InnerJoin,qedim);
//Relation manual
qedim.WhereClause(new SysDaEqualsExpression(
new SysDaFieldExpression(purchLine, fieldStr(PurchLine, InventDimId)),
new SysDaFieldExpression(Invdim, fieldStr(InventDim, InventDimId))));
qepl.WhereClause(new SysDaEqualsExpression(
new SysDaFieldExpression(purchLine, fieldStr(PurchLine, VendAccount)),
new SysDaValueExpression("000013")));
var so1 = new SysDaSearchObject(qepl);
var ss1 = new SysDaSearchStatement();
while (ss1.nextRecord(so1))
{
info(purchLine.ItemId + purchLine.InventDimId);
}
Update:
// Update - ON SysDa API
PurchTable purchTable;
var updateObj = new SysDaUpdateObject(purchTable);
updateObj.settingClause()
.add(fieldStr(PurchTable, PurchName), new SysDaValueExpression( "SAN"));
updateObj.whereClause(new SysDaEqualsExpression(
new SysDaFieldExpression(purchTable, fieldStr(PurchTable, OrderAccount)),
new SysDaValueExpression("000013")));
//Updating the rows.
ttsbegin;
new SysDaUpdateStatement().execute(updateObj);
ttscommit;
Insert:
// Insert - ON SysDa API
sanLanguageTable santab;
var Io = new SysDaInsertObject(santab);
Io.fields()
.add(fieldStr(sanLanguageTable, Name))
.add(fieldStr(sanLanguageTable, Description));
VendGroup source;
var qe = new SysDaQueryObject(source);
var s1 = qe.projection()
.Add(fieldStr(VendGroup, Name))
.Add(fieldStr(VendGroup, VendGroup));
Io.query(qe);
var istate = new SysDaInsertStatement();
ttsbegin;
istate.executeQuery(Io);
ttscommit;
Delete:
// Delete - ON SysDa API
sanLanguageTable santab;
var qe = new SysDaQueryObject(santab);
var s = qe.projection()
.add(fieldStr(sanLanguageTable, Name));
var ds = new SysDaDeleteStatement();
var delobj = new SysDaDeleteObject(qe);
ttsbegin;
ds.executeQuery(delobj);
ttscommit;
//To get no of rrows after deletion
info("Number of rows after deletion: " + any2Str(t.RowCount()));
Important:
You can use the toString() method on SysDaQueryObject, SysDaUpdateObject, SysDaInsertObject, and SysDaQueryObject objects to view the statement that you're building.
Related Objects: (SysDa API)
Base Enum:
SysDaAggregateFieldType
SysDaFirstOnlyHint
SysDaJoinKind
Class:
SysDaAggregateProjectionField
SysDaAndExpression
SysDaAvgOfField
SysDaBinaryExpression
SysDaCountOfField
SysDaCrossCompany
SysDaCrossCompanyAll
SysDaCrossCompanyContainer
SysDaDataAccessStatement
SysDaDeleteObject
SysDaDeleteStatement
SysDaDivideExpression
SysDaEqualsExpression
SysDaFieldExpression
SysDaFindStatement
SysDaGreaterThanExpression
SysDaGreaterThanOrEqualsExpression
SysDaGroupBys
SysDaInsertObject
SysDaInsertStatement
SysDaIntDivExpression
SysDaLessThanExpression
SysDaLessThanOrEqualsExpression
SysDaLikeExpression
SysDaMaxOfField
SysDaMinOfField
SysDaMinusExpression
SysDaModExpression
SysDaMultiplyExpression
SysDaNotEqualsExpression
SysDaOrderBys
SysDaOrExpression
SysDaPlusExpression
SysDaProjectionField
SysDaQueryExpression
SysDaQueryObject
SysDaSearchObject
SysDaSearchStatement
SysDaSelection
SysDaSettingsList
SysDaSumOfField
SysDaUpdateObject
SysDaUpdateStatement
SysDaValueExpression
SysDaValueField