WizardWu 編程網(wǎng)

protected void Button1_Click(object sender, EventArgs e)
    {
        SqlConnection cn = new SqlConnection("server=localhost;database=Northwind;integrated security=true");
        SqlTransaction tx = null;
        try
        {
            cn.Open();
            tx = cn.BeginTransaction();
            SqlCommand cmd1 = new SqlCommand("INSERT INTO Employees (LastName, FirstName) VALUES('Wu', 'Wizard')", cn);
            cmd1.Transaction = tx;
            cmd1.ExecuteNonQuery();


            cn.ChangeDatabase("AdventureWorksDW");


            SqlCommand cmd2 = new SqlCommand("INSERT INTO DimGeography (City) VALUES ('Taipei')", cn);

            cmd2.Transaction = tx;

            cmd2.ExecuteNonQuery();
            tx.Commit();
            Response.Write("跨越兩個數(shù)據(jù)庫的 LTE 本機事務(wù)成功 !");
        }
        catch (SqlException ex)
        {
            tx.Rollback();
            Response.Write("發(fā)生錯誤: " + ex.Message);
        }
        finally
        {            
            cn.Close();
            cn.Dispose();
        }
    }

using System;
using System.Data;

using System.Transactions;
using System.Data.SqlClient;

public class Class1
{
    private string strConnString = System.Configuration.ConfigurationManager.ConnectionStrings["Conn_Northwind"].ToString();

    public Class1()
    {
    }

    public string func1()
    {
        SqlConnection conn = null;
        SqlCommand cmd = null;
        int intTheNewestID = 0;
        string strReturn = "";

        //Insert 后，立即 Select 出數(shù)據(jù)庫最新插入的這一筆記錄，其 id 值 (identity, 由數(shù)據(jù)庫自動增號)
        string strSql = "INSERT INTO Employees (LastName, FirstName) VALUES('Wu', 'Wizard') ; SELECT @@identity; ";

        //Required 選項: 當前環(huán)境若無事務(wù)，則創(chuàng)建新事務(wù)，否則就加入當前環(huán)境的同一個事務(wù)
        using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required))
        {
            try
            {
                conn = new SqlConnection(strConnString);
                conn.Open();
                if (conn.State == ConnectionState.Open)
                {
                    cmd = new SqlCommand(strSql, conn);
                    intTheNewestID = Convert.ToInt32(cmd.ExecuteScalar());

                    //調(diào)用 Class 2 的函數(shù)，將其也加入同一個事務(wù)
                    Class2 c2 = new Class2();
                    strReturn = c2.func2(intTheNewestID);

                    scope.Complete();
                }
            }
            catch (Exception ex)
            {
                throw new Exception("組件一 - 發(fā)生數(shù)據(jù)庫訪問錯誤: " + ex.ToString());
            }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                conn.Dispose();
            }
        }

        return strReturn;    //返回前臺的網(wǎng)頁中顯示
    }
}

using System;
using System.Data;

using System.Transactions;
using System.Data.SqlClient;

public class Class2
{
    private string strConnString = System.Configuration.ConfigurationManager.ConnectionStrings["Conn_Northwind"].ToString();

    public Class2()
    {
    }

    public string func2(int intTheNewestID)
    {
        SqlConnection conn = null;
        SqlCommand cmd1 = null;
        SqlCommand cmd2 = null;
        int intInserted = 0;
        string strReturn = "";
        string strSql1 = "INSERT INTO Employees (LastName, FirstName) VALUES('Lee', 'David')";
        string strSql2 = "SELECT LastName FROM Employees WHERE EmployeeID=" + intTheNewestID;

        //Required 選項: 當前環(huán)境若無事務(wù)，則創(chuàng)建新事務(wù)，否則就加入當前環(huán)境的同一個事務(wù)。在此例中，會啟動 DTC，第二句 Select 會成功。
        //RequiresNew 選項: 總是創(chuàng)建新的事務(wù)，會造成 Class1、Class2 不會處于同一個事務(wù)里。在此例中，不會啟動 DTC，第二句 Select 會失敗。
        //Suppress 選項: 不加入此一事務(wù)。
        using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required))
        {
            try
            {
                conn = new SqlConnection(strConnString);
                conn.Open();
                if (conn.State == ConnectionState.Open)
                {
                    cmd1 = new SqlCommand(strSql1, conn);
                    intInserted = cmd1.ExecuteNonQuery();

                    //取得組件一里，剛剛才插入的那一筆記錄，以確認組件一、組件二確實是在同一個事務(wù)中執(zhí)行，而不是拆分成兩個事務(wù)
                    cmd2 = new SqlCommand(strSql2, conn);
                    strReturn = cmd2.ExecuteScalar().ToString();

                    scope.Complete();
                }
            }
            catch (Exception ex)
            {
                throw new Exception("組件二 - 數(shù)據(jù)庫訪問發(fā)生錯誤: " + ex.ToString());
            }
            finally
            {
                if (cmd1 != null)
                    cmd1.Dispose();
                if (cmd2 != null)
                    cmd2.Dispose();
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                conn.Dispose();
            }
        }

        return strReturn;    //返回組件一
    }
}

using System;

using System.Data;
using System.Transactions;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page 
{
    private string strConnString = System.Configuration.ConfigurationManager.ConnectionStrings["Conn_Northwind"].ToString();

    protected void Page_Load(object sender, EventArgs e)
    {
    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        SqlConnection conn = null;
        SqlCommand cmd = null;

        string strSql = "INSERT INTO Employees (LastName, FirstName) VALUES('Wu', 'Wizard')";

        //用 CommittableTransaction 進行明確式事務(wù)
        using (CommittableTransaction tran = new CommittableTransaction())
        {
            try
            {
                conn = new SqlConnection(strConnString);
                conn.Open();
                conn.EnlistTransaction(tran);
                if (conn.State == ConnectionState.Open)
                {
                    cmd = new SqlCommand(strSql, conn);
                    cmd.ExecuteNonQuery();

                    //指定 Callback 函數(shù)為 OnCommitted
                    AsyncCallback ac = new AsyncCallback(OnCommitted);
                    tran.BeginCommit(ac, null);    //開始一個異步事務(wù)

                    //tran.Commit();    //同步事務(wù)的寫法
                }
            }
            catch (Exception ex)
            {
                tran.Rollback();
                Response.Write("程序發(fā)生錯誤: " + ex.Message);
            }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                conn.Dispose();
            }
        }
    }

    //執(zhí)行到這個方法時，會從 Thread Pool 里取得一條線程，進行異步的事務(wù)
    private void OnCommitted(IAsyncResult ar)     //傳入一個 IAsyncResult 參數(shù)
    {
        CommittableTransaction Tx;
        Tx = (CommittableTransaction)ar;

        try
        {
            using ((Tx))
            {
                Tx.EndCommit(ar);    //結(jié)束異步事務(wù)
            }

            Response.Write("異步事務(wù)完成，已成功插入一條記錄。");
        }
        catch (TransactionException ex)
        {
            Tx.Rollback();
            Response.Write("異步事務(wù)失敗，錯誤信息為：" + ex.Message);
        }
        finally
        {
            if (Tx != null)
                Tx.Dispose();
        }
    }

}