using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using CSFramework.DB.Common;
using CSFramework.DB.Models;
using MySql.Data.MySqlClient;
using MySql.Data.Types;
using Oracle.ManagedDataAccess.Client;
using Oracle.ManagedDataAccess.Types;

namespace CSFramework.DB
{
    /// <summary>
    /// 通用多数据库底层驱动（支持SqlServer,MySQL,Oracle) 
    /// 1.依赖app.config文件,需要配置数据库提供者.
    /// 2.将app.config文件内容复制到应用程序的配置文件，因程序内使用AppSettings以及加载Oracle,MySql底层驱动。
    /// </summary>
    public class DatabaseMDB : IDatabase
    {
        private string _ConnectionString;
        private DatabaseType _DatabaseType;

        public DatabaseMDB(DatabaseType providerType, string connectionString)
        {
            _DatabaseType = providerType;
            _ConnectionString = connectionString;
        }

        public DatabaseType DatabaseType => _DatabaseType;

        public CommandHelper CreateSqlProc(string spName)
        {
            var db =
                DatabaseFactory.CreateDatabase(_DatabaseType,
                    _ConnectionString);
            return db.CreateSqlProc(spName);
        }

        public CommandHelper CreateCommand(string commandText)
        {
            var db =
                DatabaseFactory.CreateDatabase(_DatabaseType,
                    _ConnectionString);
            return db.CreateCommand(commandText);
        }

        public int CommandTimeout
        {
            get
            {
                var defaultTimeOut = 30;
                return DatabaseFactory.CommandTimeOut > 0
                    ? DatabaseFactory.CommandTimeOut
                    : defaultTimeOut;
            }
        }

        public int ConnectionTimeout
        {
            get
            {
                var defaultTimeOut = 15;
                return DatabaseFactory.ConnectionTimeOut > 0
                    ? DatabaseFactory.ConnectionTimeOut
                    : defaultTimeOut;
            }
        }

        public string ConnectionString
        {
            get => _ConnectionString;
            set => _ConnectionString = value;
        }

        public DateTime GetServerTime()
        {
            var db =
                DatabaseFactory.CreateDatabase(_DatabaseType,
                    _ConnectionString);
            return db.GetServerTime();
        }


        public string BuildConnectionString(string server, int port,
            string dbName, string uid, string pwd, int timeout = 15)
        {
            if (_DatabaseType == DatabaseType.SqlServer)
                return new DatabaseMSSQL("").BuildConnectionString(server, port,
                    dbName, uid, pwd, timeout);

            if (_DatabaseType == DatabaseType.MySQL)
                return new DatabaseMySQL("").BuildConnectionString(server, port,
                    dbName, uid, pwd, timeout);

            if (_DatabaseType == DatabaseType.Oracle)
                return new DatabaseOracle("").BuildConnectionString(server,
                    port, dbName, uid, pwd, timeout);

            throw new Exception("数据库类型不支持！");
        }

        public int DefaultPort
        {
            get
            {
                if (_DatabaseType == DatabaseType.SqlServer) return 1433;
                if (_DatabaseType == DatabaseType.MySQL) return 3306;
                if (_DatabaseType == DatabaseType.Oracle) return 1521;
                throw new Exception("数据库类型不支持！");
            }
        }

        public DbParameter CreateTimestampParameter(string parameterName,
            string fieldName)
        {
            DbParameter p;

            if (DatabaseType.SqlServer == _DatabaseType)
            {
                p = new SqlParameter();
                (p as SqlParameter).SqlDbType = SqlDbType.Timestamp;
            }
            else if (DatabaseType.MySQL == _DatabaseType)
            {
                p = new MySqlParameter();
                (p as MySqlParameter).MySqlDbType = MySqlDbType.Timestamp;
            }
            else if (DatabaseType.Oracle == _DatabaseType)
            {
                p = new OracleParameter();
                (p as OracleParameter).OracleDbType = OracleDbType.TimeStamp;
            }
            else
            {
                throw new Exception("请扩展" + GetType().Name +
                                    ".CreateTimestampParameter方法！");
            }

            p.SourceColumn = fieldName;

            return p;
        }

