DbHelper第三版, 数据库通吃

using System;
using System.Collections;
using System.Data;
using System.Data.Common;
using System.Configuration;

namespace Lee.Data
{
    public class DataAccess
    {
        #region 字段 构造函数
        public readonly DbProviderFactory Factory;
        public readonly ConnectionStringSettings ConnectionSettings;

        public static DataAccess Instance = new DataAccess();

        DataAccess() {
            this.ConnectionSettings = ConfigurationManager.ConnectionStrings["default"];
            this.Factory = DbProviderFactories.GetFactory(this.ConnectionSettings.ProviderName);
        }
        #endregion

        #region 创建 DbConnection, DbCommand对象
        /// <summary>
        /// 获取一个新的DbConnection对象
        /// </summary>
        /// <returns>DbConnection</returns>
        public DbConnection CreateConnection() {
            DbConnection connection = Factory.CreateConnection();
            connection.ConnectionString = this.ConnectionSettings.ConnectionString;
            return connection;
        }

        /// <summary>
        /// 使用sql语句创建一个DbCommand对象
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public DbCommand CreateCommand(String sql) {
            return this.CreateCommand(sql, CommandType.Text, null, this.CreateConnection());
        }

        /// <summary>
        /// 使用sql语句,DbConnection对象创建一个DbCommand对象
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="connection">DbConnection对象</param>
        /// <returns></returns>
        public DbCommand CreateCommand(String sql, DbConnection connection) {
            return this.CreateCommand(sql, CommandType.Text, null, connection);
        }

        /// <summary>
        /// 使用sql语句, CommandType, DbParameter数组 创建一个DbCommand对象
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="type">DbCommand所执行命令的类型</param>
        /// <param name="parameters">DbCommand的参数</param>
        /// <returns></returns>
        public DbCommand CreateCommand(String sql, CommandType type, DbParameter[] parameters) {
            DbCommand cmd = Factory.CreateCommand();
            cmd.CommandType = type;
            cmd.CommandText = sql;
            cmd.Connection = this.CreateConnection();
            if(parameters != null && parameters.Length > 0)
                cmd.Parameters.AddRange(parameters);
            return cmd;
        }

        /// <summary>
        /// 使用sql语句, CommandType, DbParameter数组, DbConnection对象 创建一个DbCommand对象
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="type">DbCommand所执行命令的类型</param>
        /// <param name="parameters">DbCommand的参数</param>
        /// <param name="connection">connection对象</param>
        /// <returns></returns>
        public DbCommand CreateCommand(String sql, CommandType type, DbParameter[] parameters, DbConnection connection) {
            DbCommand cmd = null;
            if (connection == null) cmd = this.CreateConnection().CreateCommand();
            else cmd = connection.CreateCommand();
            cmd.CommandType = type;
            cmd.CommandText = sql;
            if (parameters != null && parameters.Length > 0)
                cmd.Parameters.AddRange(parameters);
            return cmd;
        }
        #endregion

