Wms_WebApi/WestDistance/Core/DapperORMCore.Dappers/DapperSQLExtensions.cs

using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Collections;
using DapperORMCore.Dapper.BaseModel;
using DapperORMCore.Model.CoreModel;
using DapperORMCore.String.Enums;
using DapperORMCore.Model.BaseModel;
using DapperORMCore.String.Consts;

namespace DapperORMCore.Dapper
{
    #region 扩展Dapper操作
    /// <summary>
    /// 获取对应操作语句
    /// </summary>
    public static class DapperSQLExtensions
    {
        #region add
        /// <summary>
        /// 插入 返回字段限定
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="con"></param>
        /// <param name="item"></param>
        /// <param name="filterNames"></param>
        /// <param name="outFieldName"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public static string InsertOutParm<T>(this IDbConnection con, T item, IEnumerable<string> filterNames, string outFieldName, IDbTransaction transaction = null) where T : class
        {
            var outSql = DynamicQuery.GetInsertQuery(typeof(T).Name, item, outFieldName, filterNames);
            return outSql;
        }

        /// <summary>
        /// 插入数据模型
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="con"></param>
        /// <param name="item"></param>
        /// <param name="filters"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public static string Insert<T>(this IDbConnection con, T item, string[] nofilters, IDbTransaction transaction = null) where T : class
        {
            var outSql = DynamicQuery.GetInsertQuery(typeof(T).Name, item, "", nofilters);
            return outSql;
        }

        /// <summary>
        /// 插入数据模型(不支持DataTable)
        /// </summary>
        /// <param name="con"></param>
        /// <param name="item"></param>
        /// <param name="tableName"></param>
        /// <param name="nofilters"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public static string Insert(this IDbConnection con, object item, string tableName, string[] nofilters, IDbTransaction transaction = null)
        {
            var outSql = DynamicQuery.GetInsertQuery(tableName, item, "", nofilters);
            return outSql;
        }
        #endregion

        #region delete
        /// <summary>
        /// 删除数据模型
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="con"></param>
        /// <param name="item"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public static string Delete<T>(this IDbConnection con, T item, string identityFieldName, IDbTransaction transaction = null) where T : class
        {
            //TODO以后这里还要优化,这里只支持一个字段主键
            var props = typeof(T).GetProperties();
            var columns = props.Select(p => p.Name).Where(s => s == identityFieldName).ToArray();
            var columnsValue = props.Where(s => s.Name == identityFieldName).
                        Select(p => p.GetValue(item, null).ToString()).FirstOrDefault();
            var parameters = columns.Select(name => "and " + name + SQLBaseString._TO + "'" + columnsValue + "'").ToList();
            var outSql = string.Join(SQLBaseString._AND, parameters);
            outSql = string.Format(SQLString._DeleteBase, typeof(T).Name, outSql);
            //var result = con.Query<T>(sql, item, transaction);
            return outSql;
        }

        /// <summary>
        /// 删除单一条件SQL记录
        /// </summary>
        /// <param name="con"></param>
        /// <param name="tableName"></param>
        /// <param name="whereParm"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public static string Delete<T>(this IDbConnection con, string fieldName, string value, IDbTransaction transaction = null) where T : class
        {
            var whereParm = string.Format(SQLBaseString._FIELD_VALUE, fieldName, value);
            var outSql = DynamicQuery.GetDeleteQuery(typeof(T).Name, whereParm);
            //con.Query(sql, transaction);
            return outSql;
        }

        /// <summary>
        /// 删除多个条件SQL记录
        /// </summary>
        /// <param name="con"></param>
        /// <param name="tableName"></param>
        /// <param name="whereParm"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public static string Delete<T>(this IDbConnection con, IEnumerable<FieldKeyInfo> fieldKeyList, IDbTransaction transaction = null) where T : class
        {
            var whereParm = DynamicQuery.GetWhereSQL(fieldKeyList);
            var outSql = DynamicQuery.GetDeleteQuery(typeof(T).Name, whereParm);
            return outSql;
        }