        public Type DateTimeType
        {
            get
            {
                var T = typeof(SqlDateTime);
                switch (_DatabaseType)
                {
                    case DatabaseType.SqlServer:
                        T = typeof(SqlDateTime);
                        break;
                    case DatabaseType.MySQL:
                        T = typeof(MySqlDateTime);
                        break;
                    case DatabaseType.Oracle:
                        T = typeof(OracleDate);
                        break;
                }

                return T;
            }
        }

        public string ParamSymboName { get; set; }

        public DbCommand CreateCommand(string commandText,
            CommandType commandType)
        {
            var cmd = DataProviderFactory.GetFactory(_DatabaseType)
                .CreateCommand();
            cmd.CommandText = CodeSafeHelper.GetSafeSQL(commandText);
            cmd.CommandType = commandType;
            cmd.CommandTimeout = CommandTimeout;
            return cmd;
        }

        public DbParameter CreateParameter(string parameterName,
            object parameterValue)
        {
            var p = DataProviderFactory.GetFactory(_DatabaseType)
                .CreateParameter();
            p.ParameterName = parameterName;
            p.Value = parameterValue;
            return p;
        }

        public DbParameter CreateParameter(string parameterName,
            DbType parameterType, int size, string sourceColumn,
            object parameterValue)
        {
            var p = DataProviderFactory.GetFactory(_DatabaseType)
                .CreateParameter();
            p.ParameterName = parameterName;
            p.Value = parameterValue;
            p.DbType = parameterType;
            p.Size = size;
            p.SourceColumn = sourceColumn;
            return p;
        }

        public DbCommandBuilder CreateCommandBuilder()
        {
            return DataProviderFactory.GetFactory(_DatabaseType)
                .CreateCommandBuilder();
        }

        public DbDataAdapter CreateDataAdapter()
        {
            return DataProviderFactory.GetFactory(_DatabaseType)
                .CreateDataAdapter();
        }

        public DbDataAdapter CreateDataAdapter(
            AdapterRowUpdatingEvent eventHandler)
        {
            return DataProviderFactory.GetFactory(_DatabaseType)
                .CreateDataAdapter();
        }

        public DbConnection CreateConnection()
        {
            return CreateConnection(_DatabaseType, _ConnectionString);
        }

        public DbConnection CreateConnection(string connectionString)
        {
            return CreateConnection(_DatabaseType, connectionString);
        }

        public DbTransaction TransBegin()
        {
            return CreateConnection().BeginTransaction();
        }

        public void TransCommit(DbTransaction trans,
            bool closeConnection = false)
        {
            trans.Commit();
            if (closeConnection) Close(trans.Connection);
        }

        public void TransRollback(DbTransaction trans,
            bool closeConnection = false)
        {
            trans.Rollback();
            if (closeConnection) Close(trans.Connection);
        }

        public DataSet GetDataSet(string SQL)
        {
            DbConnection connection = null;
            try
            {
                connection = CreateConnection();
                return GetDataSet(SQL, connection);
            }
            finally
            {
                Close(connection);
            }
        }

        /// <summary>
        /// 执行带参数的 Sql 语句或存储过程，并返回 DataSet 对象。
        /// </summary>
        /// <param name="SQL">要执行的 Sql 语句或存储过程名等。</param>
        /// <param name="type">CommandType 参数类型，即该命令是 sql 语句，还是存储过程名等。</param>
        /// <param name="paramlist">参数集合。</param>
        /// <returns>DataSet 对象。</returns>
        public virtual DataSet GetDataSet(string SQL, CommandType type,
            IDataParameter[] paramlist)
        {
            DbCommand cmd = null;
            DbDataAdapter adapter = null;
            DbConnection connection = null;
            try
            {
                connection = CreateConnection();

                cmd = DataProviderFactory.GetFactory(_DatabaseType)
                    .CreateCommand();
                cmd.CommandText = CodeSafeHelper.GetSafeSQL(SQL);
                cmd.CommandType = type;
                cmd.Connection = connection;

                if (paramlist != null)
                    cmd.Parameters.AddRange(paramlist);

                var ds = new DataSet();
                adapter = DataProviderFactory.GetFactory(_DatabaseType)
                    .CreateDataAdapter();
                adapter.SelectCommand = cmd;
                adapter.Fill(ds);

                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (adapter != null) adapter.Dispose();
                if (cmd != null) cmd.Dispose();
                Close(connection);
            }
        }

