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-22SQLEXPRESS;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-22SQLEXPRESS;Initial Catalog=TestDatabase;User Id=sa;Password=sa;”)
conn.open(@”Provider=SQLOLEDB;Data Source=VS-08-22SQLEXPRESS;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-22SQLEXPRESS;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-22SQLEXPRESS;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-22SQLEXPRESS;Initial Catalog=TestDatabase;”);
cmd.activeConnection(conn);
cmd.commandText(“update Customer set CustName =’Update’ where CustId=’3′”);
cmd.execute();
//End