C# dapper通用分页函数

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

1. 通用函数如下

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

/// 

/// dapper通用分页函数
/// 
/// 泛型集合实体类
/// 查询语句
/// 排序（字段 DESC/ASC）
/// 当前页
/// 当前页显示条数
/// 结果集总数
public IEnumerable GetPageList(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();
        }
    }
}

2. 使用方法

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