How to use .NET DataTable in Microsoft Dynamics 365 and Why
Have you ever found yourself in a situation whereby you were working with Dynamics 365 and you were mesmerized by the built-in SQL syntax that can be accessed right from the get-go, and yet, deep inside you were missing your favorite .NET collections, LINQ syntax, and other good stuff that simplifies your life? Yeah, me too. Don’t get me wrong, it is quite a rare occasion when available X++ options are not enough. Even in the worst-case scenario you might solve your problem by creating a complex “query-view” structure, or you can create a class hierarchy built around containers, lists or maps. But what if you need to have a flexible data structure, and yet be able to sort or filter it down. Sure, it is achievable with a container, but they are slow and limited by available RAM, plus you will spend a lot of time writing methods required to work with data. The same can be said about X++ lists and maps. And while being fast and easy to use, queries and views built around them might not be flexible enough, and usually are designed to cover some particular scenario.
If you’ve ever found yourself in this situation, you should probably reconsider your approach. But what if we could use DataTable and other related objects from .NET? We are not bound by the old complex X++ to CIL compilation process after all. In this article, I am going to show you how it can be achieved and what can be done.
First, you need to reference System.Data library in your class:
Second, instantiate your instances and initialize DataColumn objects of a specific type
After your table structure has been finshed, it can be populated with data. This is a tricky part. Usually, when talking about C# we would be able to use this kind of syntax to populate the data in DataTable.Rows:
However, in X++ it is not possible to access DataRow field by index, so we are going to use System.Object array instance and DataRow.Add() method (make sure that the data types inside your array correlate with the types of columns inside DataTable):
Now, your DataTable instance is ready and can be used to do some cool stuff. And although there is no way to use LINQ syntax alongside X++ (or I just haven’t discovered it yet), you can easily access the data, order it iterate through or even add new columns if needed.
To access the data inside DataTable, you will need two new instances of System.Collections.IEnumerator:
If needed, you can use pretty much any method of DataTable or DataView, like:
To sum up, I will reitorate, I am not suggesting using DataTables instead of AX tables or collections. I just want to show you that, in Dynamics 365 for Operations, usage of .NET structures, right from X++ code is far less complex compared to AX2012. I do believe that some tasks, that require iterative approach, might be accomplished with a use of external C# code, or libraries.