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

来源：互联网发布：c语言 volatile 编辑：程序博客网时间：2024/05/13 18:52

下面是用一个事务处理访问两个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 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 配置