Dynamics 365 Finance and Supply Chain – Extensibility of SQL Operations
Extensions, Extensions, Extensions! Today we will be talking with X++ developers engaged in database requests for Dynamics 365. If you are trying to extend methods using traditional select statements in X++, expect some challenges. Now that all D365 customizations are done in extensions instead of overlaying, there are some new ways to tackle SQL operations. Microsoft has added a relatively new capability in X++, the In operator. They have also added a new SysDa API to build query objects, which became available in platform update 22. Besides these examples, the simple act of refactoring a bigger method that builds a query, uses expressions and keywords, and splitting it into separate methods, makes it much easier to extend.
Take this simple select for purchase lines with a status of either Backorder or Received:
If this query is in the middle of a larger method, it would be harder to create an extension just to modify the where clause of the select statement to change which statuses you want to include or exclude. For those of you that might be used to T-SQL, you can now utilize the In operator in X++ as well. You can create a separate container with just the statuses you want to use:
Even better, you could use a separate method for just the container, and this would be much more extensible. It would be easy for a developer to just extend that method and add another purchase status to include in the container that is used for the select statement.
The next level in SQL operation extensibility is to utilize the SysDa API. The “Da” in SysDa stands for Data access. With this, you can create query objects that not only are extensible, but can also provide better performance with less overhead than a QueryRun object.
Above we see our example query to select PurchLine records using the SysDa API. In more complex situations from the out-of-the-box code in D365, this can be refactored to make the WHERE clause its own method. Again, a developer can then extend just the method for the WHERE to modify it as necessary for the business requirement.
Microsoft has been refactoring many out-of-the-box functions to utilize SysDa as well, and regularly publishes which SQL operations have been made extensible with each updated version. This example only showed a simple Select query, but SysDa can also be used to Update, Insert, and Delete. The SysDa queries also support more clauses than just this simple where clause example, such as order by and the ability to utilize joins.
For more information, visit https://docs.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/dev-ref/sysda