Dynamics 365 for Finance and Operations allows developers to build X++ SQL statements that are flexible enough to fit into any custom business process. However, in some cases, the usage of X++ SQL is either not effective or not possible at all. One such case is when we run data upgrade tasks during an application version upgrade. A standard application contains a set of data upgrade tasks to be completed during the version upgrade. If the application is highly customized, then most likely, standard tasks have to be modified in order to reflect data dictionary customization's, or a new set of tasks have to be created to make sure data is handled correctly during the upgrade.
Normally, at this stage, SQL statements are so complex that they can only be created using database-specific SQL and executed directly in the database. Additionally, running direct SQL statements dramatically increases data upgrade performance because most of the code is executed on the database server where all the data resides. This is very important while working with large volumes of data.
This recipe will demonstrate how to execute SQL statements directly. We will connect to the current Dynamics 365 for Finance and Operations database directly using an additional connection and retrieve a list of vendor accounts.
Carry out the following steps in order to complete this recipe:
- In the Dynamics 365 Project, create a new class named
VendTableSqlusing the following code snippet:
class VendTableSql
{
/// <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;
SqlSystem sqlSystem;
SqlStatementExecutePermission sqlPermission;
ResultSet resultSet;
DictTable tblVendTable;
DictTable tblDirPartyTable;
DictField fldParty;
DictField fldAccountNum;
DictField fldDataAreaId;
DictField fldBlocked;
DictField fldRecId;
DictField fldName;
tblVendTable = new DictTable(tableNum(VendTable));
tblDirPartyTable = new DictTable(tableNum(DirPartyTable));
fldParty = new DictField(
tableNum(VendTable),
fieldNum(VendTable,Party));
fldAccountNum = new DictField(
tableNum(VendTable),
fieldNum(VendTable,AccountNum));
fldDataAreaId = new DictField(
tableNum(VendTable),
fieldNum(VendTable,DataAreaId));
fldBlocked = new DictField(
tableNum(VendTable),
fieldNum(VendTable,Blocked));
fldRecId = new DictField(
tableNum(DirPartyTable),
fieldNum(DirPartyTable,RecId));
fldName = new DictField(
tableNum(DirPartyTable),
fieldNum(DirPartyTable,Name));
sqlSystem = new SqlSystem();
sqlStatement = 'SELECT %1, %2 FROM %3 ' +
'JOIN %4 ON %3.%5 = %4.%6 ' +
'WHERE %7 = %9 AND %8 = %10';
sqlStatement = strFmt(
sqlStatement,
fldAccountNum.name(DbBackend::Sql),
fldName.name(DbBackend::Sql),
tblVendTable.name(DbBackend::Sql),
tblDirPartyTable.name(DbBackend::Sql),
fldParty.name(DbBackend::Sql),
fldRecId.name(DbBackend::Sql),
fldDataAreaId.name(DbBackend::Sql),
fldBlocked.name(DbBackend::Sql),
sqlSystem.sqlLiteral(curext(), true),
sqlSystem.sqlLiteral(CustVendorBlocked::No, true));
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)));
}
}
} - Run the class to retrieve a list of vendors directly from the database, as shown in the following screenshot:
We start the code by creating the DictTable and DictField objects to handle the vendor table and its fields, which are used later in the query. The DirPartyTable is used to get additional vendor information.
A new SqlSystem object is also created. It is used to convert D365 types to SQL types.
Next, we set up a SQL statement with a number of placeholders for the table or field names and field values to be inserted later.
The main query creation takes place next, when the query placeholders are replaced with the right values. Here, we use the previously created DictTable and DictField type objects by calling their name() methods with the DbBackend::Sql enumeration as an argument. This ensures that we pass the name in the exact manner it is used in the database-some of the SQL field names are not necessary, which is the same as field names within the application.
We also use the sqlLiteral() method of the previously created sqlSystem object to properly format SQL values in order to ensure that they do not have any unsafe characters.
The value of the sqlStatement variable that holds the prepared SQL query depending on your environment is as follows:
SELECT ACCOUNTNUM, NAME FROM VENDTABLE
JOIN DIRPARTYTABLE ON VENDTABLE.PARTY = DIRPARTYTABLE.RECID
WHERE DATAAREAID = 'usmf' AND BLOCKED = 0 Once the SQL statement is ready, we initialize a direct connection to the database and run the statement. The results are returned in the resultSet object, and we get them by using the while statement and calling the next() method until the end.
Note that we created an sqlPermission object of the type SqlStatementExecutePermission here and called its assert() method before executing the statement. This is required in order to comply with Dynamics 365 for Operation's trustworthy computing requirements.
Another thing that needs to be mentioned is that when building direct SQL queries, special attention has to be paid to license, configuration, and security keys. Some tables or fields might be disabled in the application and may contain no data in the database.
The code in this recipe can be also used to connect to external ODBC databases. We only need to replace the UserConnection class with the OdbcConnection class and use text names instead of the DictTable and DictField objects.
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.