ASP.NET Sqltransaction类的使用

有4个数据表A,B,C,D 将4个表的数据批量复制到表E中，所以就写了4条insert的sql语句，使用Sqltransaction类，并且当任何一个insert执行错误，所有其他的插入动作就回滚。

string sql1, sql2, sql3, sql4;//这四个sql语句是插入sql语句； 
SqlConnection con = new SqlConnection();//这里要添加连接字符串 
con.Open; 
SqlTransaction tran = con.BeginTransaction(); 
SqlCommand com = new SqlCommand(); 
com.Connection = con; 
com.CommandText = sql1; 
com.EndExecuteNonQuery(); 
com.CommandText = sql2; 
com.EndExecuteNonQuery(); 
com.CommandText = sql3; 
com.EndExecuteNonQuery(); 
com.CommandText = sql4; 
com.EndExecuteNonQuery(); 
try 
{ 
tran.Commit(); 
} 
catch 
{ 
tran.Rollback(); 
} 
finally 
{ 
con.Close(); 
tran.Dispose(); 
} 

在提交或回滚 SqlTransaction 时，应始终使用 Try/Catch 进行异常处理。如果连接终止或事务已在服务器上回滚，则 Commit 和 Rollback 都会生成 InvalidOperationException。

 以下代码来自 ms帮助文档

  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); } } } }

转载于:https://www.cnblogs.com/21vicky21/archive/2010/09/28/2002933.html