一个操作SqlServer的DBControl类

在这里写了一个操作SqlServer的数据库操作类，如果有问题，请大家指出

 

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

namespace DBControl
{
    public class DBHelp
    {
        #region 基本方法

        //连接字符串
        private string ConnectString
        {
            get
            {
                return System.Configuration.ConfigurationManager.AppSettings["SqlConString"];
            }
        }

        /// <summary>
        /// 新建一个SqlConnection,如果失败则抛出异常
        /// </summary>
        /// <returns>SqlConnection</returns>
        private SqlConnection NewConnection()
        {
            try
            {
                SqlConnection sqlCon = new SqlConnection(ConnectString);
                sqlCon.Open();
                return sqlCon;
            }
            catch
            {
                throw new Exception("创建SqlConnection时出错！");
            }
        }

        /// <summary>
        /// 新建一个SqlCommand,如果失败则抛出异常
        /// </summary>
        /// <param name="cmdText">CommandText</param>
        /// <param name="sqlCon">SqlConnection</param>
        /// <param name="cmdType">CommandType</param>
        /// <param name="parameters">CommandType</param>
        /// <returns>SqlParameter</returns>
        private SqlCommand NewCommand(String cmdText, SqlConnection sqlCon, CommandType cmdType, SqlParameter[] parameters)
        {
            try
            {
                SqlCommand sqlCom = new SqlCommand(cmdText, sqlCon);
                sqlCom.CommandType = cmdType;
                if (parameters != null)
                { sqlCom.Parameters.AddRange(parameters); }
                return sqlCom;
            }
            catch
            {
                throw new Exception("创建SqlCommand时出错！");
            }
        }

        /// <summary>
        /// 新建一个SqlDataAdapter,如果失败则抛出异常
        /// </summary>
        /// <param name="sqlCom">SqlCommand</param>
        /// <returns></returns>
        private SqlDataAdapter NewDataAdapter(SqlCommand sqlCom)
        {
            try
            {
                SqlDataAdapter sqlDadp = new SqlDataAdapter(sqlCom);
                return sqlDadp;
            }
            catch
            {
                throw new Exception("创建SqlDataAdapter时出错！");
            }
        }

        #endregion

        #region 查询用方法

        /// <summary>
        /// 查询数据并返回一个DataSet,适用于包含多条select语句的查询
        /// </summary>
        /// <param name="cmdText">查询语句或者存储过程名称</param>
        /// <param name="cmdType">指定查询类型</param>
        /// <param name="cmdType">参数数组</param>
        /// <returns>一个包含多个查询结果DataSet</returns>
        public DataSet ExecuteDataSet(String cmdText, CommandType cmdType, SqlParameter[] parameters)
        {
            DataSet set = new DataSet();
            using (SqlConnection sqlCon = NewConnection())
            {
                if (sqlCon.State == ConnectionState.Closed)
                { sqlCon.Open(); }
                using (SqlCommand sqlCom = NewCommand(cmdText, sqlCon, cmdType, parameters))
                {
                    using (SqlDataAdapter sqlDadp = NewDataAdapter(sqlCom))
                    {
                        sqlDadp.Fill(set);
                    }
                }
            }
            return set;
        }

        /// <summary>
        /// 查询数据并返回一个DataSet,适用于包含多条select语句的查询
        /// </summary>
        /// <param name="cmdText">查询语句或者存储过程名称</param>
        /// <param name="cmdType">指定查询类型</param>
        /// <returns>一个包含多个查询结果DataSet</returns>
        public DataSet ExecuteDataSet(String cmdText, CommandType cmdType)
        {
            if (cmdType == CommandType.StoredProcedure)
            {
                DataSet set = (DataSet)DBCache.GetCache(cmdText);
                if (set == null)
                {
                    set = ExecuteDataSet(cmdText, cmdType, null);
                    DBCache.SetCache(cmdText, set);
                }
                return set;
            }
            else
            {
                return ExecuteDataSet(cmdText, cmdType, null);
            }
        }

        /// <summary>
        /// 查询数据并返回一个DataTable
        /// </summary>
        /// <param name="cmdText">查询语句或者存储过程名称</param>
        /// <param name="cmdType">指定查询类型</param>
        /// <param name="cmdType">参数数组</param>
        /// <returns>DataTable</returns>
        public DataTable ExecuteDataTable(String cmdText, CommandType cmdType, SqlParameter[] parameters)
        {
            return ExecuteDataSet(cmdText,cmdType,parameters).Tables[0];
        }

        /// <summary>
        /// 查询数据并返回一个DataTable
        /// </summary>
        /// <param name="cmdText">查询语句或者存储过程名称</param>
        /// <param name="cmdType">指定查询类型</param>
        /// <param name="cmdType">参数数组</param>
        /// <returns>DataTable</returns>
        public DataTable ExecuteDataTable(String cmdText, CommandType cmdType)
        {
            if (cmdType == CommandType.StoredProcedure)
            {
                DataTable table = (DataTable)DBCache.GetCache(cmdText);
                if (table == null)
                {
                    table = ExecuteDataTable(cmdText, cmdType, null);
                    DBCache.SetCache(cmdText, table);
                }
                return table;
            }
            else
            {
                return ExecuteDataTable(cmdText, cmdType, null);
            }
        }

