数据库操作类

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.OracleClient;
using System.Data.Odbc;
using System.Collections;

namespace ClassLibrary
{
    public static class DBHelper
    {
        #region 字段
        public static DbConnection _conn;        //定义连接对象
        private static DbCommand _cmd;            //定义命令对象
        private static DbParameter[] _paras;      //定义参数列表
        public static string _connString;         //定义连接字符串
        private static string _dbType;            //定义数据库类型
        #endregion

        #region 构造函数
        /// <summary>
        /// 构造函数
        /// </summary>
        static DBHelper()
        {
            _dbType = GetDbType();
            _connString = GetConnString();         
          
            switch (_dbType)
            {
                case "sql":
                              _conn = new SqlConnection();
                              _cmd = new SqlCommand();
                              break;
                case "oledb":
                              _conn = new OleDbConnection();
                              _cmd = new OleDbCommand();
                              break;
                case "oracle":
                              _conn = new OracleConnection();
                              _cmd = new OracleCommand();
                              break;
            }
       
        }
        #endregion

        #region 连接设置
        /// <summary>
        ///  打开连接
        /// </summary>
        /// <param name="conn">连接对象</param>
        public static void OpenConnection(DbConnection conn)
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            else if (conn.State == ConnectionState.Broken)
            {
                CloseConnection(conn);
                conn.Open();
            }
        }

        /// <summary>
        /// 关闭连接
        /// </summary>
        /// <param name="conn">连接对象</param>
        public static void CloseConnection(DbConnection conn)
        {
            conn.Close();
        }
        #endregion

        #region 获得数据库连接对象,命令对象和参数对象

        /// <summary>
        /// 获得数据库类型
        /// </summary>
        public static string GetDbType()
        {
            return System.Configuration.ConfigurationSettings.AppSettings.AllKeys[0].ToString();
        }

        /// <summary>
        /// 获得连接字符串
        /// </summary>
        public static string GetConnString()
        {
            try
            {
                return System.Configuration.ConfigurationSettings.AppSettings[_dbType].ToString();
            }
            catch(Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 获得连接对象
        /// </summary>
        /// <param name="connStr">连接字符串</param>
        public static void GetDbConnection(string connStr)
        {
            _conn.ConnectionString = connStr;
        }

        /// <summary>
        /// 获得存储过程参数
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="spName">存储过程名</param>
        /// <returns>参数列表</returns>
        public static DbParameter[] GetSpParameterSet(string connectionString, string spName)
        {
            return GetSpParameterSet(connectionString, spName, false);
        }
       
        /// <summary>
        /// 获得存储过程参数
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="spName">存储过程名</param>
        /// <param name="includeReturnValueParameter">是否存在返回参数</param>
        /// <returns>参数列表</returns>
        public static DbParameter[] GetSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter)
        {
            Hashtable paramCache = Hashtable.Synchronized(new Hashtable());
            string hashKey = connectionString + ":" + spName + (includeReturnValueParameter ? ":include ReturnValue Parameter" : "");

            _paras = (DbParameter[])paramCache[hashKey];

            if (_paras == null)
            {
                _paras = (DbParameter[])(paramCache[hashKey] = DiscoverSpParameterSet(connectionString, spName, includeReturnValueParameter));
            }

            return CloneParameters(_paras);
        }

        /// <summary>
        /// 查找出参数
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="spName">存储过程名</param>
        /// <param name="includeReturnValueParameter">是否包含返回参数</param>
        /// <returns>参数列表</returns>
        public static DbParameter[] DiscoverSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter)
        {
            //获得连接对象
            GetDbConnection(_connString);
            //打开连接
            OpenConnection(_conn);
            //设置命令对象属性
            _cmd.CommandText = spName;
            _cmd.Connection = _conn;
            _cmd.CommandType = CommandType.StoredProcedure;
            //根据数据库类型来确定命令创建器和参数列表的实例化对象
           
            switch (_dbType)
            {
                case "sql":
                    SqlCommandBuilder.DeriveParameters((SqlCommand)_cmd);
                    break;
                case "oledb":
                    OleDbCommandBuilder.DeriveParameters((OleDbCommand)_cmd);
                    break;
                case "oracle":
                    OracleCommandBuilder.DeriveParameters((OracleCommand)_cmd);
                    break;
            }

            if (!includeReturnValueParameter && _dbType != "oracle")
            {
                _cmd.Parameters.RemoveAt(0);
            }
           
            switch (_dbType)
            {
                case "sql":
                    _paras = new SqlParameter[_cmd.Parameters.Count];
                    break;
                case "oledb":
                    _paras = new OleDbParameter[_cmd.Parameters.Count];
                    break;
                case "oracle":
                    _paras = new OracleParameter[_cmd.Parameters.Count];
                    break;
            }
            //赋值参数列表到命令参数列表中
            _cmd.Parameters.CopyTo(_paras, 0);
            //关闭连接
            CloseConnection(_conn);
            return _paras;
        }