        #region 执行sql语句
        /// <summary>
        /// 执行sql语句返回受影响的行数
        /// </summary>
        /// <param name="sql">要执行的sql语句</param>
        /// <returns></returns>
        public int ExecuteNoneQuery(String sql) {
            using (DbConnection connection = this.CreateConnection()) {
                using (DbCommand command = connection.CreateCommand()) {
                    this.Open(connection);
                    command.CommandText = sql;
                    return command.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 使用指定的DbConnection对象执行sql语句并返回受影响的行数
        /// </summary>
        /// <param name="sql">要执行的sql语句</param>
        /// <param name="connection">DbConnection对象</param>
        /// <returns></returns>
        public int ExecuteNoneQuery(String sql, DbConnection connection) {
            using (DbCommand command = connection.CreateCommand()) {
                command.CommandText = sql;
                this.Open(connection);
                return command.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 执行sql语句获取第一行第一列的值 (Object类型)
        /// </summary>
        /// <param name="sql">要执行的sql语句</param>
        /// <returns></returns>
        public Object ExecuteScalar(String sql) {
            using (DbConnection connection = this.CreateConnection()) {
                using (DbCommand command = connection.CreateCommand()) {
                    command.CommandText = sql;
                    this.Open(connection);
                    return command.ExecuteScalar();
                }
            }
        }

        /// <summary>
        /// 使用指定的DbConnection对象执行sql语句并获取第一行第一列的值 (Object类型)
        /// </summary>
        /// <param name="sql">要执行的sql语句</param>
        /// <param name="connection">DbConnection对象</param>
        /// <returns></returns>
        public Object ExecuteScalar(String sql, DbConnection connection) {
            using (DbCommand command = connection.CreateCommand()) {
                command.CommandText = sql;
                command.Connection = connection;
                this.Open(connection);
                return command.ExecuteScalar();
            }
        }

        /// <summary>
        /// 执行sql语句获取第一行第一列的值 (int类型)
        /// </summary>
        /// <param name="sql">要执行的sql语句</param>
        /// <returns></returns>
        public Int32? ExecuteScalarGetInt(String sql) {
            Object obj =  this.ExecuteScalar(sql);
            return obj == null ? null : (Int32?)obj;
        }

        /// <summary>
        /// 使用指定的DbConnection执行sql语句并获取第一行第一列的值 (int类型)
        /// </summary>
        /// <param name="sql">要执行的sql语句</param>
        /// <param name="connection">DbConnection对象</param>
        /// <returns></returns>
        public Int32? ExecuteScalarGetInt(String sql, DbConnection connection) {
            Object obj = this.ExecuteScalar(sql, connection);
            return obj == null ? null : (Int32?)obj;
        }

        /// <summary>
        /// 执行sql语句并获取第一行第一列的值 (String类型)
        /// </summary>
        /// <param name="sql">要执行的sql语句</param>
        /// <returns></returns>
        public String ExcuteScalarGetString(String sql) {
            Object obj = this.ExecuteScalar(sql);
            return obj == null ? null : obj.ToString();
        }

        /// <summary>
        /// 使用指定的DbConnection执行sql语句并获取第一行第一列的值 (String类型)
        /// </summary>
        /// <param name="sql">要执行的sql语句</param>
        /// <param name="connection">DbConnection对象</param>
        /// <returns></returns>
        public String ExcuteScalarGetString(String sql, DbConnection connection) {
            Object obj = this.ExecuteScalar(sql, connection);
            return obj == null ? null : obj.ToString();
        }

        /// <summary>
        /// 使用指定的sql语句创建一个DbDataReader对象
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public DbDataReader ExecuteReader(String sql) {
            DbConnection connection = this.CreateConnection();
            DbCommand command = connection.CreateCommand();
            command.CommandText = sql;
            this.Open(connection);
            return command.ExecuteReader(CommandBehavior.CloseConnection);
        }

        /// <summary>
        /// 使用指定的DbConnection对象执行sql语句并获取一个DbDataReader对象
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="connection">DbConnection对象</param>
        /// <returns></returns>
        public DbDataReader ExecuteReader(String sql, DbConnection connection) {
            DbCommand command = connection.CreateCommand();
            command.CommandText = sql;
            this.Open(connection);
            return command.ExecuteReader();
        }

        /// <summary>
        /// 执行sql语句返回DataTable
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public DataTable GetDataTable(String sql) {
            using (DbConnection connection = this.CreateConnection()) {
                using (DbDataAdapter adapter = Factory.CreateDataAdapter()) {
                    using (adapter.SelectCommand = connection.CreateCommand()) {
                        adapter.SelectCommand.CommandText = sql;
                        DataTable table = new DataTable();
                        adapter.Fill(table);
                        return table;
                    }
                }
            }
        }

        /// <summary>
        /// 执行sql语句返回DataTable
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="connection"></param>
        /// <returns></returns>
        public DataTable GetDataTable(String sql, DbConnection connection) {
            using (DbDataAdapter adapter = Factory.CreateDataAdapter()) {
                using (adapter.SelectCommand = connection.CreateCommand()) {
                    adapter.SelectCommand.CommandText = sql;
                    DataTable table = new DataTable();
                    adapter.Fill(table);
                    return table;
                }
            }
        }

        #endregion

        #region 执行存储过程
        public ArrayList ExecuteProc(String procName, DbParameter[] parameters) {
            DbCommand command = this.CreateCommand(procName, CommandType.StoredProcedure, parameters);
            ArrayList values = new ArrayList(parameters.Length / 2);
            try {
                this.Open(command);
                command.ExecuteNonQuery();
                foreach (DbParameter item in parameters)
                    if (item.Direction == ParameterDirection.Output)
                        values.Add(item.Value);
            }
            catch { throw; }
            finally { command.Connection.Dispose(); command.Dispose(); }
            return values;
        }

        /// <summary>
        /// 执行存储过程并将输出参数作为int数组返回
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns></returns>
        public Int32[] ExecuteProcGetInt(String procName, DbParameter[] parameters) {
            ArrayList values = this.ExecuteProc(procName, parameters);
            Int32[] intValues = new Int32[values.Count];
            for (int i = 0; i < values.Count; i++)
                intValues[i] = (Int32)values[i];
            return intValues;
        }

        /// <summary>
        /// 执行存储过程并将输出参数作为String数组返回
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns></returns>
        public String[] ExecuteProcGetString(String procName, DbParameter[] parameters) {
            ArrayList values = this.ExecuteProc(procName, parameters);
            String[] stringValues = new String[values.Count];
            for (int i = 0; i < values.Count; i++)
                stringValues[i] = values[i].ToString();
            return stringValues;
        }

        /// <summary>
        /// 执行存储过程并获取一个DbDataReader对象
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns></returns>
        public DbDataReader ExecuteProcGetDataReader(String procName, DbParameter[] parameters) {
            DbCommand command = this.CreateCommand(procName, CommandType.StoredProcedure, parameters);
            DbDataReader reader = null;
            try { this.Open(command); reader = command.ExecuteReader(CommandBehavior.CloseConnection); }
            catch { throw; }
            //finally { command.Connection.Dispose(); command.Dispose(); }
            return reader;
        }

        /// <summary>
        /// 执行存储过程获取DataTable
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns></returns>
        public DataTable ExecuteProcGetDataTable(String procName, DbParameter[] parameters) {
            DataTable table = new DataTable();
            DbDataAdapter adapter = null;

            try {
                adapter = Factory.CreateDataAdapter();
                adapter.SelectCommand = this.CreateCommand(procName, CommandType.StoredProcedure, parameters);
                adapter.Fill(table);
            }
            catch { throw; }
            finally {
                adapter.SelectCommand.Connection.Dispose();
                adapter.SelectCommand.Dispose();
                adapter.Dispose();
            }
            return table;
        }

        #endregion

        #region 其它

        /// <summary>
        /// 创建一个输入参数
        /// </summary>
        /// <param name="name"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public DbParameter CreateParameter(String name, Object value) {
            return this.CreateParameter(name, value, DbType.Object, ParameterDirection.Input);
        }

        /// <summary>
        /// 创建一个输入参数
        /// </summary>
        /// <param name="name"></param>
        /// <param name="value"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public DbParameter CreateParameter(String name, Object value, DbType type) {
            return this.CreateParameter(name, value, type, ParameterDirection.Input);
        }

        /// <summary>
        /// 创建一个输出参数
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public DbParameter CreateParameter(String name, DbType type) {
            DbParameter parameter = Factory.CreateParameter();
            parameter.ParameterName = name;
            parameter.Direction = ParameterDirection.Output;
            parameter.DbType = type;
            return parameter;
        }

        public DbParameter CreateParameter(String name, Object value, DbType type, ParameterDirection direct) {
            DbParameter parameter = Factory.CreateParameter();
            parameter.ParameterName = name;
            parameter.Value = value;
            parameter.DbType = type;
            parameter.Direction = direct;
            return parameter;
        }

        /// <summary>
        /// 打开连接
        /// </summary>
        /// <param name="connection">需要打开的DbConnection对象</param>
        public void Open(DbConnection connection) {
            if(connection.State != ConnectionState.Open)
                try { connection.Open(); }
                catch { throw; }
        }

        /// <summary>
        /// 打开连接
        /// </summary>
        /// <param name="command">需要打开的DbCommand对象</param>
        public void Open(DbCommand command) {
            if (command.Connection.State == ConnectionState.Open)
                return;
            try { command.Connection.Open(); }
            catch { throw; }
        }
        #endregion
    }
}