Execute SQL statement directly from Dynamics AX

This is not a recommended approach to run query against AX, but sometimes there's a need for it (Eg. stored procedure), below is the sample code to run SQL statement directly to the AX database (you'll need to do manual security check due to this approach will skip AX security validation).

I usually has a class with 2 simple method:

• executeUpdate
  Used to execute INSERT, UPDATE, DELETE statement
• executeQuery
  Used to execute SELECT statement

//Class :: SQLUtility ==========================================

class SQLUtility

{

}

//Method :: executeUpdate ======================================

server static int executeUpdate(str sql, Connection connection = new Connection())

{

    SqlStatementExecutePermission   sqlPerm;

    Statement                       statement;

    int                             rowsAffected;

    ;

    if(sql)

    {

        sqlPerm = new SqlStatementExecutePermission(sql);

        sqlPerm.assert();

        statement    = connection.createStatement();

        rowsAffected = statement.executeUpdate(sql);

        CodeAccessPermission::revertAssert();

    }

    return rowsAffected;

}

//Method :: executeQuery =======================================

server static ResultSet executeQuery(str sql, Connection connection = new Connection())

{

    SqlStatementExecutePermission   sqlPerm;

    Statement                       statement;

    ResultSet                       resultSet;

    ;

    sqlPerm = new SqlStatementExecutePermission(sql);

    sqlPerm.assert();

    statement = connection.createStatement();

    resultSet = statement.executeQuery(sql);

    CodeAccessPermission::revertAssert();

    return resultSet;

}

//Job :: Job_DirectSQLTest =====================================

static void Job_DirectSQLTest(Args _args)

{

    ResultSet       testRS;

    ;

    

    testRS = SQLUtility::executeQuery("select top 10 ItemId from InventTable");

    while(testRS.next())

    {

        info(testRS.getString(1));

    }

}

Result of running the job " Job_DirectSQLTest  "