May 18, 2016

X++에서 OLEDB연결후 T-SQL Transaction 사용하기

void AndyTEST()
{
    //Oledb new connection
    System.Exception                    e;
    System.Data.OleDb.OleDbConnection   objConn;
    System.Data.OleDb.OleDbCommand      cmdSelect;
    System.Data.OleDb.OleDbDataReader   reader;
    System.Data.OleDb.OleDbTransaction  oleTransaction;
    str                     ServerNm,DBName,UID,PW,connectStr,query;
    str                     DISCPERCENT,PURCHASETYPE,DELIVERYDATE;
   
    ServerNm="AXTEST";
    DBName="TEST";
    UID="Admin";
    PW="qwe~123!@";
    connectStr = 'Provider=SQLOLEDB.1;';
    connectStr = connectStr + 'Persist Security Info=False;Server=';
    connectStr = connectStr + ServerNm  +';';          //Server IP
    connectStr = connectStr + 'database=';
    connectStr = connectStr + DBName +';';          //SQL DB Name
    connectStr = connectStr + 'user id =';
    connectStr = connectStr + UID  +';';         //SQL User ID
    connectStr = connectStr + 'password=';
    connectStr = connectStr + PW;
   
    objConn = new System.Data.OleDb.OleDbConnection(connectStr);
    objConn.Open();
   
    cmdSelect = objConn.CreateCommand();
   
    try
    {
        oleTransaction = objConn.BeginTransaction();
       
        query="SELECT DISCPERCENT AS DISCPERCENT,PURCHASETYPE AS PURCHASETYPE,DELIVERYDATE AS DELIVERYDATE";
        query+=" FROM EDU_TABLE WITH(NOLOCK)";
       
        cmdSelect.set_Transaction(oleTransaction);
       
        cmdSelect.set_CommandText(query);
   
        reader = cmdSelect.ExecuteReader();
   
        while(reader.Read())
        {
            DISCPERCENT      =   reader.GetValue(0);
            PURCHASETYPE     =   reader.GetValue(1);
            DELIVERYDATE     =   reader.GetValue(2);
           
            info(strFmt("DELIVERYDATE = %1",DISCPERCENT));
            info(strFmt("PURCHASETYPE = %1",PURCHASETYPE));
            info(strFmt("DELIVERYDATE = %1",DELIVERYDATE));
   
            if (objConn)
            {
                objConn.Close();  
            }
        }
       
        oleTransaction.Commit();
    }
    catch
    {
        oleTransaction.Rollback();
    }
}

No comments:

Post a Comment