C#中跨数据库增删改的事务控制

在程序的开发过程中，对数据库的增删改通常要用到事务，用来实现的全部更新和全部回滚。单数据库还比较好做，如果遇到同时操作多个数据库增删改，可以用下面的方法

一。TransactionScope类

TransactionScope类是framework2.0 新增的一个类，在System.Transactions命名空间中，使用时必须先添加System.Transactions引用；另外还要在windows控制面版-->管理工具-->服务-->Distributed Transaction Coordinator-->属性-->启动，启动这个服务.示例代码如下：

try
            {
using (TransactionScope scope = new TransactionScope())
                {
//更新northwind数据库的Employees表
using (SqlConnection conOne = new SqlConnection("server=.;uid=sa;pwd=123;database=northwind"))
                    {
                        conOne.Open();
                        SqlCommand command = new SqlCommand("update Employees set lastname='chen' where employeeid='1'", conOne);
int i = command.ExecuteNonQuery();
                    }
//更新pubs数据库的jobs表
using (SqlConnection conTwo = new SqlConnection("server=.;uid=sa;pwd=123;database=pubs"))
                    {
                        conTwo.Open();
                        SqlCommand command = new SqlCommand("update jobs set job_desc='chen' where job_id='1'", conTwo);
int i = command.ExecuteNonQuery();
                    }
                    scope.Complete();  //提交事物
                }
            }
catch (Exception ex)       //发生异常后自动回滚
            {
//throw;
            }

二。另一种方法就是建立两个事务，两个连接了。代码如下；

    SqlConnection conNorthwind = new SqlConnection("server=.;uid=sa;pwd=123;database=northwind");
            SqlConnection conPubs = new SqlConnection("server=.;uid=sa;pwd=123;database=pubs");
            SqlCommand commandNorthwind = new SqlCommand();
            SqlCommand commandPubs = new SqlCommand();
try
            {
                conNorthwind.Open();
                conPubs.Open();
//更新northwind数据库的Employees表
                SqlTransaction tranNorthwind = conNorthwind.BeginTransaction();
                commandNorthwind.Connection = conNorthwind;
                commandNorthwind.Transaction = tranNorthwind;
                commandNorthwind.CommandText = "update Employees set lastname='chen' where employeeid='1'";
int i = commandNorthwind.ExecuteNonQuery();
//更新pubs数据库的jobs表
                SqlTransaction tranPubs = conPubs.BeginTransaction();
                commandPubs.Connection = conPubs;
                commandPubs.Transaction = tranPubs;
                commandPubs.CommandText = "update jobs set job_desc='chen' where job_id='1'";
int k = commandPubs.ExecuteNonQuery();
//throw new Exception();
//提交事务
                commandNorthwind.Transaction.Commit();
                conNorthwind.Close();
                commandPubs.Transaction.Commit();
                conPubs.Close();
            }
catch (Exception ex)
            {
//回滚事务
if (commandNorthwind.Transaction != null && conNorthwind != null)
                {
                    commandNorthwind.Transaction.Rollback();
                    conNorthwind.Close();
                }
if (commandPubs.Transaction!= null && conPubs != null)
                {
                    commandPubs.Transaction.Rollback();
                    conPubs.Close();
                }
//throw;
            }