#region << 版 本 注 释 >>
/****************************************************
* 文 件 名:
* Copyright(c) ITdos
* CLR 版本: 4.0.30319.18408
* 创 建 人:steven hu
* 电子邮箱:
* 官方网站:www.ITdos.com
* 创建日期:2010-2-10
* 文件描述:
******************************************************
* 修 改 人:ITdos
* 修改日期:
* 备注描述:
*******************************************************/
#endregion

using System;
using System.Data;
using System.Data.Common;
using System.Globalization;
using System.Text;
using Dos;
using Dos.ORM;

namespace Dos.ORM
{
    
    /// <summary>
    /// Database
    /// </summary>
    public sealed class Database : ILogable
    {
        private DbProvider dbProvider;

        /// <summary>
        /// Default Database
        /// </summary>
        public static Database Default = new Database(ProviderFactory.Default);

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="dbProvider"></param>
        public Database(DbProvider dbProvider)
        {
            this.dbProvider = dbProvider;
        }

        #region Properties

        /// <summary>
        /// Gets the connect string.
        /// </summary>
        /// <value>The connect string.</value>
        public string ConnectionString
        {
            get
            {
                return dbProvider.ConnectionString;
            }
        }

        /// <summary>
        /// Gets the DbProviderFactory
        /// </summary>
        public DbProviderFactory DbProviderFactory
        {
            get
            {
                return dbProvider.DbProviderFactory;
            }
        }

        /// <summary>
        /// Gets the db provider.
        /// </summary>
        /// <value>The db provider.</value>
        public DbProvider DbProvider
        {
            get
            {
                return dbProvider;
            }
        }

        #endregion

        #region Log

        /// <summary>
        /// OnLog event.
        /// </summary>
        public event LogHandler OnLog;

        /// <summary>
        /// Writes the log.
        /// </summary>
        /// <param name="command">The command.</param>
        public void WriteLog(DbCommand command)
        {
            if (OnLog != null)
            {
                StringBuilder sb = new StringBuilder();

                sb.Append(string.Format("{0}:\t{1}\t\r\n", command.CommandType, command.CommandText));
                if (command.Parameters != null && command.Parameters.Count > 0)
                {
                    sb.Append("Parameters:\r\n");
                    foreach (DbParameter p in command.Parameters)
                    {
                        sb.Append(string.Format("{0}[{2}] = {1}\r\n", p.ParameterName, p.Value, p.DbType));
                    }
                }
                sb.Append("\r\n");

                OnLog(sb.ToString());
            }
        }

        /// <summary>
        /// Writes the log.
        /// </summary>
        /// <param name="logMsg">The log MSG.</param>
        public void WriteLog(string logMsg)
        {
            if (OnLog != null)
            {
                OnLog(logMsg);
            }
        }

        #endregion

        #region Private Members

        private DbCommand CreateCommandByCommandType(CommandType commandType, string commandText)
        {
            DbCommand command = dbProvider.DbProviderFactory.CreateCommand();
            command.CommandType = commandType;
            command.CommandText = commandText;
            
            return command;
        }
        private void DoLoadDataSet(DbCommand command, DataSet dataSet, string[] tableNames)
        {
            Check.Require(tableNames != null && tableNames.Length > 0, "tableNames could not be null or empty.");
            Check.Require(dataSet != null, "dataSet could not be null.");

            using (DbDataAdapter adapter = GetDataAdapter())
            {
                WriteLog(command);

                ((IDbDataAdapter)adapter).SelectCommand = command;


                string systemCreatedTableNameRoot = "Table";
                for (int i = 0; i < tableNames.Length; i++)
                {
                    string systemCreatedTableName = (i == 0)
                         ? systemCreatedTableNameRoot
                         : systemCreatedTableNameRoot + i;

                    adapter.TableMappings.Add(systemCreatedTableName, tableNames[i]);
                }

                adapter.Fill(dataSet);

            }
        }
        private object DoExecuteScalar(DbCommand command)
        {

            WriteLog(command);

            return command.ExecuteScalar();

        }

        private int DoExecuteNonQuery(DbCommand command)
        {
            if (IsBatchConnection)
            {
                batchCommander.Process(command);
                return 0;
            }


            WriteLog(command);

            return command.ExecuteNonQuery();

        }
        private IDataReader DoExecuteReader(DbCommand command, CommandBehavior cmdBehavior)
        {

            WriteLog(command);

            return command.ExecuteReader(cmdBehavior);

        }
        private DbTransaction BeginTransaction(DbConnection connection)
        {
            return connection.BeginTransaction();
        }
        private IDbTransaction BeginTransaction(DbConnection connection, IsolationLevel il)
        {
            return connection.BeginTransaction(il);
        }
        private void PrepareCommand(DbCommand command, DbConnection connection)
        {
            Check.Require(command != null, "command could not be null.");
            Check.Require(connection != null, "connection could not be null.");

            command.Connection = connection;

            dbProvider.PrepareCommand(command);

        }