        /// <summary>
        /// 复制参数
        /// </summary>
        /// <param name="originalParameters">参数源</param>
        /// <returns>目标参数</returns>
        public static DbParameter[] CloneParameters(DbParameter[] originalParameters)
        {
            //根据数据库类型来确定具体的参数对象,将源参数列表copy到新参数列表中
            switch (_dbType)
            {
                case "sql":
                    _paras = new SqlParameter[originalParameters.Length];

                    for (int i = 0, j = originalParameters.Length; i < j; i++)
                    {
                        _paras[i] = (SqlParameter)((ICloneable)originalParameters[i]).Clone();
                    }
                    break;
                case "oledb":
                    _paras = new OleDbParameter[originalParameters.Length];

                    for (int i = 0, j = originalParameters.Length; i < j; i++)
                    {
                        _paras[i] = (OleDbParameter)((ICloneable)originalParameters[i]).Clone();
                    }
                    break;
                case "oracle":
                    _paras = new OracleParameter[originalParameters.Length];

                    for (int i = 0, j = originalParameters.Length; i < j; i++)
                    {
                        _paras[i] = (OracleParameter)((ICloneable)originalParameters[i]).Clone();
                    }
                    break;

            }
        
            return _paras;
        }

        /// <summary>
        /// 匹配参数列表的值
        /// </summary>
        /// <param name="commandParameters">命令参数</param>
        /// <param name="parameterValues">参数值</param>
        public static void AssignParameterValues(DbParameter[] commandParameters, object[] parameterValues)
        {
            //判断命令参数列表和参数值列表是否为空
            //任一个为空就返回不操作,否则继续执行下一步
            if ((commandParameters == null) || (parameterValues == null))
            {
                return;
            }
            //判断命令参数列表的长度是否和参数值列表的长度匹配,如果不匹配就提示错误信息
            if (commandParameters.Length != parameterValues.Length)
            {
                throw new ArgumentException("参数定义和给出的值不匹配.");
            }
            //遍历参数列表,将参数值依次赋值给命令参数
            for (int i = 0, j = commandParameters.Length; i < j; i++)
            {
                commandParameters[i].Value = parameterValues[i];
            }
        }

        /// <summary>
        /// 设置数据库命令对象
        /// </summary>
        /// <param name="command">命令对象</param>
        /// <param name="connection">连接对象</param>
        /// <param name="transaction">事务对象</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令文本</param>
        /// <param name="commandParameters">命令参数</param>
        public static void PrepareCommand(DbCommand command, DbConnection connection, DbTransaction transaction, CommandType commandType, string commandText, DbParameter[] commandParameters)
        {
            //判断连接状态是否为打开
            //如果不是,则重新打开连接
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }
            //设置命令对象的连接属性
            command.Connection = connection;
            //设置命令对象的命令文本
            command.CommandText = commandText;
            //判断事务是否为空,如果不为空,则设置命令对象的事务属性
            if (transaction != null)
            {
                command.Transaction = transaction;
            }
            //设置命令对象的命令类型
            command.CommandType = commandType;
            //判断命令参数列表是否为空,不为空则把参数添加到命令对象中
            if (commandParameters != null)
            {
                AttachParameters(command, commandParameters);
            }