        /// <summary>
        /// 删除多个条件SQL记录
        /// </summary>
        /// <param name="con"></param>
        /// <param name="tableName"></param>
        /// <param name="whereParm"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public static string Delete<T>(this IDbConnection con, string tableName, string fieldName, string value, IDbTransaction transaction = null) where T : class
        {
            var outSql = string.Format(SQLString._DeleteBase, tableName, string.Format("and {0}='1'", fieldName, value));
            return outSql;
        }

        /// <summary>
        /// 删除表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="con"></param>
        /// <param name="item"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public static string DropTable<T>(this IDbConnection con, T item, IDbTransaction transaction = null) where T : class
        {
            var outSql = DynamicQuery.GetDropTableQuery(typeof(T).GetType().Name);
            return outSql;
        }
        #endregion

        #region update
        /// <summary>
        /// 更新模型数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="con"></param>
        /// <param name="item"></param>
        /// <param name="transaction"></param>
        public static string Update<T>(this IDbConnection con, T item, string identityFieldName, string noUpdateIdentityFieldName, IDbTransaction transaction = null) where T : class
        {
            var outSql = DynamicQuery.GetUpdateQuery(typeof(T).Name, item, identityFieldName, noUpdateIdentityFieldName);
            return outSql;
        }

        /// <summary>
        /// 更新模型数据Value
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="con"></param>
        /// <param name="item"></param>
        /// <param name="value"></param>
        /// <param name="identityFieldName"></param>
        /// <param name="noUpdateIdentityFieldName"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public static string Update<T>(this IDbConnection con, T item, string value, string identityFieldName, string noUpdateIdentityFieldName, IDbTransaction transaction = null) where T : class
        {
            var outSql = DynamicQuery.GetUpdateQuery(typeof(T).Name, item, value, identityFieldName, noUpdateIdentityFieldName);
            return outSql;
        }

        /// <summary>
        /// 更新模型数据Object
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="con"></param>
        /// <param name="item"></param>
        /// <param name="value"></param>
        /// <param name="identityFieldName"></param>
        /// <param name="noUpdateIdentityFieldName"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public static string Update(this IDbConnection con, object item, string value, string identityFieldName, string noUpdateIdentityFieldName,
                        IDbTransaction transaction = null)
        {
            var outSql = DynamicQuery.GetUpdateQuery(item.GetType().Name, item, value, identityFieldName, noUpdateIdentityFieldName);
            return outSql;
        }

        /// <summary>
        /// 更新拼接语句
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="con"></param>
        /// <param name="item"></param>
        /// <param name="whereKeyList"></param>
        /// <param name="setKeyList"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public static string Update<T>(this IDbConnection con, IEnumerable<FieldKeyInfo> whereKeyList, IEnumerable<FieldKeyInfo> setKeyList, IDbTransaction transaction = null) where T : class
        {
            var tableName = typeof(T).Name;
            var whereInfo = string.Join(SQLBaseString._AND, whereKeyList.Select(m => m.FieldName + "='" + m.Value + "'"));
            var setInfo = string.Join(",", setKeyList.Select(m => m.FieldName + "='" + m.Value + "'"));
            var outSql = string.Format(SQLString._UpdateBase, tableName, setInfo, whereInfo);
            con.Execute(outSql, null, transaction);
            return outSql;
        }

        /// <summary>
        /// 更新模型数据SQL
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="con"></param>
        /// <param name="sql"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public static string Update<T>(this IDbConnection con, string sql, IDbTransaction transaction = null)
        {
            return sql;
        }
        #endregion

        #region exceute sql
        /// <summary>
        /// 执行SQL
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="con"></param>
        /// <param name="sql"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public static string ExceuteSQL<T>(this IDbConnection con, string sql, IDbTransaction transaction = null)
        {
            return sql;
        }
        #endregion

