Business Central OnPremise – Microsoft AL connected to SQL Server by .NET

Business Central OnPremise – Microsoft AL connect to SQL Server by .NET

SCOPE

In an Al project for Business Central Onpremise, ability to use direct calls to SQL Server using .NET; for those who have not yet switched to the Cloud (transition) it can be useful for connecting to interfaces etc.

STEPS

• Declare .NET Assembly in AL Project
• Connect to SQL Server using .NET
• Read & Load Data from SQL (read SQL Server Object and load data to recordset) – DataReader()
• Exec a SQL Server Object (SQL statements, stored procedures, functions etc.) – ExecuteNonQuery()

DECLARE .NET Assembly in VsCode-AL

The necessary .net assemblies must be declared in the AL project, once declared they can be used in the project.

//.DOTNET DECLARATIONS

dotnet

{

//MSCORLIB – SYSTEMDATETIME

assembly(mscorlib)

{

type(System.DateTime; DateTime) { }   //DATETIME

type(System.Environment; env) { }

type(System.Reflection.Assembly; assembly) { }

type(System.IO.Path; path) { }

type(System.Enum; Enum) { }

type(System.Convert; Convert) { }

type(System.Text.StringBuilder; StringBuilder) { }

type(System.Array; Array) { }

type(System.Collections.IEnumerator; IEnumerator) { }

}

// SQL Connection – System.Data

assembly(System.Data)

{

Version = ‘4.0.0.0’;

Culture = ‘neutral’;

PublicKeyToken = ‘b77a5c561934e089’;

type(System.Data.SqlClient.SqlConnection; SqlConnection) { }

type(System.Data.SqlClient.SqlCommand; SqlCommand) { }

type(System.Data.SqlClient.SqlDataReader; SqlDataReader) { }

type(System.Data.DataTable; DataTable) { }

type(System.Data.DataRow; DataRow) { }

type(System.Data.DataColumn; DataColumn) { }

type(System.Data.DataColumnCollection; DataColumnCollection) { }

type(System.Data.DataRowCollection; DataRowCollection) { }

type(“System.Data.SqlClient.SqlParameter”; “SqlParameter”) { }

type(“System.Data.SqlClient.SqlParameterCollection”; “SqlParameterCollection”) { }

type(“System.Data.SqlClient.SqlConnectionStringBuilder”; “SqlConnectionStringBuilder”) { }

type(“System.Data.SqlDbType”; “SqlDbType”) { }

}

}

VARIABLES DECLARATIONS for .NET Objects in VsCode-AL

Then you need to declare variables and point them to .NET objects declared in the project.

// *** VARIABLES ***

var

// SQL .NET

SQLCommand: dotnet SQLCommand;

SQLParams: dotnet SqlParameter;

SQLparamcyclecode: DotNet SqlParameter;

SQLDBType: DotNet SqlDbType;

SQLDataReader: DotNet SqlDataReader;

HasSQLConnSetup: Boolean;

SQLConnection: dotnet SQLConnection;

InputTxt: label ‘Input’;

OutputTxt: label ‘Output’;

DataSourceTok: label ‘Data Source=%1;Initial Catalog=%2;User Id=%3;Password=%4;’;

SQLConnSetup: Record “AltERSQL Connection Setup”;

CONNECT to SQL SERVER by .NET and define PARAMETERS

It is also convenient to declare a reusable SQL connection and a management of the parameters passable to the stored procedures to be invoked.

// ***  SQL CONNECT ***

procedure SQLConnect()

var

ConnStr: Text[1024];

begin

GetSQLConnSetup();

SQLConnSetup.TestField(Provider);

SQLConnSetup.TestField(“Server Name”);

SQLConnSetup.TestField(“Database Name”);

SQLConnSetup.TestField(“User ID”);

SQLConnSetup.TestField(Password);

ConnStr := StrSubstNo(DataSourceTok,

SQLConnSetup.”Server Name”,

SQLConnSetup.”Database Name”, SQLConnSetup.”User ID”,

SQLConnSetup.Password);

SQLConnection := SQLConnection.SQLConnection(ConnStr);

SQLConnection.Open();

end;

// ** GET CONNECTION SETUP **

procedure GetSQLConnSetup()

begin

if not HasSQLConnSetup then

SQLConnSetup.Get();

HasSQLConnSetup := true;

end;

// ** CREATE SQL PARAMETER **

