網站開發

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

          }
}