C#-Dapper分页帮助类

繁花锦簇

于 2024-08-04 02:40:53 发布

阅读量34

点赞数

文章标签： c# java 数据库开发语言

using Dapper;
using System.Collections.Generic;
using System.Data;
using System.Text;

namespace DBDapper
{
    /// <summary>
    /// 分页帮助类
    /// </summary>
    public class PageListHelper
    {
        /// <summary>
        /// dapper通用分页方法且排序不正确
        /// </summary>
        /// <typeparam name="T">泛型集合实体类</typeparam>
        /// <param name="conn">数据库连接池连接对象</param>
        /// <param name="files">列名</param>
        /// <param name="tableName">表名</param>
        /// <param name="where">条件</param>
        /// <param name="orderby">排序</param>
        /// <param name="pageIndex">当前页</param>
        /// <param name="pageSize">当前页显示条数</param>
        /// <param name="total">结果集总数</param>
        /// <returns></returns>
        public static IEnumerable<T> GetPageList<T>(IDbConnection conn, string files, string tableName, string where, string orderby, int pageIndex, int pageSize,out int total)
        {
            int skip = 1;
            if (pageIndex > 0)
            {
                skip = (pageIndex - 1) * pageSize + 1;
            }
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("SELECT COUNT(1) FROM {0} where {1};", tableName, where);
            sb.AppendFormat(@"SELECT  {0}
                                FROM(SELECT ROW_NUMBER() OVER(ORDER BY {3}) AS RowNum,{0}
                                          FROM  {1}
                                          WHERE {2}
                                        ) AS result
                                WHERE  RowNum >= {4}   AND RowNum <= {5}
                                ORDER BY {3}", files, tableName, where, orderby, skip, pageIndex * pageSize);
            using (var reader = conn.QueryMultiple(sb.ToString()))
            //using (var reader = conn.QueryFirstOrDefault(sb.ToString()))
            {
                total = reader.ReadFirst<int>();
                return reader.Read<T>();
            }
        }

        /// <summary>
        /// dapper通用分页方法-简单多表查询
        /// </summary>
        /// <typeparam name="T">泛型集合实体类</typeparam>
        /// <param name="conn">数据库连接池连接对象</param>
        /// 
        /// <param name="files">内层列名</param>
        /// <param name="files2">外层列名</param>
        /// 
        /// <param name="tableName">表名</param>
        /// <param name="where">条件</param>
        /// <param name="orderby">排序</param>
        /// <param name="pageIndex">当前页</param>
        /// <param name="pageSize">当前页显示条数</param>
        /// <param name="total">结果集总数</param>
        /// <returns></returns>
        public static IEnumerable<T> GetPageList2<T>(IDbConnection conn, string files, string files2, string tableName, string where, string orderby, int pageIndex, int pageSize, out int total)
        {
            int RowNumMin = 1,RowNumMax = 1, rcount=0;

            // 提前取总个数
            StringBuilder sb1 = new StringBuilder();
            sb1.AppendFormat("SELECT COUNT(1) FROM {0} where {1}", tableName, where);
            using (var reader1 = conn.QueryMultiple(sb1.ToString()))
            {
                rcount = reader1.ReadFirst<int>();
            }
            // 计算本次要取的条
            if (pageIndex > 0)
            {
                RowNumMax =rcount- pageSize*(pageIndex - 1);

                int RowNumMin1 = rcount - pageSize * pageIndex + 1;
                RowNumMin = RowNumMin1 >= 1? RowNumMin1:1;
            }

            // 取数据
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("SELECT COUNT(1) FROM {0} where {1};", tableName, where);
            sb.AppendFormat(@"SELECT  {6}
                                FROM(SELECT ROW_NUMBER() OVER(ORDER BY {3}) AS RowNum,{0}
                                          FROM  {1}
                                          WHERE {2}
                                        ) AS result
                                WHERE  RowNum >= {4}   AND RowNum <= {5}
                                ORDER BY {3}", files, tableName, where, orderby, RowNumMin, RowNumMax, files2);
            using (var reader = conn.QueryMultiple(sb.ToString()))
            //using (var reader = conn.QueryFirstOrDefault(sb.ToString()))
            {
                total = reader.ReadFirst<int>();
                return reader.Read<T>();
            }
        }
    }
}