c# mysql 事务回滚_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;

}