How to pass ado.net objects (e.g. DataReader) into AX custom class using .net business connector in C#

(0) Share

Report

Posted on by Preeti Sonambekar

1,245

Hi All,

I have my application written in C# where I am using .net business connector to access AX. I have an intermediate database which contains huge amount of data and what I want to do is, write t-sql queries in my c# code to get that huge data into dataareader and then pass this datareader as a parameter to AX custom class (a class written by me).

Inside the class I want to access this datareader to insert this data into AX tables.

Please suggest how do I achieve this? If possible please share some example code.

Thanks,

Preeti

*This post is locked for comments

I have the same question (0)

All responses (15)

Answers (0)

Steeve Gilbert 1,501 on at

Like (0)

Report

I don't know how to pass that DataReader but as an alternative you could query your huge database from Ax using ADO. Exemple :

COM curConnection ;

COM recordSet;

curConnection = new COM("ADODB.Connection");

curConnection.open(dsnString);

recordSet = ConnTemp.Execute("SELECT * from YourTable")

....

Was this reply helpful? Yes No
Preeti Sonambekar 1,245 on at

Like (0)

Report

Thanks a lot for your reply...But can you please send me the complete code for this as there are too many unknown variables.

Was this reply helpful? Yes No
Steeve Gilbert 1,501 on at

Like (0)

Report

Here's a complete sample. I've used Ax database but that could be any db.

COM adoConn;

COM recordSet;

COM fields;

COM field;

COMVariant fieldValue;

adoConn = new COM("ADODB.Connection");

//http://www.connectionstrings.com/

adoConn.open("Provider=SQLNCLI;Server=yourServerName;Database=yourDbName;Trusted_Connection=yes;");

recordSet = adoConn.execute("Select Name from VendTable");

while (!recordSet.eof())

{

fields = recordSet.fields();

field = fields.Item("Name");

fieldValue = field.value();

info(fieldValue.bStr());

recordSet.moveNext();

}

adoConn.close();

Was this reply helpful? Yes No
Preeti Sonambekar 1,245 on at

Like (0)

Report

great...this example works...thank you so much...this way I can access two databases also if they are on the same server like this.

recordSet = adoConn.execute("Select * into myDB..MyCustTable from customertable");

here customertable belongs to the database for which we have created the connection string.

What if the two databases are on two different servers? How do I select the data from one database on one server and insert into another database in some other server. Please suggest.

Was this reply helpful? Yes No
Steeve Gilbert 1,501 on at

Like (0)

Report

With SQL Server you can use "Linked Server" feature. Check in SSMS, folder "Server Objects". Add a remote server there. With that you'll be able to reference a table on another server like this : remoteServer.myDB.dbo.yourCustTable.

Was this reply helpful? Yes No
Preeti Sonambekar 1,245 on at

Like (0)

Report

Hi...I am having issues with inserting the data into recid column of the table. Basically I have my user table in AX which is created through AOT because of which it has recVersion and RecID columns ?(savedatapercompany set to No).

Now I have to load data from external database into this user table in AX. To take care of the recid column I created a temp table with IDENTITY column and insert the data from external table into this temp table whose IDENTITY column will get the sequence nos. based on the number of records loaded. Then insert this data from temp table into AX user table. This way IDENTITY column data can be used as recid in the AX user table. This is what I am doing for the same.

COM adoConn;

COM recordSet, recordSet1, recordSet2;

COM fields;

COM field;

COMVariant fieldValue;

;

adoConn = new COM("ADODB.Connection");

adoConn.open("Provider=SQLNCLI;Server=(local);Database=myDB;Trusted_Connection=yes;");

recordSet = adoConn.execute("CREATE TABLE #Temp(TaxGroupID VARCHAR(25), TaxRecID BIGINT IDENTITY(0,1) NOT NULL)");

recordSet1 = adoConn.execute("insert into #Temp(TaxGroupID) select top 10 taxgroupid from taxgroup");

recordSet2 = adoConn.execute("insert into DynamicsAX1..myUserTable(TaxGroupID,recversion, recid) select TaxGroupID, 1, TaxRecID from #Temp");

When I run it I get the following error:

"Method 'execute' in COM object of class 'ADODB.Connection' returned error code 0x80040E2F (<unknown>) which means: The INSERT statement conflicted with the CHECK constraint "CK__myUserTable__RECID__1080BE2D". The conflict occurred in database "DynamicsAx1", table "dbo.myUserTable", column 'RECID'."

Please suggest.

Was this reply helpful? Yes No
Steeve Gilbert 1,501 on at

Like (0)

Report

Make sure your Identity field doesn't start at 0. RecId cannot be 0, that's what the constraint is validating. If you run the script in SSMS does it work ok?

Was this reply helpful? Yes No
Preeti Sonambekar 1,245 on at

Like (0)

Report

You are Genius. My identity column started with 0 and so in SSMS also it didn't work.

Now when I changed it to start with 1, it worked. Thank you so much for all your help.

Was this reply helpful? Yes No
Preeti Sonambekar 1,245 on at

Like (0)

Report

Hi, I have 6 queries to retrieve data from 6 tables in intermediate database and then import them into 6 AX ERP tables. Total no. of records to be imported in 6 tables is around 1900000. When I run the job I get the following error:

Method 'execute' in COM object of class 'ADODB.Connection' returned error code 0x80040E31 (<unknown>) which means: Query timeout expired.

how do we increase connection timeout in AX class? The example code is below for one of the tables (out of 6).

COM adoConn;

COM recordSet, recordSet1, recordSet2;

COM fields;

COM field;

COMVariant fieldValue;

;

adoConn = new COM("ADODB.Connection");

adoConn.open("Provider=SQLNCLI;Server=(local);Database=myDB;Trusted_Connection=yes;");

recordSet = adoConn.execute("CREATE TABLE #Temp(TaxGroupID VARCHAR(25), TaxRecID BIGINT IDENTITY(0,1) NOT NULL)");

recordSet1 = adoConn.execute("insert into #Temp(TaxGroupID) select top 10 taxgroupid from taxgroup");

recordSet2 = adoConn.execute("insert into DynamicsAX1..myUserTable(TaxGroupID,recversion, recid) select TaxGroupID, 1, TaxRecID from #Temp");

One of the tables has 1238767 records out of 1900000 and mostly I get the error for this. When I run the job by commeting this piece of code which imports 1238767 data, then job executes successfully. But when I include this table, I get timeout expired. Once I get this exception, then even if I comment this part of code again, it keeps on throwing the same exception and I have to restart Sql Server Services.

Please suggest.

Please help.

Was this reply helpful? Yes No
Steeve Gilbert 1,501 on at

Like (0)

Report

Here's how to do it :

curConnection.ConnectionTimeout(3600); // In seconds

curConnection.CommandTimeout(3600);

See ADO API on MSND for everything you can do with the Connection and Command objects :

msdn.microsoft.com/.../ms681546%28v=VS.85%29.aspx

Was this reply helpful? Yes No

Community site session details

How to pass ado.net objects (e.g. DataReader) into AX custom class using .net business connector in C#

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Featured topics

Product updates

Community site session details

How to pass ado.net objects (e.g. DataReader) into AX custom class using .net business connector in C#

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Featured topics

Product updates