Using new DotNet datatype to call stored procedure from Nav

gstroud
gstroud Member Posts: 3
edited 2013-12-18 in NAV Three Tier
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

Comments

  • gstroud
    gstroud Member Posts: 3
    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
    sharad Member Posts: 112
    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);
    Sharad Gupta
    Navision Technical Consultant & .Net Developer
  • nhsejth
    nhsejth Member, Microsoft Employee Posts: 34
    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.
    _________________
    Niels-Henrik Sejthen
    Senior Software Developer
    Microsoft Dynamics NAV

    The information in this post is provided "AS IS" with no warranties, and confers no rights. This post does not represent the thoughts, intentions, plans or strategies of my employer. It is solely my opinion.
  • jversusj
    jversusj Member Posts: 489
    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);
    kind of fell into this...

Categories