        private void PrepareCommand(DbCommand command, DbTransaction transaction)
        {
            Check.Require(command != null, "command could not be null.");
            Check.Require(transaction != null, "transaction could not be null.");

            PrepareCommand(command, transaction.Connection);
            command.Transaction = transaction;

        }
        private static void ConfigureParameter(DbParameter param, string name, DbType dbType, int size, ParameterDirection direction, bool nullable, byte precision, byte scale, string sourceColumn, DataRowVersion sourceVersion, object value)
        {
            param.DbType = dbType;
            param.Size = size;
            param.Value = value ?? DBNull.Value;
            param.Direction = direction;
            param.IsNullable = nullable;
            param.SourceColumn = sourceColumn;
            param.SourceVersion = sourceVersion;
        }
        private DbParameter CreateParameter(string name, DbType dbType, int size, ParameterDirection direction, bool nullable, byte precision, byte scale, string sourceColumn, DataRowVersion sourceVersion, object value)
        {
            DbParameter param = CreateParameter(name);
            ConfigureParameter(param, name, dbType, size, direction, nullable, precision, scale, sourceColumn, sourceVersion, value);
            return param;
        }
        private DbParameter CreateParameter(string name)
        {
            DbParameter param = dbProvider.DbProviderFactory.CreateParameter();
            param.ParameterName = dbProvider.BuildParameterName(name);

            return param;
        }



        #endregion

        #region Close Connection

        /// <summary>
        /// Closes the connection.
        /// </summary>
        /// <param name="command">The command.</param>
        public void CloseConnection(DbCommand command)
        {
            if (command != null && command.Connection.State != ConnectionState.Closed && batchConnection == null)
            {
                if (command.Transaction == null)
                {
                    CloseConnection(command.Connection);
                    command.Dispose();
                }
            }
        }

        /// <summary>
        /// Closes the connection.
        /// </summary>
        /// <param name="conn">The conn.</param>
        public void CloseConnection(DbConnection conn)
        {
            if (conn != null && conn.State != ConnectionState.Closed)
                try
                {
                    conn.Close();
                    conn.Dispose();
                }
                catch
                {
                }
        }

        /// <summary>
        /// Closes the connection.
        /// </summary>
        /// <param name="tran">The tran.</param>
        public void CloseConnection(DbTransaction tran)
        {
            if (tran.Connection != null)
            {
                CloseConnection(tran.Connection);
                tran.Dispose();
            }
        }

        #endregion

        #region Factory Methods

        /// <summary>
        /// Gets the connection.
        /// </summary>
        /// <returns></returns>
        public DbConnection GetConnection()
        {
            if (batchConnection == null)
            {
                return CreateConnection();
            }
            else
            {
                return batchConnection;
            }
        }

        /// <summary>
        /// Gets the connection.
        /// </summary>
        /// <param name="tryOpen">if set to <c>true [try open].
        /// <returns></returns>
        public DbConnection GetConnection(bool tryOpen)
        {
            if (batchConnection == null)
            {
                return CreateConnection(tryOpen);
            }
            else
            {
                return batchConnection;
            }
        }

        /// <summary>
        /// <para>When overridden in a derived class, gets the connection for this database.</para>
        /// <seealso cref="DbConnection"/>        
        /// </summary>
        /// <returns>
        /// <para>The <see cref="DbConnection"/> for this database.
        /// </returns>
        public DbConnection CreateConnection()
        {
            DbConnection newConnection = dbProvider.DbProviderFactory.CreateConnection();
            newConnection.ConnectionString = ConnectionString;

            return newConnection;
        }

        /// <summary>
        /// <para>When overridden in a derived class, gets the connection for this database.</para>
        /// <seealso cref="DbConnection"/>        
        /// </summary>
        /// <returns>
        /// <para>The <see cref="DbConnection"/> for this database.
        /// </returns>
        public DbConnection CreateConnection(bool tryOpenning)
        {
            if (!tryOpenning)
            {
                return CreateConnection();
            }

            DbConnection connection = null;
            try
            {
                connection = CreateConnection();
                connection.Open();
            }
            catch
            {
                try
                {
                    connection.Close();
                }
                catch
                {
                }

                throw;
            }

            return connection;
        }

        /// <summary>
        /// <para>When overridden in a derived class, creates a <see cref="DbCommand"/> for a stored procedure.
        /// </summary>
        /// <param name="storedProcedureName"><para>The name of the stored procedure.
        /// <returns><para>The  for the stored procedure.       
        public DbCommand GetStoredProcCommand(string storedProcedureName)
        {
            Check.Require(!string.IsNullOrEmpty(storedProcedureName), "storedProcedureName could not be null.");

            return CreateCommandByCommandType(CommandType.StoredProcedure, storedProcedureName);
        }

