Moka_CShare_FactoryTool/FactoryTool_CShare/DAL/BaseDAL.cs

using MOKA_Factory_Tools.Database;
using Newtonsoft.Json;
using Newtonsoft.Json.Converters;
using SXLibrary;
using System;
using System.Collections;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Dynamic;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace MOKA_Factory_Tools.DAL
{
    public abstract class BaseDAL
    {
        #region 属性
        /// <summary>
        /// 数据表模型对象
        /// </summary>
        public abstract object ObjModel { get; }

        /// <summary>
        /// 数据表名
        /// </summary>
        public abstract string TableName { get; set; }

        /// <summary>
        /// 过期属性请不要使用 数据表字段名字符串,字段名以“,”号分隔
        /// </summary>
        public abstract string TableFieldNameString { get; set; }
        string _TableFieldNameStr = "";

        /// <summary>
        /// 数据表字段名字符串,字段名以“,”号分隔
        /// </summary>
        public string TableFieldNameStr
        {
            get
            {
                if (this._TableFieldNameStr.Trim() == "")
                {
                    for (int i = 0; i < this.TableFieldNames.Length; i++)
                    {
                        if (this._TableFieldNameStr.Trim() == "")
                        {
                            this._TableFieldNameStr += "[" + this.TableFieldNames[i] + "]";
                        }
                        else
                        {
                            this._TableFieldNameStr += ",[" + this.TableFieldNames[i] + "]";
                        }
                    }
                }
                if (this._TableFieldNameStr.Trim() == "")
                {
                    this._TableFieldNameStr = "*";
                }
                return this._TableFieldNameStr;
            }

            set { this._TableFieldNameStr = value; }
        }

        /// <summary>
        /// 数据表字段名数组
        /// </summary>
        public string[] TableFieldNames
        {
            get
            {
                System.Reflection.PropertyInfo[] propertyInfos = this.PropertyInfos;
                string[] fieldNames = new string[propertyInfos.Length];
                for (int i = 0; i < propertyInfos.Length; i++)
                {
                    if (!propertyInfos[i].IsSpecialName)
                    {
                        System.Reflection.PropertyInfo propertyInfo = propertyInfos[i];
                        fieldNames[i] = propertyInfo.Name;
                    }
                }
                // System.Reflection.PropertyInfo propertyInfo = ObjModel.GetType().GetProperty(propertyInfos[0].Name);

                return fieldNames;//this.TableFieldNameString.Replace("[", "").Replace("]", "").Replace(" ", "").Split(',');
            }
        }

        public ConnectionParameters cps = new ConnectionParameters();

        /// <summary>
        /// 获取要查询的当前表的字段集合字符串 用","号分隔
        /// </summary>
        /// <param name="filters"></param>
        /// <param name="isContain">如果为true 表示只返回 filters 集合中的字段 如果为false 表示返回排除 filters 集合中的字段以外的所有字段</param>
        /// <returns></returns>
        public string GetQueryTableFieldNames(string[] filters = null, bool isContain = false)
        {
            List<string> tempFilters = new List<string>();
            if (filters != null)
            {
                tempFilters.AddRange(filters);
            }
            System.Reflection.PropertyInfo[] propertyInfos = this.PropertyInfos;
            string fieldNames = "";
            for (int i = 0; i < propertyInfos.Length; i++)
            {
                if (!propertyInfos[i].IsSpecialName)
                {
                    System.Reflection.PropertyInfo propertyInfo = propertyInfos[i];
                    if ((!tempFilters.Contains(propertyInfo.Name) && !isContain) || (tempFilters.Contains(propertyInfo.Name) && isContain))
                    {
                        if (fieldNames.Trim().Length <= 0)
                        {
                            fieldNames = "[" + propertyInfo.Name + "]";
                        }
                        else
                        {
                            fieldNames += ",[" + propertyInfo.Name + "]";
                        }
                    }
                }
            }
            return fieldNames;
        }

        /// <summary>
        /// 获取属性集合
        /// </summary>
        public System.Reflection.PropertyInfo[] PropertyInfos
        {
            get
            {
                List<System.Reflection.PropertyInfo> list = new List<System.Reflection.PropertyInfo>();
                System.Reflection.PropertyInfo[] infos = ObjModel.GetType().GetProperties();
                foreach (System.Reflection.PropertyInfo info in infos)
                {
                    if (info.CanWrite)
                    {
                        list.Add(info);
                    }
                }
                return list.ToArray();
            }
        }
        #endregion

        #region 检查记录
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(long id)
        {
            return this.Exists("ID", id);
        }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int id)
        {
            return this.Exists("ID", id);
        }

        /// <summary>
        /// 根据筛选条件判断是否存在该记录
        /// </summary>
        /// <param name="filterFieldName">筛选字段名</param>
        /// <param name="filterValue">筛选值</param>
        /// <returns></returns>
        public bool Exists(string filterFieldName, object filterValue)
        {
            if (filterFieldName != "" && filterValue != null)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select count(1) from " + this.TableName + " ");
                strSql.Append(" where 1=1 and " + filterFieldName + "=@" + filterFieldName);
                SqlParameter[] parameters = { new SqlParameter("@" + filterFieldName, filterValue) };

                return DbHelper.Exists(cps.ConnectionString, strSql.ToString(), parameters);
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 根据筛选条件判断是否存在该记录
        /// </summary>
        /// <param name="filterFieldName">筛选字段名</param>
        /// <param name="filterValue">筛选值</param>
        /// <returns></returns>
        public bool Exists(string whereString)
        {
            if (whereString.Trim().Length > 0)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select count(1) from " + this.TableName + " ");
                strSql.Append(" where " + whereString);

                return DbHelper.Exists(cps.ConnectionString, strSql.ToString());
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 判断是否存某个字段
        /// </summary>
        /// <param name="columnName">列名称</param>
        /// <returns>是否存在</returns>
        public bool ColumnExists(string columnName)
        {
            return DbHelper.IsColumnExists(cps.ConnectionString, this.TableName, columnName);
        }
        #endregion

        #region 公共方法
        /// <summary>
        /// 获取最大ID()
        /// </summary>
        /// <returns></returns>
        public int GetMaxID()
        {
            return this.GetMaxID("ID");
        }

        /// <summary>
        /// 获取某字段最大值（获取字段必须为数字类型）
        /// </summary>
        /// <param name="FieldName"></param>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public int GetMaxID(string FieldName, string strWhere = "")
        {
            return DbHelper.GetMaxID(cps.ConnectionString, FieldName, this.TableName, strWhere);
        }

        /// <summary>
        /// 检查CLR功能是否开启
        /// </summary>
        /// <returns></returns>
        public bool CheckingCLR()
        {
            try
            {
                DataSet cds = DbHelper.Query(cps.ConnectionString, "select top 1  CONVERT(nvarchar(200), dbo.FunTrim(dbo.AggregateString(GM_CustomerID + '{$$}/{where}' +[GM_CustomerID] + '{$$}={$$}' + GM_RelatedPersonID + '{$$}s') + '/' + dbo.AggregateString(GM_CustomerID + '{$$}/{where}' +[GM_CustomerID] + '{$$}<>{$$}' + GM_RelatedPersonID + '{$$}s'), '/')) as CustomerID from[dbo].[tb_ErpCustomerGroupMembers]    group by GM_CustomerGroupID");
                return true;
            }
            catch
            {
                return false;
            }
        }

        public void ExecuteSql(string sql)
        {
            DbHelper.ExecuteSQL(cps.ConnectionString, sql);
        }
        #endregion

        #region 判断方法
        /// <summary>
        /// 获取是否为忽略字段 返回 true 表示忽略 false 不忽略
        /// </summary>
        /// <param name="overlookFieldList">忽略字段名列表 字段名之间用“,”号分隔</param>
        /// <param name="fieldName">要判断的字段名</param>
        /// <returns></returns>
        public bool IsOverlookFiel(string overlookFieldList, string fieldName)
        {
            bool bl = false;
            string[] tempOverlookFieldList = overlookFieldList.Split(',');
            for (int i = 0; i < tempOverlookFieldList.Length; i++)
            {
                if (tempOverlookFieldList[i].Trim().ToLower() == fieldName.Trim().ToLower())
                {
                    bl = true;
                    break;
                }
            }
            return bl;
        }
        #endregion

        #region 增加数据
        /// <summary>
        /// 增加一条数据
        /// </summary>
        /// <param name="model">Model对象</param>
        /// <param name="overlookFieldList">忽略字段名列表 字段名之间用“,”号分隔</param>
        /// <returns></returns>
        public bool Add(object model, string overlookFieldList = "ID")
        {
           
            CommandInfo comdInfo = this.GetAddCommandInfo(model, overlookFieldList);
                
            int rows = DbHelper.ExecuteSQL(cps.ConnectionString, comdInfo.CommandText, (SqlParameter[])comdInfo.Parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }                     
        }

        /// <summary>
        /// 获取插入数CommandInfo对象
        /// </summary>
        /// <param name="model"></param>
        /// <param name="overlookFieldList">要忽略的字段集合 多个字段名有“,”号分隔</param>
        /// <returns></returns>
        public object GetAddCommandInfoObject(object model, string overlookFieldList = "ID")
        {
            return this.GetAddCommandInfo(model, overlookFieldList);
        }

        /// <summary>
        /// 获取插入数CommandInfo对象
        /// </summary>
        /// <param name="model"></param>
        /// <param name="overlookFieldList">要忽略的字段集合 多个字段名有“,”号分隔</param>
        /// <param name="type"></param>
        /// <returns></returns>
        public CommandInfo GetAddCommandInfo(object model, string overlookFieldList = "ID", EffentNextType type = EffentNextType.None)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder tempFieldNameString = new StringBuilder();
            StringBuilder tempValueVariable = new StringBuilder();
            List<SqlParameter> parameterlist = new List<SqlParameter>();

            for (int i = 0; i < PropertyInfos.Length; i++)
            {
                try
                {
                    System.Reflection.PropertyInfo propertyInfo = PropertyInfos[i];
                    string tempFieldName = propertyInfo.Name;
                    object tempValue = propertyInfo.GetValue(model, null);
                    Type tempType = propertyInfo.PropertyType;
                    if (!IsOverlookFiel(overlookFieldList, tempFieldName) || (tempFieldName.ToLower() == "id" && tempType == typeof(long)))
                    {
                        try
                        {
                            if (tempType.Name == "DateTime")
                            {
                                if (tempValue.ToString().Contains("0001"))
                                {
                                    tempValue = null;
                                }
                                try
                                {
                                    DateTime tempTime;
                                    if (!DateTime.TryParse(tempValue.ToString(), out tempTime))
                                    {
                                        tempValue = null;
                                    }
                                    else if (tempTime <= Convert.ToDateTime("1900-01-01"))
                                    {
                                        tempValue = null;
                                    }
                                }
                                catch
                                {
                                    tempValue = null;
                                }
                            }
                            else if (tempFieldName.ToLower() == "id" && tempType == typeof(long) && (tempValue == null || Convert.ToInt64(tempValue) == 0))
                            {
                                byte[] buffer = Guid.NewGuid().ToByteArray();
                                tempValue = BitConverter.ToInt64(buffer, 0);
                            }
                            if (tempValue != null)
                            {
                                tempFieldNameString.Append("" + tempFieldName + ",");
                                tempValueVariable.Append("@" + tempFieldName + ",");
                                SqlParameter parameter = new SqlParameter("@" + tempFieldName, tempValue);
                                string classFullName = tempType.ToString();
                                if (classFullName.ToLower().IndexOf("system.") != 0)
                                {
                                    parameter.DbType = DbType.String;
                                    parameter = new SqlParameter("@" + tempFieldName, tempValue.ToString());
                                }
                                parameterlist.Add(parameter);
                            }
                        }
                        catch { }
                    }
                }
                catch { }
            }

            //去除结尾多余的“,”
            if (tempFieldNameString.ToString().LastIndexOf(',') == tempFieldNameString.Length - 1)
            {
                tempFieldNameString.Remove(tempFieldNameString.Length - 1, 1);
            }
            //去除结尾多余的“,”
            if (tempValueVariable.ToString().LastIndexOf(',') == tempValueVariable.Length - 1)
            {
                tempValueVariable.Remove(tempValueVariable.Length - 1, 1);
            }
            strSql.Append("insert into " + this.TableName + "(");
            strSql.Append("" + tempFieldNameString.ToString() + ")");
            strSql.Append(" values (");
            strSql.Append("" + tempValueVariable.ToString() + ")");
            strSql.Append(";select @@IDENTITY");

            SqlParameter[] parameters = parameterlist.ToArray();

            return new CommandInfo(strSql.ToString(), parameters, type);
        }

        #endregion

        #region 更新数据
        /// <summary>
        /// 根据筛选字段和SQL筛选运算符号更新数据(内部方法)
        /// </summary>
        /// <param name="model">Model对象</param>
        /// <param name="filterFieldName">筛选字段名称</param>
        /// <param name="operators">SQL筛选运算符号</param>
        /// <param name="overlookFieldList">忽略字段名列表 字段名之间用“,”号分隔</param>
        /// <param name="whereStr">Wher条件,当指定条件语句时筛选字段设置无效，不包含“where”关键字 不建义使用此参数</param>
        /// <returns></returns>
        protected bool InsideUpdate(object model, string filterFieldName = "ID", string operators = "=", string overlookFieldList = "ID", string whereStr = null)
        {
            CommandInfo comdInfo = GetUpdateCommandInfo(model, filterFieldName, operators, overlookFieldList, whereStr);
            int rows = DbHelper.ExecuteSQL(cps.ConnectionString, comdInfo.CommandText, (SqlParameter[])comdInfo.Parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 获取更新数据的UpdateCommandInfo对象 根据筛选字段和SQL筛选运算符号更新数据(内部方法)
        /// </summary>
        /// <param name="model">Model对象</param>
        /// <param name="filterFieldName">筛选字段名称</param>
        /// <param name="operators">SQL筛选运算符号</param>
        /// <param name="overlookFieldList">忽略字段名列表 字段名之间用“,”号分隔</param>
        /// <param name="whereStr">Wher条件,当指定条件语句时筛选字段设置无效，不包含“where”关键字 不建义使用此参数</param>
        public object GetUpdateCommandInfoObject(object model, string filterFieldName = "ID", string operators = "=", string overlookFieldList = "ID", string whereStr = null)
        {
            return this.GetUpdateCommandInfo(model, filterFieldName, operators, overlookFieldList, whereStr);
        }

        /// <summary>
        /// 获取更新数据的UpdateCommandInfo对象 根据筛选字段和SQL筛选运算符号更新数据(内部方法)
        /// </summary>
        /// <param name="model">Model对象</param>
        /// <param name="filterFieldName">筛选字段名称</param>
        /// <param name="operators">SQL筛选运算符号</param>
        /// <param name="overlookFieldList">忽略字段名列表 字段名之间用“,”号分隔</param>
        /// <param name="whereStr">Wher条件,当指定条件语句时筛选字段设置无效，不包含“where”关键字 不建义使用此参数</param>
        /// <param name="type"></param>
        public CommandInfo GetUpdateCommandInfo(object model, string filterFieldName = "ID", string operators = "=", string overlookFieldList = "ID", string whereStr = null, EffentNextType type = EffentNextType.None)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update " + this.TableName + " set ");

            List<SqlParameter> parameterlist = new List<SqlParameter>();
            overlookFieldList += "," + filterFieldName;
            object VerifyTimestamp = null;
            for (int i = 0; i < PropertyInfos.Length; i++)
            {
                try
                {
                    System.Reflection.PropertyInfo propertyInfo = PropertyInfos[i];
                    string tempFieldName = propertyInfo.Name;//字段名
                    object tempValue = propertyInfo.GetValue(model, null);//对应字段值
                    //如果字段名不为忽略字段则进行处理
                    if (!IsOverlookFiel(overlookFieldList, tempFieldName))
                    {
                        try
                        {
                            if (tempValue.GetType() == typeof(DateTime))
                            {
                                DateTime tempTime;

                                try
                                {
                                    if (!DateTime.TryParse(tempValue.ToString(), out tempTime))
                                    {
                                        tempValue = null;
                                    }
                                    else if (tempTime <= Convert.ToDateTime("1900-01-01"))
                                    {
                                        tempValue = null;
                                    }
                                    else if (tempTime.Year == Convert.ToDateTime("0001-01-01").Year || tempTime.Year == Convert.ToDateTime("1753-01-01").Year)
                                    {
                                        tempValue = null;
                                    }
                                }
                                catch { tempValue = null; }
                            }
                            else if (tempValue.GetType() == typeof(byte[]))
                            {
                                if (tempFieldName.ToLower() == "VerifyTimestamp".ToLower())
                                {
                                    VerifyTimestamp = tempValue;
                                    continue;
                                }
                            }
                        }
                        catch { }
                        strSql.Append("" + tempFieldName + "=@" + tempFieldName + ",");
                        SqlParameter parameter = new SqlParameter("@" + tempFieldName, tempValue);
                        string classFullName = propertyInfo.PropertyType.ToString();
                        if (classFullName.ToLower().IndexOf("system.") != 0)
                        {
                            parameter.DbType = DbType.String;
                            parameter = new SqlParameter("@" + tempFieldName, tempValue.ToString());
                        }
                        parameterlist.Add(parameter);
                    }
                }
                catch { }
            }

            //去除结尾多余的“,”
            if (strSql.ToString().LastIndexOf(',') == strSql.Length - 1)
            {
                strSql.Remove(strSql.Length - 1, 1);
            }

            if (whereStr == null)
            {
                if (filterFieldName != "" && operators != "")
                {
                    try
                    {
                        object FilterFieldValue = DbHelper.GetPropertyValue(model, filterFieldName);// model.GetType().GetProperty(filterFieldName).GetValue(model, null);
                        if (VerifyTimestamp != null)
                        {
                            strSql.Append(" where " + filterFieldName + operators + "@" + filterFieldName + " and VerifyTimestamp=@VerifyTimestamp");
                            parameterlist.Add(new SqlParameter("@VerifyTimestamp", VerifyTimestamp));
                        }
                        else
                        {
                            strSql.Append(" where " + filterFieldName + operators + "@" + filterFieldName);
                        }
                        parameterlist.Add(new SqlParameter("@" + filterFieldName, FilterFieldValue));
                    }
                    catch
                    {

                    }
                }
            }
            else
            {
                if (VerifyTimestamp != null)
                {
                    strSql.Append(" where " + filterFieldName + operators + "@" + filterFieldName + " and VerifyTimestamp=@VerifyTimestamp");
                    parameterlist.Add(new SqlParameter("@VerifyTimestamp", VerifyTimestamp));
                }
                else
                {
                    strSql.Append(" where 1=1 and (" + whereStr + ")");
                }
            }
            SqlParameter[] parameters = parameterlist.ToArray();
            return new CommandInfo(strSql.ToString(), parameters, type);
        }

        /// <summary>
        /// 根据筛选字段和SQL筛选运算符号更新数据
        /// </summary>
        /// <param name="model">Model对象</param>
        /// <param name="filterFieldName">筛选字段名称</param>
        /// <param name="operators">SQL筛选运算符号</param>
        /// <param name="overlookFieldList">忽略字段名列表 字段名之间用“,”号分隔</param>
        /// <returns></returns>
        public bool Update(object model, string filterFieldName = "ID", string operators = "=", string overlookFieldList = "ID")
        {
            return InsideUpdate(model, filterFieldName, operators, overlookFieldList);
        }

        /// <summary>
        /// 根据Wher条件更新数据 不建义使用此方法
        /// </summary>
        /// <param name="model">Model对象</param>
        /// <param name="whereStr">Wher条件，不包含“where”关键字</param>
        /// <param name="overlookFieldList">忽略字段名列表 字段名之间用“,”号分隔</param>
        /// <returns></returns>
        public bool Update(object model, string whereStr, string overlookFieldList = "ID")
        {
            return InsideUpdate(model, overlookFieldList: overlookFieldList, whereStr: whereStr);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="model"></param>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        /// 因为AMResult表没有代理主键ID，所以需要独立方法更新;
        public bool UpdateAMResult(object model, string strWhere, string IgnoreUpdateFields = "Station,SN,ODF,DSN,Line,Model,Dimension")
        {
            CommandInfo comdInfo = GetUpdateAMResultCommandInfo(model, IgnoreUpdateFields, strWhere);
            int rows = DbHelper.ExecuteSQL(cps.ConnectionString, comdInfo.CommandText, (SqlParameter[])comdInfo.Parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 
        /// 问题：Where条件后面跟的值是旧值，model的新值;
        /// </summary>
        /// <param name="model"></param>
        /// <param name="IgnoreUpdateFields"></param>
        /// <param name="whereStr"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public CommandInfo GetUpdateAMResultCommandInfo(object model, string IgnoreUpdateFields = "Station,SN,ODF,DSN,Line,Model,Dimension", string whereStr = null, EffentNextType type = EffentNextType.None)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update " + this.TableName + " set ");

            List<SqlParameter> parameterlist = new List<SqlParameter>();
            IgnoreUpdateFields += ",";
            object VerifyTimestamp = null;
            for (int i = 0; i < PropertyInfos.Length; i++)
            {
                try
                {
                    System.Reflection.PropertyInfo propertyInfo = PropertyInfos[i];
                    string tempFieldName = propertyInfo.Name;//字段名
                    object tempValue = propertyInfo.GetValue(model, null);//对应字段值
                    //如果字段名不为忽略字段则进行处理
                    if (!IsOverlookFiel(IgnoreUpdateFields, tempFieldName))
                    {
                        try
                        {
                            if (tempValue.GetType() == typeof(DateTime))
                            {
                                DateTime tempTime;

                                try
                                {
                                    if (!DateTime.TryParse(tempValue.ToString(), out tempTime))
                                    {
                                        tempValue = null;
                                    }
                                    else if (tempTime <= Convert.ToDateTime("1900-01-01"))
                                    {
                                        tempValue = null;
                                    }
                                    else if (tempTime.Year == Convert.ToDateTime("0001-01-01").Year || tempTime.Year == Convert.ToDateTime("1753-01-01").Year)
                                    {
                                        tempValue = null;
                                    }
                                }
                                catch { tempValue = null; }
                            }
                            else if (tempValue.GetType() == typeof(byte[]))
                            {
                                if (tempFieldName.ToLower() == "VerifyTimestamp".ToLower())
                                {
                                    VerifyTimestamp = tempValue;
                                    continue;
                                }
                            }
                        }
                        catch { }
                        strSql.Append("" + tempFieldName + "=@" + tempFieldName + ",");
                        SqlParameter parameter = new SqlParameter("@" + tempFieldName, tempValue);
                        string classFullName = propertyInfo.PropertyType.ToString();
                        if (classFullName.ToLower().IndexOf("system.") != 0)
                        {
                            parameter.DbType = DbType.String;
                            parameter = new SqlParameter("@" + tempFieldName, tempValue.ToString());
                        }
                        parameterlist.Add(parameter);
                    }
                }
                catch { }
            }

            //去除结尾多余的“,”
            if (strSql.ToString().LastIndexOf(',') == strSql.Length - 1)
            {
                strSql.Remove(strSql.Length - 1, 1);
            }

            if (whereStr != null)
            {
                strSql.Append(" where 1=1 and (" + whereStr + ")");
            }
            SqlParameter[] parameters = parameterlist.ToArray();
            return new CommandInfo(strSql.ToString(), parameters, type);
        }
        #endregion

        #region 删除数据
        /// <summary>
        /// 根据筛选字段或where条件删除数据
        /// </summary>
        /// <param name="filterFieldName">筛选字段名</param>
        /// <param name="operators">筛选SQL运算符</param>
        /// <param name="filterValue">筛选值</param>
        /// <param name="whereStr">Wher条件，不包含“where”关键字</param>
        /// <returns></returns>
        protected bool InsideDelete(string filterFieldName = "ID", string operators = "=", object filterValue = null, string whereStr = null)
        {
            CommandInfo comdInfo = GetDeleteCommandInfo(filterFieldName, operators, filterValue, whereStr);
            int rows = DbHelper.ExecuteSQL(cps.ConnectionString, comdInfo.CommandText, (SqlParameter[])comdInfo.Parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 根据筛选字段或where条件删除数据
        /// </summary>
        /// <param name="filterFieldName">筛选字段名</param>
        /// <param name="operators">筛选SQL运算符</param>
        /// <param name="filterValue">筛选值</param>
        /// <param name="whereStr">Wher条件，传入条件语句时筛选字段无效不建义使用 不包含“where”关键字</param>
        /// <returns></returns>
        public object GetDeleteCommandInfoObject(string filterFieldName = "ID", string operators = "=", object filterValue = null, string whereStr = null)
        {
            return this.GetDeleteCommandInfo(filterFieldName, operators, filterValue, whereStr);
        }

        /// <summary>
        /// 根据筛选字段或where条件删除数据
        /// </summary>
        /// <param name="filterFieldName">筛选字段名</param>
        /// <param name="operators">筛选SQL运算符</param>
        /// <param name="filterValue">筛选值</param>
        /// <param name="whereStr">Wher条件，传入条件语句时筛选字段无效不建义使用 不包含“where”关键字</param>
        /// <param name="type"></param>
        /// <returns></returns>
        public CommandInfo GetDeleteCommandInfo(string filterFieldName = "ID", string operators = "=", object filterValue = null, string whereStr = null, EffentNextType type = EffentNextType.None)
        {
            if (filterValue == null) { filterValue = ""; }
            List<SqlParameter> parameterlist = new List<SqlParameter>();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from " + this.TableName + " ");
            if (whereStr == null)
            {
                strSql.Append(" where " + filterFieldName + "" + operators + "@" + filterFieldName + "");
                parameterlist.Add(new SqlParameter("@" + filterFieldName, filterValue));
            }
            else
            {
                strSql.Append(" where ID>@ID and (" + whereStr + ")");
                parameterlist.Add(new SqlParameter("@ID", Convert.ToInt32(0)));
            }
            SqlParameter[] parameters = parameterlist.ToArray();
            return new CommandInfo(strSql.ToString(), parameters, type);
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        /// <param name="ID">id</param>
        public bool Delete(long ID)
        {
            return this.InsideDelete(filterValue: ID);
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        /// <param name="ID">id</param>
        public bool Delete(int ID)
        {
            return this.InsideDelete(filterValue: ID);
        }

        /// <summary>
        /// 根据筛选字段删除数据
        /// </summary>
        /// <param name="filterValue">筛选值</param>
        /// <param name="filterFieldName">筛选字段名</param>
        /// <param name="operators">筛选SQL运算符</param>
        /// <returns></returns>
        public bool Delete(object filterValue, string filterFieldName = "ID", string operators = "=")
        {
            return this.InsideDelete(filterFieldName, operators, filterValue);
        }

        /// <summary>
        /// 根据where条件删除数据 不建义使用此方法
        /// </summary>
        /// <param name="whereStr">Wher条件，不包含“where”关键字</param>
        /// <returns></returns>
        public bool Delete(string whereStr)
        {
            return this.InsideDelete(whereStr: whereStr);
        }

        /// <summary>
        /// 批量删除数据
        /// </summary>
        /// <param name="filterFieldName">筛选字段名</param>
        /// <param name="valueList">值列表，多个值用“,”分隔，字符串值用“'”号包含</param>
        public bool DeleteList(string filterFieldName, string valueList)
        {
            if (filterFieldName != "" && valueList != "")
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("delete from " + this.TableName + " ");
                strSql.Append(" where " + filterFieldName + " in (" + valueList + ")  ");
                int rows = DbHelper.ExecuteSQL(cps.ConnectionString, strSql.ToString());

                if (rows > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 按ID列表批量删除数据
        /// </summary>
        /// <param name="idList">ID列表，多个用“,”分隔</param>
        public bool DeleteList(string idList)
        {
            return this.DeleteList("ID", idList);
        }
        #endregion

        #region 查询数据

        public T GetDataRowToModelByEntity<T>(DataRow row)
        {
            if (row == null)
            {
                return Activator.CreateInstance<T>();
            }
            Dictionary<string, string> dataInfo = new Dictionary<string, string>();
            dynamic objmatch = new ExpandoObject();
            for (int i = 0; i < row.Table.Columns.Count; i++)
            {
                string value = "";
                if (row[row.Table.Columns[i].ColumnName] != null)
                {
                    value = row[row.Table.Columns[i].ColumnName].ToString();
                }
                if (!string.IsNullOrEmpty(value))
                {
                    if (row.Table.Columns[i].DataType.Name.ToLower().IndexOf("DateTime".ToLower()) != -1)
                    {
                        dataInfo.Add(row.Table.Columns[i].ColumnName, Convert.ToDateTime(value).ToString("yyyy-MM-dd HH:mm:ss"));
                    }
                    else
                    {
                        dataInfo.Add(row.Table.Columns[i].ColumnName, value);
                    }
                }
                else
                {
                    if (row.Table.Columns[i].DataType.Name.ToLower().IndexOf("String".ToLower()) != -1)
                    {
                        dataInfo.Add(row.Table.Columns[i].ColumnName, value);
                    }
                }
            }
            objmatch = dataInfo;
            string json = JsonConvert.SerializeObject((object)objmatch);

            return JsonToModelByEntity<T>(json);
        }
        T JsonToModelByEntity<T>(string json)
        {
            T t = Activator.CreateInstance<T>();
            return (T)JsonConvert.DeserializeObject(json, t.GetType());
        }

        public DataSet GetViewDataSetByCustomer(string strSQL)
        {
            return DbHelper.Query(cps.ConnectionString, strSQL);
        }

        public object DataRowToModel<T>(DataRow dr)
        {
            if (dr == null)
            {
                return Activator.CreateInstance<T>();
            }
            Dictionary<string, string> dataInfo = new Dictionary<string, string>();
            dynamic objmatch = new ExpandoObject();
            for (int i = 0; i < dr.Table.Columns.Count; i++)
            {
                string value = dr[dr.Table.Columns[i].ColumnName].ToString();
                if (!string.IsNullOrEmpty(value))
                {
                    if (dr.Table.Columns[i].DataType.Name.IndexOf("DateTime") != -1)
                    {
                        dataInfo.Add(dr.Table.Columns[i].ColumnName, Convert.ToDateTime(value).ToString("yyyy-MM-dd HH:mm:ss"));
                    }
                    else
                    {
                        dataInfo.Add(dr.Table.Columns[i].ColumnName, value);
                    }
                }
                else
                {
                    if (dr.Table.Columns[i].DataType.Name.ToLower().IndexOf("String".ToLower()) != -1)
                    {
                        dataInfo.Add(dr.Table.Columns[i].ColumnName, "");
                    }
                }
            }
            objmatch = dataInfo;
            string json = JsonConvert.SerializeObject((object)objmatch);

            return DataRowToModelByEntity<T>(json);
        }

        public object GetModelObjectByEntity<T>(string filterFieldName, object filterValue)
        {
            DataRow dr = GetDataRow(filterFieldName, filterValue);
            Dictionary<string, string> dataInfo = new Dictionary<string, string>();
            if (dr == null)
            {
                return Activator.CreateInstance<T>();
            }
            dynamic objmatch = new ExpandoObject();
            for (int i = 0; i < dr.Table.Columns.Count; i++)
            {
                string value = dr[dr.Table.Columns[i].ColumnName].ToString();
                if (!string.IsNullOrEmpty(value))
                {
                    if (dr.Table.Columns[i].DataType.Name.IndexOf("DateTime") != -1)
                    {
                        dataInfo.Add(dr.Table.Columns[i].ColumnName, Convert.ToDateTime(value).ToString("yyyy-MM-dd HH:mm:ss"));
                    }
                    else
                    {
                        dataInfo.Add(dr.Table.Columns[i].ColumnName, value);
                    }
                }
                else
                {
                    if (dr.Table.Columns[i].DataType.Name.ToLower().IndexOf("String".ToLower()) != -1)
                    {
                        dataInfo.Add(dr.Table.Columns[i].ColumnName, value);
                    }
                }
            }
            objmatch = dataInfo;
            string json = JsonConvert.SerializeObject((object)objmatch);

            return DataRowToModelByEntity<T>(json);
        }

        public object DataRowToModelByEntity<T>(int ID)
        {
            DataRow dr = GetDataRow(ID);
            if (dr == null)
            {
                return Activator.CreateInstance<T>();
            }
            Dictionary<string, string> dataInfo = new Dictionary<string, string>();
            dynamic objmatch = new ExpandoObject();
            for (int i = 0; i < dr.Table.Columns.Count; i++)
            {
                string value = dr[dr.Table.Columns[i].ColumnName].ToString();
                if (!string.IsNullOrEmpty(value))
                {
                    if (dr.Table.Columns[i].DataType.Name.IndexOf("DateTime") != -1)
                    {
                        dataInfo.Add(dr.Table.Columns[i].ColumnName, Convert.ToDateTime(value).ToString("yyyy-MM-dd HH:mm:ss"));
                    }
                    else
                    {
                        dataInfo.Add(dr.Table.Columns[i].ColumnName, value);
                    }

                }
                else
                {
                    if (dr.Table.Columns[i].DataType.Name.ToLower().IndexOf("String".ToLower()) != -1)
                    {
                        dataInfo.Add(dr.Table.Columns[i].ColumnName, value);
                    }
                }
            }
            objmatch = dataInfo;
            IsoDateTimeConverter timeFormat = new IsoDateTimeConverter();
            timeFormat.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
            string json = JsonConvert.SerializeObject((object)objmatch, Formatting.Indented, timeFormat);

            return DataRowToModelByEntity<T>(json);
        }
        object DataRowToModelByEntity<T>(string json)
        {
            T t = Activator.CreateInstance<T>();
            object obj = JsonConvert.DeserializeObject(json, t.GetType());//CustomPublicMethod.JsonToObject(json, t);
            return obj;
        }

        /// <summary>
        /// 得到一个Object对象实体
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public object GetModelObject(long ID)
        {
            return DataRowToModelObject(GetDataRow(ID));
        }

        /// <summary>
        /// 得到一个Object对象实体
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public object GetModelObject(int ID)
        {
            return DataRowToModelObject(GetDataRow(ID));
        }

        /// <summary>
        /// 根据筛选条件获取一条数据Model对象
        /// </summary>
        /// <param name="filterFieldName">筛选条件字段名</param>
        /// <param name="filterValue">值</param>
        /// <returns></returns>
        public object GetModelObject(string filterFieldName, object filterValue)
        {
            return DataRowToModelObject(GetDataRow(filterFieldName, filterValue));
        }

        /// <summary>
        /// 根据筛选条件获取一条数据Model对象
        /// </summary>
        /// <param name="filterFieldName">筛选条件字段名</param>
        /// <param name="filterValue">值</param>
        /// <param name="operators">SQL筛选运算符号</param>
        /// <returns></returns>
        public object GetModelObject(string filterFieldName, object filterValue, string operators)
        {
            return DataRowToModelObject(GetDataRow(filterFieldName, filterValue, operators));
        }

        /// <summary>
        /// 根据筛选条件获取一条数据Model对象
        /// </summary>
        /// <param name="whereString">筛选条件</param>
        /// <returns></returns>
        public object GetModelObject(string whereString)
        {
            return DataRowToModelObject(GetDataRow(whereString));
        }

        /// <summary>
        ///  得到一个object对象实体
        /// </summary>
        /// <param name="row"></param>
        /// <returns></returns>
        public object DataRowToModelObject(DataRow row)
        {
            return DataRowToModel(row, this.ObjModel);
        }

        /// <summary>
        /// 获取一条数据DataRow对象
        /// </summary>
        /// <param name="ID">id</param>
        /// <returns></returns>
        public DataRow GetDataRow(long id)
        {
            return GetDataRow("ID", id);
        }

        /// <summary>
        /// 获取一条数据DataRow对象
        /// </summary>
        /// <param name="ID">id</param>
        /// <returns></returns>
        public DataRow GetDataRow(int id)
        {
            return GetDataRow("ID", id);
        }

        /// <summary>
        /// 根据筛选条件获取一条数据DataRow对象
        /// </summary>
        /// <param name="whereString">筛选条件</param>
        /// <returns></returns>
        public DataRow GetDataRow(string whereString)
        {
            if (whereString.Trim().Length > 0)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select  top 1 " + this.TableFieldNameStr + " from " + this.TableName + " ");
                strSql.Append(" where  " + whereString);

                DataSet ds = DbHelper.Query(cps.ConnectionString, strSql.ToString());
                if (ds.Tables[0].Rows.Count > 0)
                {
                    return ds.Tables[0].Rows[0];
                }
                else
                {
                    return null;
                }
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 根据筛选条件获取一条数据DataRow对象
        /// </summary>
        /// <param name="filterFieldName">筛选条件字段名</param>
        /// <param name="filterValue">值</param>
        /// <param name="operators">SQL筛选运算符号</param>
        /// <returns></returns>
        public DataRow GetDataRow(string filterFieldName, object filterValue, string operators = "=")
        {
            if (filterFieldName != "" && filterValue != null)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select  top 1 " + this.TableFieldNameStr + " from " + this.TableName + " ");
                strSql.Append(" where 1=1 and " + filterFieldName + "" + operators + "@" + filterFieldName + " ");
                SqlParameter[] parameters = { new SqlParameter("@" + filterFieldName, filterValue) };
                DataSet ds = DbHelper.Query(cps.ConnectionString, strSql.ToString(), parameters);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    return ds.Tables[0].Rows[0];
                }
                else
                {
                    return null;
                }
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 根据筛选条件获取一条数据DataRow对象(运算符是“=”)
        /// </summary>
        /// <param name="filterFieldName">筛选条件字段名</param>
        /// <param name="filterValue">值</param>
        /// <returns></returns>
        public DataRow GetDataRow(string filterFieldName, object filterValue)
        {
            return GetDataRow(filterFieldName, filterValue, "=");
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public object DataRowToModel(DataRow row, object model)
        {
            if (row != null)
            {
                for (int i = 0; i < PropertyInfos.Length; i++)
                {
                    try
                    {
                        System.Reflection.PropertyInfo propertyInfo = PropertyInfos[i];
                        string tempFieldName = propertyInfo.Name;//字段名

                        if (row[tempFieldName] != null)
                        {
                            object tempValue = row[tempFieldName];//对应字段值
                            DbHelper.SetPropertyValue(model, propertyInfo, tempValue);
                        }
                    }
                    catch
                    {

                    }
                }
            }
            return model;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        /// <param name="strWhere">条件语句 不包含 where 关键字</param>
        /// <param name="filedOrder">SQL排序 如：id desc</param>
        public DataSet GetList(string strWhere, string filedOrder = "SN desc")
        {
            return this.GetList(0, strWhere, filedOrder);
        }

        /// <summary>
        /// 获得全部数据列表
        /// </summary>
        /// <param name="filedOrder">SQL排序 如：id desc</param>
        public DataSet GetAllList(string filedOrder = "SN desc")
        {
            return this.GetList("", filedOrder);
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        /// <param name="Top">行数</param>
        /// <param name="strWhere">条件 不包含 where 关键字</param>
        /// <param name="filedOrder">SQL排序 如：id asc</param>
        /// <returns></returns>
        public DataSet GetList(int Top, string strWhere, string filedOrder = "SN desc")
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" " + this.TableFieldNameStr + " from " + this.TableName + " ");

            if (strWhere.Trim() != "")
            {
                strSql.Append(" where 1=1 and  (" + strWhere + ")");
            }
            strSql.Append(" order by " + filedOrder);

            DataSet ds = DbHelper.Query(cps.ConnectionString, strSql.ToString());
            ds.Tables[0].TableName = this.TableName;
            return ds;
        }
        #endregion

        #region 数据分页
        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM " + this.TableName + " ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where 1=1 and  (" + strWhere + ")");
            }
            object obj = DbHelper.GetSingle(cps.ConnectionString, strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 获取分页后总页数
        /// </summary>
        /// <param name="strWhere">筛选条件</param>
        /// <param name="pageSize">页面大小</param>
        /// <returns></returns>
        public int GetByPageCount(string strWhere, int pageSize)
        {
            int pageCount = 0;
            if (pageSize > 0)
            {
                int allCount = GetRecordCount(strWhere);
                pageCount = allCount / pageSize;
                if (pageCount * pageSize < allCount)
                {
                    pageCount++;
                }
            }

            return pageCount;
        }

        public int GetByPageCount(string strWhere, int pageSize, ref int totalCount)
        {
            int pageCount = 0;
            if (pageSize > 0)
            {
                int allCount = GetRecordCount(strWhere);
                totalCount = allCount;
                pageCount = allCount / pageSize;
                if (pageCount * pageSize < allCount)
                {
                    pageCount++;
                }
            }

            return pageCount;
        }

        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        /// <param name="strWhere">筛选条件</param>
        /// <param name="pageIndex">当前页 不能小于0的整数</param>
        /// <param name="pageSize">页面大小，每页显示条数 不能小于0的整数</param>
        /// <param name="orderby">排序</param>
        /// <returns></returns>
        public DataSet GetListByPage(string strWhere, int pageIndex, int pageSize, string orderby = "SN desc")
        {
            return this.GetListByPage(strWhere, orderby, (pageIndex - 1) * pageSize, pageIndex * pageSize);
        }

        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        /// <param name="strWhere">筛选条件</param>
        /// <param name="orderby">排序</param>
        /// <param name="pageIndex">当前页</param>
        /// <param name="pageSize">页面大小，每页显示条数</param>
        /// <param name="pageCount">返回总页数</param>
        /// <returns></returns>
        public DataSet GetListByPage(string strWhere, string orderby, int pageIndex, int pageSize, ref int pageCount)
        {
            pageCount = GetByPageCount(strWhere, pageSize);
            return this.GetListByPage(strWhere, orderby, (pageIndex - 1) * pageSize, pageIndex * pageSize);
        }

        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        /// <param name="strWhere">条件</param>
        /// <param name="orderby">排序</param>
        /// <param name="startIndex">开始index</param>
        /// <param name="endIndex">结束index</param>
        /// <returns></returns>
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strWhere = " Where " + strWhere;
            }

            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                orderby = " Order By " + orderby;
            }

            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM " + this.TableName + " Where ID Not IN ");
            strSql.Append("(Select Top " + startIndex + " ID From  " + this.TableName + " " + strWhere + orderby + ")");
            strSql.Append(" And ID In ");
            strSql.Append("(Select Top " + endIndex + " ID From  " + this.TableName + " " + strWhere + orderby + ")");
            strSql.Append(orderby);
            DataSet ds = DbHelper.Query(cps.ConnectionString, strSql.ToString());
            ds.Tables[0].TableName = this.TableName;
            return ds;
        }
        #endregion

        #region 
        /// <summary>
        /// Type转Dbtype
        /// </summary>
        /// <param name="type">变量的Type类型</param>
        /// <returns>对应的DbType类型</returns>
        public static SqlDbType GetDbType(Type type)
        {
            SqlDbType result = SqlDbType.NVarChar;
            if (type.Equals(typeof(Int32)) || type.IsEnum)
                result = SqlDbType.Int;
            else if (type.Equals(typeof(Int64)))
                result = SqlDbType.Int;
            else if (type.Equals(typeof(Double)) || type.Equals(typeof(Double)))
                result = SqlDbType.Decimal;
            else if (type.Equals(typeof(DateTime)))
                result = SqlDbType.DateTime;
            else if (type.Equals(typeof(Boolean)))
                result = SqlDbType.Bit;
            else if (type.Equals(typeof(String)))
                result = SqlDbType.NVarChar;
            else if (type.Equals(typeof(Decimal)))
                result = SqlDbType.Decimal;
            else if (type.Equals(typeof(Byte[])))
                result = SqlDbType.Image;
            else if (type.Equals(typeof(Guid)))
                result = SqlDbType.UniqueIdentifier;

            return result;
        }
        #endregion

        #region 优化sql新增修改的方法

        /// <summary>
        /// 通过泛型实体生成更新语句和对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="date"></param>
        /// <param name="filterFieldName"></param>
        /// <param name="operators"></param>
        /// <param name="overlookFieldList"></param>
        /// <param name="whereStr"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public CommandInfo GetUpdateCommandInfoByEntity<T>(T date, string filterFieldName = "ID",
            string operators = "=", string overlookFieldList = "ID", string whereStr = null,
            EffentNextType type = EffentNextType.None)
        {
            var properties = ReflectionHelper.GetProperties(typeof(T));
            StringBuilder strSql = new StringBuilder();

            Dictionary<string, PropertyInfo> dicWhereData = new Dictionary<string, PropertyInfo>();

            strSql.Append("update " + this.TableName + " set ");
            List<SqlParameter> parameterlist = new List<SqlParameter>();
            var ignorePropertyNames = new HashSet<string>();
            var filterPropertyNames = new HashSet<string>();
            ///跳过的字段数据
            if (!string.IsNullOrEmpty(overlookFieldList))
            {
                foreach (var msg in overlookFieldList.Split(','))
                {
                    ignorePropertyNames.Add(msg);
                }
            }
            ///用于查询的where条件的数据
            if (!string.IsNullOrEmpty(filterFieldName))
            {
                foreach (var msg in filterFieldName.Split(','))
                {
                    filterPropertyNames.Add(msg);
                }
            }

            foreach (var property in properties)
            {
                var ignoreProperty = ignorePropertyNames.SingleOrDefault(x => x.Equals(property.Value.Name, StringComparison.CurrentCultureIgnoreCase));

                var filterProperty = filterPropertyNames.SingleOrDefault(x => x.Equals(property.Value.Name, StringComparison.CurrentCultureIgnoreCase));
                if (filterProperty != null)
                {
                    dicWhereData.Add(property.Key, property.Value);
                }

                if (ignoreProperty != null)
                    continue;

                var propertyType = ReflectionHelper.GetPropertyType(property.Value);
                if (propertyType.Name.ToLower().IndexOf("list") != -1)
                {
                    continue;
                }
                var propertyValue = ReflectionHelper.GetPropertyValue(date, property.Value);
                if (propertyValue == null)
                {
                    continue;
                }
                else if (propertyValue != null && propertyType.Name.ToLower().Equals("datetime") && propertyValue.ToString().Contains("0001"))
                {
                    continue;
                }
                else if (propertyValue != null && propertyType.Name.ToLower().Equals("datetime") && Convert.ToDateTime(propertyValue) <= DbHelper.GetNullDateTime())
                {
                    continue;
                }

                //  ColumnAction(property.Value.Name, propertyValue, propertyType, DataTypes.Object, 0);
                if (propertyValue != null)
                {
                    strSql.Append("" + property.Key + "=@" + property.Key + ",");
                    SqlParameter parameter = new SqlParameter("@" + property.Key, propertyValue);
                    parameterlist.Add(parameter);
                }
            }

            //去除结尾多余的“,”
            if (strSql.ToString().LastIndexOf(',') == strSql.Length - 1)
            {
                strSql.Remove(strSql.Length - 1, 1);
            }

            if (whereStr == null)
            {
                if (filterFieldName != "" && operators != "")
                {
                    string strWhere = "";
                    foreach (var property in dicWhereData)
                    {
                        if (!string.IsNullOrEmpty(strWhere))
                        {
                            strWhere += " and ";
                        }
                        strWhere += property.Key + operators + "@" + property.Key;

                        var propertyValue = ReflectionHelper.GetPropertyValue(date, property.Value);
                        parameterlist.Add(new SqlParameter("@" + property.Key, propertyValue));
                    }
                    strSql.Append(" where " + strWhere);
                }
            }
            else
            {
                strSql.Append(" where 1=1 and (" + whereStr + ")");
            }
            SqlParameter[] parameters = parameterlist.ToArray();
            return new CommandInfo(strSql.ToString(), parameters, type);
        }

        /// <summary>
        /// 通过泛型方法生成新增对象信息
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="date"></param>
        /// <param name="overlookFieldList"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public CommandInfo GetAddCommandInfoByEntity<T>(T date, string overlookFieldList = "ID", EffentNextType type = EffentNextType.None)
        {
            StringBuilder strSql = new StringBuilder();
            var properties = ReflectionHelper.GetProperties(typeof(T));
            StringBuilder tempFieldNameString = new StringBuilder();
            StringBuilder tempValueVariable = new StringBuilder();
            List<SqlParameter> parameterlist = new List<SqlParameter>();
            var ignorePropertyNames = new HashSet<string>();

            if (!string.IsNullOrEmpty(overlookFieldList))
            {
                foreach (var msg in overlookFieldList.Split(','))
                {
                    ignorePropertyNames.Add(msg);
                }
            }

            foreach (var property in properties)
            {
                var ignoreProperty = ignorePropertyNames.SingleOrDefault(x => x.Equals(property.Value.Name, StringComparison.CurrentCultureIgnoreCase));

                if (ignoreProperty != null)
                    continue;

                var propertyType = ReflectionHelper.GetPropertyType(property.Value);
                if (propertyType.Name.ToLower().IndexOf("list") != -1)
                {
                    continue;
                }
                var propertyValue = ReflectionHelper.GetPropertyValue(date, property.Value);
                if (propertyValue == null)
                {
                    continue;
                }
                if (propertyValue != null && propertyType.Name.ToLower().Equals("datetime") && propertyValue.ToString().Contains("0001"))
                {
                    continue;
                }
                if (propertyValue != null && propertyType.Name.ToLower().Equals("datetime") && Convert.ToDateTime(propertyValue) <= DbHelper.GetNullDateTime())
                {
                    continue;
                }
                else if (property.Key.ToLower() == "id" && propertyType == typeof(long) && (propertyValue == null || Convert.ToInt64(propertyValue) == 0))
                {
                    propertyValue = DbHelper.GenerateId();
                }
                //  ColumnAction(property.Value.Name, propertyValue, propertyType, DataTypes.Object, 0);
                if (propertyValue != null)
                {
                    tempFieldNameString.Append("" + property.Key + ",");
                    tempValueVariable.Append("@" + property.Key + ",");
                    SqlParameter parameter = new SqlParameter("@" + property.Key, propertyValue);
                    parameterlist.Add(parameter);
                }
            }

            if (tempFieldNameString.ToString().LastIndexOf(',') == tempFieldNameString.Length - 1)
            {
                tempFieldNameString.Remove(tempFieldNameString.Length - 1, 1);
            }
            //去除结尾多余的“,”
            if (tempValueVariable.ToString().LastIndexOf(',') == tempValueVariable.Length - 1)
            {
                tempValueVariable.Remove(tempValueVariable.Length - 1, 1);
            }
            strSql.Append("insert into " + this.TableName + "(");
            strSql.Append("" + tempFieldNameString.ToString() + ")");
            strSql.Append(" values (");
            strSql.Append("" + tempValueVariable.ToString() + ")");
            strSql.Append(";select @@IDENTITY");

            SqlParameter[] parameters = parameterlist.ToArray();

            return new CommandInfo(strSql.ToString(), parameters, type);

        }

        public object GetAddCommandInfoByEntity<T>(T date, string overlookFieldList = "ID")
        {
            return GetAddCommandInfoByEntity(date, overlookFieldList, EffentNextType.None);
        }

        public object GetUpdateCommandInfoByEntity<T>(T date, string filterFieldName = "ID", string operators = "=", string overlookFieldList = "ID", string whereStr = null)
        {
            return GetUpdateCommandInfoByEntity(date, filterFieldName, operators, overlookFieldList, whereStr, EffentNextType.None);
        }

        #endregion


        #region 17-04-20 添加
        /// <summary>
        /// 将数据列表转泛型数据
        /// </summary>
        /// <typeparam name="T">泛型类型</typeparam>
        /// <param name="dt">数据表数据</param>
        /// <returns>数据集</returns>
        public T GetListToEntity<T>(DataTable dt)
        {
            var jSetting = new JsonSerializerSettings { NullValueHandling = NullValueHandling.Ignore };

            T entityList = Activator.CreateInstance<T>();
            entityList = JsonConvert.DeserializeObject<T>(JsonConvert.SerializeObject(dt), jSetting);
            return entityList;
        }

        /// <summary>
        /// 将sql查询出来的数据转成泛型列表
        /// </summary>
        /// <typeparam name="T">泛型类型</typeparam>
        /// <param name="sql">sql数据查询字符串</param>
        /// <returns>数据集</returns>
        public T GetListToEntity<T>(string sql)
        {
            DataTable dt = GetViewDataSetByCustomer(sql).Tables[0];
            return GetListToEntity<T>(dt);
        }
        #endregion

        public DataSet GetListByPage(string strWhere, string orderby, int pageIndex, int pageSize, ref int pageCount, ref int Totalcount)
        {
            pageCount = GetByPageCount(strWhere, pageSize, ref Totalcount);
            return this.GetListByPage(strWhere, orderby, (pageIndex - 1) * pageSize, pageIndex * pageSize);
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        /// <param name="dt">DataTable</param>
        public List<object> GetDataTableToOblList(DataTable dt)
        {
            List<object> modelList = new List<object>();
            int rowsCount = dt.Rows.Count;
            if (rowsCount > 0)
            {
                object modelObj;
                for (int n = 0; n < rowsCount; n++)
                {
                    modelObj = DataRowToModelObject(dt.Rows[n]);
                    if (modelObj != null)
                    {
                        modelList.Add(modelObj);
                    }
                }
            }
            return modelList;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dt"></param>
        /// <returns></returns>
        public List<T> GetDataTableToOblList<T>(DataTable dt)
        {
            List<T> modelList = new List<T>();
            int rowsCount = dt.Rows.Count;
            if (rowsCount > 0)
            {
                T modelObj;
                for (int n = 0; n < rowsCount; n++)
                {
                    modelObj = (T)DataRowToModelObject(dt.Rows[n]);
                    if (modelObj != null)
                    {
                        modelList.Add(modelObj);
                    }
                }
            }
            return modelList;
        }
    }

    #region 反射助手，用于泛型实体的反射操作
    internal static class ReflectionHelper
    {
        private static readonly ConcurrentDictionary<Type, Dictionary<string, PropertyInfo>> _cachedProperties = new ConcurrentDictionary<Type, Dictionary<string, PropertyInfo>>();
        /// <summary>
        /// 得到实体反射的表达式
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="expression"></param>
        /// <returns></returns>
        public static string GetPropertyNameFromExpression<T>(Expression<Func<T, object>> expression)
        {
            string propertyPath = null;
            if (expression.Body is UnaryExpression)
            {
                var unaryExpression = (UnaryExpression)expression.Body;
                if (unaryExpression.NodeType == ExpressionType.Convert)
                    propertyPath = unaryExpression.Operand.ToString();
            }

            if (propertyPath == null)
                propertyPath = expression.Body.ToString();

            propertyPath = propertyPath.Replace(expression.Parameters[0] + ".", string.Empty);

            return propertyPath;
        }

        public static List<string> GetPropertyNamesFromExpressions<T>(Expression<Func<T, object>>[] expressions)
        {
            var propertyNames = new List<string>();
            foreach (var expression in expressions)
            {
                var propertyName = GetPropertyNameFromExpression(expression);
                propertyNames.Add(propertyName);
            }
            return propertyNames;
        }

        public static object GetPropertyValue(object item, PropertyInfo property)
        {
            var value = property.GetValue(item, null);

            return value;
        }

        public static object GetPropertyValue(object item, string propertyName)
        {
            PropertyInfo property;
            foreach (var part in propertyName.Split('.'))
            {
                if (item == null)
                    return null;

                var type = item.GetType();

                property = type.GetProperty(part);
                if (property == null)
                    return null;

                item = GetPropertyValue(item, property);
            }
            return item;
        }

        public static object GetPropertyValueDynamic(object item, string name)
        {
            var dictionary = (IDictionary<string, object>)item;

            return dictionary[name];
        }

        public static Dictionary<string, PropertyInfo> GetProperties(Type type)
        {
            var properties = _cachedProperties.GetOrAdd(type, BuildPropertyDictionary);

            return properties;
        }

        private static Dictionary<string, PropertyInfo> BuildPropertyDictionary(Type type)
        {
            var result = new Dictionary<string, PropertyInfo>();

            var properties = type.GetProperties();
            foreach (var property in properties)
            {
                result.Add(property.Name.ToLower(), property);
            }
            return result;
        }

        public static bool IsList(object item)
        {
            if (item is ICollection)
                return true;

            return false;
        }

        public static bool IsNullable(PropertyInfo property)
        {
            if (property.PropertyType.IsGenericType &&
                property.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
                return true;

            return false;
        }

        /// <summary>
        /// Includes a work around for getting the actual type of a Nullable type.
        /// </summary>
        public static Type GetPropertyType(PropertyInfo property)
        {
            if (IsNullable(property))
                return property.PropertyType.GetGenericArguments()[0];

            return property.PropertyType;
        }

        public static object GetDefault(Type type)
        {
            if (type.IsValueType)
                return Activator.CreateInstance(type);
            return null;
        }

        public static bool IsBasicClrType(Type type)
        {
            if (type.IsEnum
                || type.IsPrimitive
                || type.IsValueType
                || type == typeof(string)
                || type == typeof(DateTime))
                return true;

            return false;
        }

        public static bool IsCustomEntity<T>()
        {
            var type = typeof(T);
            if (type.IsClass && Type.GetTypeCode(type) == TypeCode.Object)
                return true;
            return false;
        }
    }
    #endregion
}