Execute SQL Command using .NET Interop

image

OK, I’ll admit it, I’m obsessed with the .NET Interop features in NAV 2009 R2. My last two blog posts have been on this and now here’s another one.

Today I wanted to see if I could use the .NET Interop features in NAV 2009 R2 to execute a SQL Stored Procedure and extract the results. I know you can do this using COM, but I figured it would be fun to try this using .NET. The great thing about .NET is it’s so well documented. My starting point for this exercise was to search for executing a SQL Stored Procedure in C# which gave me this post http://support.microsoft.com/kb/320916

I then used that to figure out which .NET classes I needed to write my C/AL code.

In this sample I’m using a SQL Command rather than a stored procedure call, but that’s just so you can try it for yourself easily without needing to create a stored procedure. It’s easy enough to change it to a stored procedure and I left the code in there but commented out.

The worst thing about using this stuff is the number of variables you need to define. I put this down to the fact that the .NET interop does not support multi-dotting where I can write things like foo.bar.thing(). I’m hoping we’ll get multi-dotting in a future version.

So for this to work, you’ll need to create a whole bunch of variables. Rather than list them here, I’ve included a text export of my Codeunit as a download.

The example is really simple. I select the names of the companies in the current database and display the Row Count and then the first 5 company names.

Here’s my code:


//Use the session table to find the database and server name for the current connection
Session.SETRANGE("My Session",TRUE);
Session.FINDFIRST;

//Open a connection using windows authentication to the current session database and server
dnSQLConnection := dnSQLConnection.SqlConnection('server='+Session."Host Name"+';'+
                                                 'database='+Session."Database Name"+';'+
                                                 'Trusted_Connection=sspi');

//Create a DataAdapter
dnSQLDataAdapter := dnSQLDataAdapter.SqlDataAdapter();

{
// Example of using a stored procedure and not a select string

//Create a SQL Command on our connection
dnSQLCommand := dnSQLCommand.SqlCommand('myStoredProc',dnSQLConnection);
//Set the command type as StoredProcedure.
dnSQLCommand.CommandType := 1;
}

//Create a SQL Command on our connection
dnSQLCommand := dnSQLCommand.SqlCommand('select * from Company',dnSQLConnection);

//Assign the SQL command as the select command on my data adapter.
dnSQLDataAdapter.SelectCommand := dnSQLCommand;

//Create a dataset
dnDataSet := dnDataSet.DataSet;

//Fill the dataset by executing my command on the data adapter.
dnSQLDataAdapter.Fill(dnDataSet);

//Get the collection of tables
dnDataTableCollection := dnDataSet.Tables;

//Get the first table
dnDataTable := dnDataTableCollection.Item(0);

//Get the collection of rows
dnDataRowCollection := dnDataTable.Rows;

MESSAGE('There are %1 Rows', dnDataRowCollection.Count);

currentRow := 0;

REPEAT
  //Get the currentRow row
  dnDataRow := dnDataRowCollection.Item(currentRow);
  MESSAGE('%1',dnDataRow.Item(1));
  currentRow += 1;
UNTIL (currentRow = 5) OR (currentRow = dnDataRowCollection.Count);

//Tidy up
dnSQLDataAdapter.Dispose();
dnSQLConnection.Close();

In a New Zealand demo database this gives the following messages:

SNAGHTML851fe47

SNAGHTML8524d6f

SNAGHTML85295e4

You can download the Codeunit here.


Related
Recommended