C#中跨数据库增删改的事务控制
在程序的开发过程中，对数据库的增删改通常要用到事务，用来实现的全部更新和全部回滚。单数据库还比较好做，如果遇到同时操作多个数据库增删改，可以用下面的方法
try
{
using (TransactionScopescope = new TransactionScope())
{
// 更新northwind数据库的Employees表
using (SqlConnectionconOne = new SqlConnection( " server=.;uid=sa;pwd=123;database=northwind " ))
{
conOne.Open();
SqlCommandcommand = new SqlCommand( " updateEmployeessetlastname='chen'whereemployeeid='1' " ,conOne);
int i = command.ExecuteNonQuery();
}
// 更新pubs数据库的jobs表
using (SqlConnectionconTwo = new SqlConnection( " server=.;uid=sa;pwd=123;database=pubs " ))
{
conTwo.Open();
SqlCommandcommand = new SqlCommand( " updatejobssetjob_desc='chen'wherejob_id='1' " ,conTwo);
int i = command.ExecuteNonQuery();
}
scope.Complete(); // 提交事物
}
}
catch (Exceptionex) // 发生异常后自动回滚
{
// throw;
}
SqlConnectionconNorthwind = new SqlConnection( " server=.;uid=sa;pwd=123;database=northwind " );
SqlConnectionconPubs = new SqlConnection( " server=.;uid=sa;pwd=123;database=pubs " );
SqlCommandcommandNorthwind = new SqlCommand();
SqlCommandcommandPubs = new SqlCommand();
try
{
conNorthwind.Open();
conPubs.Open();
// 更新northwind数据库的Employees表
SqlTransactiontranNorthwind = conNorthwind.BeginTransaction();
commandNorthwind.Connection = conNorthwind;
commandNorthwind.Transaction = tranNorthwind;
commandNorthwind.CommandText = " updateEmployeessetlastname='chen'whereemployeeid='1' " ;
int i = commandNorthwind.ExecuteNonQuery();
// 更新pubs数据库的jobs表
SqlTransactiontranPubs = conPubs.BeginTransaction();
commandPubs.Connection = conPubs;
commandPubs.Transaction = tranPubs;
commandPubs.CommandText = " updatejobssetjob_desc='chen'wherejob_id='1' " ;
int k = commandPubs.ExecuteNonQuery();
// thrownewException();
// 提交事务
commandNorthwind.Transaction.Commit();
conNorthwind.Close();
commandPubs.Transaction.Commit();
conPubs.Close();
}
catch (Exceptionex)
{
// 回滚事务
if (commandNorthwind.Transaction != null && conNorthwind != null )
{
commandNorthwind.Transaction.Rollback();
conNorthwind.Close();
}
if (commandPubs.Transaction != null && conPubs != null )
{
commandPubs.Transaction.Rollback();
conPubs.Close();
}
// throw;
}
一。 TransactionScope 类
TransactionScope 类是framework2.0 新增的一个类，在 System.Transactions命名空间中，使用时必须先添加 System.Transactions 引用；另外还要在windows控制面版-->管理工具-->服务-->Distributed Transaction Coordinator-->属性-->启动，启动这个服务.示例代码如下：
try
{
using (TransactionScopescope = new TransactionScope())
{
// 更新northwind数据库的Employees表
using (SqlConnectionconOne = new SqlConnection( " server=.;uid=sa;pwd=123;database=northwind " ))
{
conOne.Open();
SqlCommandcommand = new SqlCommand( " updateEmployeessetlastname='chen'whereemployeeid='1' " ,conOne);
int i = command.ExecuteNonQuery();
}
// 更新pubs数据库的jobs表
using (SqlConnectionconTwo = new SqlConnection( " server=.;uid=sa;pwd=123;database=pubs " ))
{
conTwo.Open();
SqlCommandcommand = new SqlCommand( " updatejobssetjob_desc='chen'wherejob_id='1' " ,conTwo);
int i = command.ExecuteNonQuery();
}
scope.Complete(); // 提交事物
}
}
catch (Exceptionex) // 发生异常后自动回滚
{
// throw;
}
二。另一种方法就是建立两个事务，两个连接了。代码如下；
SqlConnectionconNorthwind = new SqlConnection( " server=.;uid=sa;pwd=123;database=northwind " );
SqlConnectionconPubs = new SqlConnection( " server=.;uid=sa;pwd=123;database=pubs " );
SqlCommandcommandNorthwind = new SqlCommand();
SqlCommandcommandPubs = new SqlCommand();
try
{
conNorthwind.Open();
conPubs.Open();
// 更新northwind数据库的Employees表
SqlTransactiontranNorthwind = conNorthwind.BeginTransaction();
commandNorthwind.Connection = conNorthwind;
commandNorthwind.Transaction = tranNorthwind;
commandNorthwind.CommandText = " updateEmployeessetlastname='chen'whereemployeeid='1' " ;
int i = commandNorthwind.ExecuteNonQuery();
// 更新pubs数据库的jobs表
SqlTransactiontranPubs = conPubs.BeginTransaction();
commandPubs.Connection = conPubs;
commandPubs.Transaction = tranPubs;
commandPubs.CommandText = " updatejobssetjob_desc='chen'wherejob_id='1' " ;
int k = commandPubs.ExecuteNonQuery();
// thrownewException();
// 提交事务
commandNorthwind.Transaction.Commit();
conNorthwind.Close();
commandPubs.Transaction.Commit();
conPubs.Close();
}
catch (Exceptionex)
{
// 回滚事务
if (commandNorthwind.Transaction != null && conNorthwind != null )
{
commandNorthwind.Transaction.Rollback();
conNorthwind.Close();
}
if (commandPubs.Transaction != null && conPubs != null )
{
commandPubs.Transaction.Rollback();
conPubs.Close();
}
// throw;
}
