//====================================================================
// Copyright (C) 2008 allen. All rights reserved.
//====================================================================

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

namespace BinaryIntellect.DataAccess
{
    public class DatabaseHelper:IDisposable
    {
        #region members
        private string strConnectionString;
        private DbConnection objConnection;
        private DbCommand objCommand;
        private DbProviderFactory objFactory = null;
        #endregion

        #region constructor
        public DatabaseHelper(string connectionstring,Providers provider)
        {
            strConnectionString = connectionstring;
            switch (provider)
            {
                case Providers.SqlServer:
                    objFactory = SqlClientFactory.Instance;
                    break;
                case Providers.OleDb:
                    objFactory = OleDbFactory.Instance;
                    break;
                case Providers.Oracle:
                    objFactory = OracleClientFactory.Instance;
                    break;
                case Providers.ODBC:
                    objFactory = OdbcFactory.Instance;
                    break;
                case Providers.ConfigDefined:
                    string providername=ConfigurationManager.ConnectionStrings["connectionstring"].ProviderName;
                    switch (providername)
                    {
                        case "System.Data.SqlClient":
                            objFactory = SqlClientFactory.Instance;
                            break;
                        case "System.Data.OleDb":
                            objFactory = OleDbFactory.Instance;
                            break;
                        case "System.Data.OracleClient":
                            objFactory = OracleClientFactory.Instance;
                            break;
                        case "System.Data.Odbc":
                            objFactory = OdbcFactory.Instance;
                            break;
                    }
                    break;

            }
            objConnection = objFactory.CreateConnection();
            objCommand = objFactory.CreateCommand();

            objConnection.ConnectionString = strConnectionString;
            objCommand.Connection = objConnection;
        }

        public DatabaseHelper(Providers provider):this(ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString,provider)
        {
        }

        public DatabaseHelper(string connectionstring): this(connectionstring, Providers.SqlServer)
        {
        }

        public DatabaseHelper():this(ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString,Providers.ConfigDefined)
        {
        }
        #endregion

        #region AddParameter
        public int AddParameter(string name,object value)
        {
            DbParameter p = objFactory.CreateParameter();
            p.ParameterName = name;
            p.Value=value;
            return objCommand.Parameters.Add(p);
        }

        public int AddParameter(DbParameter parameter)
        {
            return objCommand.Parameters.Add(parameter);
        }       

        public int AddParameter(DbParameter[] dbParam)
        {
            if (dbParam == null)
            {
                return 0;
            }
            try
            {
                foreach (DbParameter param in dbParam)
                {
                    objCommand.Parameters.Add(param);
                }
                return dbParam.Length;
            }
            catch
            {
                return 0;
            }
        }
        #endregion

        public DbCommand Command
        {
            get
            {
                return objCommand;
            }
        }

        #region Transaction Operation
        public void BeginTransaction()
        {
            if (objConnection.State == System.Data.ConnectionState.Closed)
            {
                objConnection.Open();
            }
            objCommand.Transaction = objConnection.BeginTransaction();
        }

        public void CommitTransaction()
        {
            objCommand.Transaction.Commit();
            objConnection.Close();
        }

        public void RollbackTransaction()
        {
            objCommand.Transaction.Rollback();
            objConnection.Close();
        }
        #endregion

        #region ExecuteNonQuery
        public int ExecuteNonQuery(string query)
        {
            return ExecuteNonQuery(query, CommandType.Text, ConnectionState.CloseOnExit);
        }

        public int ExecuteNonQuery(string query,CommandType commandtype)
        {
            return ExecuteNonQuery(query, commandtype, ConnectionState.CloseOnExit);
        }

        public int ExecuteNonQuery(string query,ConnectionState connectionstate)
        {
            return ExecuteNonQuery(query,CommandType.Text,connectionstate);
        }

        public int ExecuteNonQuery(string query,CommandType commandtype, ConnectionState connectionstate)
        {
            objCommand.CommandText = query;
            objCommand.CommandType = commandtype;
            int i=-1;
            try
            {
                if (objConnection.State == System.Data.ConnectionState.Closed)
                {
                    objConnection.Open();
                }
                i = objCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                //throw ex;
            }
            finally
            {
                objCommand.Parameters.Clear();
                if (connectionstate == ConnectionState.CloseOnExit)
                {
                    objConnection.Close();
                }
            }

            return i;
        }
        #endregion

        #region Executescalar
        public object ExecuteScalar(string query)
        {
            return ExecuteScalar(query, CommandType.Text, ConnectionState.CloseOnExit);
        }

        public object ExecuteScalar(string query,CommandType commandtype)
        {
            return ExecuteScalar(query, commandtype, ConnectionState.CloseOnExit);
        }

        public object ExecuteScalar(string query, ConnectionState connectionstate)
        {
            return ExecuteScalar(query, CommandType.Text, connectionstate);
        }

        public object ExecuteScalar(string query,CommandType commandtype, ConnectionState connectionstate)
        {
            objCommand.CommandText = query;
            objCommand.CommandType = commandtype;
            object o = null;
            try
            {
                if (objConnection.State == System.Data.ConnectionState.Closed)
                {
                    objConnection.Open();
                }
                o = objCommand.ExecuteScalar();
            }
            catch (Exception ex)
            {
                //throw ex;
            }
            finally
            {
                objCommand.Parameters.Clear();
                if (connectionstate == ConnectionState.CloseOnExit)
                {
                    objConnection.Close();
                }
            }

            return o;
        }
        #endregion

        #region ExecuteReader
        public DbDataReader ExecuteReader(string query)
        {
            return ExecuteReader(query, CommandType.Text, ConnectionState.CloseOnExit);
        }

