Microsoft Dynamics 365 Finance and Operations brings with it a whole data management framework that allows us to integrate with a database in Azure SQL in a totally standard way.
This framework allows us to export ERP information to an external database for reporting (BYOD), so that we can generate our reports and dashboards in Power BI without having to attack the system database (AxDB), but… What happens if what I need is to read or perform other types of operations not allowed by this framework against an external DB?
In this article I will show you a helper that we have developed to carry out these operations in a simple way using the System.Data.SqlClient library.
The idea of this helper is that it is reusable to the maximum, allowing us to execute queries against any type of table regardless of its structure.
Entity Store Configuration
The first thing we need is to configure the connection string of the database, and for this I reuse the configuration that the standard gives us from the Data Management Workspace.
All we need to do is create a new Azure SQL DB type entity store and paste the connection string from our database. Once the connection is configured, we can move on to the code!
We are going to talk only about some important methods, the rest of the code is available in my GitHub repository for your use and enjoyment :).
Connect to Azure SQL via X ++
Of course, the first step will be to add the following using directive to our code in order to use the SQLClient library.
|
1 |
using System.Data.SqlClient; |
Next we will take into account the constructor, to which we will pass a record from the DMFSourceName table, this record is none other than the configuration of the entity store that we saw in the previous step, and if the connection string is valid (we use the validation method that is used from the Validate button itself), we continue with the execution.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
public static JATSqlClient construct(DMFSourceName _sourceName) { JATSqlClient sqlClient = new JATSqlClient(); DMFDataSource dataSource = DMFDataSource::find(_sourceName); int infologLine = infologLine(); if (DMFDataSource::validateEntityConnectionString(dataSource)) { infolog.clear(infologLine); sqlClient.dmfDataSource = dataSource; } else { throw Exception::Error; } return sqlClient; } |
Now we are going to talk about the four main methods of this helper, which are used to read, insert, update and delete records in the Azure SQL database.
The basis of these methods is the use of temporary tables within the ERP that will have the same schema as the target tables in the external database, so that, for example, in the insertion, what we will do is, from the ERP generate all the records within the temp table, ultimately passing them to the helper, which will loop through those records and generate them at the destination.
In the same way, on reading, we will retrieve the data stored in the Azure database from the helper, and then go through it and insert it into our temporary table. This temporary table will be the one that we return from our helper to the ERP business logic to facilitate the processing of the data in it.
In this way, and playing with Common and the SysDictTable and SysDictField classes, we enable our helper to work with any type of table, whatever its structure, and thus make it totally reusable and generic.
Here we can see the example of data insertion using the classes mentioned above.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
private str createInsertQuery(Common _common) { SysDictTable dictTable = new SysDictTable(_common.TableId); SysDictField dictField; FieldId fieldId = dictTable.fieldNext(0); str query = 'INSERT INTO %1 (%2) VALUES (%3)'; container conFields, conValues; while (fieldId) { dictField = dictTable.fieldObject(fieldId); if (dictField.isSql()) { conFields += dictField.name(); conValues += this.getSqlValue(_common.(fieldId), dictField.baseType()); } fieldId = dictTable.fieldNext(fieldId); } query = strFmt(query, tableId2Name(_common.TableId), con2Str(conFields, ', '), con2Str(conValues, ', ')); return query; } |
This method, as we can see, is responsible for generating and returning the SQL statement that will be executed later.
Conclusion
The objective of this post is not to show all the code used, but to explain its base, so that its operation is understood. As I indicated before, in my GitHub repository you can find both the JATSQLClient class and the JATSQLTest class, where you can see an example of use.
I hope you find it useful, and as always, any problem, suggestion or improvement, do not hesitate to contact me, I will be happy to discuss it 😊.