执行事务类

using System;

using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Collections;
using System.Collections.Specialized;
using System.Configuration;

namespace JackLB
{
 /// <summary>
 /// Sql_Base 数据库连接的基类。
 /// 在web.config 里面设置configuration <appSettings> <add key="Sql_Conn" value="Persist Security Info=False;User ID=sa;Password=1210;Data Source=localhost;Initial Catalog=SLSCHOOL;" /> </appSettings>
 /// </summary>
 public class DbHelperSQL
 {
   private string _Str_SqlCon=System.Configuration.ConfigurationSettings.AppSettings["Sql_Conn"];
  public string  Str_SqlCon
  {
   get{return _Str_SqlCon;}
   set{_Str_SqlCon=value;} 

  }
  //事务处理对象
  private System.Data.SqlClient.SqlTransaction m_SqlTransaction;

  public DbHelperSQL()
  {}
  public DbHelperSQL(string StrSql)
  {
   Str_SqlCon=StrSql;     
  }

  /// <summary>
  /// 返回一个SqlConnection连接
  /// </summary>
  /// <returns></returns>
  public   SqlConnection ConnSql() 
  {   
   SqlConnection  ConnSql=new SqlConnection(Str_SqlCon);  
   return ConnSql;  
  }

  /// <summary>
  /// 数据库连接SqlConnection打开
  /// </summary>
  public  void OpenConn( SqlConnection SqlCon)
  {  
   
   if (SqlCon.State==System.Data.ConnectionState.Closed)
   {  
    SqlCon.Open();
   }
 
  }

  /// <summary>
  /// 数据库连接SqlConnection关闭并释放所用的资源
  /// </summary>
  public  void CloseConn(SqlConnection SqlCon)
  {   
   if (SqlCon.State==System.Data.ConnectionState.Open)
   {
    SqlCon.Close();
    SqlCon.Dispose();
   }

   if (this.m_SqlTransaction !=null)
   {
    this.m_SqlTransaction.Rollback ();
    this.m_SqlTransaction=null;
   }
    
   
  
   SqlCon=null;
  }

  /// <summary>
  /// 执行一个Strsql语句或责存储过程
  /// </summary>
  /// <param name="StrSql">sql语句或责存储过程</param>
  /// <returns></returns>
  public SqlCommand CreateCommand(string StrSql)
  {
   SqlConnection ConnStr =ConnSql();
   OpenConn(ConnStr);
   SqlCommand cmd = new SqlCommand(StrSql, ConnStr);
   return cmd;
  }

#region  给予事务

  /// <summary>
  /// 执行多条SQL语句，实现数据库事务。
  /// </summary>
  /// <param name="SQLStringList">多条SQL语句</param>  
  public  void ExecuteSqlTran(ArrayList SQLStringList)
  {

   using (SqlConnection conn =ConnSql())
   {
    conn.Open();
    SqlCommand cmd = new SqlCommand();
    cmd.Connection=conn;    
    SqlTransaction tx=conn.BeginTransaction();   
    cmd.Transaction=tx;    
    try
    {     
     for(int n=0;n<SQLStringList.Count;n++)
     {
      string strsql=SQLStringList[n].ToString();
      if (strsql.Trim().Length>1)
      {
       cmd.CommandText=strsql;
       cmd.ExecuteNonQuery();
      }
     }          
     tx.Commit();     
    }
    catch(System.Data.SqlClient.SqlException E)
    {  
     tx.Rollback();
     throw new Exception(E.Message);
    }
   }
  }

  /// <summary>
  /// 执行多条SQL语句，实现数据库事务。
  /// </summary>
  /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
  public  void ExecuteSqlTran(Hashtable SQLStringList)
  { 

   using (SqlConnection conn= ConnSql())
   { 
    SqlTransaction trans;
    conn.Open();
    trans = conn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted,"SQLTransaction");

    try
    {  
    
   
     SqlCommand cmd = new SqlCommand();
     cmd.Transaction = trans;
     //循环
     foreach (DictionaryEntry myDE in SQLStringList)
     { 
      string  cmdText=myDE.Key.ToString();
      SqlParameter[] cmdParms=(SqlParameter[])myDE.Value;

      PrepareCommand(cmd,conn,trans,cmdText, cmdParms);

      int val = cmd.ExecuteNonQuery();
      cmd.Parameters.Clear();                      
      
     }
     trans.Commit();
    }
    catch(System.Data.SqlClient.SqlException E)
    {
     try
     {
      if(trans != null)
      {
       // 回滚事务
       trans.Rollback("SQLTransaction");
      }
     }
     catch (System.Data.SqlClient.SqlException E)
     {
      // 回滚事务失败
      if (trans.Connection != null)
      {
       //       return "执行回滚事务时出现 " + ex.GetType() + " 违例!" + ex.Message;

       throw new Exception(E.Message); 
      }
     }
     //                         return  "数据库命令均未完成,在执行数据库命令时出现 " + e.GetType() + " 违例!" + e.Message;
     throw new Exception(E.Message);  
    }
    finally
    {
     //关闭Connection
     if(conn != null)
     {
      conn.Close();
     }
    }
    
     
   }

   /*
   using (SqlConnection conn =ConnSql())
   {
    conn.Open();
    using (SqlTransaction trans = conn.BeginTransaction())
    {
     SqlCommand cmd = new SqlCommand();
     try
     {
      //循环
      foreach (DictionaryEntry myDE in SQLStringList)
      { 
       string  cmdText=myDE.Key.ToString();
       SqlParameter[] cmdParms=(SqlParameter[])myDE.Value;
       PrepareCommand (cmd,conn,trans,cmdText, cmdParms);
       int val = cmd.ExecuteNonQuery();
       cmd.Parameters.Clear();

       trans.Commit();
      }     
     }
     catch
     {
      trans.Rollback();
      throw;
     }
    }    
   }
   */

  }

protected  void PrepareCommand(SqlCommand cmd,SqlConnection conn,SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
  {
   if (conn.State != ConnectionState.Open)
    conn.Open();
   cmd.Connection = conn;
   cmd.CommandText = cmdText;
   if (trans != null)
    cmd.Transaction = trans;
   cmd.CommandType = CommandType.Text;//cmdType;
   if (cmdParms != null)
   {
    foreach (SqlParameter parm in cmdParms)
     cmd.Parameters.Add(parm);
   }
  }