        public DataSet GetDataSet(DbCommand cmd)
        {
            DbDataAdapter adapter = null;
            DbConnection connection = null;
            try
            {
                if (cmd.Connection == null)
                    cmd.Connection = connection = CreateConnection();

                var ds = new DataSet();
                adapter = DataProviderFactory.GetFactory(_DatabaseType)
                    .CreateDataAdapter();
                adapter.SelectCommand = cmd;
                adapter.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (adapter != null) adapter.Dispose();
                if (cmd != null) cmd.Dispose();
                Close(connection);
            }
        }

        public DataTable GetTable(string SQL, string tableName = "")
        {
            var dt = GetDataSet(SQL).Tables[0];
            if (tableName != "") dt.TableName = tableName;
            return dt;
        }

        public DataTable GetTop(int top, string tableName, string fields = "",
            List<DbParameter> where = null, string orderBy = "")
        {
            var db =
                DatabaseFactory.CreateDatabase(_DatabaseType,
                    _ConnectionString);
            return db.GetTop(top, tableName, fields, where, orderBy);
        }

        public DataTable meta_GetTableNames(string dbName = "")
        {
            var db =
                DatabaseFactory.CreateDatabase(_DatabaseType,
                    _ConnectionString);
            return db.meta_GetTableNames(dbName);
        }

        public DataTable meta_GetTableStru(string tableName)
        {
            var db =
                DatabaseFactory.CreateDatabase(_DatabaseType,
                    _ConnectionString);
            return db.meta_GetTableStru(tableName);
        }

        public DataTable meta_GetDatabase()
        {
            var db =
                DatabaseFactory.CreateDatabase(_DatabaseType,
                    _ConnectionString);
            return db.meta_GetDatabase();
        }

        public List<MetaDBNames> meta_GetDatabaseList()
        {
            var db =
                DatabaseFactory.CreateDatabase(_DatabaseType,
                    _ConnectionString);
            return db.meta_GetDatabaseList();
        }

        public List<MetaTableNames> meta_GetTableNamesList(string dbName = "")
        {
            var db =
                DatabaseFactory.CreateDatabase(_DatabaseType,
                    _ConnectionString);
            return db.meta_GetTableNamesList(dbName);
        }

        public List<MetaTableStructure> meta_GetTableStruList(string tableName)
        {
            var db =
                DatabaseFactory.CreateDatabase(_DatabaseType,
                    _ConnectionString);
            return db.meta_GetTableStruList(tableName);
        }

        public List<MetaStoreProcedure> meta_GetStoreProcList(
            string dbName = "")
        {
            var db =
                DatabaseFactory.CreateDatabase(_DatabaseType,
                    _ConnectionString);
            return db.meta_GetStoreProcList(dbName);
        }

        public DataTable GetTable(DbCommand cmd, string tableName = "")
        {
            var dt = GetDataSet(cmd).Tables[0];
            if (tableName != "") dt.TableName = tableName;
            return dt;
        }

        public DataRow GetDataRow(string SQL)
        {
            var dt = GetDataSet(SQL).Tables[0];
            return dt.Rows.Count > 0 ? dt.Rows[0] : null;
        }

        public int ExecuteSQL(string SQL)
        {
            DbConnection connection = null;
            try
            {
                connection = CreateConnection();
                return ExecuteSQL(SQL, connection);
            }
            finally
            {
                Close(connection);
            }
        }

        public int ExecuteCommand(DbCommand cmd)
        {
            DbConnection connection = null;
            try
            {
                if (cmd.Connection == null && cmd.Transaction == null)
                    cmd.Connection =
                        connection = CreateConnection(_ConnectionString);
                return cmd.ExecuteNonQuery();
            }
            finally
            {
                if (cmd.Transaction == null) Close(connection);
            }
        }

        public object ExecuteScalar(string SQL)
        {
            DbConnection connection = null;
            DbCommand cmd = null;
            try
            {
                connection = CreateConnection();
                cmd = CreateCommand(connection, SQL);
                return cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (cmd != null) cmd.Dispose();
                Close(connection);
            }
        }

        public object ExecuteScalar(DbCommand cmd)
        {
            DbConnection connection = null;
            try
            {
                if (cmd.Connection == null)
                    cmd.Connection = connection = CreateConnection();

                return cmd.ExecuteScalar();
            }
            finally
            {
                Close(connection);
            }
        }

