How to Execute Direct SQL Statement Using X++ Code in D365



In Microsoft Dynamics 365 Finance and Operation, we are very familiar with X++ SQL Statements, these statements are flexible to fit in to any custom business processes. You can create a query to retrieve data by using the query classes. By using X++ code, you can create a query and then save that query to the Application Object Tree (AOT).

If you are a beginner or new to dynamics 365 Finance & Operations, you can use Microsoft'f Free Virtual Machine. Learn How to Set Up a Free Virtual Machine for Dynamics 365 Development

But in some cases X++ SQL Statements are not effective or not possible at all. In this cases a developer must write the SQL statements to fulfil the requirement, normally these cases are little complex and can deal only with direct SQL statements. One example for such case is data upgrade during an application version upgrade.

How to Execute Direct SQL Statement Using X++ Code in D365

In this article I will show how to write and execute the SQL statement using X++ code or how to execute a direct SQL Statement, I write this article for beginners or learners who are very interested to learn the basic concepts of D365 finance and operations. Here I write very simple example for easy understanding. You can elaborate the example as per your requirement.If you are a learner or beginner, you can use free virtual machine for learning purposes.

Basic Steps

  1. Create one Model with name D365SnippetsModel.
  2. Create one project with name SQLStatementDirectProject.
  3. Create one Runnable Class (Job) with name ExecuteSqlProcess.
  4. After creating a runnable class, copy and paste the below code, 

For Best practice i have created one Table EmployeeSalaryDetailsTable which stores employees basic details(EmpId,EmpName,basicSalary,TelephoneAllowance). Then i have added two records to EmployeeSalaryDetailsTable.You can also use already existing Table for this practice for example: VendTable , CustTable etc.

how-to-execute-direct-sql-statement-using-xpp-in-d365

First part of the code is for declaring the variables and objects. Where DictTable and DictField objects are used to handle the tables and its fields. A new SqlSystem Object is also created , which 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.Second part is the execution part ,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.

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. The Info method writes the resut of the screen.After creating a basic project you can copy and paste the code to you visual studio.

class ExecuteSqlProcess
{
 
   public static void main(Args _args)
   {
       UserConnection userConnection;
       Statement statement;
       str sqlStatement;
       SqlSystem sqlSystem;
       SqlStatementExecutePermission sqlPermission;
       ResultSet resultSet;
       DictTable EmployeeSalaryDetailsTbl;
       DictField EmpId;
       DictField EmpName;
       DictField BasicSalary;
       DictField TelephoneAllowance;
       EmployeeSalaryDetailsTbl= new DictTable(tableNum(EmployeeSalaryDetailsTbl));
       EmpId = new DictField(tableNum(EmployeeSalaryDetailsTbl),fieldNum(EmployeeSalaryDetailsTbl,EmpId));
       EmpName = new DictField( tableNum(EmployeeSalaryDetailsTbl),fieldNum(EmployeeSalaryDetailsTbl,EmpName));        
       BasicSalary = new DictField(tableNum(EmployeeSalaryDetailsTbl),fieldNum(EmployeeSalaryDetailsTbl,BasicSalary));
       TelephoneAllowance = new DictField(tableNum(EmployeeSalaryDetailsTbl),fieldNum(EmployeeSalaryDetailsTbl,TelephoneAllowance));
 
       sqlSystem = new SqlSystem();
       sqlStatement = 'SELECT %1, %2, %3, %4 FROM %5';
       sqlStatement = strFmt(sqlStatement,EmpId.name(DbBackend::Sql),EmpName.name(DbBackend::Sql),BasicSalary.name(DbBackend::Sql),
           TelephoneAllowance.name(DbBackend::Sql),
           EmployeeSalaryDetailsTbl.name(DbBackend::Sql),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(
          "EmpId : %1 , Emp Name : %2 , Basic Salary : %3 , Telephone Allowance : %4",
          resultSet.getString(1),
          resultSet.getString(2),
          resultSet.getString(3),
          resultSet.getString(4)
               
               ));
       } 
 
   }
 
}   
    
 
After succesfull build and run of the project, you can see the output , which will display all the records which was previously stored in EmployeeSalaryDetailsTable .

How to Execute a Direct SQL Statement Using X++ Code in D365


Conclusion

In this article i explained the concept of how to execute a direct sql statement using X++ code in D365. You can change this codes as per your requirements.For beginers this article will be very useful. Use microsoft's free virtual machine which is already discussed in this blog.

Happy coding with D365Snippets

Some Usefull Links


Tags

Executing a direct SQL statement
Executing direct SQL Statements
Executing SQL directly from X++
How to execute Sql directly form Dynamics AX X++
Run SQL directly from X++ - AX/D365
Executing direct SQL statements
Executing a direct SQL statement 
Executing SQL directly from X++
executing sql directly from x++ run sql query
X++ data selection and manipulation overview
How to execute sql statement in x++
How to execute sql statement in d365 fo
How to execute sql statement in d365 finance & operations

0 Comments