Question

ASP.NET Sqltransaction类的使用

小弟刚出道，询问各位高手一个问题

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

Answer 1

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

Answer 2

下面的示例创建一个 SqlConnection 和一个 SqlTransaction。此示例还演示如何使用 BeginTransaction、Commit 和 Rollback 等方法。出现任何错误时事务都会回滚。Try/Catch 错误处理用于处理尝试提交或回滚事务时的所有错误。

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