        public DbDataReader ExecuteReader(string SQL)
        {
            DbConnection connection = null;
            try
            {
                connection = CreateConnection();
                return ExecuteReader(SQL, connection);
            }
            finally
            {
                Close(connection);
            }
        }

        public DbDataReader ExecuteReader(DbCommand cmd)
        {
            DbConnection connection = null;
            try
            {
                if (cmd.Connection == null) cmd.Connection = CreateConnection();
                return cmd.ExecuteReader();
            }
            finally
            {
                Close(connection);
            }
        }

        public T ExecuteReader<T>(DbCommand cmd) where T : new()
        {
            DbConnection connection = null;
            try
            {
                if (cmd.Connection == null) cmd.Connection = CreateConnection();

                var obj = default(T);
                using (var dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                        obj = DbTools.Convert2Object<T>(dataReader);
                }

                return obj;
            }
            finally
            {
                Close(connection);
            }
        }

        public T ExecuteReader<T>(string SQL) where T : new()
        {
            DbConnection connection = null;
            DbCommand command = null;
            DbDataReader dataReader = null;

            try
            {
                using (connection = CreateConnection())
                {
                    using (command = CreateCommand(SQL, CommandType.Text))
                    {
                        command.Connection = connection;
                        var obj = default(T);
                        using (dataReader = command.ExecuteReader())
                        {
                            if (dataReader.Read())
                                obj = DbTools.Convert2Object<T>(dataReader);
                        }

                        return obj;
                    }
                }
            }
            finally
            {
                if (connection != null) connection.Dispose();
                if (command != null) command.Dispose();
                if (dataReader != null) dataReader.Dispose();
            }
        }

        public List<T> ExecuteReader<T>(string SQL,
            Func<DbDataReader, T> action) where T : new()
        {
            DbConnection connection = null;
            DbCommand command = null;
            DbDataReader dataReader = null;

            try
            {
                using (connection = CreateConnection())
                {
                    using (command =
                               CreateCommand(connection, SQL, CommandType.Text))
                    {
                        var list = new List<T>();
                        using (dataReader = command.ExecuteReader())
                        {
                            while (dataReader.Read())
                                list.Add(action.Invoke(dataReader));
                        }

                        return list;
                    }
                }
            }
            finally
            {
                if (connection != null) connection.Dispose();
                if (command != null) command.Dispose();
                if (dataReader != null) dataReader.Dispose();
            }
        }

        public List<T> ExecuteReader<T>(DbCommand cmd,
            Func<DbDataReader, T> action) where T : new()
        {
            DbConnection connection = null;
            try
            {
                if (cmd.Connection == null) cmd.Connection = CreateConnection();

                var list = new List<T>();
                using (var dataReader = cmd.ExecuteReader())
                {
                    while (dataReader.Read())
                        list.Add(action.Invoke(dataReader));
                }

                return list;
            }
            finally
            {
                Close(connection);
            }
        }


        public List<T> ExecuteReaderList<T>(string SQL) where T : new()
        {
            DbConnection connection = null;
            DbCommand command = null;
            DbDataReader dataReader = null;

            try
            {
                using (connection = CreateConnection())
                {
                    using (command = CreateCommand(SQL, CommandType.Text))
                    {
                        command.Connection = connection;
                        var list = new List<T>();
                        using (dataReader = command.ExecuteReader())
                        {
                            while (dataReader.Read())
                                list.Add(DbTools.Convert2Object<T>(dataReader));
                        }

                        return list;
                    }
                }
            }
            finally
            {
                if (connection != null) connection.Dispose();
                if (command != null) command.Dispose();
                if (dataReader != null) dataReader.Dispose();
            }
        }

        public List<T> ExecuteReaderList<T>(DbCommand cmd) where T : new()
        {
            DbConnection connection = null;
            try
            {
                if (cmd.Connection == null) cmd.Connection = CreateConnection();

                var list = new List<T>();
                using (var dataReader = cmd.ExecuteReader())
                {
                    while (dataReader.Read())
                        list.Add(DbTools.Convert2Object<T>(dataReader));
                }

                return list;
            }
            finally
            {
                Close(connection);
            }
        }

