Connecting to Databases through X++ PART -II

Connecting to Databases through X++ PART -II

    ADO Connection:

ADO is a set of COM objects for accessing databases or data stores. In AX we have following Classes/Objects which help to implementing ADO concept.

Class Name	Description
CCADOConnection	Helps in establishing a connection to the target database.
CCADOCommand	Helps in executing a command (a Text type or a Stored procedure)
CCADORecordSet	Stores the data
CCADOFields	A collection of all fields in CCADORecordSet
CCADOField	A single field from the collection of fields
CCADOParameter	A class that helps in passing parameters that a command needs or demands

Here is an example:

static void dbCCADOConnection(Args _args)
{
    CCADOConnection connection = new CCADOConnection();
    CCADOCommand    ccADOCommand;
    CCADORecordSet  record;
    str connectStr = "Provider=SQLNCLI.1;Integrated Security=SSPI;"+
                     "Persist Security Info=False;Initial Catalog=AXDEVDB;Data Source= theAxapta";

    COM     recordSet;  /*This is required to call moveNext method to parse the record set. In AX 4.0 this method was there in the CCADORecordSet class but in AX 2009 this has been deleted*/
    ;

    // Executing a SQL Statement
    try
    {
        connection.open(connectStr);
        ccADOCommand = new CCADOCommand();
        ccADOCommand.commandText("Select * from CustTable where DataAreaId = ‘CEU’");
        ccADOCommand.activeConnection(connection);
        record = ccADOCommand.execute();
        recordSet = record.recordSet();
        while (!record.EOF())
        {
            info(any2str(record.fields().itemIdx(0).value()));
            recordSet.moveNext();
        }
    }
    catch
    {
        error("An Exception has occurred");
    }

    connection.close();
}

Previous Post:

Connecting to Databases through X++ PART -I

Connecting to Databases through X++ PART -II

Connecting to Databases through X++ PART -III

Connecting to Databases through X++ PART -IV

-Harry