C# dapper通用分页函数

在执行数据列表查询操作时，会大量用到分页函数，为了避免重复，大部分的操作可以通过如下泛型函数实现。

1. 通用函数如下

public static string ConnectionString = ConfigurationManager.ConnectionStrings["MyStrConn"].ConnectionString;

/// <summary>
/// dapper通用分页函数
/// </summary>
/// <typeparam name="T">泛型集合实体类</typeparam>
/// <param name="sql">查询语句</param>
/// <param name="orderBy">排序（字段 DESC/ASC）</param>
/// <param name="pageIndex">当前页</param>
/// <param name="pageSize">当前页显示条数</param>
/// <param name="total">结果集总数</param>
public IEnumerable<T> GetPageList<T>(string sql, 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}) AS Result;", sql);
    sb.AppendFormat(@"SELECT *
                        FROM(SELECT *,ROW_NUMBER() OVER(ORDER BY {1}) AS RowNum
                             FROM  ({0}) AS Temp) AS Result
                        WHERE  RowNum >= {2} AND RowNum <= {3}
                        ORDER BY {1}", sql, orderBy, skip, pageIndex * pageSize);

    using (IDbConnection conn = new SqlConnection(ConnectionString))
    {
        using (var reader = conn.QueryMultiple(sb.ToString()))
        {
            total = reader.ReadFirst<int>();
            return reader.Read<T>();
        }
    }
}

 

2. 使用方法

var total = 0；
var re = GetPageList<User>("SELECT * FROM [TestDB].[dbo].[User]", "[CreatedOn] desc, [ID] asc", 1, 20, out total);