        public List<string> GetStringList(string SQL)
        {
            DbConnection connection = null;
            DbCommand command = null;
            DbDataReader dataReader = null;

            try
            {
                using (connection = CreateConnection())
                {
                    using (command = CreateCommand(SQL, CommandType.Text))
                    {
                        command.Connection = connection;
                        var list = new List<string>();
                        using (dataReader = command.ExecuteReader())
                        {
                            while (dataReader.Read())
                                list.Add(dataReader.GetValue(0)
                                    .ToString()); //取第1列
                        }

                        return list;
                    }
                }
            }
            finally
            {
                if (connection != null) connection.Dispose();
                if (command != null) command.Dispose();
                if (dataReader != null) dataReader.Dispose();
            }
        }

        public List<string> GetStringList(DbCommand cmd)
        {
            DbConnection connection = null;
            try
            {
                if (cmd.Connection == null) cmd.Connection = CreateConnection();

                var list = new List<string>();
                using (var dataReader = cmd.ExecuteReader())
                {
                    while (dataReader.Read())
                        list.Add(dataReader.GetValue(0).ToString()); //取第1列
                }

                return list;
            }
            finally
            {
                Close(connection);
            }
        }

        public void Close(DbConnection connection)
        {
            if (connection != null)
            {
                connection.Close();
                connection.Dispose();
            }
        }

        /// <summary>
        /// 在事务内提交数据
        /// </summary>
        /// <param name="trans">事务</param>
        /// <param name="SQL">SQL语句</param>
        /// <returns></returns>
        public int ExecuteTrans(DbTransaction trans, string SQL)
        {
            DbCommand cmd = null;
            var i = -1;
            try
            {
                cmd = DataProviderFactory.GetFactory(_DatabaseType)
                    .CreateCommand();
                cmd.CommandText = CodeSafeHelper.GetSafeSQL(SQL);
                cmd.CommandType = CommandType.Text;
                cmd.Connection = trans.Connection;
                cmd.Transaction = trans;

                i = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (cmd != null) cmd.Dispose();
            }

            return i;
        }

        public int ExecuteTrans(DbTransaction trans, DbCommand cmd)
        {
            var i = -1;
            try
            {
                cmd.Connection = trans.Connection;
                cmd.Transaction = trans;
                i = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (cmd != null) cmd.Dispose();
            }

            return i;
        }

        public DbType ToDbType(string sourceType)
        {
            if (_DatabaseType == DatabaseType.SqlServer)
                return new DatabaseMSSQL("").ToDbType(sourceType);

            if (_DatabaseType == DatabaseType.MySQL)
                return new DatabaseMySQL("").ToDbType(sourceType);

            if (_DatabaseType == DatabaseType.Oracle)
                return new DatabaseOracle("").ToDbType(sourceType);

            throw new Exception("数据库类型不支持！");
        }

        public Type ToNetType(string sourceType)
        {
            if (_DatabaseType == DatabaseType.SqlServer)
                return new DatabaseMSSQL("").ToNetType(sourceType);

            if (_DatabaseType == DatabaseType.MySQL)
                return new DatabaseMySQL("").ToNetType(sourceType);

            if (_DatabaseType == DatabaseType.Oracle)
                return new DatabaseOracle("").ToNetType(sourceType);

            throw new Exception("数据库类型不支持！");
        }

        /// <summary>
        /// 返回包含参数符号的参数名称,比如：@Code, :Code, ?p_Code
        /// </summary>
        /// <param name="paramName"></param>
        /// <returns></returns>
        public string ParseParamName(string paramName)
        {
            var db =
                DatabaseFactory.CreateDatabase(_DatabaseType,
                    _ConnectionString);
            return db.ParseParamName(paramName);
        }


        /// <summary>
        /// 获取当前表最新(最大)的自增字段值
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public int GetMaxID(string tableName)
        {
            var db =
                DatabaseFactory.CreateDatabase(_DatabaseType,
                    _ConnectionString);
            return db.GetMaxID(tableName);
        }


        public bool UpdateDataSet(DataTable ds, string tableName, string KEY)
        {
            //先删除原表数据
            var sql = "";
            var ErrorMsg = "";
            foreach (DataRow dr in ds.Rows)
            {
                sql = string.Format(@"delete from {0} where {1} in ('{2}')",
                    tableName, KEY, dr[KEY]);
                break;
            }

            var a = ExecuteSQL(sql);
            var colMapping = new SqlBulkCopyColumnMapping[ds.Columns.Count];
            for (var i = 0; i < ds.Columns.Count; i++)
                colMapping[i] =
                    new SqlBulkCopyColumnMapping(ds.Columns[i].ColumnName,
                        ds.Columns[i].ColumnName);

            // DataTableToSQLServer(ds, _ConnectionString, tableName, colMapping, ref ErrorMsg);
            return true;
        }


