c#使用Transactions类完成多个数据库的事务操作（分布式事务处理）

 

下面是用一个事务处理访问两个DBServer的表操作。（例子）

 

新建一个项目

 

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();        }    }

}

 

 

 

建DBServer脚本：

 

 

use [db1]

go/****** 对象:  Table [dbo].[t1]    脚本日期: 11/19/2007 13:52:51 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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>