网站开发

public abstract class SqlHelper
          {
              public static readonly string connectionString = ConfigurationManager.ConnectionStrings["SqlConnString"].ConnectionString;
        
              SqlConnection conn;

              #region open SqlConnection
              public static void Open() {
                   conn = new SqlConnection(connectionString);
                   if (conn.State != ConnectionState.Open)
                      conn.Open();
              }    
              #endregion

              #region close SqlConnection
              public static void Close() {
                  if (conn != null)
                  {
                      conn.Close();
                      conn.Dispose();
                  }
              }        
              #endregion

              #region prepare SqlCommand
              private static void PrepareCommand(SqlCommand cmd, CommandType cmdType, string cmdText, SqlParameter[] cmdParms) {
                  Open();
                  cmd.Connection = conn;
                  cmd.CommandType = cmdType;
                  cmd.CommandText = cmdText;

                  if (cmdParms != null)
                  {
                      foreach (SqlParameter parm in cmdParms)
                          cmd.Parameters.Add(parm);
                  }
              }
              #endregion

              #region parm cache
              /*
               使用一个哈希表来保存缓存的参数 只缓存参数名
               哈希表的特点:一个键对应一个值key对value(为object需要类型转化)        不能出现两个相同的键 否则error
         
               下面的哈希表parmCache定义为static即一次定义全局使用
               所以可能会出现有人在读的时候，有人在写，一般会用Lock就像Asp中用Application["count"]来统计点击数一样
               要先锁后解锁
               但.net框架提供了Synchroized          sync和syncroize中文意思 同步，同时发生
               来提供这一操作
              */
              private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());

              public static void CacheParameters(string cacheKey, params SqlParameter[] commandParameters) {
                  parmCache[cacheKey] = commandParameters;
              }

              /*
              1、为何要克隆呢 为何不直接return cachedParms
         
              有一个参数数组
              SqlParameter[] parms={
              new SqlParameter("@num1",SqlDbType.Int,4),
              new SqlParameter("@num2",SqlDbType.Int,4)
              }
              缓存该数组
              用户a和b都执行插入操作
              a用户插入了1,1
              b用户插入了2,2
              如果不用克隆的话，参数数组只有一份
              而2个用户需要根据不同的情况赋于不同的值
              所以就用了克隆了
        
              2、(ICloneable)cachedParms[i]先将HashTable转为ICloneable这样HashTable就具有了Clone()克隆方法了
              克隆一份后是什么类型呢，，当然要强制转化为(SqlParameter)了
              最后将它赋值给clonedParms[i]
              */       
              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; i < cachedParms.Length; i++)
                      clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone();

                  return clonedParms;
              }
              #endregion        
        
              //below method support sqltext and procedure

              #region ExecuteReader
              /*
               parms的作用，这也是一个知识点
               举例:
               ExecuteReader(*,*,null)成功运行
               ExecuteReader(*,*,new SqlParameter(*))成功运行
               ExecuteReader(*,*,new SqlParameter(*),new SqlParameter(*))成功运行
               ExecuteReader(*,*,{new SqlParameter(*),new SqlParameter(*),})成功运行
               它让参数类型和参数个数任意
               这可给了不是一般的好处，你不必为SqlParameter和SqlParameter[]进行重载，写上两个函数
               又为null写上一个函数，因为null会不明确调用SqlParameter的函数还是SqlParameter[]的函数
               啥你不知道我在说什么，打屁屁,那回去看看c++的函数重载
               */
              public static SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) {
            
                  SqlCommand cmd = new SqlCommand();

                  try {
                      PrepareCommand(cmd, null, cmdType, cmdText, commandParameters);
                      SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                      cmd.Parameters.Clear();
                      return rdr;
                  }
                  catch {
                      Close();
                      throw;
                  }
              }
              #endregion

              #region ExecuteNonQuery
              public static void ExecuteNonQuery(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) {

                  SqlCommand cmd = new SqlCommand();

                  PrepareCommand(cmd, cmdType, cmdText, commandParameters);
                  cmd.ExecuteNonQuery();
                  cmd.Parameters.Clear();

                  Close();
              }
              #endregion

              #region ExecuteScalar
              public static object ExecuteScalar(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) {

                  SqlCommand cmd = new SqlCommand();

                  PrepareCommand(cmd, cmdType, cmdText, commandParameters);
                  object val = cmd.ExecuteScalar();
                  cmd.Parameters.Clear();

                  Close();
                  return val;
              }
              #endregion

          }
}