Converting from ADO to .net

madsmorre

Hi
I'm now using NAV 2017.
Earlier I have made some functionality that is calling an external SQL database to retrieve some Sales Orders (and create them in NAV) that are placed here. I have used ADO to do this, but now I want to convert this to .net as this makes it possible to put it in a job queue. I'm quite new to .ent though so i'm still learning.

The Case:
I have 2 stored Procedures:
1. Called without parameters. Retrieve the orderheaders
2. Called with 1 parameter (Order No.). Retrieves the related Order lines

The (old) code is below.

Question 1: What is the syntax to do this by DotNet operations
Question 2: Is it possible to call 1 SP instead - a stored proc that hold information about both header and lines (returns sets of records)

Thanks for any ideas.
Best Regards
Mads Morre

Old Code:
ADORecordset := Conn.Execute('exec usp_GetOrders_Import');

IF NOT ADORecordset.EOF THEN REPEAT
CLEAR(Date3);
SortOrder := 1;
rOrderID := ADORecordset.Fields.Item('OrderId').Value;
//MESSAGE('OrderId %1',ADORecordset.Fields.Item('OrderId').Value);
SalesHeader_CPO.INIT;
SalesHeader_CPO.Origin := SalesHeader_CPO.Origin::B2B;
SalesHeader_CPO."Web No." := ADORecordset.Fields.Item('OrderId').Value;
SalesHeader_CPO."Sell-to Customer No." := ADORecordset.Fields.Item('CustomerId').Value;
SalesHeader_CPO."Ship-to Code" := ADORecordset.Fields.Item('ShipToId').Value;
SalesHeader_CPO."Your Reference" := ADORecordset.Fields.Item('Reference').Value;
SalesHeader_CPO."External Document No." := ADORecordset.Fields.Item('Requisition').Value;
SalesHeader_CPO.Comment := ADORecordset.Fields.Item('Comment').Value;
Date3 := ADORecordset.Fields.Item('DeliveryDate').Value;
IF (Date3 <> '') AND (Date3 <> 'NULL') THEN BEGIN
EVALUATE(Day,COPYSTR(Date3,7,2));
EVALUATE(Month,COPYSTR(Date3,5,2));
EVALUATE(Year,COPYSTR(Date3,1,4));
SalesHeader_CPO."Requested Delivery Date" := DMY2DATE(Day,Month,Year);
END;

SalesHeader_CPO."Created By" := USERID;
SalesHeader_CPO."Created Date" := WORKDATE;
SalesHeader_CPO.INSERT;

ADORecordset2 := Conn.Execute('exec usp_GetOrderLines_Import @order='+FORMAT(rOrderID));
IF NOT ADORecordset2.EOF THEN REPEAT
CLEAR(Date2);
SalesLine_CPO.INIT;
SalesLine_CPO.Origin := SalesLine_CPO.Origin::B2B;
SalesLine_CPO."Web No." := ADORecordset2.Fields.Item('OrderId').Value;
SalesLine_CPO."Line No." := SortOrder;
SalesLine_CPO."Item No." := ADORecordset2.Fields.Item('ItemId').Value;
SalesLine_CPO."Variant Code" := ADORecordset2.Fields.Item('VariantId').Value;
SalesLine_CPO.Quantity := ADORecordset2.Fields.Item('Qty').Value;
SalesLine_CPO."Unit Price" := ADORecordset2.Fields.Item('UnitPrice').Value;
Date2 := ADORecordset2.Fields.Item('DeliveryDate').Value;
IF (Date3 <> Date2) AND (Date2 <> '') AND (Date2 <> 'NULL') THEN BEGIN
EVALUATE(Day,COPYSTR(Date2,7,2));
EVALUATE(Month,COPYSTR(Date2,5,2));
EVALUATE(Year,COPYSTR(Date2,1,4));
SalesLine_CPO."Requested Delivery Date" := DMY2DATE(Day,Month,Year);
END ELSE
SalesLine_CPO."Requested Delivery Date" := SalesHeader_CPO."Requested Delivery Date";

SalesLine_CPO."Web Line No." := ADORecordset2.Fields.Item('OrderLineId').Value;
SalesLine_CPO.INSERT;
SortOrder += 1;
ADORecordset2.MoveNext;
UNTIL ADORecordset2.EOF;

ADORecordset3 := Conn.Execute('usp_Order_Set_TransferCompleted @order ='+FORMAT(SalesHeader_CPO."Web No."));
//This SP marks the order as retrieved
COMMIT;

ADORecordset.MoveNext;

UNTIL ADORecordset.EOF;

Slawek_Guzek

Find the Upgrade Toolkit for upgrading version 2009 to 2013, 2013R2 or 2015 (download installation DVD for one of these versions - the Upgrade Toolkit will be there). You will find in it a codeunit, SQL Management, which includes a complete set of .NET variables required to run queries against the SQL Server, and you will also find in there NAV code using the .NET connection and other .NET vars to run T-SQL statements used to convert the old style dimensions to dimension sets


By the way - since in NAV 2017 there is virtually no restriction on the length of a text variable you could move the TSQL code from your stored procedures into NAV

madsmorre

Hi Slawek
Thanks for replying. I will take a look at the SQL Mangement and see if there are some inspiration.

I have also thought of making the SP from within NAV, but that is not really my issue.

I have managed to write the data I need to send to the external database in dotnet code. I can also call the SP in External SQL in DotNet code.

I'm looking for the correct (or a smart way) to retrieve the answer from the SP.

In this case I have to both retrieve some header information and some line information. In ADO I called 2 SP, but I'm wondering if it would be possible to Retrieve all information in one SP and the loop through it in NAV to put it to SalesHeader TEMP and SalesLine TEMP.
I can't seem to find the syntax in DotNet code to do this.

BR
Mads Morre

Slawek_Guzek

I'm suggesting to move the T-SQL code to NAV as it is imho better to maintain the solution within NAV. All the code is clearly visible, no problem with company name prefixing table names, or field names etc as it all can be generated dynamically inside NAV. But having all the TSQL queries generated in NAV is my personal preference only, you do what suits you best.

The mentioned SQL Managemet codeunit from Upgrade Toolkit includes .NET vars and code retrieving data from tables created in another TSQL query, and pulling the data into NAV tables, so you will definitely find your almost ready to use soultion in there - although upgrade code does not use stored procedures, it builds and executes a massive TSQL query and later retrieves results from tables generated in that query.

madsmorre

Thank you Slawek
I will investigate this SQL Management Codeunit.

I think that i will look into handling the SP from NAV - this also makes it easier when I have to follow code etc.