.NET Interop: Calling Stored Procedures on SQL Server (Example 1) - Dynamics CRM: Blogs

Blog bot · 19.07.2011, 22:00

Hi there .. i've been insanely busy getting ReVision on the market, and on top of that .. enjoying a holiday with the kids .. . So now it's time to start blogging again :-).

A long time ago, I posted an article about using ADO automation to call SQL Server Stored Procedures, or better .. do whatever you like on SQL Server from within C/AL. You can read the article here.

I had many reads on this article, many comments and many referrals to it .. so I thought it would be interesting to "translate" it to .Net Interop :-).

I am aware of rashed's post and David's post about this. But when I was translating my post into a .Net Interop (i.e. Calling stored procedures), I came across some weird issues in R2 (like David).. which need an upgrade to at least build 32198 of NAV 2009 R2 (I have a list of updates and referrals to platform updates here). If you don't, you won't be able to use command-parameters (quite useful, if not "indispensable" when using stored procedures). In a separate blogpost, I will I explain what the issue was, when using an earlier build.

OK, everything is set:

R2 installed
R2 classic and RTC (Service and client) upgraded to 32198.
SQL Server installed
SQL Management Studio present (which I'll use to create my Stored Procedure, and check some stuff..)

To make it easy on me (I like to do that .. (remember my lazy programming post

)), I will take the same examples from my ADO-post, and I will do exactly the same with .Net Interop. In this blogpost: the first example .. and a few more will follow in following posts.. .

But first some basics...

Connecting to other databases using .Net Interop is actually quite the same as using ADO. Only, you don't use Automation, but you use the .Net framework (so don't forget this code will only run in an RTC environment (which currently is either the RTC client or using a NAV 2009 Web Service!

). That means, that it's still the same 3 steps you have to take:

Make a connection to the database, using a "connectionstring"
Do your thing in the database (read, write, loop data, call SP, grant permissions,...) ... basically executing your SQL statements.
Close your connection

But before you even consider to make a connection, you first have to figure out what type of database you're connecting to .. so you know which .Net namespace you should be using. It's all quite good explained in this MSDN article: http://msdn.microsoft.com/en-us/library/a6cd7c08.aspx (in fact, there is a lot to find about this topic if you just "bing" for it..).

As I can imagine that in most cases, we'll be connecting to another SQL Server Database, we'll just go for the SQL connection in this blogpost. But still, if not, and you're wondering what to do when connecting to (for example) a MySQL with .Net Interop, here's a tip: http://lmgtfy.com/?q=Connect+to+MySQL+using+.Net

.

To open a connection

I declared this variable:

Name

DataType

Subtype

SQLConnection

DotNet

'System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Data.SqlClient.SqlConnection

Working with .Net Interop, means you can (and usually have to) use constructors to create an instance of a class. So no CREATE, but a constructor. In this case, it makes sense to provide the details when "constructing the instance of your connection", so when calling the constructor, we can give some parameters with it. To open a connection, the statements you should use are something like this:

SQLConnection := SQLConnection.SqlConnection(GetConnectionString('(local)', 'NAV2009R2_iFactoDefault', '',''));
SQLConnection.Open;

The "GetConnectionstring" is a small function I wrote to compose my connectionstring for a SQL Server connection:

GetConnectionString(ServerName : Text[250];DatabaseName : Text[250];DBUserID : Text[250];DBPassword : Text[250]) : Text[1024]
IF DBUserID <> '' THEN BEGIN
ConnectionString := 'Driver={SQL Server};'
+ 'Server='+ServerName+';'
+ 'Database='+DatabaseName+';'
+ 'Uid='+DBUserID+';'
+ 'Pwd='+DBPassword+';';
END
ELSE BEGIN
ConnectionString:='Data Source='+ServerName+';'
+ 'Initial Catalog='+DatabaseName+';'
+ 'Integrated Security=SSPI;';
END;
EXIT(ConnectionString);

I tested this with my own local environment, and it seemed to log on OK.

To close a connection:

Seems obvious, but actually, it isn't. You can either Dispose or Close. So you have:

SQLConnection.Close;

Or

SQLConnection.Dispose;

Or

SQLConnection.Close;
SQLConnection.Dispose;

Now, "Dispose" should automatically call "Close" as well (following the MSDN documentation), but opinions vary when you start reading about these statements .. . Just remember this: "Close" closes the connection and puts it in a connection pool (which is going to make it possible to open the connection much faster next time). At that time, there are still possible unmanaged resources underneath the covers. "Dispose" is going to call "close" (or at least it should) and after that, release all unmanaged resources .. . So in my opinion, what you write, depends on the scenario... .

This first example

As I said .. same examples as in my ADO post, so in thisone, the intention is to create company-specific view(s) when a new company is created: Suppose that I have a linked table to some data in my Item Ledger Entry, with a distinct value on Item Location .. something I wanted to solve by creating a view in SQL Server. When a new company is created, this view has to be foreseen as well .. so I want a stored procedure that I can call when I create a company.. .

The Stored Procedure

The stored procedure looks like this (bla bla "AS IS" bla bla "No Warranties"

- e.g. this SP is not going to take into account the CompanyName-in-SQL-naming-convention-..):

CREATE PROCEDURE [dbo].[SP_CreateView_ItemLocation]
@CompanyName VARCHAR(30)
AS
BEGIN
SET NOCOUNT ON;

DECLARE @SQLString NVARCHAR(MAX)

--If the view already exists, drop the view

SET @SQLString = 'IF OBJECT_ID (''['+@CompanyName+'$SP_ItemLocation]'', ''view'') IS NOT NUL
DROP VIEW ['+@CompanyName+'$SP_ItemLocation]'

EXEC sp_executesql @SQLString

--assemble the SQLString (including the companyname)

SET @SQLString =
'CREATE VIEW [dbo].['+@CompanyName+'$SP_ItemLocation]
AS
SELECT DISTINCT ''ILE'' AS TableName, [Item No_] AS ItemNo, [Location Code] AS LocationCode,
['+@CompanyName+'$Location].Name AS LocationName
FROM dbo.['+@CompanyName+'$Item Ledger Entry], ['+@CompanyName+'$Location]
where dbo.['+@CompanyName+'$Item Ledger Entry].[Location Code] = ['+@CompanyName+'$Location].[Code]'

print @SQLString
--this "print " is optionally - it is useful when you're debugging your SP
--in SSMS, because it shows the SQLString that you have been building.

exec sp_executesql @SQLString

END

As you see, it should create a company-specific-view. Now, to call this SP, I need some code in C/AL, which uses these variables:

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

SQLParameter

DotNet

'System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Data.SqlClient.SqlParameter

And here is the code:

... <Open your connection here (see above)>

SQLCommand := SQLCommand.SqlCommand(); //Constructor

SQLCommand.CommandText := 'SP_CreateView_ItemLocation';
SQLCommand.Connection := SQLConnection;
SQLCommand.CommandType := GetEnum(SQLCommand.CommandType,'StoredProcedure');
SQLCommand.CommandTimeout := 0;

SQLParameter := SQLParameter.SqlParameter;
SQLParameter.ParameterName := '@CompanyName'; //Name of the parameter
SQLParameter.SqlDbType := GetEnum(SQLParameter.SqlDbType,'VarChar');
SQLParameter.Direction := GetEnum(SQLParameter.Direction,'Input');
SQLParameter.Size := 30; //30 characters
SQLParameter.Value:=COMPANYNAME;

SQLCommand.Parameters.Add(SQLParameter);

SQLCommand.ExecuteNonQuery;

... <Close your connection here (see above)>

First of all, we're heavily using enums here. Though, I don't want to use the int values of enums if I don't have to .. and when using ADO automation .. we had to use it for Command Types and such. Now, we're using the enums the same way as I described in this blogpost.

Basically, it's just, opening the connection, compose a command, which is going to be an SP. Because it's an SP, we can define some parameters. Add these parameters to the command, and execute away! Here are some referrals which you might need for the enums, to know the possibilities, and the values you should use as enum:

To conclude this example: it would make sense to add this code (or at least call the function) in codeunit 2 .. So that when you create a company, it's going to provide you the necessary view(s) as well. One remark there .. add it after the COMMIT in that codeunit, as you probably want your company being created, no matter the view was created are not. But of course, it all depens on the business logic! :-)

So, hope it's useful for you. Next examples will follow shortly!