        public DbDataReader ExecuteReader(string query,CommandType commandtype)
        {
            return ExecuteReader(query, commandtype, ConnectionState.CloseOnExit);
        }

        public DbDataReader ExecuteReader(string query, ConnectionState connectionstate)
        {
            return ExecuteReader(query, CommandType.Text, connectionstate);
        }

        public DbDataReader ExecuteReader(string query,CommandType commandtype, ConnectionState connectionstate)
        {
            objCommand.CommandText = query;
            objCommand.CommandType = commandtype;
            DbDataReader reader=null;
            try
            {
                if (objConnection.State == System.Data.ConnectionState.Closed)
                {
                    objConnection.Open();
                }
                if (connectionstate == ConnectionState.CloseOnExit)
                {
                    reader = objCommand.ExecuteReader(CommandBehavior.CloseConnection);
                }
                else
                {
                    reader = objCommand.ExecuteReader();
                }

            }
            catch (Exception ex)
            {
                //throw ex;
            }
            finally
            {
                objCommand.Parameters.Clear();
            }

            return reader;
        }
        #endregion

        #region ExecuteDataSet
        public DataSet ExecuteDataSet(string query)
        {
            return ExecuteDataSet(query, CommandType.Text, ConnectionState.CloseOnExit);
        }

        public DataSet ExecuteDataSet(string query,CommandType commandtype)
        {
            return ExecuteDataSet(query, commandtype, ConnectionState.CloseOnExit);
        }

        public DataSet ExecuteDataSet(string query,ConnectionState connectionstate)
        {
            return ExecuteDataSet(query, CommandType.Text, connectionstate);
        }

        public DataSet ExecuteDataSet(string query,CommandType commandtype, ConnectionState connectionstate)
        {
            DbDataAdapter adapter = objFactory.CreateDataAdapter();
            objCommand.CommandText = query;
            objCommand.CommandType = commandtype;
            adapter.SelectCommand = objCommand;
            DataSet ds = new DataSet();
            try
            {
                adapter.Fill(ds);
            }
            catch (Exception ex)
            {
                HandleExceptions(ex);
            }
            finally
            {
                objCommand.Parameters.Clear();
                if (connectionstate == ConnectionState.CloseOnExit)
                {
                    if (objConnection.State == System.Data.ConnectionState.Open)
                    {
                        objConnection.Close();
                    }
                }
            }
            return ds;
        }
        #endregion

        #region ExecuteDataTable
        public DataTable ExecuteDataTable(string query)
        {
            return ExecuteDataTable(query, CommandType.Text, ConnectionState.CloseOnExit);
        }

        public DataTable ExecuteDataTable(string query, CommandType commandtype)
        {
            return ExecuteDataTable(query, commandtype, ConnectionState.CloseOnExit);
        }

        public DataTable ExecuteDataTable(string query, ConnectionState connectionstate)
        {
            return ExecuteDataTable(query, CommandType.Text, connectionstate);
        }

        public DataTable ExecuteDataTable(string query, CommandType commandtype, ConnectionState connectionstate)
        {
            DbDataAdapter adapter = objFactory.CreateDataAdapter();
            objCommand.CommandText = query;
            objCommand.CommandType = commandtype;
            adapter.SelectCommand = objCommand;
            DataSet ds = new DataSet();
            DataTable table = null;
            try
            {
                adapter.Fill(ds);
                table = ds.Tables[0].Copy();
                ds.Dispose();
            }
            catch (Exception ex)
            {
                //throw ex;
            }
            finally
            {
                objCommand.Parameters.Clear();
                if (connectionstate == ConnectionState.CloseOnExit)
                {
                    if (objConnection.State == System.Data.ConnectionState.Open)
                    {
                        objConnection.Close();
                    }
                }
            }
            return table;
        }
        #endregion

        #region ExecuteSqlTransaction
        public bool ExecuteSqlTranByHash(Hashtable SQLStringList)
        {
            BeginTransaction();
            try
            {
                foreach (DictionaryEntry myDE in SQLStringList)
                {
                    string cmdText = myDE.Key.ToString();
                    DbParameter[] cmdParms = (DbParameter[])myDE.Value;
                    foreach (DbParameter p in cmdParms)
                    {
                        AddParameter(p);
                    }

                    int val = ExecuteNonQuery(cmdText, ConnectionState.KeepOpen);
                    objCommand.Parameters.Clear();
                }
                CommitTransaction();
                return true;
            }
            catch
            {
                RollbackTransaction();
            }
            return false;
        }

        public bool ExecuteSqlTranByList(ArrayList SQLStringList)
        {
            BeginTransaction();
            try
            {
                foreach (string s in SQLStringList)
                {
                    string cmdText = s;
                    int val = ExecuteNonQuery(cmdText, ConnectionState.KeepOpen);
                    objCommand.Parameters.Clear();
                }
                CommitTransaction();
                return true;
            }
            catch
            {
                RollbackTransaction();
            }
            return false;
        }
        #endregion

                
        public void Dispose()
        {
            objConnection.Close();
            objConnection.Dispose();
            objCommand.Dispose();
        }

    }

    public enum Providers
    {
        SqlServer, OleDb, Oracle, ODBC, ConfigDefined
    }

    public enum ConnectionState
    {
        KeepOpen, CloseOnExit
    }
}

请大家多拍砖

就像有人提出的Enterprise Library，我对这个库的使用也存在一定的疑虑，到底性能如何

欢迎大家也对Enterprise Library的使用提出一些建议