        #region sql bulk copy
        /// <summary>
        /// SQLBulkCopy批量插入数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="con"></param>
        /// <param name="list"></param>
        /// <param name="tableName"></param>
        public static DataQueueStatementInfo InsertBatchCopy<T>(this IDbConnection con, IEnumerable<T> list, string tableName
                    ) where T : class
        {
            //var outSql = tableName;
            //if (list.Any())
            //{
            //    Type type = typeof(T);
            //    var result = list.ToList().GetConvertDataTableMapper<T>();
            //    if (result.Status == OperateStatus.success.ToString())
            //    {
            //        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(con as SqlConnection))
            //        {
            //            con.Open();
            //            bulkCopy.DestinationTableName = tableName;
            //            bulkCopy.BatchSize = list.Count();
            //            if (result != null && result.Data.Rows.Count != 0)
            //            {
            //                bulkCopy.WriteToServer(result.Data);
            //            }
            //            bulkCopy.Close();
            //        }
            //    }
            //    return new DataQueueStatementInfo
            //    {
            //        ModelsT = null,
            //        DBOperationType = EnumDBOperationType.BulkCopy,
            //        SQL = sql
            //    };
            //}
            //TODO 批量处理有问题
            return null;
        }

        /// <summary>
        /// SQLBulkCopy批量插入数据DataTable
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="con"></param>
        /// <param name="list"></param>
        /// <param name="tableName"></param>
        public static DataQueueStatementInfo InsertBatchCopyDataTable(this IDbConnection con, DataTable item, string tableName)
        {
            //var outSql = tableName;
            //if (item.Rows.Count>0)
            //{
            //    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(con as SqlConnection))
            //    {
            //        con.Open();
            //        bulkCopy.DestinationTableName = tableName;
            //        bulkCopy.BatchSize = item.Rows.Count;
            //        bulkCopy.WriteToServer(item);
            //        bulkCopy.Close();
            //    }
            //    return new DataQueueStatementInfo
            //    {
            //        ModelsT = null,
            //        DBOperationType = EnumDBOperationType.BulkCopy,
            //        SQL = sql
            //    };
            //}
            //TODO 批量处理有问题
            return null;
        }
        #endregion

        #region query
        /// <summary>
        /// 查询语句模型数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="con"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static string SelectSQL<T>(this IDbConnection con, string sql)
        {
            return sql;
        }

        /// <summary>
        /// 查询语句模型表数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="con"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static string SelectTable<T>(this IDbConnection con, string sql)
        {
            var table = new DataTable("TableTemp");
            var reader = con.ExecuteReader(sql);
            table.Load(reader);
            return sql;
        }

        /// <summary>
        /// 查询语句模型表数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="con"></param>
        /// <param name="tableName"></param>
        /// <param name="where"></param>
        /// <param name="orderNames"></param>
        /// <returns></returns>
        public static string SelectTable<T>(this IDbConnection con, string tableName, string where)
        {
            var outSql = string.Format(SQLString._SelectSQLA, tableName, where);
            var table = new DataTable("TableTemp");
            var reader = con.ExecuteReader(outSql);
            table.Load(reader);
            return outSql;
        }

        /// <summary>
        /// 查询语句模型表数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="con"></param>
        /// <param name="tableName"></param>
        /// <param name="where"></param>
        /// <param name="orderNames"></param>
        /// <returns></returns>
        public static string SelectTable<T>(this IDbConnection con, string tableName, string where, string orderNames, bool isOrderDesc = false)
        {
            var outSql = string.Empty;
            if (isOrderDesc)
            {
                outSql = string.Format(SQLString._SelectSqlOrderByDesc, tableName, where == null ? "" : where, orderNames == null ? "" : orderNames);
            }
            else
            {
                outSql = string.Format(SQLString._SelectSqlOrderByDesc, tableName, where == null ? "" : where, orderNames == null ? "" : orderNames);
            }
            var table = new DataTable("TableTemp");
            var reader = con.ExecuteReader(outSql);
            table.Load(reader);
            return outSql;
        }

