Saturday, May 15, 2010

Insert / Delete / Update records into an external SQL database from AX using x++

Inserting/Deleting/Updating records into an external SQL database from AX 2009 using x++ can be done using ADO Classes in AX. This can be told from the following code snippet.

In the below code snippet CCADOConnection is used to establish a connection with the external database and CCADOCommand is used to execute the command on the external database.

If we want to establish a connection using windows authentication then the connection string must be as follows:
conn.open(@"Provider=SQLOLEDB;Integrated Security=SSPI;Data Source=VS-08-22\SQLEXPRESS;Initial Catalog=TestDatabase;")

If we want to establish a connection using SQL Server Authentication then the connection string must be as follows:
conn.open(@"Provider=SQLOLEDB;Data Source=VS-08-22\SQLEXPRESS;Initial Catalog=TestDatabase;User Id=sa;Password=sa;")


The complete program is here below:


CCADOConnection conn;
CCADOCommand cmd;
CustTable custTable;
;

conn = new CCADOConnection();
cmd = new CCADOCommand();


//To insert data into External database from Axapta Database
//Begin
// Establish a connection with SQL Server
conn.open(@"Provider=SQLOLEDB;Integrated Security=SSPI;Data Source=VS-08-22\SQLEXPRESS;Initial Catalog=TestDatabase;");
cmd.activeConnection(conn);

while select CustTable
{
//Execute the command
cmd.commandText(strfmt("insert into Customer(CustId,CustName,CustGroup,currency) values ('%1','%2',%3,'%4')",CustTable.AccountNum,CustTable.Name,CustTable.CustGroup,CustTable.Currency));
cmd.execute();
}
//End

//To Delete data from External table using X++
//Begin
conn.open(@"Provider=SQLOLEDB;Integrated Security=SSPI;Data Source=VS-08-22\SQLEXPRESS;Initial Catalog=TestDatabase;");
cmd.activeConnection(conn);
cmd.commandText("delete from Customer where CustId='2'");
cmd.execute();
//End

//To Update External table data from X++
//Begin
conn.open(@"Provider=SQLOLEDB;Integrated Security=SSPI;Data Source=VS-08-22\SQLEXPRESS;Initial Catalog=TestDatabase;");
cmd.activeConnection(conn);
cmd.commandText("update Customer set CustName ='Update' where CustId='3'");
cmd.execute();
//End

No comments: