数据库基类

/********************************
 * Produce: DbHelper
 * Version: beta 1
 * Date: 2012.11.8
 ********************************/

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

/// <summary>
/// Summary description for DBHelper
/// </summary>

namespace Self.DAL.DB
{
    public abstract class DBHelper
    {
        //获取数据库连接字符串，其属于静态变量且只读，项目中所有文档可以直接使用，但不能修改
        public static readonly string ConnectionStringLocalTransaction =
            ConfigurationManager.ConnectionStrings["strConn"].ConnectionString;

        //数据库连接..
        private SqlConnection _conn;
        private SqlCommand _cmd;

        #region "数据库基本语句"
        /// <summary>
        /// 连接数据库并打开连接
        /// </summary>
        /// <param name="strConn">数据库连接字符串</param>
        /// <returns>数据库连接</returns>
        public SqlConnection OpenConn(string strConn)
        {
            try
            {
                //若null值则新建，非null则赋值
                if (_conn == null)
                    _conn = new SqlConnection(strConn);
                else
                    _conn.ConnectionString = strConn;

                //打开
                if (_conn.State == ConnectionState.Closed)
                    _conn.Open();

                return _conn;
            }
            catch (SqlException e)
            {
                //若连接出错，则抛出异常
                throw e;
            }
        }

        /// <summary>
        /// 为执行命令准备参数
        /// </summary>
        /// <param name="conn">已经存在的数据库连接</param>
        /// <param name="trans">数据库事物处理</param>
        /// <param name="cmd">SqlCommand 命令</param>
        /// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>
        /// <param name="sql">Command text，T-SQL语句 例如 Select * from Products</param>
        /// <param name="paras">返回带参数的命令</param>
        protected static void PrepareCommand(SqlConnection conn, SqlTransaction trans, SqlCommand cmd, 
                            CommandType cmdType, string sql, SqlParameter[] paras)
        {
            cmd.Connection = conn;
            cmd.CommandText = sql;

            //判断是否需要事物处理
            if (trans != null)
                cmd.Transaction = trans;

            cmd.CommandType = cmdType;

            if (paras != null)
            {
                foreach (SqlParameter p in paras)
                {
                    if (p != null)
                    {
                        //若para没有赋值则将赋值为DBNull
                        if ((p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Input)
                            && (p.Value == null))
                        {
                            p.Value = DBNull.Value;
                        }
                        cmd.Parameters.Add(p);
                    }
                }
            }
        }


        /// <summary>
        /// 取得参数
        /// </summary>
        /// <param name="ParameterName">参数名称</param>
        /// <param name="dbType">参数类型</param>
        /// <param name="size">参数长度</param>
        /// <param name="direction">参数方向</param>
        /// <param name="value">参数值</param>
        /// <returns>参数</returns>
        public SqlParameter getParameter(string ParameterName, SqlDbType dbType, int size,
                            ParameterDirection direction, object value)
        {
            SqlParameter para = new SqlParameter(ParameterName, dbType, size);
            para.Direction = direction;

            if (value.ToString().Trim().Length != 0)
            para.Value = value;

            return para;
        }

        /// <summary>
        /// 取得参数
        /// </summary>
        /// <param name="ParameterName">参数名称</param>
        /// <param name="dbType">参数类型</param>
        /// <param name="direction">参数方向</param>
        /// <param name="value">参数值</param>
        /// <returns>SqlParameter参数</returns>
        public SqlParameter getParameter(string ParameterName, SqlDbType dbType, 
                                ParameterDirection direction, object value)
        {
            SqlParameter para = new SqlParameter(ParameterName, dbType);
            para.Direction = direction;

            if(value.ToString().Trim().Length != 0)
                para.Value = value;

            return para;
        }


        /// <summary>
        ///  主要用于事物中，执行Sql语句
        /// </summary>
        /// <param name="conn">已经存在的数据库连接</param>
        /// <param name="trans">数据库事物处理</param>
        /// <param name="cmd">SqlCommand 命令</param>
        /// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>
        /// <param name="sql">Command text，T-SQL语句 例如 Select * from Products</param>
        /// <param name="paras">返回带参数的命令</param>
        public int ExecuteNonQuery(SqlConnection conn, SqlTransaction trans, SqlCommand cmd, 
                            CommandType cmdType, string sql, SqlParameter[] paras)
        {
            cmd.Parameters.Clear();
            PrepareCommand(conn, trans, cmd, cmdType, sql, paras);
            int line = cmd.ExecuteNonQuery();
            return line;
        }

