#region

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text;
using CSFramework.DB.Common;
using CSFramework.DB.Models;

#endregion

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

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

        public string BuildConnectionString(string server, int port,
            string dbName, string uid, string pwd,
            int timeout = 15)
        {
            const string DEF_SQL_CONNECTION =
                @"Data Source={0};Initial Catalog={1};User ID={2};Password ={3};Persist Security Info=True;Connect Timeout={4};";
            var connstr = "";
            if (port == DefaultPort)
                connstr = string.Format(DEF_SQL_CONNECTION, server, dbName, uid,
                    pwd, timeout);
            else
                connstr = string.Format(DEF_SQL_CONNECTION, server + "," + port,
                    dbName, uid, pwd, timeout);

            return connstr;
        }

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

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

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

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

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

        public DbCommand CreateCommand(string commandText,
            CommandType commandType)
        {
            return new SqlCommand(commandText)
            {
                CommandText = commandText, CommandType = commandType,
                CommandTimeout = CommandTimeout
            };
        }

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

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

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

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

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

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

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

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

        public DatabaseType DatabaseType => DatabaseType.SqlServer;

        public Type DateTimeType => typeof(SqlDateTime);

        public int DefaultPort => 1433;

        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 (connection != null) Close(connection);
            }
        }

        public DbDataReader ExecuteReader(string SQL)
        {
            DbCommand cmd = null;
            DbDataReader o = null;
            DbConnection conn = null;
            try
            {
                cmd = new SqlCommand();
                cmd.CommandText = CodeSafeHelper.GetSafeSQL(SQL);
                cmd.CommandType = CommandType.Text;
                cmd.Connection = conn = CreateConnection(_ConnectionString);
                o = cmd.ExecuteReader();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (cmd != null) cmd.Dispose();

                Close(conn);
            }

            return o;
        }

        public DbDataReader ExecuteReader(DbCommand cmd)
        {
            DbConnection connection = null;
            try
            {
                if (cmd.Connection == null)
                    cmd.Connection = 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 = 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(SQL, CommandType.Text))
                    {
                        command.Connection = connection;
                        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 = 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 = 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 object ExecuteScalar(string 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)
        {
            DbConnection connection = null;
            try
            {
                if (cmd.Connection == null)
                    cmd.Connection = connection = CreateConnection();

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

        public int ExecuteSQL(string 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)
        {
            DbCommand cmd = null;
            var i = -1;
            try
            {
                cmd = new SqlCommand();
                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 DataRow GetDataRow(string SQL)
        {
            var dt = GetDataSet(SQL).Tables[0];
            return dt.Rows.Count > 0 ? dt.Rows[0] : null;
        }

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

                var ds = new DataSet();
                adapter = new SqlDataAdapter();
                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)
        {
            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)
        {
            DbCommand cmd = null;
            DbDataAdapter adapter = null;
            DbConnection connection = null;
            try
            {
                cmd = CreateCommand(SQL, type);
                cmd.Connection = connection = CreateConnection();

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

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

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

        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 = 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 DataTable GetTable(string SQL, string tableName = "")
        {
            var dt = GetDataSet(SQL).Tables[0];
            if (tableName != "") dt.TableName = tableName;

            return dt;
        }

        public DataTable GetTable(DbCommand cmd, string tableName = "")
        {
            var dt = GetDataSet(cmd).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 TOP {top} {fields} FROM {tableName} {whereSQL} {orderBy};";

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

        public DataTable meta_GetDatabase()
        {
            var sql =
                "SELECT Name AS DBName,filename AS FilePath,crdate AS CreationTime,Version FROM Master..SysDatabases";
            return GetTable(sql, "SysDatabases");
        }

        public List<MetaDBNames> meta_GetDatabaseList()
        {
            var sql =
                "SELECT Name AS DBName,filename AS FilePath,crdate AS CreationTime,Version FROM Master..SysDatabases";
            return ExecuteReaderList<MetaDBNames>(sql);
        }

        public List<MetaStoreProcedure> meta_GetStoreProcList(
            string dbName = "")
        {
            var sql =
                "SELECT type,name FROM sys.sql_modules inner join sysobjects  on sys.sql_modules.object_id = sysobjects.id  and type in ('p','fn') and category=0";

            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() == "P")
                    result.Add(new MetaStoreProcedure
                    {
                        Type = MetaStoreProcedureType.StoreProcedure,
                        Name = R["name"].ToString()
                    });

                if (R["type"].ToString().Trim().ToUpper() == "FN")
                    result.Add(new MetaStoreProcedure
                    {
                        Type = MetaStoreProcedureType.Function,
                        Name = R["name"].ToString()
                    });
            }

            return result;
        }

        public DataTable meta_GetTableNames(string dbName = "")
        {
            return GetTable(meta_GetTablesNamesSQL());
        }

        public List<MetaTableNames> meta_GetTableNamesList(string dbName = "")
        {
            return ExecuteReaderList<MetaTableNames>(meta_GetTablesNamesSQL());
        }

        public DataTable meta_GetTableStru(string tableName)
        {
            var b = CodeSafeHelper.IsCheckSQM;
            try
            {
                CodeSafeHelper.IsCheckSQM = false;
                var sp = CreateCommand(meta_GetTableStruSQL());
                sp.AddParam("TableName", tableName);
                return GetTable(sp.Command, tableName);
            }
            finally
            {
                CodeSafeHelper.IsCheckSQM = b;
            }
        }

        public List<MetaTableStructure> meta_GetTableStruList(string tableName)
        {
            var b = CodeSafeHelper.IsCheckSQM;
            try
            {
                CodeSafeHelper.IsCheckSQM = false;
                var sp = CreateCommand(meta_GetTableStruSQL());
                sp.AddParam("TableName", tableName);
                return ExecuteReaderList<MetaTableStructure>(sp.Command);
            }
            finally
            {
                CodeSafeHelper.IsCheckSQM = b;
            }
        }

        /// <summary>
        ///     参数符号.如: WHERE Code=@Code
        /// </summary>
        public string ParamSymboName { get; set; } = "@";

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

            if (paramName.IndexOf(ParamSymboName) == 0) //首字母包含
                return paramName;

            return ParamSymboName + paramName; //添加参数符号
        }

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

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

        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 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 ExecuteSQLT(string conn, string sql, object FingerDataTwo,
            object Report)
        {
            DbConnection connection = null;
            try
            {
                var ConnectString = conn;

                //string sql = "update SYS_Reports set Data = @FingerDataTwo WHERE ReportTitle= @UserId";
                var con = new SqlConnection(ConnectString);
                var cmd = new SqlCommand(sql, con);
                cmd.Parameters.Add("@FingerDataTwo", SqlDbType.VarBinary)
                    .Value = FingerDataTwo;
                cmd.Parameters.Add("@UserId", SqlDbType.VarChar).Value = Report;
                con.Open();
                var i = cmd.ExecuteNonQuery();
                con.Close();

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

        //2022-05-01 liu
        public bool AddTable(DataTable dt, string tableName)
        {
            using (var sqlCon = new SqlConnection(_ConnectionString))
            {
                try
                {
                    sqlCon.Open();
                    using (var bulkCopy = new SqlBulkCopy(sqlCon))
                    {
                        bulkCopy.DestinationTableName = tableName;
                        for (var i = 0; i < dt.Columns.Count; i++)
                            bulkCopy.ColumnMappings.Add(dt.Columns[i].Caption,
                                dt.Columns[i].Caption);

                        bulkCopy.WriteToServer(dt);
                        return true;
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    sqlCon.Close();
                }
            }
        }

        #region 封装批量插入数据SQL Server数据的方法

        /// <summary>
        ///     封装批量插入数据SQL Server数据的方法
        /// </summary>
        /// <param name="dt">源数据表</param>
        /// <param name="connectString">数据库连接字符串</param>
        /// <param name="tableName">目标表名</param>
        /// <param name="colMapping">字段映射</param>
        public bool DataTableToSQLServer(DataTable dt, string connectString,
            string tableName,
            SqlBulkCopyColumnMapping[] colMapping, ref string msg)
        {
            using (var destinationConnection = new SqlConnection(connectString))
            {
                destinationConnection.Open();

                using (var bulkCopy = new SqlBulkCopy(destinationConnection,
                           SqlBulkCopyOptions.FireTriggers, null))
                {
                    try
                    {
                        bulkCopy.DestinationTableName = tableName; //要插入的表的表名
                        bulkCopy.BatchSize = dt.Rows.Count;
                        foreach (var item in colMapping)
                            bulkCopy.ColumnMappings.Add(item);

                        bulkCopy.WriteToServer(dt);
                        return true;
                    }
                    catch (Exception ex)
                    {
                        msg = "[" + tableName + "]" + ex.Message;
                        return false;
                    }
                }
            }
        }

        #endregion

        public bool DeleteDataSet(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;
            }

            try
            {
                var a = ExecuteSQL(sql);
                if (a > 0) return true;
            }
            catch (Exception)
            {
                return false;
            }

            return false;
        }

        public int ExecuteSQL(string SQL, DbConnection conn)
        {
            DbCommand cmd = null;
            var i = -1;
            try
            {
                cmd = new SqlCommand();
                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)
        {
            DbCommand cmd = null;
            DbDataAdapter adapter = null;
            try
            {
                cmd = new SqlCommand();
                cmd.CommandText = CodeSafeHelper.GetSafeSQL(SQL);
                cmd.CommandType = CommandType.Text;
                cmd.Connection = conn;

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

                if (cmd != null) cmd.Dispose();
            }
        }

        private DbCommand meta_GetTablesNamesSQL()
        {
            var cmd =
                CreateCommand(
                    "select id,type,name from sysobjects where type in(@U,@V) order by name");
            cmd.AddParam("@U", "U");
            cmd.AddParam("@V", "V");
            return cmd.Command;
        }

        private string meta_GetTableStruSQL()
        {
            var sql = new StringBuilder();
            sql.AppendLine("SELECT ");
            sql.AppendLine("		TableName=d.name,	");
            sql.AppendLine("		FieldOrder=a.colorder,");
            sql.AppendLine("		FieldName=a.name,");
            sql.AppendLine(
                "		IsIdentity=CASE WHEN COLUMNPROPERTY(a.id,a.name, 'IsIdentity')=1 THEN 'Y' ELSE 'N' END,");
            sql.AppendLine(
                "		PK=CASE WHEN EXISTS(SELECT 1 FROM sysobjects WHERE xtype= 'PK' AND parent_obj=a.id AND name IN (");
            sql.AppendLine(
                "			SELECT name   FROM   sysindexes   WHERE   indid IN(");
            sql.AppendLine(
                "			SELECT indid   FROM   sysindexkeys   WHERE   id   =   a.id   AND   colid=a.colid))) THEN 'Y' ELSE 'N' END,");
            sql.AppendLine(
                "		FK=CASE WHEN EXISTS(SELECT 1 FROM sysobjects WHERE xtype= 'F' AND parent_obj=a.id AND name IN (");
            sql.AppendLine(
                "			SELECT name   FROM   sysindexes   WHERE   indid IN(");
            sql.AppendLine(
                "			SELECT indid   FROM   sysindexkeys   WHERE   id   =   a.id   AND   colid=a.colid))) THEN 'Y' ELSE 'N' END,");
            sql.AppendLine("		IDX=CASE WHEN EXISTS(		");
            sql.AppendLine(
                "			SELECT TOP 1 dd.name FROM sysindexes aa JOIN sysindexkeys bb ON aa.id = bb.id  AND aa.indid = bb.indid");
            sql.AppendLine("			JOIN sysobjects cc ON bb.id = cc.id");
            sql.AppendLine(
                "			JOIN syscolumns dd ON bb.id = dd.id  AND bb.colid = dd.colid");
            sql.AppendLine(
                "			WHERE dd.name=a.name AND dd.id=a.id AND cc.id=d.id 	AND aa.indid NOT IN ( 0 , 255 ) ) THEN 'Y' ELSE 'N' END,");
            sql.AppendLine("		FieldType=b.name,");
            sql.AppendLine("		FieldLength=a.length,");
            sql.AppendLine("		Prec=COLUMNPROPERTY(a.id,a.name, 'PRECISION '),");
            sql.AppendLine(
                "		Scale=isnull(COLUMNPROPERTY(a.id,a.name, 'Scale '),0),");
            sql.AppendLine(
                "		AllowNull=CASE WHEN a.isnullable=1 THEN 'Y' ELSE 'N' END,");
            sql.AppendLine("		DefaultValue=isnull(e.text, ' '),");
            sql.AppendLine(
                "		FieldCaption=CASE WHEN ISNULL(g.[value], '')='' THEN a.name ELSE g.[value] END");
            sql.AppendLine("		FROM syscolumns a");
            sql.AppendLine(
                "		LEFT JOIN systypes   b   on   a.xusertype=b.xusertype");
            sql.AppendLine(
                "		INNER JOIN sysobjects   d   on   a.id=d.id     and   d.xtype IN ('U','V')   and     d.name <> 'dtproperties'");
            sql.AppendLine(
                "		LEFT JOIN syscomments   e   on   a.cdefault=e.id");
            sql.AppendLine(
                "		LEFT JOIN sys.extended_properties   g ON a.id=g.major_id   and   a.colid=g.minor_id  "); //  --sql2005 改为 sysproperties表
            sql.AppendLine(
                "		WHERE d.name= @TableName         "); //--如果只查询指定表,加上此条件

            return sql.ToString();
        }


        #region

        protected AdapterRowUpdatingEvent _MyRowUpdatingEvent;

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

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

            return adp;
        }

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

        #endregion
    }
}