The standard Dynamics 365 for Finance and Operations application provides an alternate way of building direct SQL statements by using a set of SQLBuilder classes. By using these classes, we can create SQL statements as objects, as opposed to text. Next, we will demonstrate how to use a set of SQLBuilder classes. We will create the same SQL statement as we did before.
First, in a Dynamics 365 project, create another class named VendTableSqlBuilder using the following code snippet:
class VendTableSqlBuilder
{
/// <summary>
/// Runs the class with the specified arguments.
/// </summary>
/// <param name = "_args">The specified arguments.</param>
public static void main(Args _args)
{
UserConnection userConnection;
Statement statement;
str sqlStatement;
SqlStatementExecutePermission sqlPermission;
ResultSet resultSet;
SQLBuilderSelectExpression selectExpr;
SQLBuilderTableEntry vendTable;
SQLBuilderTableEntry dirPartyTable;
SQLBuilderFieldEntry accountNum;
SQLBuilderFieldEntry dataAreaId;
SQLBuilderFieldEntry blocked;
SQLBuilderFieldEntry name;
selectExpr = SQLBuilderSelectExpression::construct();
selectExpr.parmUseJoin(true);
vendTable = selectExpr.addTableId(
tablenum(VendTable));
dirPartyTable = vendTable.addJoinTableId(
tablenum(DirPartyTable));
accountNum = vendTable.addFieldId(
fieldnum(VendTable,AccountNum));
name = dirPartyTable.addFieldId(
fieldnum(DirPartyTable,Name));
dataAreaId = vendTable.addFieldId(
fieldnum(VendTable,DataAreaId));
blocked = vendTable.addFieldId(
fieldnum(VendTable,Blocked));
vendTable.addRange(dataAreaId, curext());
vendTable.addRange(blocked, CustVendorBlocked::No);
selectExpr.addSelectFieldEntry(
SQLBuilderSelectFieldEntry::newExpression(
accountNum,
'AccountNum'));
selectExpr.addSelectFieldEntry(
SQLBuilderSelectFieldEntry::newExpression(
name, 'Name'));
sqlStatement = selectExpr.getExpression(null);
userConnection = new UserConnection();
statement = userConnection.createStatement();
sqlPermission = new SqlStatementExecutePermission(
sqlStatement);
sqlPermission.assert();
resultSet = statement.executeQuery(sqlStatement);
CodeAccessPermission::revertAssert();
while (resultSet.next())
{
info(strfmt(
"%1 - %2",
resultSet.getString(1),
resultSet.getString(2)));
}
}
}
In the preceding method, we first create a new selectExpr object, which is based on the SQLBuilderSelectExpression class. It represents the object of the SQL statement.
Next, we add the VendTable table to it by calling its member method addTableId(). This method returns a reference to the vendTable object of the type SQLBuilderTableEntry, which corresponds to a table node in a SQL query. We also add DirPartyTable as a joined table.
Then, we create a number of field objects of the SQLBuilderFieldEntry type to be used later and two ranges to show only this company account and only the active vendor accounts.
We use addSelectFieldEntry() to add two fields to be selected. Here, we use the previously created field objects.
The SQL statement is generated once the getExpression() method is called, and the rest of the code is the same as in the previous example.
Running the class will give us results, which are exactly similar to the ones we got earlier.
