#眉標=分散式交易開發
#副標=Transaction實戰經驗談
#大標=.NET分散式交易程式開發15問
#作者=文圖／李明儒



==<反灰>===========
netsh firewall set allowedprogram %windir%\system32\msdtc.exe MSDTC enable
==<end>==============


==<反灰>===========
UPDATE OracleLinkedServerName..SchemaName.TableName SET ColA=’Blah’ WHERE ColB=’Boo’
==<end>==============



==<box>===========
程式1
static string demoCnnStr
    = "Data Source=mySql2005;User Id=blah;Password=blah;Initial Catalog=Lab;";
//查詢資料庫的共用函數
static DataTable getDataTable(string sqlCmd)
{
    SqlConnection cn = 
        new SqlConnection(demoCnnStr);
    SqlDataAdapter da = 
        new SqlDataAdapter(sqlCmd, cn);
    DataTable dt = new DataTable();
    da.Fill(dt);
    return dt;
}
//檢查啟用LTM或OleTx
static void showTrnInfo(string tag)
{
    string msg = "No Transaction!";
    if (Transaction.Current != null)
    {
        TransactionInformation ti =
        Transaction.Current.TransactionInformation;
        msg = string.Format("LTM:{0} OleTx:{1}",
            ti.LocalIdentifier, 
            ti.DistributedIdentifier);
    }
    Console.WriteLine(
        string.Format("{0}\n {1}", tag, msg));
}
//Trnasaction中即使SELECT也會導致升級成OleTx
static void TestSelInTran2()
{
    showTrnInfo("P1");
    using (TransactionScope tx = 
        new TransactionScope())
    {
        string sql = "SELECT getdate()";
        showTrnInfo("P2");
        //第一次查詢動作
        DataTable t = getDataTable(sql);
        showTrnInfo("P3");
        //第二次查詢動作
        t = getDataTable(sql);
        showTrnInfo("P4");
        tx.Complete();
    }
}==<end>==============




==<box>===========
程式2
//可共用連線的資料存取物件
class DataProvider : IDisposable
{
    SqlConnection cn = null;

    public DataProvider()
    {
        string demoCnnStr
        = "Data Source=labs-web01;User Id=lab;Password=blah;Initial Catalog=Lab;";
        cn = new SqlConnection(demoCnnStr);
        cn.Open();
    }

    public DataTable GetDataTable(string sqlCmd)
    {
        SqlDataAdapter da = new SqlDataAdapter(sqlCmd, cn);
        DataTable dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public void ExecCommand(string sqlCmd)
    {
        SqlCommand cmd = new SqlCommand(sqlCmd, cn);
        cmd.ExecuteNonQuery();
    }

    public void Dispose() 
    {
        cn.Close();
    }

}
//改用DataProvider物件存取資料庫
static void TestSelInTran3()
{
    using (TransactionScope tx =
        new TransactionScope())
    {
        using (DataProvider dp =
            new DataProvider())
        {
            string sql = "SELECT getdate()";
            showTrnInfo("P2");
            //第一次查詢動作
            DataTable t = dp.GetDataTable(sql);
            showTrnInfo("P3");
            //第二次查詢動作
            t = dp.GetDataTable(sql);
            showTrnInfo("P4");
            tx.Complete();
        }
    }
}==<end>==============



==<反灰>===========
using (TransactionScope ntx = new TransactionScope(TransactionScopeOption.Suppress))
==<end>==============


==<box>===========
程式3
static void TestTran4()
{
    CleanData(false);
    using (TransactionScope tx =
        new TransactionScope())
    {
        using (DataProvider dp =
            new DataProvider())
        {
            string sql = "SELECT getdate()";
            DataTable t = dp.GetDataTable(sql);
            showTrnInfo("Before");
            using (TransactionScope ntx =
                new TransactionScope(
                    TransactionScopeOption.Suppress
                    ))
            {
                //呼叫查詢資料庫的其他元件
                ExtProc ep = new ExtProc();
                ep.DoSomething();
            }
            showTrnInfo("After");
            tx.Complete();
        }
    }
}==<end>==============



==<反灰>===========
TSQLInsert : 406ms
CnTrnInsert: 1,078ms
LTMInser: 1,093ms
OleTxInsert: 10,890ms
==<end>==============



==<box>===========
程式4
static void TSQLInsert(int i)
{
    string sql = string.Format(
@"
SET XACT_ABORT ON
BEGIN TRAN
INSERT INTO Emp VALUES ({0},'Emp{0}')
INSERT INTO Cust VALUES ({0},'Cust{0}')
COMMIT TRAN
", i);
    DataHelper.ExecCommand(sql);
}
static void CnTrnInsert(int i)
{
    using (SqlConnection cn = new SqlConnection(
        DataHelper.DemoCnnStr))
    {
        cn.Open();
        SqlTransaction tn = 
            cn.BeginTransaction();
        string sql = string.Format(
"INSERT INTO Emp VALUES ({0},'Emp{0}')", i);
        SqlCommand cmd = 
            new SqlCommand(sql, cn, tn);
        cmd.ExecuteNonQuery();
        cmd.CommandText = string.Format(
"INSERT INTO Cust VALUES ({0},'Cust{0}')", i);
        cmd.ExecuteNonQuery();
        tn.Commit();
        cn.Close();
    }
}
static void LTMInsert(int i)
{
    using (TransactionScope tx =
        new TransactionScope())
    {
        using (DataProvider dp = 
            new DataProvider())
        {
            string sql = string.Format(
"INSERT INTO Emp VALUES ({0},'Emp{0}')", i);
            dp.ExecCommand(sql);
            sql = string.Format(
"INSERT INTO Cust VALUES ({0},'Cust{0}')", i);
            dp.ExecCommand(sql);
            tx.Complete();
        }
    }
}
static void OleTxInsert(int i)
{
    using (TransactionScope tx = 
        new TransactionScope())
    {
        string sql = string.Format(
"INSERT INTO Emp VALUES ({0},'Emp{0}')", i);
        DataHelper.ExecCommand(sql);
        sql = string.Format(
"INSERT INTO Cust VALUES ({0},'Cust{0}')", i);
        DataHelper.ExecCommand(sql);
        tx.Complete();
    }
}==<end>==============


==<反灰>===========
SET XACT_ABORT ON
BEGIN TRAN
… UPDATE COMMAND …
COMMIT TRAN
==<end>==============