Search This Blog

06 August 2011

Using ADO.Net with X++

X++ is, as you know, a powerful language which allows you to do almost all reasonable things with your data. But, sometimes you need to use the full power of your SQL-Server, or access an external database. This can be done with ODBC or ADO. But ODBC is not enabling you the full capacities of your database and ADO is not the most performant way to access databases. Instead of these two possibilities it is in most situation handier to use the ADO.Net instead of ADO (I posted an example in 2008 on msdn). The only thing to do in Dynamics Ax 2009 is to reference the System.Data assembly, which inclued the SQL-Server client implementation. The .Net framework includes a client for Oracle and ODBC. Using the Oracle-client requires the assembly System.Data.OracleClient, the ODBC is part of the System.Data assembly, like the SqlServer-client. Other providers can be found here.
This sample gives you a pretty good idea of how to implement this on your own:
public static server void ExecuteADONETQuery()
{
    str serverName;
    str catalogName;
    str ConnectionString;
    str sqlQuery;
    //ADO.Net via CLR objects. Requires referenced System.Data
    System.Data.SqlClient.SqlConnectionStringBuilder connectionStringBuilder;
    System.Data.SqlClient.SqlConnection connection;
    System.Data.SqlClient.SqlCommand command;
    System.Data.SqlClient.SqlParameterCollection parameterCollection;
    System.Data.SqlClient.SqlDataReader dataReader;
    ;
    new InteropPermission( InteropKind::ClrInterop ).assert();

    //Defining any SQL-Server 200x query....
    //use parameter instead of variables, so that the database can precompile it
    //and estimate an optimal execution plan
    sqlQuery = "SELECT DISTINCT TOP 3 PDT.ACCOUNTRELATION, PDT.ITEMRELATION,  PDT.DATAAREAID FROM PRICEDISCTABLE PDT" +
                     "   LEFT OUTER JOIN INVENTTABLE  ON (PDT.ITEMRELATION = INVENTTABLE.ITEMID " +
                     "       AND PDT.ITEMCODE = 0 " +
                     "       AND PDT.DATAAREAID = INVENTTABLE.DATAAREAID) " +
                    " WHERE PDT.DATAAREAID = @DATAAREAID ";

    //ceating the ConnectionString dynamically, based on the current connection
    serverName = SysSQLSystemInfo::construct().getLoginServer();
    catalogName = SysSQLSystemInfo::construct().getloginDatabase();
    connectionStringBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder();
    connectionStringBuilder.set_DataSource(serverName);
    //here it becomes interesting. The current execution context will be used to
    //establish a conection. If this is executed by a batch, this is the user 
    //configured for the batch
    connectionStringBuilder.set_IntegratedSecurity(true);
    connectionStringBuilder.set_InitialCatalog(catalogName);
    //all this to prevent working with a fixed string...
    //on my computer, this would be equal to
    //"Data Source=DYNAMICSVM;Initial Catalog=DynamicsAx1;Integrated Security=True"
    ConnectionString = connectionStringBuilder.get_ConnectionString();

    //initializing connection and command
    connection = new System.Data.SqlClient.SqlConnection(ConnectionString);
    command = new System.Data.SqlClient.SqlCommand(sqlQuery);
    command.set_Connection(connection);

    //initializing the parameter @DATAAREAID with AddWithValue
    //http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparametercollection.addwithvalue.aspx
    //To prevent using the System.Data.DBTypes. These might not work on the server.
    //This will automatically set the correct DBType during the runtime and prevent running
    //into this pitfall...
    parameterCollection = command.get_Parameters();
    parameterCollection.AddWithValue("@DATAAREAID", "CEE");

    //executing SQL-query
    try
    {
        //open within catch, so that the object can correcly be disposed
        //all these try-catch are quite ennoying in X++, but this because 
        //X++ does not know finally...
        connection.Open();
        try
        {
            //All code after the open must be in a seperate catch, so that the
            //open connection-object can correcly be disposed.
            dataReader = command.ExecuteReader();

            while(dataReader.Read())
            {
                //use the named columns instead of index.
               info( dataReader.get_Item("ITEMRELATION"));
            }
            //Dispose ADO.Net objects ASAP
            dataReader.Dispose();
        }
        catch //should be more precise in a real-world application
        {
            //if exception occures while reading, DataReader need to be
            dataReader.Dispose();
        }
        catch(Exception::CLRError) //CLR exception need to be handled explicitely
        //otherwise they might be 'lost'. Happy copy&pasteing
        {
            //if exception occures while reading, DataReader need to be
            dataReader.Dispose();
        }
        connection.Dispose();
    }
    catch //should be more precise in a real-world application
    {
        connection.Dispose(); //disposing connection if it fails before opening it
    }
    catch(Exception::CLRError)
    {
        connection.Dispose();
    }
    command.Dispose();
    CodeAccessPermission::revertAssert();
}

6 comments:

  1. Hi Florian, good post and blog.
    I´m trying to do it but I´m using System.Data.OracleClient for Oracle.
    My connection is static, i have user and password.I hope that you can help me how to do it, because I´m really lost. Regards.
    PD. my email is cybernauj@hotmail.com

    ReplyDelete
  2. Because you already solved the issue, just as an explanation for other readers:
    The Oracle-provider is part of the .Net framework (System.Data.OracleClient) and you just need to replace the SqlClient-related classed by those.
    Another possibility is to use the external Oracle-provider (which would be the same for IBM DB2, ...). In that case, you need to reference the 3rd-party-assemblies to Ax...
    ---
    http://msdn.microsoft.com/fr-fr/library/system.data.oracleclient(v=vs.90).aspx

    ReplyDelete
  3. Excellent Post. Also visit http://www.msnetframework.com/

    ReplyDelete
  4. Wonderful post! Youve made some very astute observations and I am thankful for the the effort you have put into your writing. Its clear that you know what you are talking about. I am looking forward to reading more of your sites content.
    Latest jobs

    ReplyDelete
  5. Hi there! Thank you for this one, because I'm totally begining with coding in Microsoft Dynamics AX. Your website is that one, which I found really helpful. I hope you're going to share your knowledge with us in the future.

    ReplyDelete
  6. Can anyone explain what this means? ADO.Net via CLR objects. Requires referenced System.Data

    ReplyDelete