SysDa API in D365 FO

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

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