        /// <summary>
        /// <para>When overridden in a derived class, creates an <see cref="DbCommand"/> for a SQL query.
        /// </summary>
        /// <param name="query"><para>The text of the query.        
        /// <returns><para>The  for the SQL query.        
        public DbCommand GetSqlStringCommand(string query)
        {
            Check.Require(!string.IsNullOrEmpty(query), "query could not be null.");

            return CreateCommandByCommandType(CommandType.Text, query);
        }

        /// <summary>
        /// Gets a DbDataAdapter with Standard update behavior.
        /// </summary>
        /// <returns>A <see cref="DbDataAdapter"/>.
        /// <seealso cref="DbDataAdapter"/>
        public DbDataAdapter GetDataAdapter()
        {
            return dbProvider.DbProviderFactory.CreateDataAdapter();
        }


        #endregion

        #region Load & Execute Methods

        /// <summary>
        /// <para>Loads a <see cref="DataSet"/> from command text in a transaction.
        /// </summary>
        /// <param name="transaction">
        /// <para>The <see cref="IDbTransaction"/> to execute the command in.
        /// </param>
        /// <param name="commandType">
        /// <para>One of the <see cref="CommandType"/> values.
        /// </param>
        /// <param name="commandText">
        /// <para>The command text to execute.</para>
        /// </param>
        /// <param name="dataSet">
        /// <para>The <see cref="DataSet"/> to fill.
        /// </param>
        /// <param name="tableNames">
        /// <para>An array of table name mappings for the <see cref="DataSet"/>.
        /// </param>
        public void LoadDataSet(DbTransaction transaction, CommandType commandType, string commandText, DataSet dataSet, string[] tableNames)
        {
            using (DbCommand command = CreateCommandByCommandType(commandType, commandText))
            {
                LoadDataSet(command, dataSet, tableNames, transaction);
            }
        }

        /// <summary>
        /// <para>Executes the <paramref name="commandText"/> interpreted as specified by the  and returns an  through which the result can be read.
        /// It is the responsibility of the caller to close the connection and reader when finished.</para>
        /// </summary>
        /// <param name="commandType">
        /// <para>One of the <see cref="CommandType"/> values.
        /// </param>
        /// <param name="commandText">
        /// <para>The command text to execute.</para>
        /// </param>
        /// <returns>
        /// <para>An <see cref="IDataReader"/> object.
        /// </returns>        
        public IDataReader ExecuteReader(CommandType commandType, string commandText)
        {
            using (DbCommand command = CreateCommandByCommandType(commandType, commandText))
            {
                return ExecuteReader(command);
            }
        }

        /// <summary>
        /// <para>Executes the <paramref name="commandText"/> interpreted as specified by the  within the given 
        /// <paramref name="transaction" /> and returns an <see cref="IDataReader"> through which the result can be read.
        /// It is the responsibility of the caller to close the connection and reader when finished.</para>
        /// </summary>
        /// <param name="transaction">
        /// <para>The <see cref="IDbTransaction"/> to execute the command within.
        /// </param>
        /// <param name="commandType">
        /// <para>One of the <see cref="CommandType"/> values.
        /// </param>
        /// <param name="commandText">
        /// <para>The command text to execute.</para>
        /// </param>
        /// <returns>
        /// <para>An <see cref="IDataReader"/> object.
        /// </returns>        
        public IDataReader ExecuteReader(DbTransaction transaction, CommandType commandType, string commandText)
        {
            using (DbCommand command = CreateCommandByCommandType(commandType, commandText))
            {
                return ExecuteReader(command, transaction);
            }
        }

        /// <summary>
        /// <para>Executes the <paramref name="command"/> and adds a new  to the existing .
        /// </summary>
        /// <param name="command">
        /// <para>The <see cref="DbCommand"/> to execute.
        /// </param>
        /// <param name="dataSet">
        /// <para>The <see cref="DataSet"/> to load.
        /// </param>
        /// <param name="tableName">
        /// <para>The name for the new <see cref="DataTable"/> to add to the .
        /// </param>        
        /// <exception cref="System.ArgumentNullException">Any input parameter was <see langword="null"/> (Nothing in Visual Basic)
        /// <exception cref="System.ArgumentException">tableName was an empty string</exception>
        public void LoadDataSet(DbCommand command, DataSet dataSet, string tableName)
        {
            LoadDataSet(command, dataSet, new string[] { tableName });
        }