        public bool InstDataSet(DataTable ds, string tableName)
        {
            //先删除原表数据
            var ErrorMsg = "";
            var colMapping = new SqlBulkCopyColumnMapping[ds.Columns.Count];
            for (var i = 0; i < ds.Columns.Count; i++)
                colMapping[i] =
                    new SqlBulkCopyColumnMapping(ds.Columns[i].ColumnName,
                        ds.Columns[i].ColumnName);

            //DataTableToSQLServer(ds, _ConnectionString, tableName, colMapping, ref ErrorMsg);
            return true;
        }

        public DbConnection CreateConnection(DatabaseType providerType,
            string connectionString)
        {
            var DBConn = DataProviderFactory.GetFactory(providerType)
                .CreateConnection();
            DBConn.ConnectionString = connectionString;
            DBConn.Open(); //打开连接
            return DBConn;
        }

        public DbCommand CreateCommand(DbConnection conn, string SQL)
        {
            var cmd = DataProviderFactory.GetFactory(_DatabaseType)
                .CreateCommand();
            cmd.CommandText = CodeSafeHelper.GetSafeSQL(SQL);
            cmd.CommandType = CommandType.Text;
            cmd.Connection = conn;
            cmd.CommandTimeout = CommandTimeout;
            return cmd;
        }

        public DbCommand CreateCommand(DbConnection conn, string SQL,
            CommandType type)
        {
            var cmd = DataProviderFactory.GetFactory(_DatabaseType)
                .CreateCommand();
            cmd.CommandText = CodeSafeHelper.GetSafeSQL(SQL);
            cmd.CommandType = type;
            cmd.Connection = conn;
            cmd.CommandTimeout = CommandTimeout;
            return cmd;
        }

        public DataSet GetDataSet(string SQL, DbConnection conn)
        {
            DbCommand cmd = null;
            DbDataAdapter adapter = null;
            try
            {
                cmd = DataProviderFactory.GetFactory(_DatabaseType)
                    .CreateCommand();
                cmd.CommandText = CodeSafeHelper.GetSafeSQL(SQL);
                cmd.CommandType = CommandType.Text;
                cmd.Connection = conn;

                var ds = new DataSet();
                adapter = DataProviderFactory.GetFactory(_DatabaseType)
                    .CreateDataAdapter();
                adapter.SelectCommand = cmd;
                adapter.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (adapter != null) adapter.Dispose();
                if (cmd != null) cmd.Dispose();
            }
        }

        public DataTable GetTable(string SQL, DbConnection conn)
        {
            return GetDataSet(SQL, conn).Tables[0];
        }

        public int ExecuteSQL(string SQL, DbConnection conn)
        {
            DbCommand cmd = null;
            var i = -1;
            try
            {
                cmd = DataProviderFactory.GetFactory(_DatabaseType)
                    .CreateCommand();
                cmd.CommandText = CodeSafeHelper.GetSafeSQL(SQL);
                cmd.CommandType = CommandType.Text;
                cmd.Connection = conn;
                i = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (cmd != null) cmd.Dispose();
            }

            return i;
        }

        public object ExecuteScalar(string SQL, DbConnection conn)
        {
            DbCommand cmd = null;
            object o = null;
            try
            {
                cmd = DataProviderFactory.GetFactory(_DatabaseType)
                    .CreateCommand();
                cmd.CommandText = CodeSafeHelper.GetSafeSQL(SQL);
                cmd.CommandType = CommandType.Text;
                cmd.Connection = conn;
                o = cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (cmd != null) cmd.Dispose();
            }

            return o;
        }

        public DbDataReader ExecuteReader(string SQL, DbConnection conn)
        {
            DbCommand cmd = null;
            DbDataReader o = null;
            try
            {
                cmd = DataProviderFactory.GetFactory(_DatabaseType)
                    .CreateCommand();
                cmd.CommandText = CodeSafeHelper.GetSafeSQL(SQL);
                cmd.CommandType = CommandType.Text;
                cmd.Connection = conn;
                o = cmd.ExecuteReader();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (cmd != null) cmd.Dispose();
            }

            return o;
        }
    }
}