Search icon CANCEL
Arrow left icon
Explore Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Conferences
Free Learning
Arrow right icon
Arrow up icon
GO TO TOP
Dynamics 365 for Finance and Operations Development Cookbook

You're reading from   Dynamics 365 for Finance and Operations Development Cookbook Recipes to explore forms, look-ups and different integrations like Power BI and MS Office for your business solutions

Arrow left icon
Product type Paperback
Published in Aug 2017
Publisher Packt
ISBN-13 9781786468864
Length 480 pages
Edition 4th Edition
Languages
Arrow right icon
Authors (2):
Arrow left icon
Deepak Agarwal Deepak Agarwal
Author Profile Icon Deepak Agarwal
Deepak Agarwal
Abhimanyu Singh Abhimanyu Singh
Author Profile Icon Abhimanyu Singh
Abhimanyu Singh
Arrow right icon
View More author details
Toc

Table of Contents (11) Chapters Close

Preface 1. Processing Data FREE CHAPTER 2. Working with Forms 3. Working with Data in Forms 4. Building Lookups 5. Processing Business Tasks 6. Data Management 7. Integration with Microsoft Office 8. Integration with Power BI 9. Integration with Services 10. Improving Development Efficiency and Performance

Executing a direct SQL statement

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.

How to do it...

Carry out the following steps in order to complete this recipe:

  1. In the Dynamics 365 Project, create a new class named VendTableSql using 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))); 
         } 
        } 
       } 
  1. Run the class to retrieve a list of vendors directly from the database, as shown in the following screenshot:

How it works...

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.

There's more...

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.

You have been reading a chapter from
Dynamics 365 for Finance and Operations Development Cookbook - Fourth Edition
Published in: Aug 2017
Publisher: Packt
ISBN-13: 9781786468864
Register for a free Packt account to unlock a world of extra content!
A free Packt account unlocks extra newsletters, articles, discounted offers, and much more. Start advancing your knowledge today.
Unlock this book and the full library FREE for 7 days
Get unlimited access to 7000+ expert-authored eBooks and videos courses covering every tech area you can think of
Renews at $19.99/month. Cancel anytime