.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:

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! Wink.

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!

Comment List
  • I think you have :-).

    Read this post:

    dynamicsuser.net/.../net-interop-issue-with-return-parameter-overloading-solved.aspx

    I'm guessing you'll have to do a runtime upgrade.

  • SQLconnection := SQLconnection.SqlConnection(GetConnectionString('EC-SQL-01', 'Staging', '',''));

    SQLconnection.Open;

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

    SQLcommand.CommandText := 'InterCo';

    SQLcommand.Connection := SQLconnection;

    SQLcommand.CommandType := GetEnum(SQLcommand.CommandType,'StoredProcedure');

    SQLcommand.CommandTimeout := 0;

    SQLCommandBuilder.DeriveParameters(SQLcommand);

    SQLcommand.Parameters[1].Value := InterCoDate;

    SQLreader := SQLcommand.ExecuteReader;

    WHILE SQLreader.Read DO BEGIN

    //Post Journal Lines

    END;

    SQLconnection.Close;

    SQLconnection.Dispose;

    using the code above I get the error when I try to compile the call is ambiguous between the methods Parameters() and Parameters()

    when I try to assign a parameter value

    SQLcommand.Parameters[1].Value := InterCoDate;

    Any ideas what I have missed?

  • SQLconnection := SQLconnection.SqlConnection(GetConnectionString('EC-SQL-01', 'Staging', '',''));

    SQLconnection.Open;

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

    SQLcommand.CommandText := 'InterCo';

    SQLcommand.Connection := SQLconnection;

    SQLcommand.CommandType := GetEnum(SQLcommand.CommandType,'StoredProcedure');

    SQLcommand.CommandTimeout := 0;

    SQLCommandBuilder.DeriveParameters(SQLcommand);

    SQLcommand.Parameters[1].Value := InterCoDate;

    SQLreader := SQLcommand.ExecuteReader;

    WHILE SQLreader.Read DO BEGIN

    //Post Journal Lines

    END;

    SQLconnection.Close;

    SQLconnection.Dispose;

    using the code above I get the error when I try to compile the call is ambiguous between the methods Parameters() and Parameters()

    when I try to assign a parameter value

    SQLcommand.Parameters[1].Value := InterCoDate;

    Any ideas what I have missed?

  • You can only run .Net Interop code in the Roletailored client (as the classic client isn't based on the .Net framework).

    So you can't run a codeunit in the classic which contains .Net Interop.

    Try to create an action somewhere in your pages that calles this function in RTC..

    hope this helps :-)

  • Sorry if my question is very silly.. but in which NAV objects should i put all this code?

    When i'm trying to create SQL connection in Codeunit below error message pops up..

    ---------------------------

    Microsoft Dynamics NAV Classic

    ---------------------------

    The .NET interop type cannot be used in code for the Classic CSide runtime.

    ---------------------------

    OK  

    ---------------------------

Related
Recommended