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:
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, LocationNameFROM 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(); //ConstructorSQLCommand.CommandText := 'SP_LoadInventory';SQLCommand.Connection := SQLConnection;SQLCommand.CommandType := GetEnum(SQLCommand.CommandType,'StoredProcedure');SQLCommand.CommandTimeout := 0;SQLParameter := SQLParameter.SqlParameter;SQLParameter.ParameterName := '@ItemNo'; //Name of the parameterSQLParameter.SqlDbType := GetEnum(SQLParameter.SqlDbType,'VarChar');SQLParameter.Direction := GetEnum(SQLParameter.Direction,'Input');SQLParameter.Size := 20; //20 charactersSQLParameter.Value:=ItemNoVariable;SQLCommand.Parameters.Add(SQLParameter);SQLReader := SQLCommand.ExecuteReader;WHILE SQLReader.Read DO BEGINGlobalInventoryBuffer.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!
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
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