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();
}

20 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. 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
  5. Can anyone explain what this means? ADO.Net via CLR objects. Requires referenced System.Data

    ReplyDelete
  6. How to execute update or insert sql query?

    ReplyDelete
  7. IT IS NICE TO BE IMPORTANT, BUT IT’S MORE IMPORTANT TO BE NICE.
    Those eyes! This rare Russian hairless breed goes by a few names including Russian Donskoy sphynx kittens for sale, Russian Hairless, Don Sphynx, and Don Hairless. Medium-sized, loyal, loving, affectionate, playful and smart are just a few of the reasons this is a much-loved breed. toy poodle for sale Fun fact: unlike the sphynx which gets its hairlessness from a recessive genetic mutation, gets its hairlessness from a dominant gene.

    ReplyDelete
  8. A thesis paper is necessary since it looks to be an official document that determines whether you will be assigned a thesis and whether your thesis will be approved. A thesis paper, in a nutshell, is a document that contains the thesis paper's key themes. It clarifies why the thesis is important and what the thesis' main purpose is. As a result, obtaining a PhD requires first obtaining approval for your thesis.
    Each thesis is accompanied by a set of instructions. However, the most challenging part is that the thesis paper must be unique. The majority of a student's academic years are spent honing language skills and developing writing tactics for thesis papers. Their thesis papers, however, are continuously rejected owing to plagiarism, despite multiple tries. Students may now get quick assistance from the SourceEssay thesis writing service to take the pressure off of creating exceptional thesis papers. Students in Manchester may now get non-plagiarized content for their thesis papers at a minimal cost by using our thesis paper help in Manchester.
    Many universities ask students to submit a thesis paper with their application if they want to apply for a scholarship. They will only be awarded the scholarship if their thesis paper is accepted. As a result, it is vital for students to submit an error-free thesis document that meets all university requirements.
    A thesis differs from a thesis paper in that the thesis is shorter and more to the point. To ensure that they receive original thesis essays, students should contact SourceEssay assignment tutors as soon as possible. A collection of experts in Manchester can provide Thesis Paper Help Manchester to students.
    They make certain that students do not have to pay expensive rates for their thesis papers, that the thesis papers are prepared to the university's criteria, and, most importantly, that students may get last-minute assignment help and submit their thesis papers on time.

    ReplyDelete
  9. https://furkittens.com/cat-persian-for-sale/
    https://furkittens.com/for-sale-maine-coon-kitten/
    https://frenchbulldogspuppiesforsale.com/
    https://chihuahuapuppiesonsale.com
    yorkiespuppiesforsale.com

    ReplyDelete
  10. Cloud storage server
    Get Storage Server with high data capacity to store all your data. Customize data storage server at best price with 24/7 Server Support.

    ReplyDelete