How to Create Connection Using ADO.Net with X++ in Ax 2012

Description:- 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. 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.

Create Job and Check Ado Connection with Credential in X++.

static void DatabaseConnection(Args _args)
{
    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 * from TableName";

    //ceating the ConnectionString dynamically, based on the current connection
    serverName = SysSQLSystemInfo::construct().getLoginServer();
    catalogName = SysSQLSystemInfo::construct().getloginDatabase();
    //serverName="ExternalServerName";
    //catalogName ="DatbaseName";   

    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...
    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);

    //parameterCollection = command.get_Parameters();
    //parameterCollection.AddWithValue("@ParameterName", "ColumnName");

    //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("FirstName"));
                dataReader.NextResult();               
            }
            //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();
    }
    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();
    }
    connection.Dispose();
    CodeAccessPermission::revertAssert();
}

Author : Umesh Patel

Related Posts