        /// <summary>
        /// <para>Executes the <paramref name="command"/> within the given  and adds a new  to the existing .
        /// </summary>
        /// <param name="command">
        /// <para>The <see cref="DbCommand"/> to execute.
        /// </param>
        /// <param name="dataSet">
        /// <para>The <see cref="DataSet"/> to load.
        /// </param>
        /// <param name="tableName">
        /// <para>The name for the new <see cref="DataTable"/> to add to the .
        /// </param>
        /// <param name="transaction">
        /// <para>The <see cref="IDbTransaction"/> to execute the command within.
        /// </param>        
        /// <exception cref="System.ArgumentNullException">Any input parameter was <see langword="null"/> (Nothing in Visual Basic).
        /// <exception cref="System.ArgumentException">tableName was an empty string.</exception>
        public void LoadDataSet(DbCommand command, DataSet dataSet, string tableName, DbTransaction transaction)
        {
            LoadDataSet(command, dataSet, new string[] { tableName }, transaction);
        }

        /// <summary>
        /// <para>Loads a <see cref="DataSet"/> from a .
        /// </summary>
        /// <param name="command">
        /// <para>The command to execute to fill the <see cref="DataSet"/>.
        /// </param>
        /// <param name="dataSet">
        /// <para>The <see cref="DataSet"/> to fill.
        /// </param>
        /// <param name="tableNames">
        /// <para>An array of table name mappings for the <see cref="DataSet"/>.
        /// </param>
        public void LoadDataSet(DbCommand command, DataSet dataSet, string[] tableNames)
        {
            using (DbConnection connection = GetConnection())
            {
                PrepareCommand(command, connection);
                DoLoadDataSet(command, dataSet, tableNames);
            }
        }

        /// <summary>
        /// <para>Loads a <see cref="DataSet"/> from a  in  a transaction.
        /// </summary>
        /// <param name="command">
        /// <para>The command to execute to fill the <see cref="DataSet"/>.
        /// </param>
        /// <param name="dataSet">
        /// <para>The <see cref="DataSet"/> to fill.
        /// </param>
        /// <param name="tableNames">
        /// <para>An array of table name mappings for the <see cref="DataSet"/>.
        /// </param>
        /// <param name="transaction">
        /// <para>The <see cref="IDbTransaction"/> to execute the command in.
        /// </param>
        public void LoadDataSet(DbCommand command, DataSet dataSet, string[] tableNames, DbTransaction transaction)
        {
            PrepareCommand(command, transaction);
            DoLoadDataSet(command, dataSet, tableNames);
        }

        /// <summary>
        /// <para>Loads a <see cref="DataSet"/> from command text.
        /// </summary>
        /// <param name="commandType">
        /// <para>One of the <see cref="CommandType"/> values.
        /// </param>
        /// <param name="commandText">
        /// <para>The command text to execute.</para>
        /// </param>
        /// <param name="dataSet">
        /// <para>The <see cref="DataSet"/> to fill.
        /// </param>
        /// <param name="tableNames">
        /// <para>An array of table name mappings for the <see cref="DataSet"/>.
        /// </param>
        public void LoadDataSet(CommandType commandType, string commandText, DataSet dataSet, string[] tableNames)
        {
            using (DbCommand command = CreateCommandByCommandType(commandType, commandText))
            {
                LoadDataSet(command, dataSet, tableNames);
            }
        }

        /// <summary>
        /// <para>Executes the <paramref name="command"/> and returns the results in a new .
        /// </summary>
        /// <param name="command"><para>The  to execute.
        /// <returns>A <see cref="DataSet"/> with the results of the .        
        public DataSet ExecuteDataSet(DbCommand command)
        {
            DataSet dataSet = new DataSet();
            dataSet.Locale = CultureInfo.InvariantCulture;
            LoadDataSet(command, dataSet, "Table");
            return dataSet;
        }

        /// <summary>
        /// <para>Executes the <paramref name="command"/> as part of the  and returns the results in a new .
        /// </summary>
        /// <param name="command"><para>The  to execute.
        /// <param name="transaction">
        /// <para>The <see cref="IDbTransaction"/> to execute the command within.
        /// </param>
        /// <returns>A <see cref="DataSet"/> with the results of the .        
        public DataSet ExecuteDataSet(DbCommand command, DbTransaction transaction)
        {
            DataSet dataSet = new DataSet();
            dataSet.Locale = CultureInfo.InvariantCulture;
            LoadDataSet(command, dataSet, "Table", transaction);
            return dataSet;
        }

        /// <summary>
        /// <para>Executes the <paramref name="commandText"/> interpreted as specified by the  and returns the results in a new .
        /// </summary>
        /// <param name="commandType">
        /// <para>One of the <see cref="CommandType"/> values.
        /// </param>
        /// <param name="commandText">
        /// <para>The command text to execute.</para>
        /// </param>
        /// <returns>
        /// <para>A <see cref="DataSet"/> with the results of the .
        /// </returns>
        public DataSet ExecuteDataSet(CommandType commandType, string commandText)
        {
            using (DbCommand command = CreateCommandByCommandType(commandType, commandText))
            {
                return ExecuteDataSet(command);
            }
        }

