Sql Server的ADO.NET访问类

/**********************************************************************************************************************************
 * 开发者：丁丁
 * 网址：http://bbs.prana.cn/
 * 博客：http://blog.csdn.net/greenvc/
 * 日期：2009年12月4日
 * 说明：数据库操作类
**********************************************************************************************************************************/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;

namespace Dingy.Data
{
    /// <summary>
    /// SqlServer帮助类
    /// </summary>
    public class SqlDB
    {
        #region 参数定义

        /// <summary>
        /// 数据库链接字符串，需要在配置文件中提供如下节点内容
        /// <connectionStrings>
        ///        <add name="SqlConn" connectionString="Server=数据库服务器名称或地址;Database=数据库名称;User ID=数据库用户;pwd=密码;"/>
        /// </connectionStrings>
        /// </summary>
        protected readonly string SqlConnString = System.Configuration.ConfigurationManager.ConnectionStrings["SqlConn"].ConnectionString;

        /// <summary>
        /// 数据库链接对象
        /// </summary>
        private SqlConnection sqlConnection;

        /// <summary>
        /// 数据库事务对象
        /// </summary>
        private SqlTransaction sqlTransaction;

        #endregion

        /// <summary>
        /// 构造函数,创建数据库连接
        /// </summary>
        public SqlDB()
        {
            if (sqlConnection == null)
            {
                sqlConnection = new SqlConnection(SqlConnString);
                sqlConnection.Open();
            }
        }

        #region 事务处理

        public SqlTransaction SqlTran
        {
            get
            {
                return sqlTransaction;
            }
            set
            {
                sqlTransaction = value;
            }
        }

        /// <summary>
        /// 打开数据库链接
        /// </summary>
        public void OpenConnection()
        {
            if (sqlConnection != null && sqlConnection.State != ConnectionState.Open)
            {
                sqlConnection.Open();
            }
        }

        /// <summary>
        /// 关闭数据库链接
        /// </summary>
        public void CloseConnection()
        {
            if (sqlConnection != null && sqlConnection.State == ConnectionState.Open)
            {
                sqlConnection.Close();
            }
        }

        /// <summary>
        /// 开始一个数据库事务
        /// </summary>
        public void BeginTransaction()
        {
            if (sqlConnection.State == ConnectionState.Closed)
            {
                sqlConnection.Open();
            }

            try
            {
                SqlTran = sqlConnection.BeginTransaction();
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.StackTrace);
            }
            catch (Exception e)
            {
                throw new Exception(e.StackTrace);
            }
        }

