在Dynamics AX 2012 X++中使用 ADO.Net 访问外部数据库

在 AX 2009 之前的版本可以通过系统集成的两ADO相关类CCADOConnection、CCADORecordSet访问外部数据库。AX 2012删除了这两个类，但我们依然可以使用ADO.NET 访问外部数据库，样例代码如下，相信大家一看便知。

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

转载请注明：ww12345678 的部落格 | AX Helper » 在Dynamics AX 2012 X++中使用 ADO.Net 访问外部数据库