3,765
Like
Share
ReportOn one of my recent projects I had to build a solution to update a NAV table with quantity on hand by location. Writing a processing report to maintain update and maintain the table would have taken less than an hour. But for this project performance of a NAV report would have not worked. The client has about 1000 locations and about 80,000 Items. Worst case scenario, you could end up with 80 million records in this table. The solution I provided uses ADO to connect to SQL server and issue a SQL statement on Item Ledger Entry Indexed view.
My solution was based on Waldo’s post
This worked fine on classic client, but when I tried to run this on Role Tailored Client (RTC), I was getting the following error.
The expression Variant cannot be type-converted to a String value.
The error was from the following line.
lADOCommand.ActiveConnection := lvarActiveConnection;
This basically means that you cannot use ‘Microsoft ActiveX Data Objects 2.8 Library’ in RTC. So what other option are available? You could build an external dll file that wraps the active x DLL file. But that creates many headaches with registering and maintaining the dll file on every workstation. The other solution that I’m going to talk about is that in NAV 2009 R2, Microsoft released a new data type called DotNet. With this data type, you can access the .Net framework and reference the classes in NAV. So the code for accessing NAV using DotNet datatype looks like this.
ServerName := 'VIRTUALXP-51168';
NavDb := 'Demo Database NAV 6R2';
ConnectionString := 'Data Source='+ServerName+';'
+ 'Initial Catalog='+NavDb+';'
+ 'Trusted_Connection=True;';
SQLConnection := SQLConnection.SqlConnection(ConnectionString);
SQLConnection.Open;
SQLCommand := SQLConnection.CreateCommand();
SQLCommand.CommandText := 'select * From Session';
SQLReader := SQLCommand.ExecuteReader;
WHILE SQLReader.Read() DO BEGIN
MESSAGE( 'Reading %1 , %2 ',SQLReader.GetInt32(0), SQLReader.GetString(1));
END;
SQLConnection.Close;
CLEAR(SQLReader);
CLEAR(SQLCommand);
CLEAR(SQLConnection);
In the example above I’m simply reading the session table and printing the “Connection ID” and “User ID”. In addition you can assign to SQLCommand.CommandText sql statement that are greater than 1024 characters.
Here are the DotNet data types for the above code.
Name DataType Subtype
SQLConnection DotNet ‘System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Data.SqlClient.SqlConnection
SQLCommand DotNet ‘System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Data.SqlClient.SqlCommand
SQLReader DotNet ‘System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Data.SqlClient.SqlDataReader
If you are on older version of NAV, you have to do executable upgrade to Dynamics NAV 2009 R2 to be able to use DotNet object types.
*This post is locked for comments