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.
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
- Create one Model with name D365SnippetsModel.
- Create one project with name SQLStatementDirectProject.
- Create one Runnable Class (Job) with name ExecuteSqlProcess.
- 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.
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)
));
}
}
}
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
