DBHelper:三層架構的進階實效的後台資料庫操作類的寫法或者叫使用者協助類

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Data;
using System.Data.SqlClient;

namespace ISText
{
    /// <summary>
    ///資料庫操作協助類
    /// </summary>
    public static class  DBHelper
    {
        public static readonly string ConnectionString=System.Configuration.ConfigurationManager.AppSetting["webDal"];
        //建立一個反衝參數來儲存sql語句參數
        //返回hashtable的同步封裝
        private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());

        /// <summary>
        /// 執行增，刪，改的資料處理
        /// </summary>
        /// <returns></returns>
        public static int ExecuteNonQuery(string connectionString,CommandType cmdType, string cmdText,params SqlParameter [] commandParameters )
        {
            SqlCommand cmd = new SqlCommand();
            using(SqlConnection conn=new SqlConnection(connectionString))
            {
                //這個表面上是執行sql語句，其實就是附加sql語句參數，和事務，預存程序等參數
                PrepareCommand(cmd, connectionString, null, cmdType, cmdText,commandParameters);
                //這時候才開始真正的執行
                int var = cmd.ExecuteNonQuery();
                return var;
            }
        }
        /// <summary>
        /// 重載執行事務
        /// </summary>
        /// <returns></returns>
        public static int ExecuteNonQuer(SqlTransaction tran,CommandType cmdType, string cmdText,params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(cmd,tran.Connection,tran,cmdType,cmdText,commandParameters);
            int var = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return var;
        }
        /// <summary>
        /// 執行查詢資料的處理
        /// 以前的版本只兩個參數
        /// 現在的版本都是四個參數
        /// </summary>
        /// <returns>資料器讀取對象</returns>
        public static SqlDataReader ExecuteReader(string connectionString,CommandType cmdType,string cmdText,params SqlParameter [] commandParameters )
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = new SqlConnection(connectionString);
            PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
            SqlDataReader sdr=cmd.ExecuteReader(CommandBehavior.CloseConnection)
                cmd.Parameters.Clear();
            return sdr;
            //傳統的則是
            //return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }
        public static object ExecuteScalar(string connString, CommandType cmdType,string cmdText,params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            using(SqlConnection conn=new SqlConnection(connString))
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                object var = cmd.ExecuteScalar();
                return var;
            }
        }
        //把sql語句的參數放到反衝數組中：切忌要放進去
        //sql語句做為key值，來遍曆參數
        public static void CacheParameters(string cacheKey,params SqlParameter[] commandParameters)
        {
            //聲明的數組parmCache
            parmCache[cacheKey] = commandParameters;
        }
        /// <summary>
        /// 開始去參數
        /// 通過sql語句
        /// 去參數要考慮到複製的方法的應用：切忌
        /// </summary>
        /// <returns></returns>
        public static SqlParameter[] GetCacheParameters(string cacheKey)
        {
            //轉換成參數數組的類型
            SqlParameter[] cacheParameter = (SqlParameter [])parmCache[cacheKey];
            if(cacheParameter==null)
            {
                return null;
            }
            SqlParameter [] cloneParams=new SqlParameter[cacheParameter.Length];
            for (int i = 0, j = cacheParameter.Length; i < j; i++)
            {
                //首先將參數轉換成一個複製介面類型:（ICloneable）cacheParameter[i]
                //（ICloneable）cacheParameter[i]
                //在複製
                ((ICloneable)cacheParameter[i]).Clone();
                //最後轉換成一個參數數群組類型的資料
                cloneParams[i] = (SqlParameter[])((ICloneable)cacheParameter[i]).Clone();
                return cloneParams;
            }
        }
        //////////總結複製的步驟：首先明白複製是把參數儲存在反衝中後取出來相當於該參數的複製或者說副本
        ///////////1.聲明一個複製的參數數組，並初始化數組的長度
        ////////////2.遍曆這個數組，不斷的進行複製：其中也有三步
         //第一：複製出來一個參數數組就轉換成一個複製類型的介面類型
         //第二：接下就開始複製，調用clone()複製方法
         //第三：在轉換成最終的參數數群組類型
        /////////////3.返回好複製的數組，即返回從反衝中儲存的參數
        /// <summary>
        /// 執行sql語句
        /// 傳統的sql語句是直接傳過來，之後附加參數
        /// 現在不緊實sql語句傳過來，而且還是單獨寫成一個執行sql語句的方法，在附加參數，同時附加了很多參數條件
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="conn"></param>
        /// <param name="tran"></param>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdParameters"></param>
        public static void PrepareCommand(SqlCommand cmd,SqlConnection conn, SqlTransaction tran,CommandType cmdType, string cmdText, SqlParameter[] cmdParameters)
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if(tran!=null)
            {
                cmd.Transaction = tran;
            }
            cmd.CommandType = cmdType;
            if(cmdParameters!=null)
            {
                foreach(SqlParameter parm in cmdParameters)
                {
                    cmd.Parameters.Add(parm);
                }
            }
        }
    }
}