Skip to main content

Notifications

Announcements

Join us in the Community for an AMA: December 12th Register Today
News and Announcements icon
Dynamics 365 Community / Blogs / DAXology (Study about Dynamics AX and Dynamics ... / Executing direct simple SQL...

Executing direct simple SQL statement in D365FO

Chaitanya Golla Profile Picture Chaitanya Golla 17,225

Hi,

In this post we will see the demo of executing simple SQL statement directly from D365FO.

  •  Created new tables by name SampleTable , SampleTrans and loaded some data into it.
  •  Constructed SQL statement operates on table SampleTable and display its data.
  •  Note: CG_ExecuteSimpleSQLStmt is a runnable class
class CG_ExecuteSimpleSQLStmt
{       
    /// <summary>
    /// Runs the class with the specified arguments.
    /// </summary>
    /// <param name = "_args">The specified arguments.</param>
    public static void main(Args _args)
    {  
        UserConnection                  userConnection;
        Statement                       statement;
        str                             sql;
        SQLSystem                       system;
        SQLStatementExecutePermission   permission;
        ResultSet                       resultSet;
       
        DictTable                       sampleTable = new DictTable(tableNum(SampleTable));
        DictField                       Id = new DictField(tableNum(SampleTable), fieldNum(SampleTable, Id));
        DictField                       tableDueDate = new DictField(tableNum(SampleTable), fieldNum(SampleTable, dueDate));
       
        system = new SqlSystem();
 
        sql = 'SELECT %1, %2 FROM %3';
 
        sql = strFmt(sql,
                    tableDueDate.name(DbBackend::Sql),  // 1
                    Id.name(DbBackend::Sql),            // 2
                    sampleTable.name(DbBackend::Sql));  // 3
 
        userConnection = new UserConnection();
        statement      = userConnection.createStatement();
 
        new SqlStatementExecutePermission(sql).assert();
       
        resultSet = statement.executeQuery(sql);
        CodeAccessPermission::revertAssert();
 
        while (resultSet.next())
        {
            info(strFmt("Id %1 and due date %2", resultSet.getString(1), resultSet.getString(2)));
        }
    }
 
}

Output

Regards,

Chaitanya Golla

Comments

*This post is locked for comments