DbHelper的一种实现

   DbHelper实现了访问Odbc,SQL Server,Oracle,OleDb等数据库的适用方法（默认访问MS SQL Server数据库），代码如下：

/*
Copyright Hotpoll 2007. All rights reserved.
 * Hu Xiaobing 2007.12.5
 * iamhxb@sina.com
Visit 热调网 for more information about us.
*/

using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.Odbc;
using System.Data.OleDb;
using System.Data.OracleClient; 

namespace Hotpoll.Data.Core
{

    public class DbHelper : IDisposable{
   
        private DbProviderFactory  _factory;
        private DbConnection       _connection;
        private DbCommand          _command;
        private bool               _useTransaction;

   
        public string ConnectionString
        {
            get
            {
                if (_connection != null)
                    return _connection.ConnectionString;
                else
                    return string.Empty;
            }
        }

        public DbConnection Connection
        {
            get
            {
                return _connection;
            }
        }

        public DbCommand Command
        {
            get
            {
                return _command;
            }
        }

        public bool UseTransaction
        {
            get
            {
                return _useTransaction;
            }
            set
            {
                _useTransaction = value;
            }
        }

        public DbHelper(){ }

        public DbHelper(string connectionString)
        {
            CreateDbHelper(connectionString,
                           DbProviders.SqlServer);
        }

        public DbHelper(string connectionString,
                        DbProviders provider)
        {
            CreateDbHelper(connectionString,
                           provider);
        }

        public void CreateDbHelper(string connectionString,
                                   DbProviders provider)
        {

            this.Dispose();

            switch (provider)
            {
                case DbProviders.SqlServer:
                    _factory = SqlClientFactory.Instance;
                    break;

                case DbProviders.Oracle:
                    _factory = OracleClientFactory.Instance;
                    break;

                case DbProviders.OleDB:
                    _factory = OleDbFactory.Instance;
                    break;

                case DbProviders.ODBC:
                    _factory = OdbcFactory.Instance;
                    break;
            }

            _connection = _factory.CreateConnection();
            _connection.ConnectionString = connectionString;

            _command = _factory.CreateCommand();
            _command.Connection = _connection;

            _useTransaction = false;
        }

        public DbParameter CreateParameter(string parameterName,
                             DbType type,
                             int size,
                             object value,
                             ParameterDirection parameterDirection )
        {
            DbParameter parameter = _factory.CreateParameter();

            parameter.ParameterName = parameterName;
            parameter.DbType = type;
            parameter.Size = size;
            parameter.Value = value;
            parameter.Direction = parameterDirection;

            return parameter;
        }

        public DbParameter CreateParameter(string parameterName,
                                           DbType type,
                                           int size,
                                           object value)
        {
            return CreateParameter(parameterName,
                                   type,
                                   size,
                                   value,
                                   ParameterDirection.Input);
        }

        public void AddParameters(DbParameter[] parameters)
        {
            _command.Parameters.Clear();

            if (parameters != null)
            {
                foreach (DbParameter parameter in parameters)
                {
                    if (parameter != null)
                    {
                        _command.Parameters.Add(parameter);
                    }
                }
            }

            _command.Parameters.Add(
                 CreateParameter("ReturnValue",
                      DbType.Int32,
                      4,
                      null,
                      ParameterDirection.ReturnValue)); 

        }

        private void BeginTransaction()
        {
            if (_useTransaction == true)
            {
                if (_connection.State !=
                        System.Data.ConnectionState.Open)
                    _connection.Open();

                _command.Transaction = _connection.BeginTransaction();
            }
        }

        private void CommitTransaction()
        {
            if (_useTransaction == true)
            {
                _command.Transaction.Commit();
                _connection.Close();
            }
        }

        private void RollbackTransaction()
        {
            if (_useTransaction == true)
            {
                _command.Transaction.Rollback();
                _connection.Close();
            }
        }

        public int ExecuteNonQuery(string strSQL)
        {
            return this.ExecuteNonQuery(strSQL,
                                        CommandType.Text,
                                        null);
        }

        public int ExecuteNonQuery(string strSQL,
                                   DbParameter[] parameters)
        {
            return this.ExecuteNonQuery(strSQL,
                                        CommandType.Text,
                                        parameters);
        }

        public int ExecuteNonQuery(string strSQL,
                                   CommandType commandType,
                                   DbParameter[] parameters)
        {

            int i = -1;

            _command.CommandText = strSQL;
            _command.CommandType = commandType;
            this.AddParameters(parameters);

            try
            {
                if (_connection.State !=
                      System.Data.ConnectionState.Open)
                    _connection.Open();

                BeginTransaction();
                i = _command.ExecuteNonQuery();
                CommitTransaction();
            }

            catch (Exception ex)
            {
                RollbackTransaction();
              
                throw (ex);
            }

            finally
            {
                _command.Parameters.Clear();

                if (_connection.State ==
                          System.Data.ConnectionState.Open)
                {
                    _connection.Close();
                }
            }

            return i;
        }