        /// <summary>
        /// <para>Executes the <paramref name="commandText"/> as part of the given  and returns the results in a new .
        /// </summary>
        /// <param name="transaction">
        /// <para>The <see cref="IDbTransaction"/> to execute the command within.
        /// </param>
        /// <param name="commandType">
        /// <para>One of the <see cref="CommandType"/> values.
        /// </param>
        /// <param name="commandText">
        /// <para>The command text to execute.</para>
        /// </param>
        /// <returns>
        /// <para>A <see cref="DataSet"/> with the results of the .
        /// </returns>
        public DataSet ExecuteDataSet(DbTransaction transaction, CommandType commandType, string commandText)
        {
            using (DbCommand command = CreateCommandByCommandType(commandType, commandText))
            {
                return ExecuteDataSet(command, transaction);
            }
        }

        /// <summary>
        /// <para>Executes the <paramref name="command"/> and returns the first column of the first row in the result set returned by the query. Extra columns or rows are ignored.
        /// </summary>
        /// <param name="command">
        /// <para>The command that contains the query to execute.</para>
        /// </param>
        /// <returns>
        /// <para>The first column of the first row in the result set.</para>
        /// </returns>
        /// <seealso cref="IDbCommand.ExecuteScalar"/>
        public object ExecuteScalar(DbCommand command)
        {
            using (DbConnection connection = GetConnection(true))
            {
                PrepareCommand(command, connection);
                return DoExecuteScalar(command);
            }
        }

        /// <summary>
        /// <para>Executes the <paramref name="command"/> within a , and returns the first column of the first row in the result set returned by the query. Extra columns or rows are ignored.
        /// </summary>
        /// <param name="command">
        /// <para>The command that contains the query to execute.</para>
        /// </param>
        /// <param name="transaction">
        /// <para>The <see cref="IDbTransaction"/> to execute the command within.
        /// </param>
        /// <returns>
        /// <para>The first column of the first row in the result set.</para>
        /// </returns>
        /// <seealso cref="IDbCommand.ExecuteScalar"/>
        public object ExecuteScalar(DbCommand command, DbTransaction transaction)
        {
            PrepareCommand(command, transaction);
            return DoExecuteScalar(command);
        }

        /// <summary>
        /// <para>Executes the <paramref name="commandText"/> interpreted as specified by the   and returns the first column of the first row in the result set returned by the query. Extra columns or rows are ignored.
        /// </summary>
        /// <param name="commandType">
        /// <para>One of the <see cref="CommandType"/> values.
        /// </param>
        /// <param name="commandText">
        /// <para>The command text to execute.</para>
        /// </param>
        /// <returns>
        /// <para>The first column of the first row in the result set.</para>
        /// </returns>
        /// <seealso cref="IDbCommand.ExecuteScalar"/>
        public object ExecuteScalar(CommandType commandType, string commandText)
        {
            using (DbCommand command = CreateCommandByCommandType(commandType, commandText))
            {
                return ExecuteScalar(command);
            }
        }

        /// <summary>
        /// <para>Executes the <paramref name="commandText"/> interpreted as specified by the  
        /// within the given <paramref name="transaction" /> and returns the first column of the first row in the result set returned by the query. Extra columns or rows are ignored.</para>
        /// </summary>
        /// <param name="transaction">
        /// <para>The <see cref="IDbTransaction"/> to execute the command within.
        /// </param>
        /// <param name="commandType">
        /// <para>One of the <see cref="CommandType"/> values.
        /// </param>
        /// <param name="commandText">
        /// <para>The command text to execute.</para>
        /// </param>
        /// <returns>
        /// <para>The first column of the first row in the result set.</para>
        /// </returns>
        /// <seealso cref="IDbCommand.ExecuteScalar"/>
        public object ExecuteScalar(DbTransaction transaction, CommandType commandType, string commandText)
        {
            using (DbCommand command = CreateCommandByCommandType(commandType, commandText))
            {
                return ExecuteScalar(command, transaction);
            }
        }

        /// <summary>
        /// <para>Executes the <paramref name="command"/> and returns the number of rows affected.
        /// </summary>
        /// <param name="command">
        /// <para>The command that contains the query to execute.</para>
        /// </param>       
        /// <seealso cref="IDbCommand.ExecuteScalar"/>
        public int ExecuteNonQuery(DbCommand command)
        {
            if (IsBatchConnection)
            {
                PrepareCommand(command, GetConnection(true));
                return DoExecuteNonQuery(command);
            }
            else
            {
//TODO 性能瓶颈
                using (DbConnection connection = GetConnection(true))
                {
                    PrepareCommand(command, connection);
                    return DoExecuteNonQuery(command);
                }
            }
        }

