using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Text;
using CSFramework.DB.Common;
using CSFramework.DB.Models;
using MySql.Data.MySqlClient;
using MySql.Data.Types;

namespace CSFramework.DB
{
    public class DatabaseMySQL : IDatabase
    {
        private string _ConnectionString;

        //存储过程、函数参数前缀
        private string ParamSymboName1 = "p_";

        public DatabaseMySQL(string connectionString)
        {
            _ConnectionString = connectionString;
        }

        public DatabaseType DatabaseType => DatabaseType.MySQL;

        public CommandHelper CreateSqlProc(string spName)
        {
            return new CommandHelper(this, spName, CommandType.StoredProcedure);
        }

        public CommandHelper CreateCommand(string commandText)
        {
            return new CommandHelper(this, commandText, CommandType.Text);
        }

        public string BuildConnectionString(string server, int port,
            string dbName, string uid, string pwd, int timeout = 15)
        {
            //Convert Zero Datetime=True;
            //Allow Zero Datetime=True; //不建议添加此属性！经测试，若加上此属性给日期类型赋值会报错： 所需类型是 MySqlDateTime，值类型与列类型不匹配。
            const string DEF_SQL_CONNECTION1 =
                "Server={0};Database={1};User ID={2};Password={3};Connection TimeOut={4};Charset=utf8;Pooling=true;Min Pool Size=0;Max Pool Size=500;Connection Lifetime=0;Convert Zero Datetime=True;";
            const string DEF_SQL_CONNECTION2 =
                "Server={0};Port={1};Database={2};User ID={3};Password={4};Connection TimeOut={5};Charset=utf8;Pooling=true;Min Pool Size=0;Max Pool Size=500;Connection Lifetime=0;Convert Zero Datetime=True;";

            var connstr = "";
            if (port == DefaultPort)
                connstr = string.Format(DEF_SQL_CONNECTION1, server, dbName,
                    uid, pwd, timeout);
            else
                connstr = string.Format(DEF_SQL_CONNECTION2, server,
                    port.ToString(), dbName, uid, pwd, timeout);
            return connstr;
        }

