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.

 

Comments

Post a Comment

Popular posts from this blog

x++ code to Generate cheque for vendor payment journal

Hi, X++ code to generate check (cheque) from Vendor payment journal (generate payments). Note: In the Method of payment field, select a method of payment that uses the Check export file format. //*********************************** /*To print check     1) PaymMode - must be filled in LedgerJournalTrans (in paymMode = export format to be check)     2) OffsetAccountType = Bank in LedgerJournalTrans     3) Generate Payment dialog, bank account = Offset Bank account in LedgerJournalTrans*/ //************************************* static void GenerateCheque_VendorPayment(Args _args) {     LedgerJournalTrans      LedgerJournalTrans = LedgerJournalTrans::findRecId(5637186584, true);     VendSumForPaym          vendSumForPaym;     boolean                 runDialog;     LedgerJournalCheckPostResults       postingResults_Client;     LedgerPostingMessage                logTxt;     ;     vendSumForPaym = CustVendSumForPaym::newLedgerJournalTrans(LedgerJournalTrans);     runDialog
Read more

Import Procurement Hierarchy / Categories with out code in ax 2012

Hi, To build the procurement category in ax 2012, we are going to use the " EcoResCategoryImportService ". This service is available in the standard ax box. In this article, interest thing is how to build the XML file from EXCEL. I love that. (It is easy to build the xml file, if we know this way) Please refer the link below for the detailed document, http://www.junctionsolutions.com/dynamicsax/loading-a-merchandising-hierarchy-with-no-programming/ Create XML from Excel. https://www.youtube.com/watch?v=9h7TW--M8BU Note: Before import, you must enable the " Execute business operations in CIL" in this path, File > Tools > Options > Development > General > Execute business operations in CIL Thanks, Vijaykarthik
Read more

Copy favorites From one user to another user - Ax 2012

Hi, The below job is used to copy the favorites from one user to another user. static void CopyFavoritesJob(Args _args) { SysPersonalization FromSysPersonalization; SysPersonalization ToSysPersonalization; UserId FromUserId= 'user01' ; UserId ToUserId= 'user03' ; ; ttsbegin ; // step 1 - delete current favorites menu from user while select forupdate ToSysPersonalization where ToSysPersonalization.ElementType==UtilElementType::UserMenu && ToSysPersonalization.UserId==ToUserId { ToSysPersonalization.doDelete(); } // step 2 - duplicate from user01 while select FromSysPersonalization where FromSysPersonalization.UserId==FromUserId && FromSysPersonalization.ElementType==UtilElementType::UserMenu { ToSysPersonalization.data(FromSysPersonalization); ToSysPersonalization.UserId=ToUserId; ToSysPersonalization.doInsert(); } ttscommit
Read more