        /// <summary>
        /// <para>Executes the <paramref name="command"/> within the given , and returns the number of rows affected.
        /// </summary>
        /// <param name="command">
        /// <para>The command that contains the query to execute.</para>
        /// </param>
        /// <param name="transaction">
        /// <para>The <see cref="IDbTransaction"/> to execute the command within.
        /// </param>
        /// <seealso cref="IDbCommand.ExecuteScalar"/>
        public int ExecuteNonQuery(DbCommand command, DbTransaction transaction)
        {
            PrepareCommand(command, transaction);
            return DoExecuteNonQuery(command);
        }

        /// <summary>
        /// <para>Executes the <paramref name="commandText"/> interpreted as specified by the  and returns the number of rows affected.
        /// </summary>
        /// <param name="commandType">
        /// <para>One of the <see cref="CommandType"/> values.
        /// </param>
        /// <param name="commandText">
        /// <para>The command text to execute.</para>
        /// </param>
        /// <returns>
        /// <para>The number of rows affected.</para>
        /// </returns>
        /// <seealso cref="IDbCommand.ExecuteScalar"/>
        public int ExecuteNonQuery(CommandType commandType, string commandText)
        {
            using (DbCommand command = CreateCommandByCommandType(commandType, commandText))
            {
                return ExecuteNonQuery(command);
            }
        }

        /// <summary>
        /// <para>Executes the <paramref name="commandText"/> interpreted as specified by the  as part of the given  and returns the number of rows affected.
        /// </summary>
        /// <param name="transaction">
        /// <para>The <see cref="IDbTransaction"/> to execute the command within.
        /// </param>
        /// <param name="commandType">
        /// <para>One of the <see cref="CommandType"/> values.
        /// </param>
        /// <param name="commandText">
        /// <para>The command text to execute.</para>
        /// </param>
        /// <returns>
        /// <para>The number of rows affected</para>
        /// </returns>
        /// <seealso cref="IDbCommand.ExecuteScalar"/>
        public int ExecuteNonQuery(DbTransaction transaction, CommandType commandType, string commandText)
        {
            using (DbCommand command = CreateCommandByCommandType(commandType, commandText))
            {
                return ExecuteNonQuery(command, transaction);
            }
        }

        /// <summary>
        /// <para>Executes the <paramref name="command"/> and returns an  through which the result can be read.
        /// It is the responsibility of the caller to close the connection and reader when finished.</para>
        /// </summary>
        /// <param name="command">
        /// <para>The command that contains the query to execute.</para>
        /// </param>
        /// <returns>
        /// <para>An <see cref="IDataReader"/> object.
        /// </returns>        
        public IDataReader ExecuteReader(DbCommand command)
        {
            DbConnection connection = GetConnection(true);
            PrepareCommand(command, connection);

            try
            {
                return DoExecuteReader(command, CommandBehavior.CloseConnection);
            }
            catch
            {
                try
                {
                    connection.Close();
                }
                catch
                {
                }

                throw;
            }
        }

        /// <summary>
        /// <para>Executes the <paramref name="command"/> within a transaction and returns an  through which the result can be read.
        /// It is the responsibility of the caller to close the connection and reader when finished.</para>
        /// </summary>
        /// <param name="command">
        /// <para>The command that contains the query to execute.</para>
        /// </param>
        /// <param name="transaction">
        /// <para>The <see cref="IDbTransaction"/> to execute the command within.
        /// </param>
        /// <returns>
        /// <para>An <see cref="IDataReader"/> object.
        /// </returns>        
        public IDataReader ExecuteReader(DbCommand command, DbTransaction transaction)
        {
            PrepareCommand(command, transaction);
            return DoExecuteReader(command, CommandBehavior.Default);
        }

        #endregion

        #region Transactions

        /// <summary>
        /// Begins the transaction.
        /// </summary>
        /// <returns></returns>
        public DbTransaction BeginTransaction()
        {
            return GetConnection(true).BeginTransaction();
        }

        /// <summary>
        /// Begins the transaction.
        /// </summary>
        /// <param name="il">The il.</param>
        /// <returns></returns>
        public DbTransaction BeginTransaction(IsolationLevel il)
        {
            return GetConnection(true).BeginTransaction(il);
        }

        #endregion

        #region DbCommand Parameter Methods

