How to execute SQL directly form Dynamics AX X++

How to execute Sql directly form Dynamics AX X++

Reference by : alirazazaidi

Dynamics Ax provide many other ways to communicate with database, but sometimes we have to execute direct query to database. For example I have to delete all items form InventTable of particular company or dataArea. I execute it as follow.

Connection      connection;

Statement       statement;

str             query;

DataAreaId      currentDataArea; 

// create connection object

connection = new Connection();

// create statement

statement = connection.createStatement();

//Find current company

currentDataArea = curext();

if(! Box::yesNo(“You are in Company: ” + currentDataArea + “. Proceed to Delete?”,DialogButton::No))

{

return;

}

//Vendors delete

query = “delete from inventTable where dataAreaId = ‘” + currentDataArea + “‘”;

new SqlStatementExecutePermission(query).assert();

statement.executeUpdate(query);

CodeAccessPermission::revertAssert();

info(“InventItems Deleted from Company: ” + currentDataArea);