Connecting to Databases through X++ PART -IV

Connecting to Databases through X++ PART -IV

Connection Class:

Connection class is mainly used for accessing the database in which a user has logged into AX i.e. Current Database and carry out the operations. This class is exetensively used in ReleaseUpdateDB classes, the classes used in data upgrades. This class cannot be run on client and should always be run on server. One more unique thing that I noticed is that the statements that you want to execute should be asserted first for permissions and then passed on to other method where they are executed. Create a class with following methods and set its RunOn property to Server.

class TestSQLExecuteClass
{
}

//This method tests the permissions for statement and then calls the method that will execute the statement
static void dbConnectionClass()
{
    ResultSet   rs;
    SqlStatementExecutePermission perm;
    ;

    perm = new SQLStatementExecutePermission("select * from CustTable where DATAAREAID = ‘CEU’");
    perm.assert();

    rs = TestSQLExecuteClass::statementExeQuery("select * from CustTable where DATAAREAID = ‘CEU’");

    while (rs.next())
    {
        info(rs.getString(1));
    }
    CodeAccessPermission::revertAssert();
}

//Executes the passed statement
private static ResultSet statementExeQuery(str _sql, Connection _con = null)
{
    ResultSet   resultSet;
    Statement   statement;
    ;

    try
    {
        if(!_con)
        {
            _con = new Connection();
        }

        statement = _con.createStatement();

// Do not call assert() here, do it in the caller
        // BP deviation documented
        resultSet = statement.executeQuery(_sql);
    }
    catch (Exception::Error)
    {
        throw error("@SYS99562");
    }

    return resultSet;
}

Now you can call the method in a job as shown below:

static void dbConnectionClass(Args _args)
{
    ;

    TestSQLExecuteClass::dbConnectionClass();
}

These examples shown here are pretty simple and easy to understand and start with. Hope it helps you in building ‘connections’

Related posts, 

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

Microsoft MVP (Most Valuable Professorial) Award 2013

Hi All,

:) :) Microsoft awarded me by MVP 2013......... :)  :)

This award is given to exceptional technical community leaders who actively share their high quality, real world expertise with others. I would like to say thanks to Microsoft ,MVP  team members and all blog readers who make this possible for me.

Thanks again to all of you.

To read more about MVP follow below link

http://blogs.msdn.com/b/mvpawardprogram/archive/2013/10/01/new-and-renewed-mvps-announced.aspx

Regards

Deepak Agarwak aka Harry

Connecting to Databases through X++ PART -III

Connecting to Databases through X++ PART -III

      OLEDB Connection:

OLEDB is a set of APIs designed by Microsoft and used for accessing different types of data stored in a uniform manner. Dynamics AX as such doesn’t have any specific classes built for this purpose. But one can make use of .Net Framework’s System.Data.OleDb namespace through AX’s COM Interoperability feature and use it in AX.

Below is an example code that depicts this scenario:

static void dbOLEDBConnection(Args _args)
{
    System.Exception                    e;
    System.Data.OleDb.OleDbConnection   objConn;
    System.Data.OleDb.OleDbCommand      cmdSelect;
    System.Data.OleDb.OleDbDataReader   reader;
    InteropPermission                   perm;
    str connectStr = "Provider=SQLNCLI.1;Integrated Security=SSPI;"+
                     "Persist Security Info=False;Initial Catalog=AX2009;Data Source= theAxapta ";
    str exceptionStr;
    ;

    try
    {
        perm = new InteropPermission(InteropKind::ClrInterop);
        if (perm == null)
        {
            throw error("Error with file permissions");
        }
        perm.assert();

        objConn = new System.Data.OleDb.OleDbConnection(connectStr);
        objConn.Open();

        cmdSelect   = objConn.CreateCommand();
        cmdSelect.set_CommandText("SELECT * FROM CustTable where DATAAREAID = ‘CEU’");
        reader      = cmdSelect.ExecuteReader();

        while (reader.Read())
        {
            info(reader.GetString(0));
        }
    }
    catch(Exception::CLRError)
    {
        CodeAccessPermission::revertAssert();

        perm = new InteropPermission(InteropKind::ClrInterop);
        if (perm == null)
        {
            return;
        }
        perm.assert();

        e = ClrInterop::getLastException();

        CodeAccessPermission::revertAssert();

        while( e )
        {
            exceptionStr += e.get_Message();
            e = e.get_InnerException();
        }
        info(exceptionStr);
    }
    catch
    {
        error("An Exception has occurred");
    }

    if(objConn)
        objConn.Close();
}

Other related posts:

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

AX 2012 R2 VPC And Materials Download

AX 2012 R2 VPC And Course Materials Download

You must have Partner Source credentials to access the following links.

Dynamics AX2012 R2 VPC
VPCLink

Dynamics AX2012 R2 Course Materials

MaterialsLink

-Harry