数据访问层的第二个类(针对sqlserver)

/// <summary>
 /// 提供对数据库的操作服务：SQL语句的执行，存储过程的调用
 /// </summary>
 public abstract class SQLHelper
 {
  private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
  {
   foreach (SqlParameter p in commandParameters)
   {
    if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null))
    {
     p.Value = DBNull.Value;
    }
    
    command.Parameters.Add(p);
   }
  }

  
  private static void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues)
  {
   if ((commandParameters == null) || (parameterValues == null))
   {
    return;
   }

   if (commandParameters.Length != parameterValues.Length)
   {
    throw new ArgumentException("Parameter count does not match Parameter Value count.");
   }

   for (int i = 0, j = commandParameters.Length; i < j; i++)
   {
    commandParameters[i].Value = parameterValues[i];
   }
  }

  private static SQLServer PrepareCommand(SqlCommand cmd,CommandType cmdType, string cmdText, SqlParameter[] intCmdParms,SqlParameter[] outCmdParms)
  {
   SQLServer sqlServer = SQLServer.Instance();
   cmd.Connection = sqlServer.Connection;
   cmd.CommandText = cmdText;
   cmd.CommandType = cmdType;

   if (intCmdParms != null)
   {
    foreach (SqlParameter parm in intCmdParms)
     cmd.Parameters.Add(parm);
   }
   if(outCmdParms != null)
   {
    foreach (SqlParameter parm in outCmdParms)
     cmd.Parameters.Add(parm);
   }
   return sqlServer;
  }

  private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
  /// <summary>
  /// 参数数组存入缓存
  /// </summary>
  /// <param name="cacheKey">键</param>
  /// <param name="cmdParms">值：参数数组</param>
  public static void CacheParameters(string cacheKey, params SqlParameter[] cmdParms)
  {
   parmCache[cacheKey] = cmdParms;
  }

  /// <summary>
  /// 取出参数数组
  /// </summary>
  /// <param name="cacheKey">键</param>
  /// <returns>参数数组</returns>
  public static SqlParameter[] GetCachedParameters(string cacheKey)
  {
   SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey];
   
   if (cachedParms == null)
    return null;
   
   SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length];

   for (int i = 0, j = cachedParms.Length; i < j; i++)
    clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone();

   return clonedParms;
  }

  /// <summary>
  /// 对连接执行 Transact-SQL 语句并返回受影响的行数。
  /// </summary>
  /// <param name="cmdType">指定如何解释命令字符串</param>
  /// <param name="cmdText">T-SQL 命令行 或 存储过程名</param>
  /// <param name="cmdParms">参数数组</param>
  /// <returns>受影响的行数</returns>
  public static int ExecuteNonQuery(CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
  {
   SqlCommand cmd = new SqlCommand();
   SQLServer sqlServer = PrepareCommand(cmd, cmdType, cmdText, cmdParms,null);
   try
   {
    sqlServer.Connection.Open();
    sqlServer.BeginTransaction();
    int val = cmd.ExecuteNonQuery();    
    sqlServer.CommitTransaction();
    cmd.Parameters.Clear();
    return val;
   }
   catch
   {
    sqlServer.RollbackTransaction();
    throw;
   }
   finally
   {
    sqlServer.Connection.Close();
   }
   
  }

  /// <summary>
  /// 生成DataReader
  /// </summary>
  /// <param name="cmdType">指定如何解释命令字符串</param>
  /// <param name="cmdText">T-SQL 命令行 或 存储过程名</param>
  /// <param name="cmdParms">参数数组</param>
  /// <returns>受影响的行数</returns>
  public static SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
  {
   SqlCommand cmd = new SqlCommand();
   SQLServer sqlServer = PrepareCommand(cmd, cmdType, cmdText, cmdParms,null);

   try
   {
    SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
    cmd.Parameters.Clear();
    return dr;
   }
   catch
   {
    sqlServer.Connection.Close();
    throw;
   }
  }

  /// <summary>
  /// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略额外的列或行。
  /// </summary>
  /// <param name="cmdType">指定如何解释命令字符串</param>
  /// <param name="cmdText">T-SQL 命令行 或 存储过程名</param>
  /// <param name="cmdParms">参数数组</param>
  /// <returns>结果集中第一行的第一列</returns>
  public static object ExecuteScalar(CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
  {
   SqlCommand cmd = new SqlCommand();
   SQLServer sqlServer = PrepareCommand(cmd, cmdType, cmdText, cmdParms,null);
   try
   {
    sqlServer.Connection.Open();
    sqlServer.BeginTransaction();
    object val = cmd.ExecuteScalar();    
    sqlServer.CommitTransaction();
    cmd.Parameters.Clear();
    return val;
   }
   catch
   {
    sqlServer.RollbackTransaction();
    throw;
   }
   finally
   {
    sqlServer.Connection.Close();
   }
  }

  /// <summary>
  /// 执行存储过程，带输出参数
  /// </summary>
  /// <param name="StoredProcedure">存储过程名</param>
  /// <param name="outValue">输出值引用</param>
  /// <param name="outCmdParms">输出参数数组</param>
  /// <param name="cmdParms">输入参数数组</param>
  public static void ExecuteStoredProcedure(string StoredProcedure,ref object[] outValue,SqlParameter[] outCmdParms, params SqlParameter[] cmdParms)
  {
   SqlCommand cmd = new SqlCommand();
   SQLServer sqlServer = PrepareCommand(cmd, CommandType.StoredProcedure, StoredProcedure, cmdParms,outCmdParms);
   try
   {
    sqlServer.Connection.Open();
    sqlServer.BeginTransaction();
    cmd.ExecuteNonQuery();   
    sqlServer.CommitTransaction();
    if(outCmdParms != null)
     for(int i=0;i<outCmdParms.Length;i++)
      outValue[i] = outCmdParms[i].Value;
    cmd.Parameters.Clear();
   }
   catch
   {
    sqlServer.RollbackTransaction();
    throw;
   }
   finally
   {
    sqlServer.Connection.Close();
   }
  }

  /// <summary>
  /// 执行存储过程，返回结果集
  /// </summary>
  /// <param name="StoredProcedure">存储过程名</param>
  /// <param name="cmdParms">参数数组</param>
  /// <returns>结果集</returns>
  public static DataTable ExecuteStoredProcedure(string StoredProcedure,params SqlParameter[] cmdParms)
  {
   SqlDataAdapter sda = new SqlDataAdapter();
   SqlCommand cmd = new SqlCommand();
   SQLServer sqlServer = PrepareCommand(cmd, CommandType.StoredProcedure, StoredProcedure, cmdParms,null);
   DataTable dt = new DataTable();
   sda.SelectCommand = cmd;
   try
   {
    sqlServer.Connection.Open();
    sqlServer.BeginTransaction();
    sda.Fill(dt);
    sqlServer.CommitTransaction();
    cmd.Parameters.Clear();
    return dt;
   }
   catch
   {
    sqlServer.RollbackTransaction();
    throw;
   }
   finally
   {
    sqlServer.Connection.Close();
   }
  }