procedure CreateSQLParams(var ParamsSQL: dotnet SqlParameter; ParamName: Text[50];

ParamType: Text[50];

ParamDirection: Text[50];

ParamSize: Integer;

ParamValue: Text[500])

begin

ParamsSQL := ParamsSQL.SqlParameter();

ParamsSQL.ParameterName(ParamName);

ParamsSQL.SqlDbType(getEnum(ParamsSQL.SqlDbType, ParamType));

ParamsSQL.Direction(getEnum(ParamsSQL.Direction, ParamDirection));

//Direction

if (ParamSize > 0) then

ParamsSQL.Size(ParamSize);

if (ParamDirection = InputTxt) or (ParamDirection = OutputTxt) then

ParamsSQL.Value(ParamValue);

end;

// ** GET ENUM **

procedure getEnum(SystemEnum: dotnet Enum; SystemEnumValue: Text[30]): Integer

var

SystemConvert: dotnet Convert;

begin

exit(SystemConvert.ToInt32(SystemEnum.Parse(SystemEnum.GetType(), SystemEnumValue)));

end;

SQL DATA READER (CURSOR READ-ONLY)

To read the data it is best to use the DataReader object which allows you to use the HASROWS and READ properties to scroll the data of the active connection; there are other methods but this is the fastest and READONLY by design.

// ** SQL DATA READER DEMO **

procedure SQLDataReaderDemo(parTmpl: code[20])

var

Integer: Integer;

SQLReader: DotNet SqlDataReader;

ItemNo: code[20];

IdItem: Integer;

begin

if IsNull(SQLConnection) then

SQLConnect();

Clear(SQLCommand);

SQLCommand := SQLConnection.CreateCommand();

SQLCommand.CommandText := ‘select * From Item’;

// ** EXEC READER **

SQLReader := SQLCommand.ExecuteReader;

IF SQLReader.HasRows then BEGIN

WHILE SQLReader.Read() DO BEGIN

ItemNo := SQLReader.GetString(0);

IdItem := SQLReader.GetInt32(1);

END;

REPEAT

ItemNo := SQLReader.GetString(0);

IdItem := SQLReader.GetInt32(1);

UNTIL SQLReader.NextResult()

END;

end;

CALLING A STORED PROCEDURE EXAMPLE

Example of calling a SQL stored procedure – SQLCommand.ExecuteNonQuery();

// *** FUNCTION UpdateSalesOrderDashboard ***

procedure UpdateSalesOrderDashboard(parTmpl: Code[20]; parBatch: Code[20]; parSalesRefNo: Text[250]; parCompanyRefNo: Text[250]; parSalesOrderNo: Text[250]; parSalesSellCustNo: Text[250]; parSalesBillCustNo: Text[250]; parSalesperson: Text[250]; parSubsalesperson: Text[250]; parOrderDate: Text[250]; parOrderType: Text[250]; parShipmentDate: Text[250]; parBrand: Text[250]; parOnlyReserved: Boolean; parCompany: code[50]; parUserID: code[50]; parHideDialog: Boolean)

var

Query2Exec: Text[1024];

WindowD: Dialog;

TxtProcess: label ‘Processing’;

OnlyReserved: Integer;

begin

Query2Exec := ”;

if parOnlyReserved then

OnlyReserved := 1

else

OnlyReserved := 0;

if IsNull(SQLConnection) then

SQLConnect();

Query2Exec := ‘sp_LoadSalesOrdersDashboard’; //Stored Procedure

Clear(SQLCommand);

SQLCommand := SQLConnection.CreateCommand();

SQLCommand.CommandText(Query2Exec);

SQLCommand.CommandType := SQLCommand.CommandType::StoredProcedure;

SQLCommand.CommandTimeout := 180;

// ASSIGN PARAMETER

Clear(SQLparamcyclecode);

SQLparamcyclecode := SQLparamcyclecode.SqlParameter();

SQLparamcyclecode.ParameterName := ‘@Tmpl’;

SQLparamcyclecode.DbType := SQLparamcyclecode.DbType::String;

SQLparamcyclecode.Size := 20;

SQLparamcyclecode.Value := parTmpl;

SQLparamcyclecode.Direction := SQLparamcyclecode.Direction.Input;

SQLCommand.Parameters.Add(SQLparamcyclecode);

if not parHideDialog then

WindowD.Open(TxtProcess);

SQLCommand.ExecuteNonQuery();

Message(‘Executed’);

if not parHideDialog then

WindowD.Close();

SQLConnection.Close();

Clear(SQLCommand);

Clear(SQLConnection);

end;

}