            return;
        }

        /// <summary>
        /// 设置命令对象的参数
        /// </summary>
        /// <param name="command">命令对象</param>
        /// <param name="commandParameters">参数对象</param>
        public static void AttachParameters(DbCommand command, DbParameter[] commandParameters)
        {
            //清空命令参数
            command.Parameters.Clear();

            //遍历参数列表,依次将参数添加到命令对象中
            foreach (DbParameter p in commandParameters)
            {
                //判断参数列表的项,如果为输出参数,则赋值为空
                if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null))
                {
                    p.Value = DBNull.Value;
                }
              
                //添加命令参数
                command.Parameters.Add(p);
            }
        }

        #endregion

        #region 数据操作
       
        /// <summary>
        /// 执行 UPDATE、INSERT 和 DELETE 语句，返回值为该命令所影响的行数
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令文本</param>
        /// <returns>int</returns>
        public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText)
        {
            return ExecuteNonQuery(connectionString, commandType, commandText, (DbParameter[])null);
        }

        /// <summary>
        /// 执行 UPDATE、INSERT 和 DELETE 语句，返回值为该命令所影响的行数
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令文本</param>
        /// <param name="commandParameters">命令参数</param>
        /// <returns>int</returns>
        public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, DbParameter[] commandParameters)
        {
            //清空命令参数
            _cmd.Parameters.Clear();
            //获得连接对象
            GetDbConnection(connectionString);
            //打开连接
            OpenConnection(_conn);
            //设置命令对象属性
            PrepareCommand(_cmd, _conn, (DbTransaction)null, commandType, commandText, commandParameters);
            //执行命令文本将结果保存到 retval 中
            int retval = _cmd.ExecuteNonQuery();
            //清空命令参数
            _cmd.Parameters.Clear();
            //关闭连接
            CloseConnection(_conn);
            return retval;
        }

        /// <summary>
        /// 执行 UPDATE、INSERT 和 DELETE 语句，返回值为该命令所影响的行数
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="spName">存储过程名</param>
        /// <param name="parameterValues">参数值</param>
        /// <returns>int</returns>
        public static int ExecuteNonQuery(string connectionString, string spName, params object[] parameterValues)
        {
            //判断参数值列表是否为空,如果为空,则调用没有参数列表的函数,
            //否则把参数值赋给参数列表,然后执行带参数列表的函数
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                //获得命令文本的参数列表
                _paras = GetSpParameterSet(connectionString, spName);
                //把参数值赋给参数列表
                AssignParameterValues(_paras, parameterValues);

                return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, _paras);
            }
            else
            {
                return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary>
        /// 执行 UPDATE、INSERT 和 DELETE 语句，返回值为该命令所影响的行数
        /// </summary>
        /// <param name="transaction">事务</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令文本</param>
        /// <returns>int</returns>
        public static int ExecuteNonQuery(DbTransaction transaction, CommandType commandType, string commandText)
        {
            return ExecuteNonQuery(transaction, commandType, commandText, (DbParameter[])null);
        }

        /// <summary>
        /// 执行 UPDATE、INSERT 和 DELETE 语句，返回值为该命令所影响的行数
        /// </summary>
        /// <param name="transaction">事务</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令文本</param>
        /// <param name="commandParameters">命令参数</param>
        /// <returns>int</returns>
        public static int ExecuteNonQuery(DbTransaction transaction, CommandType commandType, string commandText, DbParameter[] commandParameters)
        {
            //清空命令参数
            _cmd.Parameters.Clear();
            //设置命令对象属性
            PrepareCommand(_cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);

            //执行命令文本将结果保存到 retval 中
            int retval = _cmd.ExecuteNonQuery();
            //清空命令参数
            _cmd.Parameters.Clear();
            return retval;
        }

        /// <summary>
        /// 执行 UPDATE、INSERT 和 DELETE 语句，返回值为该命令所影响的行数
        /// </summary>
        /// <param name="transaction">事务</param>
        /// <param name="spName">存储过程名</param>
        /// <param name="parameterValues">参数值</param>
        /// <returns>int</returns>
        public static int ExecuteNonQuery(DbTransaction transaction, string spName, params object[] parameterValues)
        {
            //判断参数值列表是否为空,如果为空,则调用没有参数列表的函数,
            //否则把参数值赋给参数列表,然后执行带参数列表的函数
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                //获得命令文本的参数列表
                _paras = GetSpParameterSet(transaction.Connection.ConnectionString, spName);
                //把参数值赋给参数列表
                AssignParameterValues(_paras, parameterValues);

                return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, _paras);
            }
            else
            {
                return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary>
        /// 执行查询，并将查询返回的结果集中第一行的第一列作为 .NET Framework 数据类型返回。忽略额外的列或行
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令文本</param>
        /// <returns>object</returns>
        public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText)
        {
            return ExecuteScalar(connectionString, commandType, commandText, (DbParameter[])null);
        }

        /// <summary>
        /// 执行查询，并将查询返回的结果集中第一行的第一列作为 .NET Framework 数据类型返回。忽略额外的列或行
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令文本</param>
        /// <param name="commandParameters">命令参数</param>
        /// <returns>object</returns>
        public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, DbParameter[] commandParameters)
        {
            //清空命令参数列表
            _cmd.Parameters.Clear();
            //获得连接对象
            GetDbConnection(connectionString);
            //打开连接
            OpenConnection(_conn);
            //设置命令对象属性
            PrepareCommand(_cmd, _conn, (DbTransaction)null, commandType, commandText, commandParameters);
            //执行命令文本将结果保存到 retval 中
            object retval = _cmd.ExecuteScalar();
            //清空命令参数列表
            _cmd.Parameters.Clear();
            //关闭连接
            CloseConnection(_conn);
            return retval;
        }

        /// <summary>
        /// 执行查询，并将查询返回的结果集中第一行的第一列作为 .NET Framework 数据类型返回。忽略额外的列或行
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="spName">存储过程名</param>
        /// <param name="parameterValues">参数值</param>
        /// <returns>object</returns>
        public static object ExecuteScalar(string connectionString, string spName, params object[] parameterValues)
        {
            //判断参数值列表是否为空,如果为空,则调用没有参数列表的函数,
            //否则把参数值赋给参数列表,然后执行带参数列表的函数
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                //获得命令文本的参数列表
                _paras = GetSpParameterSet(connectionString, spName);
                //把参数值赋给参数列表
                AssignParameterValues(_paras, parameterValues);

                return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, _paras);
            }
            else
            {
                return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary>
        /// 执行查询，并将查询返回的结果集中第一行的第一列作为 .NET Framework 数据类型返回。忽略额外的列或行
        /// </summary>
        /// <param name="transaction">事务</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令文本</param>
        /// <returns>object</returns>
        public static object ExecuteScalar(DbTransaction transaction, CommandType commandType, string commandText)
        {
            return ExecuteScalar(transaction, commandType, commandText, (DbParameter[])null);
        }

        /// <summary>
        /// 执行查询，并将查询返回的结果集中第一行的第一列作为 .NET Framework 数据类型返回。忽略额外的列或行
        /// </summary>
        /// <param name="transaction">事务</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令文本</param>
        /// <param name="commandParameters">名林参数</param>
        /// <returns>object</returns>
        public static object ExecuteScalar(DbTransaction transaction, CommandType commandType, string commandText, DbParameter[] commandParameters)
        {
            PrepareCommand(_cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);

            object retval = _cmd.ExecuteScalar();

            _cmd.Parameters.Clear();
            return retval;        
        }

        /// <summary>
        /// 执行查询，并将查询返回的结果集中第一行的第一列作为 .NET Framework 数据类型返回。忽略额外的列或行
        /// </summary>
        /// <param name="transaction">事务</param>
        /// <param name="spName">存储过程名</param>
        /// <param name="parameterValues">参数值</param>
        /// <returns>object</returns>
        public static object ExecuteScalar(DbTransaction transaction, string spName, params object[] parameterValues)
        {
            //判断参数值列表是否为空,如果为空,则调用没有参数列表的函数,
            //否则把参数值赋给参数列表,然后执行带参数列表的函数
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                //获得命令文本的参数列表
                _paras = GetSpParameterSet(transaction.Connection.ConnectionString, spName);
                //把参数值赋给参数列表
                AssignParameterValues(_paras, parameterValues);

                return ExecuteScalar(transaction, CommandType.StoredProcedure, spName, _paras);
            }
            else
            {
                return ExecuteScalar(transaction, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary>
        ///将结果作为一个数据集返回
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令文本</param>
        /// <returns>DataSet</returns>
        public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText)
        {
            return ExecuteDataset(connectionString, commandType, commandText, (DbParameter[])null);
        }

        /// <summary>
        /// 将结果作为一个数据集返回
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令文本</param>
        /// <param name="commandParameters">命令参数</param>
        /// <returns>DataSet</returns>
        public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, DbParameter[] commandParameters)
        {
            GetDbConnection(connectionString);
            OpenConnection(_conn);
            PrepareCommand(_cmd, _conn, (DbTransaction)null, commandType, commandText, commandParameters);
            DbDataAdapter _da=null;
            switch (_dbType)
            {
                case "sql":
                    _da = new SqlDataAdapter((SqlCommand)_cmd);
                    break;
                case "oledb":
                    _da = new OleDbDataAdapter((OleDbCommand)_cmd);
                    break;
                case "oracle":
                    _da = new OracleDataAdapter((OracleCommand)_cmd);
                    break;
            }

            DataSet ds = new DataSet();

            _da.Fill(ds);

            _cmd.Parameters.Clear();
            CloseConnection(_conn);
            return ds;            
        }

        /// <summary>
        /// 将结果作为一个数据集返回
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="spName">存储过程名</param>
        /// <param name="parameterValues">参数值</param>
        /// <returns>DataSet</returns>
        public static DataSet ExecuteDataset(string connectionString, string spName, params object[] parameterValues)
        {
            //判断参数值列表是否为空,如果为空,则调用没有参数列表的函数,
            //否则把参数值赋给参数列表,然后执行带参数列表的函数
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                //获得命令文本的参数列表
                _paras = GetSpParameterSet(connectionString, spName);
                //把参数值赋给参数列表
                AssignParameterValues(_paras, parameterValues);

                return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, _paras);
            }
            else
            {
                return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary>
        /// 将结果作为一个数据集返回
        /// </summary>
        /// <param name="transaction">事务</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令文本</param>
        /// <returns>DataSet</returns>
        public static DataSet ExecuteDataset(DbTransaction transaction, CommandType commandType, string commandText)
        {
            return ExecuteDataset(transaction, commandType, commandText, (DbParameter[])null);
        }

        /// <summary>
        /// 将结果作为一个数据集返回
        /// </summary>
        /// <param name="transaction">事务</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令文本</param>
        /// <param name="commandParameters">命令参数</param>
        /// <returns>DataSet</returns>
        public static DataSet ExecuteDataset(DbTransaction transaction, CommandType commandType, string commandText, DbParameter[] commandParameters)
        {
            PrepareCommand(_cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
            DbDataAdapter _da=null;
            switch (_dbType)
            {
                case "sql":
                    _da = new SqlDataAdapter((SqlCommand)_cmd);
                    break;
                case "oledb":
                    _da = new OleDbDataAdapter((OleDbCommand)_cmd);
                    break;
                case "oracle":
                    _da = new OracleDataAdapter((OracleCommand)_cmd);
                    break;
            }

            DataSet ds = new DataSet();

            _da.Fill(ds);

            _cmd.Parameters.Clear();

            return ds;      
        }

        /// <summary>
        /// 将结果作为一个数据集返回
        /// </summary>
        /// <param name="transaction">事务</param>
        /// <param name="spName">存储过程名</param>
        /// <param name="parameterValues">参数值</param>
        /// <returns>DataSet</returns>
        public static DataSet ExecuteDataset(DbTransaction transaction, string spName, params object[] parameterValues)
        {
            //判断参数值列表是否为空,如果为空,则调用没有参数列表的函数,
            //否则把参数值赋给参数列表,然后执行带参数列表的函数
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                //获得命令文本的参数列表
                _paras = GetSpParameterSet(transaction.Connection.ConnectionString, spName);
                //把参数值赋给参数列表
                AssignParameterValues(_paras, parameterValues);

                return ExecuteDataset(transaction, CommandType.StoredProcedure, spName, _paras);
            }
            else
            {
                return ExecuteDataset(transaction, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary>
        ///将结果作为一个DataTable返回
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令文本</param>
        /// <returns>DataTable</returns>
        public static DataTable ExecuteDataTable(string connectionString, CommandType commandType, string commandText)
        {
            return ExecuteDataset(connectionString, commandType, commandText).Tables[0];
        }

        /// <summary>
        /// 将结果作为一个DataTable返回
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令文本</param>
        /// <param name="commandParameters">命令参数</param>
        /// <returns>DataTable</returns>
        public static DataTable ExecuteDataTable(string connectionString, CommandType commandType, string commandText, DbParameter[] commandParameters)
        {
            return ExecuteDataset(connectionString, commandType, commandText, commandParameters).Tables[0];
        }

        /// <summary>
        /// 将结果作为一个DataTable返回
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="spName">存储过程名</param>
        /// <param name="parameterValues">参数值</param>
        /// <returns>DataTable</returns>
        public static DataTable ExecuteDataTable(string connectionString, string spName, params object[] parameterValues)
        {
            return ExecuteDataset(connectionString, spName, parameterValues).Tables[0];
        }

        /// <summary>
        /// 将结果作为一个DataTable返回
        /// </summary>
        /// <param name="transaction">事务</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令文本</param>
        /// <returns>DataTable</returns>
        public static DataTable ExecuteDataTable(DbTransaction transaction, CommandType commandType, string commandText)
        {
            return ExecuteDataset(transaction, commandType, commandText).Tables[0];
        }

        /// <summary>
        /// 将结果作为一个DataTable返回
        /// </summary>
        /// <param name="transaction">事务</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令文本</param>
        /// <param name="commandParameters">命令参数</param>
        /// <returns>DataTable</returns>
        public static DataTable ExecuteDataTable(DbTransaction transaction, CommandType commandType, string commandText, DbParameter[] commandParameters)
        {
            return ExecuteDataset(transaction, commandType, commandText, commandParameters).Tables[0];
        }

        /// <summary>
        /// 将结果作为一个DataTable返回
        /// </summary>
        /// <param name="transaction">事务</param>
        /// <param name="spName">存储过程名</param>
        /// <param name="parameterValues">参数值</param>
        /// <returns>DataTable</returns>
        public static DataTable ExecuteDataTable(DbTransaction transaction, string spName, params object[] parameterValues)
        {
            return ExecuteDataset(transaction, spName, parameterValues).Tables[0];
        }

        /// <summary>
        /// 数据库连接拥有者类型
        /// </summary>
        public enum DBConnectionOwnership
        {
            Internal,
            External
        }

        /// <summary>
        /// 将结果作为DataReader返回
        /// </summary>
        /// <param name="connection">连接命令</param>
        /// <param name="transaction">事务</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令文本</param>
        /// <param name="commandParameters">命令参数</param>
        /// <param name="connectionOwnership">连接拥有者</param>
        /// <returns>DbDataReader</returns>
        public static DbDataReader ExecuteReader(DbConnection connection, DbTransaction transaction, CommandType commandType, string commandText, DbParameter[] commandParameters, DBConnectionOwnership connectionOwnership)
        {
            //清空命令参数列表
            _cmd.Parameters.Clear();
            //设置命令对象属性
            PrepareCommand(_cmd, connection, transaction, commandType, commandText, commandParameters);
            //定义一个为空的数据读取器对象
            DbDataReader _dr = null;

            //确定数据读取器对象

            if (connectionOwnership == DBConnectionOwnership.External)
            {
                _dr = _cmd.ExecuteReader();
            }
            else
            {
                _dr = _cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }

            return _dr;
        }

        /// <summary>
        /// 将结果作为DataReader返回
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令文本</param>
        /// <returns>DbDataReader</returns>
        public static DbDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText)
        {
            return ExecuteReader(connectionString, commandType, commandText, (DbParameter[])null);
        }

        /// <summary>
        /// 将结果作为DataReader返回
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令文本</param>
        /// <param name="commandParameters">命令参数</param>
        /// <returns>DbDataReader</returns>
        public static DbDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, DbParameter[] commandParameters)
        {
            try
            {
                //获得连接对象
                GetDbConnection(connectionString);   
                //打开连接
                OpenConnection(_conn);
                return ExecuteReader(_conn, null, commandType, commandText, commandParameters, DBConnectionOwnership.Internal);
            }
            catch
            {
                //关闭连接
                CloseConnection(_conn);
                return null;
                //throw;
            }
        }

        /// <summary>
        /// 将结果作为DataReader返回
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="spName">存储过程名</param>
        /// <param name="parameterValues">参数值</param>
        /// <returns>DbDataReader</returns>
        public static DbDataReader ExecuteReader(string connectionString, string spName, params object[] parameterValues)
        {
            //判断参数值列表是否为空,如果为空,则调用没有参数列表的函数,
            //否则把参数值赋给参数列表,然后执行带参数列表的函数
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                //获得命令文本的参数列表
                _paras = GetSpParameterSet(connectionString, spName);
                //把参数值赋给参数列表
                AssignParameterValues(_paras, parameterValues);

                return ExecuteReader(connectionString, CommandType.StoredProcedure, spName, _paras);
            }
            else
            {
                return ExecuteReader(connectionString, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary>
        /// 将结果作为DataReader返回
        /// </summary>
        /// <param name="transaction">事务</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令文本</param>
        /// <returns>DbDataReader</returns>
        public static DbDataReader ExecuteReader(DbTransaction transaction, CommandType commandType, string commandText)
        {
            return ExecuteReader(transaction, commandType, commandText, (DbParameter[])null);
        }

        /// <summary>
        /// 将结果作为DataReader返回
        /// </summary>
        /// <param name="transaction">事务</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令文本</param>
        /// <param name="commandParameters">命令参数</param>
        /// <returns>DbDataReader</returns>
        public static DbDataReader ExecuteReader(DbTransaction transaction, CommandType commandType, string commandText, DbParameter[] commandParameters)
        {
            return ExecuteReader(transaction.Connection, transaction, commandType, commandText, commandParameters, DBConnectionOwnership.External);    
        }

        /// <summary>
        /// 将结果作为DataReader返回
        /// </summary>
        /// <param name="transaction">事务</param>
        /// <param name="spName">存储过程名</param>
        /// <param name="parameterValues">参数值</param>
        /// <returns>DbDataReader</returns>
        public static DbDataReader ExecuteReader(DbTransaction transaction, string spName, params object[] parameterValues)
        {
            //判断参数值列表是否为空,如果为空,则调用没有参数列表的函数,
            //否则把参数值赋给参数列表,然后执行带参数列表的函数
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                //获得命令文本的参数列表
                _paras = GetSpParameterSet(transaction.Connection.ConnectionString, spName);
               
                //把参数值赋给参数列表
                AssignParameterValues(_paras, parameterValues);

                return ExecuteReader(transaction, CommandType.StoredProcedure, spName, _paras);
            }
            else
            {
                return ExecuteReader(transaction, CommandType.StoredProcedure, spName);
            }
        }
       
        #endregion
       
        #region 事务操作

        /// <summary>
        /// 事务处理方法，将 N 条 SQL 语句作为事务一起执行
        /// </summary>
        public static bool ExecuteTransaction(string connectionString, string[] commandText)
        {
            //获取连接对象
            GetDbConnection(connectionString);

            //打开连接
            OpenConnection(_conn);

            //定义事务
            DbTransaction _trans = _conn.BeginTransaction();
            try
            {
                //设置命令对象属性
                _cmd.Connection = _conn;
                _cmd.Transaction = _trans;

                //遍历读取命令文本列表,依次执行每一条命令
                for (int i = 0; i < commandText.Length; i++)
                {
                    _cmd.CommandText = commandText[i];
                    _cmd.ExecuteNonQuery();
                }

                //提交事务
                _trans.Commit();
                return true;
            }
            catch (Exception)
            {
                //回滚事务
                _trans.Rollback();
                return false;
            }
            finally
            {
                //关闭连接
                CloseConnection(_conn);
            }
        }    

        /// <summary>
        /// 事务处理方法，将一条 SQL 语句作为事务执行
        /// </summary>
        public static bool ExecuteTransaction(string connectionString, string commandText)
        {
            return ExecuteTransaction(connectionString, commandText, (DbParameter[])null);
        }

        /// <summary>
        /// 事务处理方法，将一条 SQL 语句作为事务执行
        /// </summary>
        public static bool ExecuteTransaction(string connectionString, string commandText, DbParameter[] commandParameters)
        {
            //获得连接对象
            GetDbConnection(connectionString);
            //打开连接
            OpenConnection(_conn);
            //定义事务
            DbTransaction _trans = _conn.BeginTransaction();
            try
            {
                //设置命令对象属性
                _cmd.Connection = _conn;
                _cmd.Transaction = _trans;
                _cmd.CommandText = commandText;

                if(commandParameters!=null)
                {
                    //遍历命令参数列表,把参数赋给命令对象
                    foreach (DbParameter p in (DbParameter[])commandParameters)
                    {
                        if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null))
                        {
                            p.Value = DBNull.Value;
                        }

                        _cmd.Parameters.Add(p);
                    }
                }

                //执行命令文本
                _cmd.ExecuteNonQuery();

                //提交事务
                _trans.Commit();
                return true;
            }
            catch (Exception)
            {
                //回滚事务
                _trans.Rollback();
                return false;
            }
            finally
            {
                //关闭连接
                CloseConnection(_conn);
            }
        }
       
        #endregion
    }
}