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.