.NET Interop: Calling Stored Procedures on SQL Server (Example 2)

What am I trying to do here .. well, I'm working on three examples on how to call stored procedures, and where it could be useful for. The first blog article was about the first example, and also explained some basics. Therefore I strongly recommend to read that one, before you continue on this article. These will be the examples that I will be talking about:

• Example 1: Creating company-specific views when a new company is created
• Example 2: Compose a SELECT string, execute it and show the results in NAV
• Example 3: Use an output parameter in the Stored Procedure as a result

This being the blogpost of example 2, we'll talk about composing a SQL SELECT string. In fact, what I'll try to do is:

• I'll provide an "Item No." as the input parameter of my Stored Procedure,
• I'll compose a UNION-type of SQL String that is going to get the data from the views from my previous examples, DISTINCTed on a few fields..
• I'll call that SP in NAV, put the results into a TempTable, and show it to the user.

As you can see .. I tried to do a lot of SQL stuff, which is not really easy to do in NAV (unions, distincts and views.. ). And I won't lie, also this example is based on the examples I did for the ADO-post, which you can find here. I'm just trying to put that in decent .Net Interop usage.. :-).

Enough bla-bla, let's boom-boom!

My SP looks something like this (I added some comments to make things clearer):

ALTER PROCEDURE [dbo].[SP_LoadInventory]
@ItemNo AS VARCHAR(20)
AS  
BEGIN
SET NOCOUNT ON;
DECLARE @CompanyName VARCHAR(30)
DECLARE @OrigCompanyName VARCHAR(30)
DECLARE @SQLString NVARCHAR(MAX)
DECLARE @Counter INT
 
DECLARE curCompany CURSOR FOR SELECT [Name] as CompanyName from dbo.Company
OPEN curCompany

SET @Counter = 0
SET @SQLString=''
 
FETCH NEXT FROM curCompany INTO @CompanyName
-- Begin looping all companies in the database
 
WHILE @@FETCH_STATUS = 0
BEGIN
-- Converting the wonderful NAV-supported-but-best-not-used-in-SQL characters
SET @OrigCompanyName = @CompanyName
SET @CompanyName = REPLACE(@CompanyName,'.','_');
SET @CompanyName = REPLACE(@CompanyName,'"','_');
SET @CompanyName = REPLACE(@CompanyName,'\','_');
SET @CompanyName = REPLACE(@CompanyName,'/','_');
SET @CompanyName = REPLACE(@CompanyName,'''','_');
 
-- Only put the UNION in between of two SELECT statements
IF @Counter > 0
BEGIN
SET @SQLString = @SQLString + 'UNION'
END
SET @SQLString = @SQLString +
'
SELECT DISTINCT ''' + @OrigCompanyName + ''' AS CompanyName,ItemNo, LocationCode, LocationName
FROM dbo.[' + @CompanyName + '$SP_ItemLocation] a
WHERE ItemNo = ''' + @ItemNo + '''
'
FETCH NEXT FROM curCompany INTO @CompanyName
 
SET @Counter = @Counter + 1
 
END;
 
print @SQLString -- to make it easier to debug
 
EXEC sp_executesql @SQLString
 
CLOSE curCompany
DEALLOCATE curCompany
 
END

As you can see, it's going to UNION the results I'm getting from the SELECTs on the Company-specific views I created in my previous post. This for all companies, no matter how many companies I have in my database.. . Quite generic, no configuration required. Thank you SQL Server! .

As this SP is going to give us a table, we're going to try to present that table in a page, with this code

... <Open your connection here>

SQLCommand := SQLCommand.SqlCommand(); //Constructor
SQLCommand.CommandText := 'SP_LoadInventory';
SQLCommand.Connection := SQLConnection;
SQLCommand.CommandType := GetEnum(SQLCommand.CommandType,'StoredProcedure');
SQLCommand.CommandTimeout := 0;

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

SQLCommand.Parameters.Add(SQLParameter);

SQLReader := SQLCommand.ExecuteReader;

WHILE SQLReader.Read DO BEGIN
GlobalInventoryBuffer.INIT;
GlobalInventoryBuffer."Item No." := SQLReader.Item('ItemNo');
GlobalInventoryBuffer."Company Name" := SQLReader.Item('CompanyName');
GlobalInventoryBuffer."Location Code" := SQLReader.Item('LocationCode');
GlobalInventoryBuffer."Location Name" := SQLReader.Item('LocationName');
GlobalInventoryBuffer.INSERT;
END;

PAGE.RUN(PAGE::"Global Inventory Overview", GlobalInventoryBuffer);

... <Close your connection here>

This is quite a difference with the ADO automation version, isn't it? Well, it makes sense to use the datareader (as that's all we want to do here). It gives us a clean an nice way to read our resultset. The SQL Reader is a DotNet variable of: 'System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Data.SqlClient.SqlDataReader

That's all for example 2. I don't state this example is a "useful" example, but it shows you can combine the "power of SQL Server " (views, any SELECT statement, ...) with NAV quite easily. And now you can do it by using the .Net framework!