使用Transactions類完成多個數據庫的事務操作

use [db1]

go
/****** 對象:  Table [dbo].[t1]    腳本日期: 11/19/2007 13:52:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[t1](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [name] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
 [pwd] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
 [email] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]

use [db2]
GO
/****** 對象:  Table [dbo].[t2]    腳本日期: 11/19/2007 13:51:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[t2](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [name] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
 [state] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]

web.config:

 <connectionStrings>
  <add name="sqlconnstring_t1" connectionString="server=192.168.1.1;database=db1;uid=sa;passWord=sa;"/>
  <add name="sqlconnstring_t2" connectionString="server=192.168.1.1;database=db2;uid=sa;passWord=sa;"/>
 </connectionStrings>

新建一個ASP.Net項目。增加對system.Transactions的引用。

using System;
using System.Data;
using System.Data.SqlClIEnt;
using System.Configuration;
using System.Web;
using System.Web.Configuration;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Transactions;

public partial class _Default : System.Web.UI.Page
{
    protected void Button1_Click(object sender, EventArgs e)
    {
        SqlConnection s1 = new SqlConnection(WebConfigurationManager.ConnectionStrings["sqlconnstring_t1"].ConnectionString);
        SqlConnection s2 = new SqlConnection(WebConfigurationManager.ConnectionStrings["sqlconnstring_t2"].ConnectionString);

        string queryString1 = string.Empty;
        string queryString2 = string.Empty;
        queryString1 = string.Format(" insert into t1(name,pwd,email) values(''{0}'',''{1}'',''{2}'') ",this.TextBox1.Text,this.TextBox2.Text,this.TextBox3.Text);
        queryString2 = string.Format(" insert into t2(name,state) values(''{0}'',''{1}'') ",this.TextBox4.Text,this.TextBox5.Text);
       
        SqlCommand sc1 = new SqlCommand(queryString1,s1);       
        SqlCommand sc2 = new SqlCommand(queryString2, s2);

        s1.Open();
        SqlTransaction sqlTran1 = s1.BeginTransaction();
        s2.Open();
        SqlTransaction sqlTran2 = s2.BeginTransaction();

        using (TransactionScope transScope = new TransactionScope())
        {
            try
            {
                sc1.Transaction = sqlTran1;
                sc1.ExecuteScalar();

                sc2.Transaction = sqlTran2;
                sc2.ExecuteScalar();
            }
            catch(SqlException ex)
            {
                sqlTran1.Rollback();
              sqlTran2.Rollback();
                s1.Close();//如果不增加數據庫事務，連接關閉時，數據會寫入數據庫
                s2.Close();
                return;
            }
            sqlTran1.Commit();
            sqlTran2.Commit();   
            transScope.Complete();
            s1.Close();
            s2.Close();
        }
    }

}