        /// <summary>
        /// 开始一个数据库事务
        /// </summary>
        /// <param name="aIsolationLevel">连接的事务锁定行为</param>
        public void BeginTransaction(IsolationLevel isolationLevel)
        {
            try
            {
                SqlTran = sqlConnection.BeginTransaction(isolationLevel);
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.StackTrace);
            }
            catch (Exception e)
            {
                throw new Exception(e.StackTrace);
            }
        }

        /// <summary>
        /// 提交一个数据库事务
        /// </summary>
        public void CommitTransaction()
        {
            try
            {
                sqlTransaction.Commit();
                CloseConnection();
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.StackTrace);
            }
            catch (Exception e)
            {
                throw new Exception(e.StackTrace);
            }
        }

        /// <summary>
        /// 回退一个数据库事务
        /// </summary>
        public void RollBackTransaction()
        {
            try
            {
                SqlTran.Rollback();
                CloseConnection();

            }
            catch (SqlException ex)
            {
                throw new Exception(ex.StackTrace);
            }
            catch (Exception e)
            {
                throw new Exception(e.StackTrace);
            }
        }

        #endregion

        #region 标准操作

        /// <summary>
        /// 执行Sql语句返回SqlDataReader
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="parameters">参数集合</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandBehavior">查询影响说明</param>
        /// <returns>满足条件的SqlDataReader</returns>
        public SqlDataReader ExecuteDataReader(string Sql, List<SqlParameter> parameters, CommandType commandType, CommandBehavior commandBehavior)
        {
            return CreateSqlCommand(Sql, parameters, commandType).ExecuteReader(commandBehavior);
        }

        /// <summary>
        /// 执行Sql语句返回SqlDataReader
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="parameters">参数集合</param>
        /// <param name="commandType">命令类型</param>
        /// <returns>满足条件的SqlDataReader</returns>
        public SqlDataReader ExecuteDataReader(string Sql, List<SqlParameter> parameters, CommandType commandType)
        {
            return ExecuteDataReader(Sql, parameters, commandType, CommandBehavior.Default);
        }

        /// <summary>
        /// 执行Sql语句返回SqlDataReader
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="parameters">参数集合</param>
        /// <returns>满足条件的SqlDataReader</returns>
        public SqlDataReader ExecuteDataReader(string Sql, List<SqlParameter> parameters)
        {
            return ExecuteDataReader(Sql, parameters, CommandType.Text, CommandBehavior.Default);
        }

        /// <summary>
        /// 执行Sql语句返回SqlDataReader
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <returns>满足条件的SqlDataReader</returns>
        public SqlDataReader ExecuteDataReader(string Sql)
        {
            return ExecuteDataReader(Sql, null, CommandType.Text, CommandBehavior.Default);
        }

        /// <summary>
        /// 执行Sql语句返回单个对象
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="parameters">参数集合</param>
        /// <param name="commandType">命令类型</param>
        /// <returns>满足条件的单个对象</returns>
        public object ExecuteScalar(string Sql, List<SqlParameter> parameters, CommandType commandType)
        {
            return CreateSqlCommand(Sql, parameters, commandType).ExecuteScalar();
        }

        /// <summary>
        /// 执行Sql语句返回单个对象
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="parameters">参数集合</param>
        /// <returns>满足条件的单个对象</returns>
        public object ExecuteScalar(string Sql, List<SqlParameter> parameters)
        {
            return ExecuteScalar(Sql, parameters, CommandType.Text);
        }

        /// <summary>
        /// 执行Sql语句返回单个对象
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <returns>满足条件的单个对象</returns>
        public object ExecuteScalar(string Sql)
        {
            return ExecuteScalar(Sql, null, CommandType.Text);
        }

        /// <summary>
        /// 执行非查询类的Sql语句
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="parameters">参数集合</param>
        /// <param name="commandType">命令类型</param>
        /// <returns>成功执行所影响的记录数</returns>
        public int ExecuteNonQuery(string Sql, List<SqlParameter> parameters, CommandType commandType)
        {
            return CreateSqlCommand(Sql, parameters, commandType).ExecuteNonQuery();
        }

        /// <summary>
        /// 执行非查询类的Sql语句z
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="parameters">参数集合</param>
        /// <returns>成功执行所影响的记录数</returns>
        public int ExecuteNonQuery(string Sql, List<SqlParameter> parameters)
        {
            return ExecuteNonQuery(Sql, parameters, CommandType.Text);
        }

        /// <summary>
        /// 执行非查询类的Sql语句
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <returns>成功执行所影响的记录数</returns>
        public int ExecuteNonQuery(string Sql)
        {
            return ExecuteNonQuery(Sql, null, CommandType.Text);
        }

        /// <summary>
        /// 执行Sql语句返回DataTable
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="parameters">参数集合</param>
        /// <param name="commandType">命令类型</param>
        /// <returns>DataTable</returns>
        public DataTable ExecuteDataTable(string Sql, List<SqlParameter> sqlParameters, CommandType commandType)
        {
            SqlDataAdapter dataAdapter = new SqlDataAdapter(CreateSqlCommand(Sql, sqlParameters, commandType));
            DataTable dataTable = new DataTable();
            dataAdapter.Fill(dataTable);
            return dataTable;
        }

        /// <summary>
        /// 执行Sql语句返回DataTable
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="parameters">参数集合</param>
        /// <returns>DataTable</returns>
        public DataTable ExecuteDataTable(string Sql, List<SqlParameter> sqlParameters)
        {
            return ExecuteDataTable(Sql, sqlParameters, CommandType.Text);
        }

        /// <summary>
        /// 执行Sql语句返回DataTable
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <returns>DataTable</returns>
        public DataTable ExecuteDataTable(string Sql)
        {
            return ExecuteDataTable(Sql, null, CommandType.Text);
        }

        /// <summary>
        /// 执行Sql语句返回DataSet
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="parameters">参数集合</param>
        /// <param name="commandType">命令类型</param>
        /// <returns>DataSet</returns>
        public DataSet ExecuteDataSet(string Sql, List<SqlParameter> sqlParameters, CommandType commandType)
        {
            SqlDataAdapter dataAdapter = new SqlDataAdapter(CreateSqlCommand(Sql, sqlParameters, commandType));
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet);
            return dataSet;
        }

        /// <summary>
        /// 执行Sql语句返回DataSet
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="parameters">参数集合</param>
        /// <returns>DataSet</returns>
        public DataSet ExecuteDataSet(string Sql, List<SqlParameter> sqlParameters)
        {
            return ExecuteDataSet(Sql, sqlParameters, CommandType.Text);
        }

        /// <summary>
        /// 执行Sql语句返回DataSet
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <returns>DataSet</returns>
        public DataSet ExecuteDataSet(string Sql)
        {
            return ExecuteDataSet(Sql, null, CommandType.Text);
        }

        /// <summary>
        /// 执行查询，返回查询的分页结果数据集
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="parameters">参数集合</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="PageIndex">页索引</param>
        /// <param name="PageSize">每页的记录数</param>
        /// <returns>分页结果数据集</returns>
        public DataSet ExecuteDataSet(string Sql, List<SqlParameter> sqlParameters, CommandType commandType, int PageIndex, int PageSize)
        {
            //设置导入的起始地址
            int firstPage = PageIndex * PageSize;

            SqlDataAdapter dataAdapter = new SqlDataAdapter(CreateSqlCommand(Sql, sqlParameters, commandType));
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, firstPage, PageSize, "PranaTable");

            return dataSet;
        }

        /// <summary>
        /// 执行查询，返回查询的分页结果数据集
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="parameters">参数集合</param>
        /// <param name="PageIndex">页索引</param>
        /// <param name="PageSize">每页的记录数</param>
        /// <returns>分页结果数据集</returns>
        public DataSet ExecuteDataSet(string Sql, List<SqlParameter> sqlParameters, int PageIndex, int PageSize)
        {
            return ExecuteDataSet(Sql, sqlParameters, CommandType.Text, PageIndex, PageSize);
        }

        /// <summary>
        /// 执行查询，返回查询的分页结果数据集
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="PageIndex">页索引</param>
        /// <param name="PageSize">每页的记录数</param>
        /// <returns>分页结果数据集</returns>
        public DataSet ExecuteDataSet(string Sql, int PageIndex, int PageSize)
        {
            return ExecuteDataSet(Sql, null, CommandType.Text, PageIndex, PageSize);
        }

        /// <summary>
        /// 创建Sql命令对象
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="parameters">参数集合</param>
        /// <param name="commandType">命令类型</param>
        /// <returns>成功创建的SqlCommand对象</returns>
        private SqlCommand CreateSqlCommand(string Sql, List<SqlParameter> parameters, CommandType commandType)
        {
            SqlCommand sqlCommand = new SqlCommand(Sql, sqlConnection, SqlTran);
            sqlCommand.CommandType = commandType;
            if (parameters != null)
            {
                foreach (SqlParameter parameter in parameters)
                {
                    sqlCommand.Parameters.Add(parameter);
                }
            }
            return sqlCommand;
        }

        #endregion

        #region 缓存操作

        /// <summary>
        /// Hashtable中缓存的参数
        /// </summary>
        private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());

        /// <summary>
        /// 缓存查询的OleDb参数对象.
        /// </summary>
        /// <param name="cacheKey"></param>
        /// <param name="cmdParms"></param>
        public void CacheParameters(string cacheKey, params SqlParameter[] cmdParms)
        {
            parmCache[cacheKey] = cmdParms;
        }

        /// <summary>
        /// 从缓存获取指定的参数对象数组.
        /// </summary>
        /// <param name="cacheKey"></param>
        /// <returns></returns>
        public 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++)
            {
                clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone();
            }

            return clonedParms;
        }

        #endregion

        #region 增删改查

        private static bool haveTransaction = false;

        public static bool HaveTransaction
        {
            get
            {
                return haveTransaction;
            }
            set
            {
                haveTransaction = value;
            }
        }

        /// <summary>
        /// 查询字符串的默认长度
        /// </summary>
        private const int defaultCommandTextLength = 256;

        /// <summary>
        /// 根据表名等参数创建数据库查询语句
        /// </summary>
        /// <param name="tableName">表名称</param>
        /// <param name="topNum">返回的记录数</param>
        /// <param name="field">字段名称</param>
        /// <param name="condition">条件</param>
        /// <param name="order">排序字段</param>
        /// <returns>数据库查询语句</returns>
        private StringBuilder CreateSqlCommandText(string tableName, int topNum, string field, string condition, string order)
        {
            StringBuilder Sql = new StringBuilder(defaultCommandTextLength);
            Sql.Append("SELECT ");
            if (topNum > 0)
            {
                Sql.AppendFormat("TOP {0} ", topNum);
            }
            Sql.AppendFormat("{0} FROM {1} ", field, tableName);
            if (condition != null && condition.Trim().Length != 0)
            {
                Sql.AppendFormat("WHERE {0} ", condition);
            }
            if (order != null && order.Trim().Length != 0)
            {
                Sql.AppendFormat(" ORDER BY {0}", order);
            }
            return Sql;
        }

        /// <summary>
        /// 向表中插入数据
        /// </summary>
        /// <param name="tableName">表名称</param>
        /// <param name="field">字段名称字符串</param>
        /// <param name="fieldValue">字段值参数字符串</param>
        /// <param name="parameters">字段参数集合</param>
        /// <returns>成功插入的记录数</returns>
        public int Insert(string tableName, string field, string fieldValue, List<SqlParameter> parameters)
        {
            try
            {
                if (HaveTransaction == false)
                {
                    OpenConnection();
                }
                StringBuilder Sql = new StringBuilder(defaultCommandTextLength);
                Sql.AppendFormat("INSERT INTO {0}({1}) VALUES({2});", tableName, field,
                    fieldValue);
                return ExecuteNonQuery(Sql.ToString(), parameters);
            }
            finally
            {
                if (HaveTransaction == false)
                {
                    CloseConnection();
                }
            }

        }

        /// <summary>
        /// 从表中删除数据
        /// </summary>
        /// <param name="tableName">表名称</param>
        /// <param name="condition">删除条件</param>
        /// <param name="parameters">删除条件参数集合</param>
        /// <returns>成功删除的记录数</returns>
        public int Delete(string tableName, string condition, List<SqlParameter> parameters)
        {
            try
            {
                if (HaveTransaction == false)
                {
                    OpenConnection();
                }
                StringBuilder Sql = new StringBuilder(defaultCommandTextLength);
                Sql.AppendFormat("DELETE FROM {0} ", tableName);
                if (condition != null && condition.Trim().Length != 0)
                {
                    Sql.AppendFormat("WHERE {0}", condition);
                }
                return ExecuteNonQuery(Sql.ToString(), parameters);
            }
            finally
            {
                if (HaveTransaction == false)
                {
                    CloseConnection();
                }
            }
        }

        /// <summary>
        /// 根据条件修改表中的数据
        /// </summary>
        /// <param name="tableName">表名称</param>
        /// <param name="fieldValue">字段名城</param>
        /// <param name="condition">修改条件</param>
        /// <param name="parameters">修改参数集合</param>
        /// <returns>成功修改的记录数</returns>
        public int Update(string tableName, string fieldValue, string condition, List<SqlParameter> parameters)
        {
            try
            {
                if (HaveTransaction == false)
                {
                    OpenConnection();
                }
                StringBuilder Sql = new StringBuilder(defaultCommandTextLength);
                Sql.AppendFormat("UPDATE {0} SET {1} ", tableName, fieldValue);
                if (condition != null && condition.Trim().Length != 0)
                {
                    Sql.AppendFormat("WHERE {0}", condition);
                }
                return ExecuteNonQuery(Sql.ToString(), parameters);
            }
            finally
            {
                if (HaveTransaction == false)
                {
                    CloseConnection();
                }
            }
        }

        #endregion
    }
}