To access other Database tables from AX 2012 (using ADO.net)

Hi,

The below code is used to connect the external DB to access tables from ax.

(In this approach we eliminated the standard ODBC DSN configuration. which is time consuming process. because we have to setup the ODBC DSN in all client machine)

static void GetCustomerInfoFromExternalDB(Args _args)

{

    System.Data.SqlClient.SqlConnectionStringBuilder    CSB;

    System.Data.SqlClient.SqlConnection                 con;

    System.Data.SqlClient.SqlCommand                    cmd;

    System.Data.SqlClient.SqlParameterCollection        parmColl;

    System.Data.SqlClient.SqlDataReader                 dataReader;

    str                                                 sql,

                                                        serverName,

                                                        DatabaseName,

                                                        conString;

    System.Exception                                    InteropException;

    boolean                                             ret = true;

    HRMParameters                                       hrmParameters = HRMParameters::find();

    str                                                 custAccount,CustName;

    Transdate                                           custTransDate;

 

    //marcos

     

    #define.ExternalUserName("sa")

    #define.ExternaluserPWD("pAssword")

    ;

    new InteropPermission(InteropKind::ClrInterop).assert();

    sql                 = "Select * from PMSCustomerTable where custId='"+_custId+"'";

    serverName          = SysSQLSystemInfo::construct().getLoginServer(); //database server

    //databaseName        = SysSQLSystemInfo::construct().getloginDatabase(); //It provide the current ax database

    databaseName        = hrmParameters.3rdPartyDBName; //database name to connect

 

    CSB                 = new System.Data.SqlClient.SqlConnectionStringBuilder();

    CSB.set_DataSource(servername);

    CSB.set_InitialCatalog(databaseName);

    CSB.set_IntegratedSecurity(false);

    CSB.set_UserID(#ExternalUserName);

    CSB.set_Password(#ExternaluserPWD);

 

    conString           = CSB.get_ConnectionString();

    con                 = new System.Data.SqlClient.SqlConnection(conString);

    cmd                 = new System.Data.SqlClient.SqlCommand();

    cmd.set_Connection(con);

    cmd.set_CommandText(sql);

    try

    {

        con.Open();

        dataReader = cmd.ExecuteReader();

        if(dataReader)

        {

            while(dataReader.Read())

            {

                                                 //To avoid the DBNull to String Type cast error

                        if (!dataReader.IsDBNull(dataReader.GetOrdinal(<FieldName>)))                       custccount      = dataReader.GetString(1);
               if (!dataReader.IsDBNull(dataReader.GetOrdinal(<FieldName>)))                       custName        = dataReader.GetString(2);

            custTransDate = DateTimeUtil::date(dataReader.GetDateTime(3));               

            }

        }

 

        cmd.Dispose();

        con.Dispose();

        CodeAccessPermission::revertAssert();

    }

    catch(Exception::CLRError)

    {

        interopException = CLRInterop::getLastException();

        while(!CLRInterop::isNull(interopException.get_InnerException()))

        throw error(CLRInterop::getAnyTypeForObject(interopException.get_Message()));

    }

}
 
Note:
1) To use the username & password which is using to connect the sql server using sql server authentication. (username & password for windows authentication has failed during the con.open statement
2) use this link to create a user with sql server authentication
https://www.katieandemil.com/sql-server-2012-add-user-login-with-sql-server-authentication?tab=article
3) Instead of hardcoding the username & password make it parameterized.