        /// <summary>
        /// 主要用于事物中， 执行带返回值的Sql语句
        /// </summary>
        /// <param name="conn">已经存在的数据库连接</param>
        /// <param name="trans">数据库事物处理</param>
        /// <param name="cmd">SqlCommand 命令</param>
        /// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>
        /// <param name="sql">Command text，T-SQL语句 例如 Select * from Products</param>
        /// <param name="paras">返回带参数的命令</param>
        public object ExecuteScalar(SqlConnection conn, SqlTransaction trans, SqlCommand cmd,
                            CommandType cmdType, string sql, SqlParameter[] paras)
        {
            cmd.Parameters.Clear();
            PrepareCommand(conn, trans, cmd, cmdType, sql, paras);
            object obj = cmd.ExecuteScalar();
            return obj;
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        /// <param name="_conn">数据库连接</param>
        public void CloseConn(SqlConnection _conn)
        {
            if ((_conn != null) && (_conn.State == ConnectionState.Open))
                _conn.Close();
        }


        /// <summary>
        /// 检查Sql语句是否合法
        /// </summary>
        /// <param name="sqlStr">Sql语句</param>
        protected virtual void CheckArgs(string sqlStr)
        {
            if (sqlStr == null)
                throw new ArgumentNullException();
            if (sqlStr.Length == 0)
                throw new ArgumentOutOfRangeException();
        }
        #endregion


        /// <summary>
        /// 该函数只是个例子，用于示范如何使用事物来执行Sql语句
        /// </summary>
        /// <param name="dbcon">数据库连接字符串</param>
        /// <param name="sql">Sql语句</param>
        /// <param name="type">命令类型</param>
        /// <param name="paras">SqlParameter数组</param>
        /// <returns>返回影响行数</returns>
        private void ExampleExecuteNonQuery(string dbcon, string sql, CommandType type, SqlParameter[] paras)
        {
            CheckArgs(sql);

            SqlTransaction _trans;
            _conn = this.OpenConn(dbcon);
            _trans = _conn.BeginTransaction();

            try
            {
                _cmd = new SqlCommand();
                this.ExecuteNonQuery(_conn,_trans, _cmd, type, sql, paras);
                _trans.Commit();
            }
            catch (Exception ex)
            {
                _trans.Rollback();
                throw ex;
            }
            finally
            {
                CloseConn(_conn);
            }
        }

        /// <summary>
        /// 执行Sql语句,返回受影响的行数
        /// </summary>
        /// <param name="dbcon">数据库连接字符串</param>
        /// <param name="sql">Sql语句</param>
        /// <param name="type">命令类型</param>
        /// <param name="paras">SqlParameter数组</param>
        /// <returns>返回影响行数</returns>
        public int ExecuteNonQuery(string dbcon, string sql, CommandType type, SqlParameter[] paras)
        {
            CheckArgs(sql);

            try
            {
                _conn = this.OpenConn(dbcon);
                _cmd = new SqlCommand();
                PrepareCommand(_conn, null, _cmd, type, sql, paras);
                int lines = _cmd.ExecuteNonQuery();
                _cmd.Parameters.Clear();
                return lines;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseConn(_conn);
            }
        }

        /// <summary>
        /// 执行Sql语句,返回语句指定参数
        /// </summary>
        /// <param name="dbcon">数据库连接字符串</param>
        /// <param name="sql">Sql语句</param>
        /// <param name="type">命令类型</param>
        /// <param name="paras">SqlParameter数组</param>
        /// <returns>返回指定参数</returns>
        public object ExecuteScalar(string dbcon, string sql, CommandType type, SqlParameter[] paras)
        {
            CheckArgs(sql);
            try
            {
                _conn = this.OpenConn(dbcon);
                _cmd = new SqlCommand();
                PrepareCommand(_conn, null, _cmd, type, sql, paras);
                object obj = _cmd.ExecuteScalar();
                _cmd.Parameters.Clear();
                return obj;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseConn(_conn);
            }          
        }

        /// <summary>
        /// 执行查询语句，数据dataTable的集合
        /// </summary>
        /// <param name="dbcon">数据库连接字符串</param>
        /// <param name="sql">Sql语句</param>
        /// <param name="type">命令类型</param>
        /// <param name="paras">SqlParameter数组</param>
        /// <returns>返回查询表</returns>
        public static DataTable getTable(string dbcon, string sql, CommandType type, SqlParameter[] paras)
        {
            if(sql == null)
                throw new ArgumentNullException();
            if(sql.Length == 0)
                throw new ArgumentOutOfRangeException();

            SqlCommand cmd = new SqlCommand();
            DataSet ds = new DataSet();
            SqlConnection conn = new SqlConnection();

            try
            {
                conn.ConnectionString = dbcon;
                conn.Open();
                PrepareCommand(conn, null, cmd, type, sql, paras);
                SqlDataAdapter adp = new SqlDataAdapter();
                adp.SelectCommand = cmd;
                adp.Fill(ds);

                DataTable table = ds.Tables;
                return table;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        #region "参数缓存"

        // 哈希表用来存储缓存的参数信息，哈希表可以存储任意类型的参数。
        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[] commandParameters)
        {
            parmCache[cacheKey] = commandParameters;
        }


        /// <summary>
        /// 获取被缓存的参数
        /// </summary>
        /// <param name="cacheKey">用于查找参数的KEY值</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++)
                //使用clone方法复制参数列表中的参数
                clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone();

            return clonedParms;
        }

        #endregion
    }
}<pre>