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);
                }
            }
        }
    }
}