如下是使用事务的一段代码: private static void ExecuteSqlTransaction(string connectionString) { using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); SqlCommand command = connection.CreateCommand(); SqlTransaction transaction; // Start a local transaction. transaction = connection.BeginTransaction("SampleTransaction"); // Must assign both transaction object and connection // to Command object for a pending local transaction command.Connection = connection; command.Transaction = transaction; try { command.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')"; command.ExecuteNonQuery(); command.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')"; command.ExecuteNonQuery(); // Attempt to commit the transaction. transaction.Commit(); Console.WriteLine("Both records are written to database."); } catch (Exception ex) { Console.WriteLine("Commit Exception Type: {0}", ex.GetType()); Console.WriteLine(" Message: {0}", ex.Message); // Attempt to roll back the transaction. try { transaction.Rollback(); } catch (Exception ex2) { // This catch block will handle any errors that may have occurred // on the server that would cause the rollback to fail, such as // a closed connection. Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType()); Console.WriteLine(" Message: {0}", ex2.Message); } } } }
SqlTransaction transaction = connection.BeginTransaction();
然后在Command上使用这个事务.
private static void ExecuteSqlTransaction(string connectionString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open(); SqlCommand command = connection.CreateCommand();
SqlTransaction transaction; // Start a local transaction.
transaction = connection.BeginTransaction("SampleTransaction"); // Must assign both transaction object and connection
// to Command object for a pending local transaction
command.Connection = connection;
command.Transaction = transaction; try
{
command.CommandText =
"Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";
command.ExecuteNonQuery();
command.CommandText =
"Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')";
command.ExecuteNonQuery(); // Attempt to commit the transaction.
transaction.Commit();
Console.WriteLine("Both records are written to database.");
}
catch (Exception ex)
{
Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
Console.WriteLine(" Message: {0}", ex.Message); // Attempt to roll back the transaction.
try
{
transaction.Rollback();
}
catch (Exception ex2)
{
// This catch block will handle any errors that may have occurred
// on the server that would cause the rollback to fail, such as
// a closed connection.
Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
Console.WriteLine(" Message: {0}", ex2.Message);
}
}
}
}
不是自己写的
用同一个SqlConnection即可.
如果是不同的数据库, 是分布式事务.
可以用transactionscope(2005才有)或servicecomponet,详情可查阅
网上有关分布式事务的文件
那是需要定义一个全局变量的Connection么?
using (SqlConnection conn = new SqlConnection(connectionString))
{
SqlTransaction transaction = connection.BeginTransaction("SampleTransaction");
Sqlcommand cmd1 = new Sqlcommand ();//更新a表
cmd1.connection = conn;
Sqlcommand cmd2 = new Sqlcommand ();//更新b表
cmd2.connection = conn; try
{
cmd1.ExecuteNoquery();
cmd2.ExecuteNoquery();
tran.commit();
}
catch
{
tran.rollback();
}
}