分页查询优化

新]根据查询实体获取查询结果[SQL_CALC_FOUND_ROWS;SELECT FOUND_ROWS()一次性取出分页数据和满足条件的所有数据行]

Select SQL_CALC_FOUND_ROWS 

SELECT FOUND_ROWS() --获取总行数

/// <summary>
        /// [新]根据查询实体获取查询结果
        /// </summary>
        /// <typeparam name="SearchT">查询实体类型</typeparam>
        /// <typeparam name="ResultT">响应结果类型</typeparam>
        /// <param name="searchEntity">查询实体对象</param>
        /// <param name="resultEntity">响应结果对象</param>
        /// <param name="pageIndex">页索引</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="rowsCount">返回行总数</param>
        /// <param name="tableName">表名</param>
        /// <param name="orderBy">排序列名[多个请用逗号分开,注意最后一个不要指定排序方式]</param>
        /// <param name="orderByEnum">排序方式</param>
        /// <param name="excludeExpressions">过滤属性集合</param>
        /// <returns></returns>
        public static List<ResultT> GetListByPageSearchEntityNew<SearchT, ResultT>(SearchT searchEntity, ResultT resultEntity,
            int pageIndex, int pageSize, out int rowsCount, string tableName, string orderBy = "", OrderByEnum orderByEnum = OrderByEnum.Desc,
            params Expression<Func<ResultT, object>>[] excludeExpressions)
            where ResultT : EntityBase, new()
        {
            //返回的结果集
            List<ResultT> resultList = new List<ResultT>();
            rowsCount = 0;
            var sqlSelect = string.Empty;
            Dictionary<string, PropertyInfo> propertieList = null;
            var sqlParameters = GetListSqlByPageSearchEntity(searchEntity, resultEntity, out sqlSelect, ref propertieList,
                pageIndex, pageSize, tableName, orderBy, orderByEnum, excludeExpressions);
            if (!string.IsNullOrEmpty(sqlSelect) && propertieList != null && propertieList.Count > 0)
            {
                resultList = GetExecuteResultTeskNew<ResultT>(sqlSelect, out rowsCount, sqlParameters, propertieList);
                sqlParameters.Clear();
            }
            return resultList;
        }

        /// <summary>
        /// [新]根据查询实体获取查询结果
        /// </summary>
        /// <typeparam name="SearchT">查询实体类型</typeparam>
        /// <typeparam name="ResultT">响应结果类型</typeparam>
        /// <param name="searchEntity">查询实体对象</param>
        /// <param name="resultEntity">响应结果对象</param>
        /// <param name="pageIndex">页索引</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="rowsCount">返回行总数</param>
        /// <param name="dataBaseName">库名</param>
        /// <param name="tableName">表名</param>
        /// <param name="orderBy">排序列名[多个请用逗号分开,注意最后一个不要指定排序方式]</param>
        /// <param name="orderByEnum">排序方式</param>
        /// <param name="excludeExpressions">过滤属性集合</param>
        /// <returns></returns>
        public static List<ResultT> GetListByPageSearchEntityNew<SearchT, ResultT>(SearchT searchEntity, ResultT resultEntity,
            int pageIndex, int pageSize, out int rowsCount, string dataBaseName, string tableName, string orderBy = "", OrderByEnum orderByEnum = OrderByEnum.Desc,
            params Expression<Func<ResultT, object>>[] excludeExpressions)
            where ResultT : EntityBase, new()
        {
            //返回的结果集
            List<ResultT> resultList = new List<ResultT>();
            rowsCount = 0;
            var sqlSelect = string.Empty;
            Dictionary<string, PropertyInfo> propertieList = null;
            var sqlParameters = GetListSqlByPageSearchEntity(searchEntity, resultEntity, out sqlSelect, ref propertieList,
                pageIndex, pageSize, tableName, orderBy, orderByEnum, excludeExpressions);
            if (!string.IsNullOrEmpty(sqlSelect) && propertieList != null && propertieList.Count > 0)
            {
                resultList = GetExecuteResultTeskNew<ResultT>(sqlSelect, out rowsCount, sqlParameters, propertieList, dataBaseName);
                sqlParameters.Clear();
            }
            return resultList;
        }

        /// <summary>
        /// [新]根据查询实体获取查询结果[SQL_CALC_FOUND_ROWS;SELECT FOUND_ROWS()一次性取出分页数据和满足条件的所有数据行]
        /// </summary>
        /// <typeparam name="SearchT">查询实体类型</typeparam>
        /// <typeparam name="ResultT">响应结果类型</typeparam>
        /// <param name="searchEntity">查询实体对象</param>
        /// <param name="resultEntity">响应结果对象</param>
        /// <param name="sqlSelect">输出查询SQL</param>
        /// <param name="propertieList">实体属性集合</param>
        /// <param name="pageIndex">页索引</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="rowsCount">返回行总数</param>
        /// <param name="tableName">表名</param>
        /// <param name="orderBy">排序列名[多个请用逗号分开,注意最后一个不要指定排序方式]</param>
        /// <param name="orderByEnum">排序方式</param>
        /// <param name="excludeExpressions">过滤属性集合</param>
        /// <returns></returns>
        public static List<MySqlParameter> GetListSqlByPageSearchEntity<SearchT, ResultT>(SearchT searchEntity, ResultT resultEntity,
            out string sqlSelect, ref Dictionary<string, PropertyInfo> propertieList, int pageIndex, int pageSize,
            string tableName, string orderBy = "", OrderByEnum orderByEnum = OrderByEnum.Desc,
            params Expression<Func<ResultT, object>>[] excludeExpressions)
            where ResultT : EntityBase, new()
        {
            var sqlParameters = new List<MySqlParameter>();
            sqlSelect = string.Empty;
            //sqlCount = string.Empty;
            if (searchEntity != null && resultEntity != null)
            {
                var typeSearch = searchEntity.GetType();
                var typeResult = resultEntity.GetType();
                tableName = GetTableName(typeResult, tableName);

                if (!string.IsNullOrEmpty(tableName))
                {
                    StringBuilder SelectSql = new StringBuilder();
                    SelectSql.Append("Select SQL_CALC_FOUND_ROWS ");
                    //StringBuilder CountSql = new StringBuilder();
                    //CountSql.AppendFormat("Select count(0) From {0}", tableName);
                    StringBuilder WhereSql = new StringBuilder(" Where");
                    WhereSql.Append(" RowStatus = 0");

                    //返回属性
                    propertieList = GetProperties(typeResult);

                    //更新条件属性集合和参数集合
                    Dictionary<string, object> excludeColumnList = new Dictionary<string, object>();
                    //过滤不返回属性
                    if (excludeExpressions != null && excludeExpressions.Count() > 0)
                    {
                        var excludeColumns = (from c in excludeExpressions select c).ToArray();

                        //是否收整理过滤属性
                        if (propertieList != null && propertieList.Count() > 0)
                        {
                            foreach (var item in excludeColumns)
                            {
                                var propertyName = GetExpressionsPropertyName<ResultT>(item);
                                if (!string.IsNullOrEmpty(propertyName))
                                {
                                    //获取条件列名
                                    excludeColumnList.Add(propertyName, propertyName);
                                }
                            }
                        }
                    }

                    var searchPropertieList = GetProperties(typeSearch);
                    //整理查询条件属性和参数
                    foreach (var item in searchPropertieList.Values)
                    {
                        //验证值是否有效
                        if (WhereValueValidate<SearchT>(searchEntity, item))
                        {
                            GetQueryWhere<SearchT>(searchEntity, sqlParameters, WhereSql, item);
                        }
                    }
                    //CountSql.Append(WhereSql);
                    foreach (var item in propertieList.Values)
                    {
                        var isKey = false;
                        //是否添加查询属性
                        if (ValidateIsAddPropertie(typeResult, item, OperationType.Select, excludeColumnList, out isKey))
                        {
                            SelectSql.AppendFormat("{0},", item.Name);
                        }
                        if (isKey && string.IsNullOrEmpty(orderBy))
                        {
                            //默认主键降序排列
                            orderBy = item.Name;
                        }
                    }
                    if (SelectSql.Length > 7)
                    {
                        SelectSql.Remove(SelectSql.Length - 1, 1);
                    }
                    SelectSql.AppendFormat(" FROM {0} ", tableName);
                    SelectSql.Append(WhereSql);
                    if (!string.IsNullOrEmpty(orderBy))
                    {
                        SelectSql.AppendFormat(" ORDER BY {0} {1}", orderBy, orderByEnum);
                    }
                    SelectSql.AppendFormat(" LIMIT {0},{1}; SELECT FOUND_ROWS()", (pageIndex - 1) * pageSize, pageSize);
                    sqlSelect = SelectSql.ToString();
                    //sqlCount = CountSql.ToString();
                }
            }
            return sqlParameters;
        }