        /// <summary>
        /// Adds a new In <see cref="DbParameter"/> object to the given <paramref name="command"/>.
        /// </summary>
        /// <param name="command">The command to add the parameter.</param>
        /// <param name="name"><para>The name of the parameter.
        /// <param name="dbType"><para>One of the  values.
        /// <param name="size"><para>The maximum size of the data within the column.
        /// <param name="direction"><para>One of the  values.
        /// <param name="nullable"><para>Avalue indicating whether the parameter accepts  (Nothing in Visual Basic) values.
        /// <param name="precision"><para>The maximum number of digits used to represent the .
        /// <param name="scale"><para>The number of decimal places to which  is resolved.
        /// <param name="sourceColumn"><para>The name of the source column mapped to the DataSet and used for loading or returning the .
        /// <param name="sourceVersion"><para>One of the  values.
        /// <param name="value"><para>The value of the parameter.       
        public void AddParameter(DbCommand command, string name, DbType dbType, int size, ParameterDirection direction, bool nullable, byte precision, byte scale, string sourceColumn, DataRowVersion sourceVersion, object value)
        {
            DbParameter parameter = CreateParameter(name, dbType == DbType.Object ? DbType.String : dbType, size, direction, nullable, precision, scale, sourceColumn, sourceVersion, value);
            command.Parameters.Add(parameter);
        }

        /// <summary>
        /// <para>Adds a new instance of a <see cref="DbParameter"/> object to the command.
        /// </summary>
        /// <param name="command">The command to add the parameter.</param>
        /// <param name="name"><para>The name of the parameter.
        /// <param name="dbType"><para>One of the  values.        
        /// <param name="direction"><para>One of the  values.                
        /// <param name="sourceColumn"><para>The name of the source column mapped to the DataSet and used for loading or returning the .
        /// <param name="sourceVersion"><para>One of the  values.
        /// <param name="value"><para>The value of the parameter.    
        public void AddParameter(DbCommand command, string name, DbType dbType, ParameterDirection direction, string sourceColumn, DataRowVersion sourceVersion, object value)
        {
            AddParameter(command, name, dbType, 0, direction, false, 0, 0, sourceColumn, sourceVersion, value);
        }

        /// <summary>
        /// Adds a new Out <see cref="DbParameter"/> object to the given <paramref name="command"/>.
        /// </summary>
        /// <param name="command">The command to add the out parameter.</param>
        /// <param name="name"><para>The name of the parameter.
        /// <param name="dbType"><para>One of the  values.        
        /// <param name="size"><para>The maximum size of the data within the column.        
        public void AddOutParameter(DbCommand command, string name, DbType dbType, int size)
        {
            AddParameter(command, name, dbType, size, ParameterDirection.Output, true, 0, 0, String.Empty, DataRowVersion.Default, DBNull.Value);
        }

        /// <summary>
        /// Adds a new In <see cref="DbParameter"/> object to the given <paramref name="command"/>.
        /// </summary>
        /// <param name="command">The command to add the in parameter.</param>
        /// <param name="name"><para>The name of the parameter.
        /// <param name="dbType"><para>One of the  values.                
        /// <remarks>
        /// <para>This version of the method is used when you can have the same parameter object multiple times with different values.</para>
        /// </remarks>        
        public void AddInParameter(DbCommand command, string name, DbType dbType)
        {
            AddParameter(command, name, dbType, ParameterDirection.Input, String.Empty, DataRowVersion.Default, null);
        }

        /// <summary>
        /// Adds a new In <see cref="DbParameter"/> object to the given <paramref name="command"/>.
        /// </summary>
        /// <param name="command">The commmand to add the parameter.</param>
        /// <param name="name"><para>The name of the parameter.
        /// <param name="dbType"><para>One of the  values.                
        /// <param name="value"><para>The value of the parameter.      
        public void AddInParameter(DbCommand command, string name, DbType dbType, object value)
        {
            AddParameter(command, name, dbType, ParameterDirection.Input, String.Empty, DataRowVersion.Default, value);
        }

        /// <summary>
        /// Adds a new In <see cref="DbParameter"/> object to the given <paramref name="command"/>.
        /// </summary>
        /// <param name="command">The commmand to add the parameter.</param>
        /// <param name="name"><para>The name of the parameter.
        /// <param name="dbType"><para>One of the  values.       
        /// <param name="size">size</param>
        /// <param name="value"><para>The value of the parameter.      
        public void AddInParameter(DbCommand command, string name, DbType dbType, int size, object value)
        {
            AddParameter(command, name, dbType, size, ParameterDirection.Input, true, 0, 0, String.Empty, DataRowVersion.Default, value);
        }

        /// <summary>
        /// Adds a new In <see cref="DbParameter"/> object to the given <paramref name="command"/>.
        /// </summary>
        /// <param name="command">The commmand to add the parameter.</param>
        /// <param name="name"><para>The name of the parameter.
        /// <param name="value"><para>The value of the parameter.      
        public void AddInParameter(DbCommand command, string name, object value)
        {
            AddParameter(command, name, DbType.Object, ParameterDirection.Input, String.Empty, DataRowVersion.Default, value);
        }