        /// <summary>
        /// 查询数据并返回一个DataRow
        /// </summary>
        /// <param name="cmdText">查询语句或者存储过程名称</param>
        /// <param name="cmdType">指定查询类型</param>
        /// <param name="cmdType">参数数组</param>
        /// <returns>DataRow</returns>
        public DataRow ExecuteDataRow(String cmdText, CommandType cmdType, SqlParameter[] parameters)
        {
            return ExecuteDataSet(cmdText, cmdType, parameters).Tables[0].Rows[0];
        }

        /// <summary>
        /// 查询数据并返回一个DataRow
        /// </summary>
        /// <param name="cmdText">查询语句或者存储过程名称</param>
        /// <param name="cmdType">指定查询类型</param>
        /// <returns>DataRow</returns>
        public DataRow ExecuteDataRow(String cmdText, CommandType cmdType)
        {
            if (cmdType == CommandType.StoredProcedure)
            {
                DataRow row = (DataRow)DBCache.GetCache(cmdText);
                if (row == null)
                {
                    row = ExecuteDataRow(cmdText, cmdType, null);
                    DBCache.SetCache(cmdText, row);
                }
                return row;
                
            }
            else
            {
                return ExecuteDataRow(cmdText, cmdType, null);
            }
        }

        /// <summary>
        /// 查询数据并返回第一个数据
        /// </summary>
        /// <param name="cmdText">查询语句或者存储过程名称</param>
        /// <param name="cmdType">指定查询类型</param>
        /// <param name="parameters">参数数组</param>
        /// <returns>Object</returns>
        public Object ExecuteScalar(String cmdText, CommandType cmdType, SqlParameter[] parameters)
        {
            Object obj = new Object();
            using (SqlConnection sqlCon = NewConnection())
            {
                if (sqlCon.State == ConnectionState.Closed)
                { sqlCon.Open(); }
                using (SqlCommand sqlCom = NewCommand(cmdText, sqlCon, cmdType, parameters))
                {
                    obj = sqlCom.ExecuteScalar();
                }
            }
            return obj;
        }

        /// <summary>
        /// 查询数据并返回第一个数据
        /// </summary>
        /// <param name="cmdText">查询语句或者存储过程名称</param>
        /// <param name="cmdType">指定查询类型</param>
        /// <returns>Object</returns>
        public Object ExecuteScalar(String cmdText, CommandType cmdType)
        {
            if (cmdType == CommandType.StoredProcedure)
            {
                Object obj = DBCache.GetCache(cmdText);
                if (obj == null)
                {
                    obj = ExecuteScalar(cmdText, cmdType, null);
                    DBCache.SetCache(cmdText, obj);
                }
                return obj;
            }
            else
            {
                return ExecuteScalar(cmdText, cmdType, null);
            }
        }

        #endregion

        #region 新增、删除、更新用方法

        /// <summary>
        /// 返回执行是否成功，适用于新增、删除、更新操作
        /// </summary>
        /// <param name="cmdText">查询语句或者存储过程名称</param>
        /// <param name="cmdType">指定查询类型</param>
        /// <param name="parameters">参数数组</param>
        /// <returns>true表示执行成功,false表示执行失败</returns>
        public bool ExecuteBool(String cmdText, CommandType cmdType, SqlParameter[] parameters)
        {
            bool b = false;
            using (SqlConnection sqlCon = NewConnection())
            {
                if (sqlCon.State == ConnectionState.Closed)
                { sqlCon.Open(); }
                using (SqlCommand sqlCom = NewCommand(cmdText, sqlCon, cmdType, parameters))
                {
                    b = sqlCom.ExecuteNonQuery() > 0 ? true : false;
                }
            }
            return b;
        }

        /// <summary>
        /// 返回执行是否成功，适用于新增、删除、更新操作
        /// </summary>
        /// <param name="cmdText">查询语句或者存储过程名称</param>
        /// <param name="cmdType">指定查询类型</param>
        /// <returns>true表示执行成功,false表示执行失败</returns>
        public bool ExecuteBool(String cmdText, CommandType cmdType)
        {
            return ExecuteBool(cmdText, cmdType, null);
        }
        
        #endregion
    }
}

 

这个操作类是基于缓存的，在进行大数据量或多用户进行查询数据的时候会有帮助

下面就是缓存类

 

using System;
using System.Collections.Generic;
using System.Text;
using System.Web;
using System.Web.Caching;


namespace DBControl
{
    /// <summary>
    /// 缓存
    /// </summary>
    public sealed class DBCache
    {
        private static Cache myCache = HttpContext.Current.Cache;

        /// <summary>
        /// 将数据以键值对的形式存入缓存
        /// </summary>
        /// <param name="Key">键</param>
        /// <param name="Value">值</param>
        public static void SetCache(String Key, Object Value)
        {
            myCache.Insert(Key, Value);
        }

        /// <summary>
        /// 根据键在缓存中寻找以键值对的形式存入的值
        /// </summary>
        /// <param name="Key">键</param>
        /// <returns>值</returns>
        public static Object GetCache(String Key)
        {
            return myCache.Get(Key);
        }

        /// <summary>
        /// 根据键在缓存中清除以键值对的形式存入的值
        /// </summary>
        /// <param name="Key">键</param>
        public static void RemoveCache(String Key)
        {
            myCache.Remove(Key);
        }
    }
}