SqlTransaction 数据库编程事务使用示例

在提交或回滚 SqlTransaction 时，应始终使用 Try/Catch 进行异常处理。如果连接终止或事务已在服务器上回滚，则 Commit 和 Rollback 都会生成 InvalidOperationException。
 
下面的示例创建一个 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();
            }
        }