Using new DotNet datatype to call stored procedure from Nav

gstroud

Has anyone done any work on call stored procedures from codeunits using the new DotNet datatype?

I'm having a problem with the SqlCommand objects CommandType property accepting "StoredProcedure". The property is an Enumeration type so in order to set it to StoredProcedure you have to use the value of 1.

Debugging confirms this I set it then step over it and it doesn't take.


Thanks

Greg

gstroud

I was able to solve it. There were a couple of things that through me off. One the CommandType didn't have a constructor. Secondly the enum types are incorrectly documented.

Here is how you do it (Example):


SQLCmd := SQLCmd.SqlCommand('NameOfStoredProcedure',SQLConnection); //Creates the SqlCommand object
CmdType := SQLCmd.CommandType; // Assign the CommandType to a local variable CommandType
SQLCmd.CommandType := CmdType.Parse(CmdType.GetType(),'StoredProcedure'); // Parse out the Enumerated type of StoredProcedure

sharad

I've done it using 'Microsoft ActiveX Data Objects 2.8 Library'.Command AUTOMATION

Code Below:

IF ISCLEAR(NADOConnection) THEN CREATE(NADOConnection);

NADOConnection.ConnectionString:='Driver={SQL Server};Server='+ServerName+';Database='+DatabaseName+';Uid='+USERID+';Pwd=xyz;';
NADOConnection.Open;
//Open command
IF ISCLEAR(NADOCommand) THEN
CREATE(NADOCommand);

lvarActiveConnection := NADOConnection;
NADOCommand.ActiveConnection := lvarActiveConnection;

NADOCommand.CommandText := 'SP_StockLedger';
NADOCommand.CommandType := 4;
NADOCommand.CommandTimeout := 0;

//lADOParameter:=NADOCommand.CreateParameter('@CompanyName', 200, 1, 30,COMPANYNAME);
//NADOCommand.Parameters.Append(lADOParameter);

NADOCommand.Execute;
IF ISCLEAR(NADORecordSet) THEN
CREATE(NADORecordSet);

NADORecordSet.ActiveConnection := lvarActiveConnection;
NADORecordSet.Open(NADOCommand);

WHILE NOT NADORecordSet.EOF DO BEGIN
// insert in temporary table
TempTable.INIT;


TempTable.INSERT;
NADORecordSet.MoveNext;
END;
//Close Connection
NADOConnection.Close;
CLEAR(NADOConnection);

nhsejth

FYI, moving forward, it's not a recommend approach to use ActiveX or COM technology. Whereever possible you should implement integration components using the DotNet data type.

jversusj

Hi all,
I'm converting an old ADO call in a codeunit to use the new dotnet data type.

I can successfully call and execute my stored procedure, but I am having trouble trying to find the right syntax to get the value of my output parameter. I found Waldo's blog on the subject (using dotnet interop), but he never got around to example 3 (using an output parameter).

any tips? i have done some web searches and found examples in other languages. When i attempt to use a similar approach in NAV, it either does not work or will not compile.

I'm sure it is something relatively easy, i'm just not experienced enough with dotnet.

My legacy line of code that read the result of the output parameter:
sResult := FORMAT(ADOCommand.Parameters.Item('@Result').Value);

EDIT:
Embarrassed to say I got it to work. Must have had a typo upon an earlier attempt at the same thing:
sResult := FORMAT(SQLCommand.Parameters.Item('@Result').Value);