        public int DefaultPort => 3306;


        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;
            }
        }

        /// <summary>
        /// 服务器时间
        /// </summary>
        public DateTime GetServerTime()
        {
            var o = ExecuteScalar("SELECT NOW();");
            return Convert.ToDateTime(o);
        }

        public Type DateTimeType => typeof(MySqlDateTime);

        /// <summary>
        /// 参数符号.如: WHERE Code=?Code, MySQL默认不带参数符号.
        /// 
        /// Insert(X)VALUES(?X)
        /// 
        /// 注意：MySQL数据库，存储过程、函数的参数前面统一加了"p_"
        /// 
        /// </summary>
        public string ParamSymboName { get; set; } = "?p_";

        /// <summary>
        /// 返回包含参数符号的参数名称,比如：@Code, :Code, ?p_Code
        /// </summary>
        /// <param name="paramName"></param>
        /// <returns></returns>
        public string ParseParamName(string paramName)
        {
            if (string.IsNullOrEmpty(ParamSymboName) ||
                string.IsNullOrEmpty(paramName))
            {
                return paramName;
            }
            else
            {
                if (paramName.IndexOf(ParamSymboName) == 0) //首字母包含默认参数符号 ?p_
                {
                    return paramName;
                }
                else
                {
                    if (paramName.IndexOf(ParamSymboName1) ==
                        0) //参数前缀是p_开头, 如：p_Name，改为:?p_Name
                        return "?" + paramName;
                    else
                        return ParamSymboName + paramName; //添加参数符号
                }
            }
        }

        /// <summary>
        /// 创建时间戳参数
        /// </summary>
        /// <param name="parameterName">参数名</param>
        /// <param name="fieldName">字段名</param>
        /// <returns></returns>
        public DbParameter CreateTimestampParameter(string parameterName,
            string fieldName)
        {
            var p = new MySqlParameter();
            p.ParameterName = parameterName;
            p.MySqlDbType = MySqlDbType.Timestamp;
            p.Size = 8;
            p.SourceColumn = fieldName;
            return p;
        }

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

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

        public DbCommand CreateCommand(string commandText,
            CommandType commandType)
        {
            if (CommandType.Text == commandType)
                commandText = ParseSqlEnd(commandText); //SQL结尾添加;

            return new MySqlCommand(commandText)
            {
                CommandText = commandText, CommandType = commandType,
                CommandTimeout = CommandTimeout
            };
        }

        public DbParameter CreateParameter(string parameterName,
            object parameterValue)
        {
            parameterName = ParseParamName(parameterName);
            return new MySqlParameter
            {
                ParameterName = parameterName, Value = parameterValue,
                IsNullable = true
            };
        }

        public DbParameter CreateParameter(string parameterName,
            DbType parameterType, int size, string sourceColumn,
            object parameterValue)
        {
            parameterName = ParseParamName(parameterName);
            return new MySqlParameter
            {
                ParameterName = parameterName, DbType = parameterType,
                Size = size, SourceColumn = sourceColumn,
                Value = parameterValue, IsNullable = true
            };
        }


        public DbCommandBuilder CreateCommandBuilder()
        {
            return new MySqlCommandBuilder();
        }

        public DbDataAdapter CreateDataAdapter()
        {
            return new MySqlDataAdapter();
        }

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

        public DbConnection CreateConnection(string connectionString)
        {
            DbConnection DBConn = new MySqlConnection(connectionString);
            DBConn.Open(); //打开连接
            return DBConn;
        }

        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 int ExecuteCommand(DbCommand cmd)
        {
            if (CommandType.Text == cmd.CommandType)
                cmd.CommandText =
                    CodeSafeHelper.GetSafeSQL(
                        ParseSqlEnd(cmd.CommandText)); //SQL结尾添加;

            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 DbDataReader ExecuteReader(string SQL)
        {
            SQL = ParseSqlEnd(SQL); //SQL结尾添加;

            DbCommand cmd = null;
            DbDataReader o = null;
            DbConnection conn = null;
            try
            {
                conn = CreateConnection(_ConnectionString);
                cmd = new MySqlCommand();
                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();
                Close(conn);
            }

            return o;
        }

        public DbDataReader ExecuteReader(DbCommand cmd)
        {
            if (CommandType.Text == cmd.CommandType)
                cmd.CommandText =
                    CodeSafeHelper.GetSafeSQL(
                        ParseSqlEnd(cmd.CommandText)); //SQL结尾添加;

            DbConnection connection = null;
            try
            {
                if (cmd.Connection == null)
                {
                    connection = CreateConnection();
                    cmd.Connection = connection;
                }

                return cmd.ExecuteReader();
            }
            finally
            {
                if (connection != null) Close(connection);
            }
        }

        public T ExecuteReader<T>(DbCommand cmd) where T : new()
        {
            if (CommandType.Text == cmd.CommandType)
                cmd.CommandText =
                    CodeSafeHelper.GetSafeSQL(
                        ParseSqlEnd(cmd.CommandText)); //SQL结尾添加;

            DbConnection connection = null;
            try
            {
                if (cmd.Connection == null)
                    cmd.Connection = 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()
        {
            SQL = ParseSqlEnd(SQL); //SQL结尾添加;

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

                    return obj;
                }
            }
        }

        public List<T> ExecuteReaderList<T>(string SQL) where T : new()
        {
            SQL = ParseSqlEnd(SQL); //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<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()
        {
            if (CommandType.Text == cmd.CommandType)
                cmd.CommandText =
                    CodeSafeHelper.GetSafeSQL(
                        ParseSqlEnd(cmd.CommandText)); //SQL结尾添加;

            DbConnection connection = null;
            try
            {
                if (cmd.Connection == null)
                    cmd.Connection = 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<T> ExecuteReader<T>(string SQL,
            Func<DbDataReader, T> action) where T : new()
        {
            SQL = ParseSqlEnd(SQL); //SQL结尾添加;

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

                    return list;
                }
            }
        }

        public List<T> ExecuteReader<T>(DbCommand cmd,
            Func<DbDataReader, T> action) where T : new()
        {
            if (CommandType.Text == cmd.CommandType)
                cmd.CommandText =
                    CodeSafeHelper.GetSafeSQL(
                        ParseSqlEnd(cmd.CommandText)); //SQL结尾添加;

            DbConnection connection = null;
            try
            {
                if (cmd.Connection == null)
                    cmd.Connection = 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<string> GetStringList(string SQL)
        {
            SQL = ParseSqlEnd(SQL); //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)
        {
            if (CommandType.Text == cmd.CommandType)
                cmd.CommandText =
                    CodeSafeHelper.GetSafeSQL(
                        ParseSqlEnd(cmd.CommandText)); //SQL结尾添加;

            DbConnection connection = null;
            try
            {
                if (cmd.Connection == null)
                    cmd.Connection = 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 object ExecuteScalar(string SQL)
        {
            SQL = ParseSqlEnd(SQL); //SQL结尾添加;

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

        public object ExecuteScalar(DbCommand cmd)
        {
            if (CommandType.Text == cmd.CommandType)
                cmd.CommandText =
                    CodeSafeHelper.GetSafeSQL(
                        ParseSqlEnd(cmd.CommandText)); //SQL结尾添加;

            DbConnection connection = null;
            try
            {
                if (cmd.Connection == null)
                    cmd.Connection = connection = CreateConnection();

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

        public int ExecuteSQL(string SQL)
        {
            SQL = ParseSqlEnd(SQL); //SQL结尾添加;
            DbConnection connection = null;
            try
            {
                connection = CreateConnection();
                return ExecuteSQL(SQL, connection);
            }
            finally
            {
                Close(connection);
            }
        }

        /// <summary>
        /// 在事务内提交数据
        /// </summary>
        /// <param name="trans">事务</param>
        /// <param name="SQL">SQL语句</param>
        /// <returns></returns>
        public int ExecuteTrans(DbTransaction trans, string SQL)
        {
            SQL = ParseSqlEnd(SQL); //SQL结尾添加;
            DbCommand cmd = null;
            var i = -1;
            try
            {
                cmd = new MySqlCommand();
                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)
        {
            if (CommandType.Text == cmd.CommandType)
                cmd.CommandText =
                    CodeSafeHelper.GetSafeSQL(
                        ParseSqlEnd(cmd.CommandText)); //SQL结尾添加;

            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 DataSet GetDataSet(DbCommand cmd)
        {
            if (CommandType.Text == cmd.CommandType)
                cmd.CommandText =
                    CodeSafeHelper.GetSafeSQL(
                        ParseSqlEnd(cmd.CommandText)); //SQL结尾添加;

            DbDataAdapter adapter = null;
            DbConnection connection = null;
            try
            {
                if (cmd.Connection == null)
                {
                    connection = CreateConnection(_ConnectionString);
                    cmd.Connection = connection;
                }

                var ds = new DataSet();
                adapter = new MySqlDataAdapter();
                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(string SQL)
        {
            SQL = ParseSqlEnd(SQL); //SQL结尾添加;
            DbConnection connection = null;
            try
            {
                connection = CreateConnection(_ConnectionString);
                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)
        {
            if (CommandType.Text == type)
                SQL = ParseSqlEnd(SQL); //SQL结尾添加;

            DbCommand cmd = null;
            DbDataAdapter adapter = null;
            DbConnection connection = null;
            try
            {
                connection = CreateConnection();

                cmd = CreateCommand(SQL, type);
                cmd.Connection = connection;

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

                var ds = new DataSet();
                adapter = 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 = "")
        {
            SQL = ParseSqlEnd(SQL); //SQL结尾添加;
            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 cmd = CreateCommand("");

            fields = string.IsNullOrWhiteSpace(fields) ? "*" : fields;
            orderBy = string.IsNullOrEmpty(orderBy)
                ? ""
                : " ORDER BY " + orderBy;

            var whereSQL = "";
            if (where != null && where.Count > 0)
            {
                string pName;
                foreach (var p in where)
                {
                    pName = p.ParameterName;
                    if (pName.IndexOf(ParamSymboName) >= 0) //参数名称带有符号,如：@Code
                        whereSQL = whereSQL +
                                   $" AND {pName.Replace(ParamSymboName, "")}={p.ParameterName}";
                    else
                        whereSQL = whereSQL +
                                   $" AND {p.ParameterName}={ParamSymboName + p.ParameterName}";

                    cmd.Command.Parameters.Add(p);
                }
            }

            whereSQL = string.IsNullOrEmpty(whereSQL)
                ? ""
                : " WHERE 1=1 " + whereSQL;

            var SQL =
                $"SELECT {fields} FROM {tableName} {whereSQL} {orderBy} LIMIT {top};";

            cmd.Command.CommandText = CodeSafeHelper.GetSafeSQL(SQL);
            return GetTable(cmd.Command, tableName);
        }

        public DataTable GetTable(DbCommand cmd, string tableName = "")
        {
            cmd.CommandText =
                CodeSafeHelper.GetSafeSQL(
                    ParseSqlEnd(cmd.CommandText)); //SQL结尾添加;

            var dt = GetDataSet(cmd).Tables[0];
            if (tableName != "") dt.TableName = tableName;
            return dt;
        }

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

        public DbType ToDbType(string sourceType)
        {
            return DBDataTypes.GetDbTypeByMySqlType(sourceType);

            ////若转换有误。
            //MySqlDbType type = (MySqlDbType)Enum.Parse(typeof(MySqlDbType), sourceType, true);
            //MySqlParameter p = new MySqlParameter("", null);
            //p.MySqlDbType = type;
            //p.ResetDbType();
            //return p.DbType;
        }

        public Type ToNetType(string sourceType)
        {
            return DBDataTypes.GetNetTypeByMySql(sourceType);
        }

        public DataTable meta_GetTableNames(string dbName = "")
        {
            var cmd = CreateCommand("");
            cmd.AddParam("P1", "BASE TABLE");
            cmd.AddParam("P2", "VIEW");
            var sql = "";
            if (string.IsNullOrWhiteSpace(dbName))
            {
                sql =
                    $"select 0 as id,TABLE_TYPE as type,TABLE_NAME as name from information_schema.tables where  TABLE_SCHEMA=(select database())  AND TABLE_TYPE IN ({ParamSymboName}P1,{ParamSymboName}P2)";
            }
            else
            {
                sql =
                    $"select 0 as id,TABLE_TYPE as type,TABLE_NAME as name from information_schema.tables where  TABLE_SCHEMA={ParamSymboName}DBName AND TABLE_TYPE IN ({ParamSymboName}P1,{ParamSymboName}P2)";
                cmd.AddParam("DBName", dbName);
            }

            cmd.Command.CommandText = sql;
            return GetTable(cmd.Command);
        }

        public List<MetaTableNames> meta_GetTableNamesList(string dbName = "")
        {
            var sql = "";
            if (string.IsNullOrWhiteSpace(dbName))
                sql =
                    "select 0 as id,TABLE_TYPE as type,TABLE_NAME as name from information_schema.tables where  TABLE_SCHEMA=(select database())  AND TABLE_TYPE IN ('BASE TABLE','VIEW')";
            else
                sql =
                    "select 0 as id,TABLE_TYPE as type,TABLE_NAME as name from information_schema.tables where  TABLE_SCHEMA='" +
                    dbName + "'  AND TABLE_TYPE IN ('BASE TABLE','VIEW')";

            return ExecuteReaderList<MetaTableNames>(sql);
        }

        public DataTable meta_GetDatabase()
        {
            var sql =
                "select SCHEMA_NAME AS DBName from information_schema.schemata;";
            return GetTable(sql, "schemata");
        }

        public List<MetaDBNames> meta_GetDatabaseList()
        {
            var sql =
                "select SCHEMA_NAME AS DBName from information_schema.schemata;";
            return ExecuteReaderList<MetaDBNames>(sql);
        }

        public DataTable meta_GetTableStru(string tableName)
        {
            var b = CodeSafeHelper.IsCheckSQM;
            try
            {
                CodeSafeHelper.IsCheckSQM = false;
                var sql = meta_GetTableStruSQL(tableName);
                return GetTable(sql, tableName);
            }
            finally
            {
                CodeSafeHelper.IsCheckSQM = b;
            }
        }

        public List<MetaTableStructure> meta_GetTableStruList(string tableName)
        {
            var b = CodeSafeHelper.IsCheckSQM;
            try
            {
                CodeSafeHelper.IsCheckSQM = false;
                var sql = meta_GetTableStruSQL(tableName);
                return ExecuteReaderList<MetaTableStructure>(sql);
            }
            finally
            {
                CodeSafeHelper.IsCheckSQM = b;
            }
        }


        public List<MetaStoreProcedure> meta_GetStoreProcList(
            string dbName = "")
        {
            var sql =
                $"select type,name from mysql.proc where db = '{dbName}' and `type` IN ('PROCEDURE','FUNCTION');";

            CodeSafeHelper.IsCheckSQM = false;
            var result = new List<MetaStoreProcedure>();
            var sp = CreateCommand(sql);
            var dt = GetTable(sp.Command);
            foreach (DataRow R in dt.Rows)
            {
                if (R["type"].ToString().Trim().ToUpper() == "PROCEDURE")
                    result.Add(new MetaStoreProcedure
                    {
                        Type = MetaStoreProcedureType.StoreProcedure,
                        Name = R["name"].ToString()
                    });
                if (R["type"].ToString().Trim().ToUpper() == "FUNCTION")
                    result.Add(new MetaStoreProcedure
                    {
                        Type = MetaStoreProcedureType.Function,
                        Name = R["name"].ToString()
                    });
            }

            return result;
        }

        /// <summary>
        /// 获取当前表最新(最大)的自增字段值
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public int GetMaxID(string tableName)
        {
            var sql = $"SELECT LAST_INSERT_ID();";
            var o = ExecuteScalar(sql);
            return o == DBNull.Value || o == null ? 0 : Convert.ToInt32(o);
        }

        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 int ExecuteSQL(string SQL, DbConnection conn)
        {
            SQL = ParseSqlEnd(SQL); //SQL结尾添加;

            DbCommand cmd = null;
            var i = -1;
            try
            {
                cmd = new MySqlCommand();
                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 DataSet GetDataSet(string SQL, DbConnection conn)
        {
            SQL = ParseSqlEnd(SQL); //SQL结尾添加;
            DbCommand cmd = null;
            DbDataAdapter adapter = null;
            try
            {
                cmd = new MySqlCommand();
                cmd.CommandText = CodeSafeHelper.GetSafeSQL(SQL);
                cmd.CommandType = CommandType.Text;
                cmd.Connection = conn;

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

        /// <summary>
        /// 给字符串结尾添加SQL语句结尾符号;
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        private string ParseSqlEnd(string sql)
        {
            if (string.IsNullOrWhiteSpace(sql)) return sql;

            var symbol = ";"; //SQL语句结尾符号
            if (sql.Substring(sql.Length - 1, 1) != symbol) sql = sql + symbol;
            return sql;
        }

        private string meta_GetTableStruSQL(string tableName)
        {
            var sql = new StringBuilder();

            sql.AppendLine("SELECT ");
            sql.AppendLine("		A.TABLE_NAME AS TableName,");
            sql.AppendLine("		A.TABLE_COMMENT AS TableDesc,");
            sql.AppendLine("		B.ORDINAL_POSITION AS FieldOrder,");
            sql.AppendLine("		B.COLUMN_NAME AS FieldName,");
            sql.AppendLine(
                "		CASE WHEN EXTRA='auto_increment' THEN 'Y' ELSE 'N' END AS IsIdentity,");
            sql.AppendLine(
                "		CASE WHEN COLUMN_KEY = 'PRI' THEN 'Y' ELSE 'N' END AS PK,");
            sql.AppendLine(
                "		CASE WHEN EXISTS(SELECT * FROM information_schema.`TABLE_CONSTRAINTS` WHERE TABLE_SCHEMA=A.TABLE_SCHEMA AND TABLE_NAME = A.TABLE_NAME AND CONSTRAINT_TYPE = 'FOREIGN KEY') THEN 'Y' ELSE 'N' END AS FK,");
            sql.AppendLine(
                "		CASE WHEN EXISTS(SELECT * FROM information_schema.`STATISTICS` WHERE TABLE_SCHEMA=A.TABLE_SCHEMA AND TABLE_NAME = A.TABLE_NAME AND INDEX_NAME = B.COLUMN_NAME) THEN 'Y' ELSE 'N' END AS IDX,	");
            sql.AppendLine("		DATA_TYPE AS FieldType,");
            sql.AppendLine(
                "		CASE WHEN REPLACE(REPLACE(REPLACE(COLUMN_TYPE,DATA_TYPE,''),'(',''),')','')='' THEN 0 ELSE REPLACE(REPLACE(REPLACE(COLUMN_TYPE,DATA_TYPE,''),'(',''),')','') END AS FieldLength,");
            sql.AppendLine("		B.NUMERIC_PRECISION AS Prec,");
            sql.AppendLine("		IFNULL(B.NUMERIC_SCALE,0) AS Scale,");
            sql.AppendLine(
                "		CASE WHEN IS_NULLABLE = 'YES' THEN 'Y' ELSE 'N' END AS AllowNull,");
            sql.AppendLine("		IFNULL(COLUMN_DEFAULT,'') AS DefaultValue,");
            sql.AppendLine(
                "		CASE WHEN IFNULL(COLUMN_COMMENT,'')='' THEN B.COLUMN_NAME ELSE COLUMN_COMMENT END AS FieldCaption ");
            sql.AppendLine("FROM information_schema.`TABLES` A");
            sql.AppendLine(
                "INNER JOIN information_schema.`COLUMNS` B ON A.TABLE_SCHEMA=B.TABLE_SCHEMA AND A.TABLE_NAME = B.TABLE_NAME ");
            sql.AppendLine(
                "WHERE A.TABLE_SCHEMA=DATABASE() AND A.TABLE_NAME = '" +
                tableName + "' AND B.TABLE_SCHEMA=DATABASE();");

            return sql.ToString();
        }


        #region

        protected AdapterRowUpdatingEvent _MyRowUpdatingEvent = null;

        public DbDataAdapter CreateDataAdapter(
            AdapterRowUpdatingEvent eventHandler)
        {
            _MyRowUpdatingEvent = eventHandler;

            var adp = new MySqlDataAdapter();
            adp.RowUpdating += Adp_RowUpdating;

            return adp;
        }

        private void Adp_RowUpdating(object sender, MySqlRowUpdatingEventArgs e)
        {
            if (_MyRowUpdatingEvent != null) _MyRowUpdatingEvent(sender, e);
        }

        #endregion
    }
}