using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace XS_DAL
{
    /// <summary>
    /// 数据库操作
    /// </summary>
    public static class DBOperator
    {
        /// <summary>
        /// 数据库帮助类
        /// </summary>
        private static DBHelper dbo;
        private static DBHelper dbo2;

        private static int ty = 0;

        /// <summary>
        /// 初始化数据库帮助类
        /// </summary>
        private static void Init(int i = 0)
        {
            if (dbo == null && i == 0)
            {
                //数据库类型
                string DbType = ConfigurationManager.AppSettings["DbType"];
                //连接字符串
                string DbConnectionString = "";

                if (i == 1)
                {
                    DbConnectionString = ConfigurationManager.ConnectionStrings["connstring02"].ConnectionString;//升降库中表连接
                }
                else
                {
                    DbConnectionString = ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;
                }

                //数据库提供程序的固定名称
                string dbProviderName = string.Empty;

                switch (DbType.ToUpper())
                {
                    case "SQL":
                        dbProviderName = "System.Data.SqlClient";
                        break;
                    case "ORACLE":
                        dbProviderName = "System.Data.OracleClient";
                        break;
                    case "MYSQL":
                        dbProviderName = "MySql.Data.MySqlClient";
                        break;
                    case "DB2":
                        dbProviderName = "IBM.Data.DB2";
                        break;
                }

                dbo = new DBHelper(DbConnectionString, dbProviderName);
            }

            if (dbo2 == null && i == 1)
            {
                //数据库类型
                string DbType = ConfigurationManager.AppSettings["DbType"];
                //连接字符串
                string DbConnectionString = "";

                if (i == 1)
                {
                    DbConnectionString = ConfigurationManager.ConnectionStrings["connstring02"].ConnectionString;//升降库中表连接
                }
                else
                {
                    DbConnectionString = ConfigurationManager.ConnectionStrings["connstring"].ConnectionString;
                }

                //数据库提供程序的固定名称
                string dbProviderName = string.Empty;

                switch (DbType.ToUpper())
                {
                    case "SQL":
                        dbProviderName = "System.Data.SqlClient";
                        break;
                    case "ORACLE":
                        dbProviderName = "System.Data.OracleClient";
                        break;
                    case "MYSQL":
                        dbProviderName = "MySql.Data.MySqlClient";
                        break;
                    case "DB2":
                        dbProviderName = "IBM.Data.DB2";
                        break;
                }

                dbo2 = new DBHelper(DbConnectionString, dbProviderName);
            }
        }

        /// <summary>
        /// 查询表主键
        /// </summary>
        public static List<string> GetKeyColumn<T>(this T model, int i = 0)
        {
            try
            {
                Init(i);

                Type t = model.GetType();

                string sql = "select column_name as key_col from information_schema.key_column_usage where table_name = '" + t.Name.ToLower() + "';";

                DataTable dt = new DataTable();
                if (i == 0)
                {
                    dt = dbo.GetDataTable(sql);
                }
                else
                {
                    dt = dbo2.GetDataTable(sql);
                }

                List<string> keys = new List<string>();

                foreach (DataRow dr in dt.Rows)
                {
                    keys.Add(dr["key_col"].ToString().ToLower());
                }

                return keys;
            }
            catch (Exception)
            {
                return new List<string>();
            }
        }
        /// <summary>
        /// 查询自增长列
        /// </summary>
        public static List<string> GetAutoKeyColumn<T>(this T model, int i = 0)
        {
            try
            {
                Init(i);

                Type t = model.GetType();

                string sql = "select name as key_auto_col from syscolumns where colstat = '1' and id = object_id('" + t.Name.ToLower() + "');";

                DataTable dt = new DataTable();
                if (i == 0)
                {
                    dt = dbo.GetDataTable(sql);
                }
                else
                {
                    dt = dbo2.GetDataTable(sql);
                }

                List<string> keys = new List<string>();

                foreach (DataRow dr in dt.Rows)
                {
                    keys.Add(dr["key_auto_col"].ToString().ToUpper());
                }

                return keys;
            }
            catch (Exception)
            {
                return new List<string>();
            }
        }

        /// <summary>
        /// 插入单条数据
        /// </summary>
        public static bool Insert<T>(this T model, int i = 0)
        {
            try
            {
                Init(i);

                string sql = string.Empty;
                List<string> strSql1 = new List<string>();
                List<string> strSql2 = new List<string>();

                Type t = model.GetType();
                PropertyInfo[] pInfo = t.GetProperties();
                List<string> aColumn = GetAutoKeyColumn(model);

                foreach (var item in pInfo.Where(c => !aColumn.Contains(c.Name.ToUpper())))
                {
                    string tName = item.PropertyType.Name;

                    if (item.PropertyType.IsGenericType)
                    {
                        if (item.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
                        {
                            tName = item.PropertyType.GetGenericArguments()[0].Name;
                        }
                    }

                    switch (tName.ToLower())
                    {
                        case "boolean":
                            if (item.GetValue(model, null) != null)
                            {
                                strSql1.Add(item.Name);
                                strSql2.Add(Convert.ToBoolean(item.GetValue(model, null)) ? "1" : "0");
                            }
                            else
                            {
                                strSql1.Add(item.Name);
                                strSql2.Add("0");
                            }
                            break;
                        case "byte":
                        case "short":
                        case "int32":
                        case "int64":
                        case "decimal":
                            if (item.GetValue(model, null) != null)
                            {
                                strSql1.Add(item.Name);
                                strSql2.Add(item.GetValue(model, null).ToString());
                            }
                            else
                            {
                                strSql1.Add(item.Name);
                                strSql2.Add("0");
                            }
                            break;
                        case "string":
                            if (item.GetValue(model, null) != null)
                            {
                                strSql1.Add(item.Name);
                                strSql2.Add("'" + item.GetValue(model, null).ToString() + "'");
                            }
                            else
                            {
                                strSql1.Add(item.Name);
                                strSql2.Add("''");
                            }
                            break;
                        case "datetime":
                            if (item.GetValue(model, null) != null)
                            {
                                strSql1.Add(item.Name);
                                strSql2.Add("'" + Convert.ToDateTime(item.GetValue(model, null)).ToString("yyyy-MM-dd HH:mm:ss") + "'");
                            }
                            else
                            {
                                strSql1.Add(item.Name);
                                strSql2.Add("'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "'");
                            }
                            break;
                    }
                }

                sql = "insert into " + t.Name.ToLower() + "(" + String.Join(",", strSql1) + ") values(" + String.Join(",", strSql2) + ");";

                if (i == 0)
                {
                    return dbo.ExecuteNonQuery(sql) > 0;
                }
                else
                {
                    return dbo2.ExecuteNonQuery(sql) > 0;
                }

            }
            catch (Exception ex)
            {
                return false;
            }
        }
        /// <summary>
        /// 按条件更新数据
        /// </summary>
        public static bool Update<T>(this T model, string strSet, string strWhere = "", int i = 0)
        {
            try
            {
                Init(i);

                Type t = model.GetType();

                string sql = "update " + t.Name.ToLower() + " set " + strSet;

                if (!string.IsNullOrWhiteSpace(strWhere))
                {
                    sql += " where " + strWhere;
                }

                sql += ";";

                if (i == 0)
                {
                    return dbo.ExecuteNonQuery(sql) > 0;
                }
                else
                {
                    return dbo2.ExecuteNonQuery(sql) > 0;
                }
                //return dbo.ExecuteNonQuery(sql) > 0;
            }
            catch (Exception)
            {
                return false;
            }
        }

        /// <summary>
        /// 按条件删除数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="model"></param>
        /// <param name="strWhere"></param>
        /// <param name="i"></param>
        /// <returns></returns>
        public static bool Delete<T>(this T model, string strWhere = "", int i = 0)
        {
            try
            {
                Init(i);

                Type t = model.GetType();

                string sql = "delete " + t.Name.ToLower();

                if (!string.IsNullOrWhiteSpace(strWhere))
                {
                    sql += " where " + strWhere;
                }

                sql += ";";

                if (i == 0)
                {
                    return dbo.ExecuteNonQuery(sql) > 0;
                }
                else
                {
                    return dbo2.ExecuteNonQuery(sql) > 0;
                }
                //return dbo.ExecuteNonQuery(sql) > 0;
            }
            catch (Exception)
            {
                return false;
            }
        }

        /// <summary>
        /// 按条件查询对象实体
        /// </summary>
        public static DataTable GetTable<T>(this T model, string strWhere = "", string strOrderBy = "", int i = 0)
        {
            try
            {
                Init(i);

                Type t = model.GetType();

                string sql = "select * from " + t.Name.ToLower();

                if (!string.IsNullOrWhiteSpace(strWhere))
                {
                    sql += " where " + strWhere;
                }

                if (!string.IsNullOrWhiteSpace(strOrderBy))
                {
                    sql += " order by " + strOrderBy;
                }

                sql += ";";
                if (i == 0)
                {
                    return dbo.GetDataTable(sql);
                }
                else
                {
                    return dbo2.GetDataTable(sql);
                }
                //return dbo.GetDataTable(sql);
            }
            catch (Exception)
            {
                return new DataTable();
            }
        }

        public static List<T> GetModelList<T>(this T model, string strWhere = "", string strOrderBy = "", int i = 0)
        {
            try
            {
                Init(i);

                Type t = model.GetType();

                string sql = "select * from " + t.Name.ToLower();

                if (!string.IsNullOrWhiteSpace(strWhere))
                {
                    sql += " where " + strWhere;
                }

                if (!string.IsNullOrWhiteSpace(strOrderBy))
                {
                    sql += " order by " + strOrderBy;
                }

                sql += ";";
                if (i == 0)
                {
                    return ToDataList<T>(dbo.GetDataTable(sql));
                }
                else
                {
                    return ToDataList<T>(dbo2.GetDataTable(sql));
                }
                //return dbo.GetDataTable(sql);
            }
            catch (Exception)
            {
                return new List<T>();
            }
        }

        /// <summary>
        /// 分页查询结果排序(参数均必填)
        /// </summary>
        /// <param name="strName">视图或表名</param>
        /// <param name="strWhere">查询条件</param>
        /// <param name="strOrderby">排序条件</param>
        /// <param name="page">页码</param>
        /// <param name="lines">每页行数</param>
        /// <returns></returns>
        public static DataTable GetTable<T>(this T model, string strWhere, string strOrderby, int page, int lines, int i = 0)
        {
            try
            {
                Init(i);

                if (page == 0 || lines == 0 || string.IsNullOrWhiteSpace(strWhere) || string.IsNullOrWhiteSpace(strOrderby))
                {
                    return new DataTable();
                }

                Type t = model.GetType();

                string sql = "select * from (select row_number() over (order by " + strOrderby + ") as i_row,* from " + t.Name.ToLower() + " where " + strWhere

+ ") q where q.i_row between " + (lines * (page - 1) + 1) + " and " + lines * page + ";";

                if (i == 0)
                {
                    return dbo.GetDataTable(sql);
                }
                else
                {
                    return dbo2.GetDataTable(sql);
                }
                //return dbo.GetDataTable(sql);
            }
            catch (Exception)
            {
                return new DataTable();
            }
        }

        /// <summary>
        /// 数据行转对象实体
        /// </summary>
        public static T DataRowToModel<T>(this T model, DataRow row)
        {
            if (row != null)
            {
                Type t = model.GetType();
                T temp = (T)Activator.CreateInstance(t);
                PropertyInfo[] pInfo = t.GetProperties();

                foreach (var item in pInfo)
                {
                    string tName = item.PropertyType.Name;

                    if (item.PropertyType.IsGenericType)
                    {
                        if (item.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
                        {
                            tName = item.PropertyType.GetGenericArguments()[0].Name;
                        }
                    }

                    if (row[tName.ToLower()] != null)
                    {
                        switch (tName.ToLower())
                        {
                            case "boolean":
                                item.SetValue(temp, row[item.Name.ToLower()].ToString() == "1", null);
                                break;
                            case "byte":
                            case "short":
                            case "int32":
                            case "int64":
                            case "decimal":
                                item.SetValue(temp, row[item.Name.ToLower()], null);
                                break;
                            case "string":
                                if (row[item.Name.ToLower()] != null && row[item.Name.ToLower()] != DBNull.Value)
                                {
                                    item.SetValue(temp, row[item.Name.ToLower()].ToString(), null);
                                }
                                break;
                            case "datetime":
                                if (row[item.Name.ToLower()] != null && row[item.Name.ToLower()] != DBNull.Value)
                                {
                                    item.SetValue(temp, Convert.ToDateTime(row[item.Name.ToLower()]), null);
                                }
                                break;
                        }
                    }
                }

                return temp;
            }
            else
            {
                return default(T);
            }
        }



        /// <summary>
        /// 批量插入数据
        /// </summary>
        public static bool Insert<T>(this List<T> modelList, int i = 0)
        {
            try
            {
                Type t = modelList.First().GetType();
                PropertyInfo[] pInfo = t.GetProperties();

                StringBuilder sql = new StringBuilder();

                List<string> aColumn = GetAutoKeyColumn(modelList.First());

                foreach (var model in modelList)
                {
                    List<string> strSql1 = new List<string>();
                    List<string> strSql2 = new List<string>();

                    foreach (var item in pInfo.Where(c => !c.Name.Contains("_init") && !aColumn.Contains(c.Name)))
                    {
                        switch (item.PropertyType.Name.ToLower())
                        {
                            case "bool":
                                if (item.GetValue(model, null) != null)
                                {
                                    strSql1.Add(item.Name);
                                    strSql2.Add(Convert.ToBoolean(item.GetValue(model, null)) ? "1" : "0");
                                }
                                else
                                {
                                    strSql1.Add(item.Name);
                                    strSql2.Add("0");
                                }
                                break;
                            case "byte":
                            case "short":
                            case "int32":
                            case "int64":
                            case "decimal":
                                if (item.GetValue(model, null) != null)
                                {
                                    strSql1.Add(item.Name);
                                    strSql2.Add(item.GetValue(model, null).ToString());
                                }
                                else
                                {
                                    strSql1.Add(item.Name);
                                    strSql2.Add("0");
                                }
                                break;
                            case "string":
                                if (item.GetValue(model, null) != null)
                                {
                                    strSql1.Add(item.Name);
                                    strSql2.Add("'" + item.GetValue(model, null).ToString() + "'");
                                }
                                else
                                {
                                    strSql1.Add(item.Name);
                                    strSql2.Add("''");
                                }
                                break;
                            case "datetime":
                                if (item.GetValue(model, null) != null)
                                {
                                    strSql1.Add(item.Name);
                                    strSql2.Add("'" + Convert.ToDateTime(item.GetValue(model, null)).ToString("yyyy-MM-dd HH:mm:ss") + "'");
                                }
                                break;
                        }
                    }

                    sql.AppendLine("insert into " + t.Name.ToLower() + "(" + String.Join(",", strSql1) + ") values(" + String.Join(",", strSql2) + ");");
                }
                if (i == 0)
                {
                    return dbo.ExecuteNonQuery(sql.ToString()) > 0;
                }
                else
                {
                    return dbo2.ExecuteNonQuery(sql.ToString()) > 0;
                }
                //return dbo.ExecuteNonQuery(sql.ToString()) > 0;
            }
            catch (Exception)
            {
                return false;
            }
        }

        /// <summary>
        /// 批量插入数据,并返回异常
        /// </summary>
        public static bool Insert<T>(this List<T> modelList, out string retEx, int i = 0)
        {

            retEx = string.Empty;
            try
            {

                Type t = modelList.First().GetType();

                PropertyInfo[] pInfo = t.GetProperties();


                StringBuilder sql = new StringBuilder();

                List<string> aColumn = GetAutoKeyColumn(modelList.First());

                foreach (var model in modelList)
                {
                    List<string> strSql1 = new List<string>();
                    List<string> strSql2 = new List<string>();

                    foreach (var item in pInfo.Where(c => !c.Name.Contains("_init") && !aColumn.Contains(c.Name)))
                    {
                        string type = "";
                        type = item.PropertyType.Name.ToLower();
                        if (item.PropertyType.IsGenericType && item.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
                        {
                            type = item.PropertyType.GetGenericArguments()[0].Name;
                        }
                        switch (type.ToLower())
                        {
                            case "bool":
                                if (item.GetValue(model, null) != null)
                                {
                                    strSql1.Add(item.Name);
                                    strSql2.Add(Convert.ToBoolean(item.GetValue(model, null)) ? "1" : "0");
                                }
                                else
                                {
                                    strSql1.Add(item.Name);
                                    strSql2.Add("0");
                                }
                                break;
                            case "byte":
                            case "short":
                            case "int32":
                            case "int64":
                            case "decimal":
                                if (item.GetValue(model, null) != null)
                                {
                                    strSql1.Add(item.Name);
                                    strSql2.Add(item.GetValue(model, null).ToString());
                                }
                                else
                                {
                                    strSql1.Add(item.Name);
                                    strSql2.Add("0");
                                }
                                break;
                            case "string":
                                if (item.GetValue(model, null) != null)
                                {
                                    strSql1.Add(item.Name);
                                    strSql2.Add("'" + item.GetValue(model, null).ToString() + "'");
                                }
                                else
                                {
                                    strSql1.Add(item.Name);
                                    strSql2.Add("''");
                                }
                                break;
                            case "datetime":
                                if (item.GetValue(model, null) != null)
                                {
                                    strSql1.Add(item.Name);
                                    strSql2.Add("'" + Convert.ToDateTime(item.GetValue(model, null)).ToString("yyyy-MM-dd HH:mm:ss") + "'");
                                }
                                break;
                        }
                    }

                    sql.AppendLine("insert into " + t.Name.ToLower() + "(" + String.Join(",", strSql1) + ") values(" + String.Join(",", strSql2) + ");");
                }
                Init(i);
                if (i == 0)
                {
                    return dbo.ExecuteNonQuery(sql.ToString()) > 0;
                }
                else
                {
                    return dbo2.ExecuteNonQuery(sql.ToString()) > 0;
                }
                //return dbo.ExecuteNonQuery(sql.ToString()) > 0;
            }
            catch (Exception ex)
            {
                retEx = ex.Message;
                LogHelper.ErrorLog(ex);
                return false;
            }
        }

        public static T ChangeType<T>(object value)
        {
            var t = typeof(T);

            if (t.IsGenericType && t.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
            {
                if (value == null)
                {
                    return default(T);
                }

                t = Nullable.GetUnderlyingType(t);
            }

            return (T)Convert.ChangeType(value, t);
        }


        /// <summary>
        /// 插入单条数据,并返回异常
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="model">插入数据</param>
        /// <param name="retEx">返回异常</param>
        /// <returns></returns>
        public static bool Insert<T>(this T model, out string retEx, int i = 0)
        {
            retEx = string.Empty;
            try
            {
                Init(i);

                string sql = string.Empty;
                List<string> strSql1 = new List<string>();
                List<string> strSql2 = new List<string>();

                Type t = model.GetType();
                PropertyInfo[] pInfo = t.GetProperties();
                List<string> aColumn = GetAutoKeyColumn(model);

                foreach (var item in pInfo.Where(c => !aColumn.Contains(c.Name)))
                {
                    string tName = item.PropertyType.Name;

                    if (item.PropertyType.IsGenericType)
                    {
                        if (item.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
                        {
                            tName = item.PropertyType.GetGenericArguments()[0].Name;
                        }
                    }

                    switch (tName.ToLower())
                    {
                        case "boolean":
                            if (item.GetValue(model, null) != null)
                            {
                                strSql1.Add(item.Name);
                                strSql2.Add(Convert.ToBoolean(item.GetValue(model, null)) ? "1" : "0");
                            }
                            else
                            {
                                strSql1.Add(item.Name);
                                strSql2.Add("0");
                            }
                            break;
                        case "byte":
                        case "short":
                        case "int32":
                        case "int64":
                        case "decimal":
                            if (item.GetValue(model, null) != null)
                            {
                                strSql1.Add(item.Name);
                                strSql2.Add(item.GetValue(model, null).ToString());
                            }
                            else
                            {
                                strSql1.Add(item.Name);
                                strSql2.Add("0");
                            }
                            break;
                        case "string":
                            if (item.GetValue(model, null) != null)
                            {
                                strSql1.Add(item.Name);
                                strSql2.Add("'" + item.GetValue(model, null).ToString() + "'");
                            }
                            else
                            {
                                strSql1.Add(item.Name);
                                strSql2.Add("''");
                            }
                            break;
                        case "datetime":
                            if (item.GetValue(model, null) != null)
                            {
                                strSql1.Add(item.Name);
                                strSql2.Add("'" + Convert.ToDateTime(item.GetValue(model, null)).ToString("yyyy-MM-dd HH:mm:ss") + "'");
                            }
                            else
                            {
                                strSql1.Add(item.Name);
                                strSql2.Add("'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "'");
                            }
                            break;
                    }
                }

                sql = "insert into " + t.Name.ToLower() + "(" + String.Join(",", strSql1) + ") values(" + String.Join(",", strSql2) + ");";

                if (i == 0)
                {
                    return dbo.ExecuteNonQuery(sql) > 0;
                }
                else
                {
                    return dbo2.ExecuteNonQuery(sql) > 0;
                }
                //return dbo.ExecuteNonQuery(sql) > 0;
            }
            catch (Exception ex)
            {
                retEx = ex.Message;
                LogHelper.ErrorLog(ex);
                return false;
            }
        }

        /// <summary>
        /// 批量插入数据, 删除唯一条件外旧数据，给表必须有唯一  i_id 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="modelList">插入集合</param>
        /// <param name="delecel">删除列指定唯一,</param>
        /// <param name="retEx">返回异常</param>
        /// <returns></returns>
        public static bool Insert<T>(this List<T> modelList, string delecel, out string retEx)
        {
            retEx = string.Empty;
            try
            {
                Type t = modelList.First().GetType();
                PropertyInfo[] pInfo = t.GetProperties();

                StringBuilder sql = new StringBuilder();

                List<string> aColumn = GetAutoKeyColumn(modelList.First());

                foreach (var model in modelList)
                {
                    List<string> strSql1 = new List<string>();
                    List<string> strSql2 = new List<string>();

                    foreach (var item in pInfo.Where(c => !c.Name.Contains("_init") && !aColumn.Contains(c.Name)))
                    {
                        switch (item.PropertyType.Name.ToLower())
                        {
                            case "bool":
                                if (item.GetValue(model, null) != null)
                                {
                                    strSql1.Add(item.Name);
                                    strSql2.Add(Convert.ToBoolean(item.GetValue(model, null)) ? "1" : "0");
                                }
                                else
                                {
                                    strSql1.Add(item.Name);
                                    strSql2.Add("0");
                                }
                                break;
                            case "byte":
                            case "short":
                            case "int32":
                            case "int64":
                            case "decimal":
                                if (item.GetValue(model, null) != null)
                                {
                                    strSql1.Add(item.Name);
                                    strSql2.Add(item.GetValue(model, null).ToString());
                                }
                                else
                                {
                                    strSql1.Add(item.Name);
                                    strSql2.Add("0");
                                }
                                break;
                            case "string":
                                if (item.GetValue(model, null) != null)
                                {
                                    strSql1.Add(item.Name);
                                    strSql2.Add("'" + item.GetValue(model, null).ToString() + "'");
                                }
                                else
                                {
                                    strSql1.Add(item.Name);
                                    strSql2.Add("''");
                                }
                                break;
                            case "datetime":
                                if (item.GetValue(model, null) != null)
                                {
                                    strSql1.Add(item.Name);
                                    strSql2.Add("'" + Convert.ToDateTime(item.GetValue(model, null)).ToString("yyyy-MM-dd HH:mm:ss") + "'");
                                }
                                break;
                        }
                    }

                    sql.AppendLine("insert into " + t.Name.ToLower() + "(" + String.Join(",", strSql1) + ") values(" + String.Join(",", strSql2) + ");");
                }
                sql.Append("delete " + t.Name.ToLower() + " where i_id not in(select i_id from   " + t.Name.ToLower() + "  where i_id  in(select max(i_id) from  " + t.Name.ToLower() + "  group by " + delecel + "));");

                return dbo.ExecuteNonQuery(sql.ToString()) > 0;
            }
            catch (Exception ex)
            {
                retEx = ex.Message;
                LogHelper.ErrorLog(ex);
                return false;
            }
        }

        /// <summary>
        /// 修改返回异常
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="model"> 实体类</param>
        /// <param name="retUpEx">返回异常</param>
        /// <param name="strSet">修改参数</param>
        /// <param name="strWhere">修改条件</param>
        /// <returns></returns>
        public static bool Update<T>(this T model, out string retUpEx, string strSet, string strWhere = "", int i = 0)
        {
            retUpEx = string.Empty;
            try
            {
                Init(i);

                Type t = model.GetType();

                string sql = "update " + t.Name.ToLower() + " set " + strSet;

                if (!string.IsNullOrWhiteSpace(strWhere))
                {
                    sql += " where " + strWhere;
                }

                sql += ";";

                return dbo.ExecuteNonQuery(sql) > 0;
            }
            catch (Exception ex)
            {
                retUpEx = ex.Message;
                LogHelper.ErrorLog(ex);
                return false;
            }
        }

        #region 附加

        /// <summary>
        /// 按条件查询对象实体
        /// </summary>
        public static DataTable GetTable(string tablename, string strWhere = "", string strOrderBy = "", int i = 0)
        {
            try
            {
                Init(i);

                string sql = "select * from " + tablename;

                if (!string.IsNullOrWhiteSpace(strWhere))
                {
                    sql += " where " + strWhere;
                }

                if (!string.IsNullOrWhiteSpace(strOrderBy))
                {
                    sql += " order by " + strOrderBy;
                }

                sql += ";";

                return dbo.GetDataTable(sql);
            }
            catch (Exception ex)
            {
                return new DataTable();
            }
        }

        /// <summary>
        /// sql查询
        /// </summary>
        public static DataTable GetTableSql(string sql, int i = 0)
        {
            try
            {
                Init(i);

                if (i == 0)
                {
                    return dbo.GetDataTable(sql);
                }
                else
                {
                    return dbo2.GetDataTable(sql);
                }
                //return dbo.GetDataTable(sql);
            }
            catch (Exception)
            {
                return new DataTable();
            }
        }
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="i"></param>
        /// <returns></returns>
        public static bool DelSql(string sql, out string retEx, int i = 0)
        {
            retEx = string.Empty;
            try
            {
                Init(i);

                if (i == 0)
                {
                    return dbo.ExecuteNonQuery(sql) > 0;
                }
                else
                {
                    return dbo2.ExecuteNonQuery(sql) > 0;
                }
                //return dbo.ExecuteNonQuery(sql) > 0;
            }
            catch (Exception ex)
            {
                retEx = ex.Message;
                LogHelper.ErrorLog(ex);
                return false;
            }
        }
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="i"></param>
        /// <returns></returns>
        public static bool UpdateSql(string sql, out string retEx, int i = 0)
        {
            retEx = string.Empty;
            try
            {
                Init(i);

                if (i == 0)
                {
                    return dbo.ExecuteNonQuery(sql) > 0;
                }
                else
                {
                    return dbo2.ExecuteNonQuery(sql) > 0;
                }
                //return dbo.ExecuteNonQuery(sql) > 0;
            }
            catch (Exception ex)
            {
                retEx = ex.Message;
                LogHelper.ErrorLog(ex);
                return false;
            }
        }
        #endregion

        /// <summary>
        /// DataTable转成List
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static List<T> ToDataList<T>(DataTable dt)
        {
            var list = new List<T>();
            var plist = new List<PropertyInfo>(typeof(T).GetProperties());
            foreach (DataRow item in dt.Rows)
            {
                T s = Activator.CreateInstance<T>();
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    PropertyInfo info = plist.Find(p => p.Name == dt.Columns[i].ColumnName);
                    if (info != null)
                    {
                        try
                        {
                            if (!Convert.IsDBNull(item[i]))
                            {
                                object v = null;
                                if (info.PropertyType.ToString().Contains("System.Nullable"))
                                {
                                    v = Convert.ChangeType(item[i], Nullable.GetUnderlyingType(info.PropertyType));
                                }
                                else
                                {
                                    v = Convert.ChangeType(item[i], info.PropertyType);
                                }
                                info.SetValue(s, v, null);
                            }
                        }
                        catch (Exception ex)
                        {
                            throw new Exception("字段[" + info.Name + "]转换出错,value:" + item[i] + ex.ToString());
                        }
                    }
                }
                list.Add(s);
            }
            return list;
        }



        /// <summary>
        /// 按条件查询主键
        /// </summary>
        public static int sqlzj(string sql, int i = 0)
        {
            try
            {
                Init(i);



                int ind = Convert.ToInt32(dbo.ExecuteScalar(sql));
                return ind;
            }
            catch (Exception)
            {
                return 0;
            }
        }
        /// <summary>
        /// 执行update操作
        /// </summary>
        public static void sqlend(string sql, int i = 0)
        {
            try
            {
                Init(i);



                dbo.ExecuteNonQuery(sql);
            }
            catch (Exception)
            {

            }
        }
    }
}