        public object ExecuteScaler(string strSQL,
                                    DbParameter[] parameters)
        {
            return ExecuteScaler(strSQL,
                                 CommandType.Text,
                                 parameters);
        }

        public object ExecuteScaler(string strSQL,
                                    CommandType commandType,
                                    DbParameter[] parameters)
        {

            object obj = null;

            _command.CommandText = strSQL;
            _command.CommandType = commandType;
            this.AddParameters(parameters);

            try
            {
                if (_connection.State !=
                          System.Data.ConnectionState.Open)
                {
                    _connection.Open();
                }

                BeginTransaction();
                obj = _command.ExecuteScalar();
                CommitTransaction();

            }

            catch (Exception ex)
            {
                RollbackTransaction();
                throw (ex);
            }

            finally
            {
                _command.Parameters.Clear();

                if (_connection.State ==
                        ystem.Data.ConnectionState.Open)
                {
                    _connection.Close();
                }
            }

            return obj;
        }

        public DbDataReader ExecuteReader(string strSQL)
        {
            return this.ExecuteReader(strSQL,
                                      CommandType.Text,
                                      null);
        }

 
        public DbDataReader ExecuteReader(string strSQL,
                                          CommandType commandType)
        {
            return this.ExecuteReader(strSQL, commandType, null);
        }

        public DbDataReader ExecuteReader(string strSQL,
                             CommandType commandType,
                             DbParameter[] parameters)
        {
            DbDataReader reader = null;

            _command.CommandText = strSQL;
            _command.CommandType = commandType;
            this.AddParameters(parameters);

            try
            {
                if (_connection.State !=
                         System.Data.ConnectionState.Open)
                {
                    _connection.Open();
                }

                BeginTransaction();
 
                reader = 
                _command.ExecuteReader(CommandBehavior.CloseConnection);

                CommitTransaction();

            }

            catch (Exception ex)
            {
                throw (ex);
            }

            finally
            {
                _command.Parameters.Clear();
            }

            return reader;
        }

        public DataSet ExecuteDataSet(string strSQL,
                                      CommandType commandType,
                                      string tableName)
        {
            return this.ExecuteDataSet(strSQL,
                                       commandType,
                                       null,
                                       tableName);
        }

        public DataSet ExecuteDataSet(string strSQL, string tableName)
        {
            return this.ExecuteDataSet(strSQL,
                                       CommandType.Text,
                                       null,
                                       tableName);
        }

        public DataSet ExecuteDataSet(string strSQL,
                                      DbParameter[] parameters,
                                      string tableName)
        {
            return this.ExecuteDataSet(strSQL,
                                       CommandType.Text,
                                       parameters,
                                       tableName);
        }

        public DataSet ExecuteDataSet(string strSQL, 
                                      CommandType commandType,
                                      DbParameter[] parameters,
                                      string tableName)
        {

            DataSet ds = new DataSet();

            this.FillDataSet(strSQL,
                             commandType,
                             parameters,
                             ds,
                             tableName);
 
            return ds;
        }

        public void FillDataSet(string strSQL,
                                CommandType commandType,
                                DataSet ds,
                                string tableName)
        {
            this.FillDataSet(strSQL,
                             commandType,
                             null,
                             ds,
                             tableName);
        }

        public void FillDataSet(string strSQL,
                                DbParameter[] parameters,
                                DataSet ds,
                                string tableName)
        {
            this.FillDataSet(strSQL,
                             CommandType.Text, 
                             parameters,
                             ds,
                             tableName);
        }

        public void FillDataSet(string strSQL,
                                DataSet ds,
                                string tableName)
        {
            this.FillDataSet(strSQL,
                             CommandType.Text, 
                             null,
                             ds,
                             tableName);
        }

        public void FillDataSet(string strSQL, 
                                CommandType commandType,
                                DbParameter[] parameters,
                                DataSet ds,
                                string tableName)
        {
            DbDataAdapter adapter = _factory.CreateDataAdapter();

            _command.CommandText = strSQL;
            _command.CommandType = commandType;
            this.AddParameters(parameters);

            adapter.SelectCommand = _command;

            try
            {
                if (_connection.State !=
                        System.Data.ConnectionState.Open)
                {
                    _connection.Open();
                }

                this.BeginTransaction();

                adapter.Fill(ds, tableName);

                this.CommitTransaction();
            }

            catch (Exception ex)
            {
                throw (ex);
            }

            finally
            {
                _command.Parameters.Clear();

                if (_connection.State ==
                        System.Data.ConnectionState.Open)
                {
                    _connection.Close();
                }
            }

        }

      
        public void Dispose()
        {

            if (_connection != null)
            {
                if (_connection.State == 
                        ConnectionState.Open)
                    _connection.Close();

                _connection.Dispose();
                _connection = null;
            }

            if (_command != null)
            {
                _command.Parameters.Clear();
                _command.Dispose();
                _command = null;
            }
        }

        /// <summary>
        /// An enums of database providers
        /// </summary>
        public enum DbProviders
        {
            SqlServer,
            OleDB,
            ODBC,
            Oracle
        }
    }
}