A couple of years ago, the idea was to have everything in one major ERP application. Nowadays, the trend shifts towards platforms and Business Central becomes a piece of software in a diverse environment. It will become a key skill for every consultant and developer to integrate other software into Business Central and vice versa. There are a couple of options how to integrate (flat file, webservices) and today I want to show you how to query a SQL server and store the result in XML format with only 4 lines of code.
Prerequisites
- On-premise only: Because the solution utilizes DotNet, it is not possible to deploy this solution into the cloud version of Business Central. You have to add the attribute
"target": "Internal"
to your app.json file. - You must tell VSCode where your DotNet assemblies are located. Erik Hougaard explains the necessary configuration in this blog post.
- You must have access to a SQL Server.
DotNet namespaces
Let’s get started with the definition of DotNet namespaces. The best is to have a separate file in the project that holds all DotNet definitions.
dotnet { assembly(System.Data) { Version = '4.0.0.0'; Culture = 'neutral'; PublicKeyToken = 'b77a5c561934e089'; type(System.Data.DataSet; SQLDataSet) { } type(System.Data.SqlClient.SqlDataAdapter; SQLDataAdapter) { } } }
Send query and receive response
Now, let’s have a look for the function that makes use of the DotNet objects and sends a query to a SQL server and returns the response.
local procedure GetSQLData(): Text var SQLDataSet: DotNet SQLDataSet; SQLDataAdapter: DotNet SQLDataAdapter; SQLConnection: Text; SQLQuery: Text; SQLResultAsXML: Text; begin //connection using authentication with username & password SQLConnection := 'Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;' // connection using windows authentication //SQLConnection := 'Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;' // sql query SQLQuery := 'SELECT * FROM MyTable'; SQLDataAdapter := SQLDataAdapter.SqlDataAdapter(SQLQuery, SQLConnection); SQLDataSet := SQLDataSet.DataSet(); SQLDataAdapter.Fill(SQLDataSet); SQLDataSet.WriteXml(SQLResultAsXML); exit(SQLResultAsXML); end;
The SQL connection string offers a variety of additional options for different kinds of environments. You can find more information about this topic here.
Why XML?
The benefit of data in XML format is that the Data Exchange framework of Business Central can be utilized. It can be used to create data mappings and to configure further data processing.