        /// <summary>
        /// 查询模型数据排序(带限定T)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="con"></param>
        /// <param name="fieldKeyList">带限定T</param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static string SelectT<T>(this IDbConnection con, IEnumerable<FieldKeyInfo<T>> fieldKeyList, string sql = "") where T : class
        {
            fieldKeyList.ToList().ForEach(m =>
            {
                m.Value = m.FieldName + m.Operation + "'" + m.Value + "'";
            });

            var inSql = string.Empty;
            var fieldString = string.Join(SQLBaseString._AND, fieldKeyList.ToList().Select(m => m.Value).ToArray());
            if (string.IsNullOrWhiteSpace(sql))
            {
                inSql = string.Format(SQLString._SelectSQL, typeof(T).Name, fieldString);
            }
            else
            {
                inSql = string.Format(SQLString._SelectSQL,
                        string.Format(SQLBaseString._INCLUDE_TEMP_VIEW, sql), fieldString);
            }
            return inSql;
        }

        /// <summary>
        /// 查询模型数据排序(不带限定T)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="con"></param>
        /// <param name="fieldKeyList">不带限定T</param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static string Select<T>(this IDbConnection con, IEnumerable<FieldKeyInfo> fieldKeyList,
                        string sql = "") where T : class
        {
            var inSql = string.Empty;
            var fieldString = DynamicQuery.GetWhereSQL(fieldKeyList);
            if (fieldKeyList.Count() != 0)
            {
                if (string.IsNullOrWhiteSpace(sql))
                {
                    inSql = string.Format(SQLString._SelectSQL, typeof(T).Name, fieldString);
                }
                else
                {
                    inSql = string.Format(SQLString._SelectSQL,
                            string.Format(SQLBaseString._INCLUDE_TEMP_VIEW, sql), fieldString);
                }
            }
            else
            {
                if (string.IsNullOrWhiteSpace(sql))
                {
                    inSql = string.Format(SQLString._Select, typeof(T).Name);
                }
                else
                {
                    inSql = string.Format(SQLString._SelectSQLA,
                            string.Format(SQLBaseString._INCLUDE_TEMP_VIEW, sql), fieldString);
                }
            }
            return inSql;
        }

        /// <summary>
        /// 查询模型数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="con"></param>
        /// <param name="criteria"></param>
        /// <returns></returns>
        public static string Select<T>(this IDbConnection con, object criteria = null)
        {
            var properties = PropertyManage.ParseProperties(criteria);
            var outSqlPairs = PropertyManage.GetSqlPairs(properties.AllNames, SQLBaseString._AND);
            var outSql = string.Format(SQLString._SelectSQLA, typeof(T).Name, outSqlPairs);
            return outSql;
        }

        /// <summary>
        /// 查询模型数据排序
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="con"></param>
        /// <param name="orderName"></param>
        /// <param name="sleepOrder"></param>
        /// <returns></returns>
        public static string SelectOrder<T>(this IDbConnection con, string orderName, bool sleepOrder = false)
        {
            var outSql = string.Format(SQLString._SelectBaseOrderBy, typeof(T).Name, orderName);
            if (sleepOrder)
            {
                outSql = outSql + SQLBaseString._DESC;
            }
            return outSql;
        }

        /// <summary>
        /// 查询模型数据第一条(有条件)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="con"></param>
        /// <param name="fieldKeyList"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static string SelectOrderFirst<T>(this IDbConnection con, IEnumerable<FieldKeyInfo<T>> fieldKeyList, string sql = "") where T : class
        {
            fieldKeyList.ToList().ForEach(m =>
            {
                m.Value = m.FieldName + "='" + m.Value;
            });
            var outSql = string.Empty;
            var fieldString = string.Join(SQLBaseString._AND, fieldKeyList.ToList().Select(m => m.Value).ToArray());
            if (string.IsNullOrWhiteSpace(sql))
            {
                outSql = string.Format(SQLString._SelectBaseFirst, typeof(T).Name, fieldString);
            }
            else
            {
                outSql = string.Format(SQLString._SelectBaseFirst,
                        string.Format(SQLBaseString._INCLUDE_TEMP_VIEW, sql), fieldString);
            }
            return outSql;
        }

        /// <summary>
        /// 查询模型数据第一条(无条件)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="con"></param>
        /// <param name="orderName"></param>
        /// <param name="sleepOrder"></param>
        /// <returns></returns>
        public static string SelectOrderFirst<T>(this IDbConnection con, string orderName, bool sleepOrder = false)
        {
            var outSql = string.Format(SQLString._SelectBaseFirstOrderBy, typeof(T).Name, orderName);
            if (sleepOrder)
            {
                outSql = outSql + SQLBaseString._DESC;
            }
            return outSql;
        }

        /// <summary>
        /// 查询指定单一条件数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="con"></param>
        /// <param name="fieldName"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static string SelectSQL<T>(this IDbConnection con, string fieldName, string value) where T : class
        {
            var outSql = string.Format(SQLString._SelectIdFirstSQLA, typeof(T).Name, fieldName, value);
            return outSql;
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="con"></param>
        /// <param name="fieldName"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static string SelectPage<T>(this IDbConnection con, string sql, string orderByName, int pageSize, int pageIndex, bool sleepOrder = false) where T : class
        {
            var outSql = string.Format(SQLString._SelectPageBase, sql, orderByName, sleepOrder ? "DESC" : "", pageSize * (pageIndex - 1), pageSize);
            return outSql;
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="con"></param>
        /// <param name="fieldName"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static string SelectPage<T>(this IDbConnection con, string orderByName, int pageSize, int pageIndex, string whereSQL = "", bool sleepOrder = false) where T : class
        {
            var sql = string.Format(SQLString._SelectSQLA, typeof(T).Name, whereSQL);
            var outSql = string.Format(SQLString._SelectPageBase, sql, orderByName, sleepOrder ? "DESC" : "", pageSize * (pageIndex - 1), pageSize);
            return outSql;
        }

        #endregion

        #region count
        /// <summary>
        /// 统计记录总数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="con"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public static string Count<T>(this IDbConnection con, string parms) where T : class
        {
            var outSql = string.Format(SQLString._CountBase, typeof(T).Name, parms);
            return outSql;
        }
        #endregion
    }
    #endregion

    #region Dynamic查询
    /// <summary>
    /// Dynamic查询
    /// </summary>
    public sealed class DynamicQuery
    {
        #region 获取插入语句结果DataTable
        /// <summary>
        /// 获取插入语句结果DataTable
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="item"></param>
        /// <param name="outFieldName"></param>
        /// <param name="filterNames"></param>
        /// <returns></returns>
        public static void GetInsertQuery(string tableName, DataTable item,
                                string outFieldName,
                    IEnumerable<string> filterNames)
        {
            for (int i = 0; i < item.Rows.Count; i++)
            {
                var fieldList = new List<string>();
                var valueList = new List<string>();

            }
        }
        #endregion

        #region 获取插入语句结果
        /// <summary>
        /// 获取插入语句结果
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="tableName"></param>
        /// <param name="item"></param>
        /// <param name="filterNames"></param>
        /// <param name="outFieldName"></param>
        /// <returns></returns>
        public static string GetInsertQuery(string tableName, object item,
                    string outFieldName,
                    IEnumerable<string> filterNames)
        {
            var props = item.GetType().GetProperties();
            var columns = props.Where(m => m.GetValue(item, null) != null).
                        //去除时间类型中的默认值
                        Where(m => m.GetValue(item, null).ToString() != DateTime.MinValue.ToString()
                                        //&& (m.GetValue(item, null).GetType() = Type.GetType("System.DateTime", true, true)
                                        ).Select(p => "[" + p.Name + "]").ToList();

            var values = new List<string>();

            if (filterNames != null)
            {
                foreach (var items in filterNames)
                {
                    columns = columns.Where(s => s != "[" + items + "]").ToList();
                }
                foreach (PropertyInfo detail in props)
                {
                    if (columns.Where(m => m.ToString() == "[" + detail.Name + "]").Any())
                    {
                        values.Add(item.GetType().GetProperty(detail.Name).GetValue(item, null).ToString());
                    }
                }
                if (string.IsNullOrWhiteSpace(outFieldName))
                {
                    return string.Format(SQLString._InsertBase,
                                 tableName,
                                 string.Join(",", columns),
                                 "'" + string.Join("','", values) + "'");
                }
                return string.Format(SQLString._InsertOutFirstFieldBase,
                                 tableName,
                                 string.Join(",", columns),
                                 "'" + string.Join("','", values) + "'",
                                 outFieldName);
            }
            else
            {
                foreach (PropertyInfo detail in props)
                {
                    if (item.GetType().GetProperty(detail.Name).GetValue(item, null) != null)
                    {
                        values.Add(item.GetType().GetProperty(detail.Name).GetValue(item, null).ToString());
                    }
                }
                if (string.IsNullOrWhiteSpace(outFieldName))
                {
                    return string.Format(SQLString._InsertBase,
                                 tableName,
                                 string.Join(",", columns),
                                 "'" + string.Join("','", values) + "'");
                }

                return string.Format(SQLString._InsertOutFirstFieldBase,
                                 tableName,
                                 string.Join(",", columns),
                                 "'" + string.Join("','", values) + "'",
                                 outFieldName);
            }
        }
        #endregion

        #region 获取删除语句结果
        /// <summary>
        /// 获取删除语句结果
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="whereParm"></param>
        /// <returns></returns>
        public static string GetDeleteQuery(string tableName, string whereParm)
        {
            if (!string.IsNullOrWhiteSpace(tableName))
            {
                return string.Format(SQLString._DeleteBaseA,
                            tableName, whereParm);
            }
            else
            {
                return string.Empty;
            }
        }
        #endregion

        #region 获取删除表语句结果
        /// <summary>
        /// 获取删除表语句结果
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static string GetDropTableQuery(string tableName)
        {
            if (!string.IsNullOrWhiteSpace(tableName))
            {
                return string.Format(SQLString._DropBase,
                            tableName);
            }
            else
            {
                return string.Empty;
            }
        }
        #endregion

        #region 获取更新语句结果
        /// <summary>
        /// 获取更新语句结果
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="tableName"></param>
        /// <param name="item"></param>
        /// <returns></returns>
        public static string GetUpdateQuery(string tableName, object item, string identityFieldName, string noUpdateIdentityFieldNames)
        {
            var props = item.GetType().GetProperties();
            var columns = props.Where(s => s.Name != identityFieldName &&
                        !s.Name.Equals(identityFieldName) &&
                        !s.Name.Equals(noUpdateIdentityFieldNames) && //TODO 这里只支持一个不更新字段名称,后期改
                        s.GetValue(item, null) != null &&
                        ((s.GetValue(item, null).ToString() != DateTime.MinValue.ToString() &&
                                        s.GetValue(item, null).GetType() == Type.GetType("System.DateTime", true, true))
                         || s.GetValue(item, null).GetType() != Type.GetType("System.DateTime", true, true))
                        ).ToArray();
            var parameters = columns.Select(s => "[" + s.Name + "]" + SQLBaseString._TO + "'" +
                        s.GetValue(item, null) + "'").ToList();
            var whereParameter = props.Where(s => s.Name.ToLower() == identityFieldName.ToLower()).FirstOrDefault();
            return string.Format(SQLString._UpdateBase,
                                 tableName,
                                 string.Join(",", parameters),
                            string.Format("[" + identityFieldName + "]" + "='" +
                                 whereParameter.GetValue(item, null)) + "'"
                        );
        }


        public static string GetUpdateQuery(string tableName, object item, string identityFieldName)
        {
            var props = item.GetType().GetProperties();
            var columns = props.Where(s => s.Name != identityFieldName &&
                        !s.Name.Equals(identityFieldName) &&     //TODO 这里只支持一个不更新字段名称,后期改
                        s.GetValue(item, null) != null &&
                        ((s.GetValue(item, null).ToString() != DateTime.MinValue.ToString() &&
                                        s.GetValue(item, null).GetType() == Type.GetType("System.DateTime", true, true))
                         || s.GetValue(item, null).GetType() != Type.GetType("System.DateTime", true, true))
                        ).ToArray();
            var parameters = columns.Select(s => "[" + s.Name + "]" + SQLBaseString._TO + "'" +
                        s.GetValue(item, null) + "'").ToList();
            var whereParameter = props.Where(s => s.Name.ToLower() == identityFieldName.ToLower()).FirstOrDefault();
            return string.Format("UPDATE {0} SET {1} ",
                                 tableName,
                                 string.Join(",", parameters),
                            string.Format("[" + identityFieldName + "]" + "='" +
                                 whereParameter.GetValue(item, null)) + "'"
                        );
        }



        #endregion

        #region 获取更新语句结果Value
        /// <summary>
        /// 获取更新语句结果Value
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="tableName"></param>
        /// <param name="item"></param>
        /// <returns></returns>
        public static string GetUpdateQuery(string tableName, object item, string value, string identityFieldName, string noUpdateIdentityFieldNames)
        {
            var props = item.GetType().GetProperties();
            var columns = props.Where(s => s.Name != identityFieldName &&
                        !s.Name.Equals(identityFieldName) &&
                        !s.Name.Equals(noUpdateIdentityFieldNames) && //TODO 这里只支持一个不更新字段名称,后期改
                        s.GetValue(item, null) != null &&
                        (s.GetValue(item, null).ToString() != DateTime.MinValue.ToString() &&
                                        s.GetValue(item, null).GetType() != Type.GetType("System.DateTime", true, true))
                        ).ToArray();
            var parameters = columns.Select(s => "[" + s.Name + "]" + SQLBaseString._TO + "'" +
                        s.GetValue(item, null) + "'").ToList();
            return string.Format(SQLString._UpdateBase,
                                 tableName,
                                 string.Join(",", parameters),
                            string.Format("[" + identityFieldName + "]" + "='" +
                                 value) + "'"
                        );
        }
        #endregion

        #region 获取Where语句段
        /// <summary>
        /// 获取Where语句段
        /// </summary>
        /// <param name="fieldKeyList"></param>
        /// <returns></returns>
        public static string GetWhereSQL(IEnumerable<FieldKeyInfo> fieldKeyList)
        {
            if (fieldKeyList == null)
            {
                return "";
            }
            var paramList = new List<string>();
            fieldKeyList.Select(m => m.GroupIndex).Distinct().ToList().ForEach(index =>
              {
                  var parmItem = new List<string>();
                  fieldKeyList.Where(m => m.GroupIndex == index).ToList().ForEach(m =>
                  {
                      switch (m.FileType.Value)
                      {
                          case EnumCSharpPropertyType.BOOL:
                          case EnumCSharpPropertyType.BOOLEAN:
                              parmItem.Add(string.Format("{3} {0} {2} {1}", m.FieldName,
                                      Convert.ToInt32(m.Value), m.Operation, m.WhereItemType.ToString()));
                              break;
                          case EnumCSharpPropertyType.INT:
                          case EnumCSharpPropertyType.INT16:
                          case EnumCSharpPropertyType.INT32:
                          case EnumCSharpPropertyType.DECIMAL:
                          case EnumCSharpPropertyType.DOUBLE:
                          case EnumCSharpPropertyType.FLOAT:
                              parmItem.Add(string.Format("{2} {0} = {1}", m.FieldName, m.Value,
                                            m.WhereItemType.ToString()));
                              break;
                          case EnumCSharpPropertyType.BYTE:
                              break;
                          case EnumCSharpPropertyType.DATE:
                          case EnumCSharpPropertyType.DATETIME:
                          case EnumCSharpPropertyType.STRING:
                              switch (m.Operation)
                              {
                                  //包含和不包含有特殊意思在
                                  case DBOperationString._ContainIn:
                                      parmItem.Add(string.Format("{2} {1} {0}",
                                          string.Format(DBOperationString._ContainIn, m.Value), m.FieldName,
                                            m.WhereItemType.ToString()));
                                      break;
                                  case DBOperationString._NoContainIn:
                                      parmItem.Add(string.Format("{2} {1} {0}",
                                          string.Format(DBOperationString._NoContainIn, m.Value), m.FieldName,
                                            m.WhereItemType.ToString()));
                                      break;
                                  case DBOperationString._In:
                                      parmItem.Add(string.Format("{2} {1} {0}",
                                          string.Format(DBOperationString._In, m.Value), m.FieldName,
                                            m.WhereItemType.ToString()));
                                      break;
                                  case DBOperationString._NoEqual:
                                      parmItem.Add(string.Format("{2} {0} !='{1}'", m.FieldName, m.Value,
                                            m.WhereItemType.ToString()));
                                      break;
                                  default:
                                      parmItem.Add(string.Format("{2} {0} ='{1}'", m.FieldName, m.Value,
                                            m.WhereItemType.ToString()));
                                      break;
                              }
                              break;
                      };
                  });
                  paramList.Add("(1=1 " + string.Join(" ", parmItem) + ")");
              });
            return string.Join(" AND ", paramList);
        }
        #endregion

    }
    #endregion

    #region private

    /// <summary>
    /// 动态工具
    /// </summary>
    public static class DynamicUtil
    {
        #region 转换扩展
        /// <summary>
        /// 转换模型
        /// </summary>
        /// <param name="operationStatusInfo"></param>
        /// <returns></returns>
        public static OperateResultInfo<DataTable> GetConvertDataTableMapper<T>(this T tModels)
        {
            var tList = new List<T>();
            tList.Add(tModels);
            return new OperateResultInfo<DataTable>
            {
                AffectedRows = 0,
                Data = ModelsToDataTable<T>(tList),
                Message = string.Empty,
                Status = OperateStatus.Success,
            };
        }

        /// <summary>
        /// 转换List模型
        /// </summary>
        /// <param name="operationStatusInfo"></param>
        /// <returns></returns>
        public static OperateResultInfo<DataTable> GetConvertDataTableMapper<T>(this List<T> tModels)
        {
            return new OperateResultInfo<DataTable>
            {
                AffectedRows = 0,
                Data = ModelsToDataTable<T>(tModels),
                Message = string.Empty,
                Status = OperateStatus.Success,
            };
        }
        /// <summary>
        /// 模型转换成Datatable
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="collection"></param>
        /// <returns></returns>
        public static DataTable ModelsToDataTable<T>(IEnumerable<T> collection)
        {
            var props = typeof(T).GetProperties();
            var dt = new DataTable();
            dt.Columns.AddRange(props.Select(p => new DataColumn(p.Name, p.PropertyType)).ToArray());
            if (collection.Count() > 0)
            {
                for (int i = 0; i < collection.Count(); i++)
                {
                    ArrayList tempList = new ArrayList();
                    foreach (PropertyInfo pi in props)
                    {
                        object obj = pi.GetValue(collection.ElementAt(i), null);
                        tempList.Add(obj);
                    }
                    object[] array = tempList.ToArray();
                    dt.LoadDataRow(array, true);
                }
            }
            return dt;
        }
        #endregion
    }
    #endregion
}