在提交或回滚 SqlTransaction 时，应始终使用 Try/Catch 进行异常处理。如果连接终止或事务已在服务器上回滚，则 Commit 和 Rollback 都会生成 InvalidOperationException。
下面的示例创建一个 SqlConnection 和一个 SqlTransaction。此示例还演示如何使用 BeginTransaction、Commit 和 Rollback 等方法。出现任何错误时事务都会回滚。Try/Catch 错误处理用于处理尝试提交或回滚事务时的所有错误。
下面的示例创建一个 SqlConnection 和一个 SqlTransaction。此示例还演示如何使用 BeginTransaction、Commit 和 Rollback 等方法。出现任何错误时事务都会回滚。Try/Catch 错误处理用于处理尝试提交或回滚事务时的所有错误。
using
System;
using System.Configuration;
using System.Data.SqlClient;
using System.Transactions;
protected void Page_Load( object sender, EventArgs e)
{
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[ " ConnStr " ].ToString()))
{
conn.Open();
SqlTransaction _transaction = conn.BeginTransaction();
try
{
SqlCommand _cmd = new SqlCommand( " Insert Into T_Test(F_Name) values(@Name) " ,conn);
_cmd.Parameters.Add( new SqlParameter( " @Name " , " Test1 " ));
_cmd.Transaction = _transaction;
_cmd.ExecuteNonQuery();
_cmd.Parameters.Clear();
_cmd.Parameters.Add( new SqlParameter( " @Name " , " Test2 " + new String( ' 2 ' , 100 )));
_cmd.ExecuteNonQuery(); // F_Name为Nvarchar(50)，这里会报错
_transaction.Commit();
Response.Write( " 二条记录已经成功入库! " );
}
catch (Exception ex)
{
Response.Write( " 发生错误： " + ex.Message.ToString());
try
{
_transaction.Rollback();
Response.Write( " <br/>事务已成功回滚! " );
}
catch (Exception ex2)
{
Response.Write( " <br/>回滚失败： " + ex2.Message.ToString());
}
}
_transaction.Dispose();
}
}
using System.Configuration;
using System.Data.SqlClient;
using System.Transactions;
protected void Page_Load( object sender, EventArgs e)
{
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[ " ConnStr " ].ToString()))
{
conn.Open();
SqlTransaction _transaction = conn.BeginTransaction();
try
{
SqlCommand _cmd = new SqlCommand( " Insert Into T_Test(F_Name) values(@Name) " ,conn);
_cmd.Parameters.Add( new SqlParameter( " @Name " , " Test1 " ));
_cmd.Transaction = _transaction;
_cmd.ExecuteNonQuery();
_cmd.Parameters.Clear();
_cmd.Parameters.Add( new SqlParameter( " @Name " , " Test2 " + new String( ' 2 ' , 100 )));
_cmd.ExecuteNonQuery(); // F_Name为Nvarchar(50)，这里会报错
_transaction.Commit();
Response.Write( " 二条记录已经成功入库! " );
}
catch (Exception ex)
{
Response.Write( " 发生错误： " + ex.Message.ToString());
try
{
_transaction.Rollback();
Response.Write( " <br/>事务已成功回滚! " );
}
catch (Exception ex2)
{
Response.Write( " <br/>回滚失败： " + ex2.Message.ToString());
}
}
_transaction.Dispose();
}
}
如果换成时下流行的Linq To Sql写法，大致如下：
using
(DataContext context
=
new
DataContext(connStr))
{
SqlConnection conn = new SqlConnection();
if (context.Connection != null && context.Connection.State != ConnectionState.Open) context.Connection.Open();
context.Transaction = context.Connection.BeginTransaction();
try
{
User user = new User();
user.Name = " aaaa " ;
users.Add(user);
context.SubmitChanges();
users.Remove(users.Single(u => u.Name == " aaaa " ));
context.SubmitChanges();
context.Transaction.Commit();
}
catch
{
try
{
context.Transaction.Rollback();
}
catch
{
//
}
}
}
{
SqlConnection conn = new SqlConnection();
if (context.Connection != null && context.Connection.State != ConnectionState.Open) context.Connection.Open();
context.Transaction = context.Connection.BeginTransaction();
try
{
User user = new User();
user.Name = " aaaa " ;
users.Add(user);
context.SubmitChanges();
users.Remove(users.Single(u => u.Name == " aaaa " ));
context.SubmitChanges();
context.Transaction.Commit();
}
catch
{
try
{
context.Transaction.Rollback();
}
catch
{
//
}
}
}