        /// <summary>
        /// Adds a new In <see cref="DbParameter"/> object to the given <paramref name="command"/>.
        /// </summary>
        /// <param name="command">The command to add the parameter.</param>
        /// <param name="name"><para>The name of the parameter.
        /// <param name="dbType"><para>One of the  values.                
        /// <param name="sourceColumn"><para>The name of the source column mapped to the DataSet and used for loading or returning the value.
        /// <param name="sourceVersion"><para>One of the  values.
        public void AddInParameter(DbCommand command, string name, DbType dbType, string sourceColumn, DataRowVersion sourceVersion)
        {
            AddParameter(command, name, dbType, 0, ParameterDirection.Input, true, 0, 0, sourceColumn, sourceVersion, null);
        }


        /// <summary>
        /// Adds a new In and Out
        /// </summary>
        public void AddInputOutputParameter(DbCommand command, string name, DbType dbType, int size, object value)
        {
            AddParameter(command, name, dbType, size, ParameterDirection.InputOutput, true, 0, 0, String.Empty, DataRowVersion.Default, value);
        }


        /// <summary>
        /// Adds a new return
        /// </summary>
        public void AddReturnValueParameter(DbCommand command, string name, DbType dbType, int size)
        {
            AddParameter(command, name, dbType, size, ParameterDirection.ReturnValue, true, 0, 0, String.Empty, DataRowVersion.Default, DBNull.Value);
        }


        /// <summary>
        /// Adds parameters
        /// </summary>
        public void AddParameter(DbCommand command, params DbParameter[] parameters)
        {
            if (null == parameters || parameters.Length == 0)
                return;
            foreach (DbParameter p in parameters)
            {
                p.ParameterName = dbProvider.BuildParameterName(p.ParameterName);
                command.Parameters.Add(p);
            }

        }


        /// <summary>
        /// 给命令添加参数  where paramters
        /// </summary>
        /// <param name="command"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        internal DbCommand AddCommandParameter(DbCommand command, params Parameter[] parameters)
        {
            if (null == parameters || parameters.Length == 0)
                return command;
            //var i = 0;
            foreach (Parameter p in parameters)
            {
                DbParameter dbParameter = CreateParameter(p.ParameterName);// + i
                dbParameter.Value = p.ParameterValue;
                if (p.ParameterDbType.HasValue)
                    dbParameter.DbType = p.ParameterDbType.Value;
                if (p.ParameterSize.HasValue)
                    dbParameter.Size = p.ParameterSize.Value;
                command.Parameters.Add(dbParameter);
                //i++;
            }

            return command;
        }

        #endregion

        #region Batch Database

        private DbConnection batchConnection = null;

        private BatchCommander batchCommander = null;

        /// <summary>
        /// Gets a value indicating whether this instance is batch connection.
        /// </summary>
        /// <value>
        /// 	<c>true</c> if this instance is batch connection; otherwise, false.
        /// </value>
        public bool IsBatchConnection
        {
            get
            {
                return (batchConnection != null);
            }
        }

        /// <summary>
        /// Begins the batch connection.   the default size of the batch is 10.
        /// </summary>
        public void BeginBatchConnection()
        {
            BeginBatchConnection(10);
        }

        /// <summary>
        /// Begins the batch connection.
        /// </summary>
        /// <param name="batchSize">Size of the batch.</param>
        public void BeginBatchConnection(int batchSize)
        {
            BeginBatchConnection(batchSize, (DbTransaction)null);
        }

        /// <summary>
        /// Begins the batch connection.
        /// </summary>
        /// <param name="batchSize">Size of the batch.</param>
        /// <param name="tran">The tran.</param>
        public void BeginBatchConnection(int batchSize, DbTransaction tran)
        {
            batchConnection = CreateConnection(true);

            batchCommander = new BatchCommander(this, batchSize, tran);
        }

        /// <summary>
        /// Begins the batch connection.
        /// </summary>
        /// <param name="batchSize">Size of the batch.</param>
        /// <param name="il">The il.</param>
        public void BeginBatchConnection(int batchSize, IsolationLevel il)
        {
            batchConnection = CreateConnection(true);

            batchCommander = new BatchCommander(this, batchSize, il);
        }

        /// <summary>
        /// Ends the batch connection.
        /// </summary>
        public void EndBatchConnection()
        {
            batchCommander.Close();
            CloseConnection(batchConnection);
            batchConnection = null;
            batchCommander = null;
        }

        /// <summary>
        /// Executes the pending batch operations.
        /// </summary>
        public void ExecutePendingBatchOperations()
        {
            batchCommander.ExecuteBatch();
        }

        #endregion
    }
}