基于Dapper的分页实现，支持筛选，排序，结果集总数，非存储过程 _结果集

仰天大笑出门去，我辈岂是蓬蒿人。这篇文章主要讲述基于Dapper的分页实现，支持筛选，排序，结果集总数，非存储过程相关的知识，希望能为你提供帮助。
简介
之前事先搜索了下博客园上关于Dapper分页的实现，有是有，但要么是基于存储过程，要么支持分页，而不支持排序，或者搜索条件不是那么容易维护。
代码
首先先上代码： https://github.com/jinweijie/Dapper.PagingSample
方法定义
以下是我的一个分页的实现，虽然不是泛型（因为考虑到where条件以及sql语句的搭配），但是应该可以算是比较通用的了，方法定义如下：

public Tuple< IEnumerable< Log> , int> Find(LogSearchCriteria criteria , int pageIndex , int pageSize , string[] asc , string[] desc);

以上函数定义是一个查询Log的示例，返回结果中，Tuple的第一个值是结果集，第二个值是总行数（例如，总共有100条记录，每页10条，当前第一页，那么第一个值是10条记录，第二个值是100）
在示例项目中，我用两种方法实现了分页：
1. 第一种是基于2此查询，第一次得到总数，第二次查询得到结果集。
2. 第二种是基于1此查询，用了SqlServer 的Offest/Fetch，所以只支持Sql Server 2012+，所以大家根据自己用的Sql Server版本选择不同的实现，这里当然是第二种实现效率更高一点。
运行示例
1. 将Github的Repo下载或者Clone到本地以后，到Database目录下，解压缩Database.7z
2. Attach到Sql Server上。默认我使用Sql Server LocalDB，连接字符串是 Data Source=(localdb)\\MSSQLLocalDB; Initial Catalog=DapperPagingSample; integrated security=True; 如果你用的不是LocalDB，请酌情修改App.Config的连接字符串。
3. Ctrl+F5运行程序，示例项目里，我用了一个简单的WinForm程序，但应该可以比较好的演示分页效果。

文章图片

多表支持
增加了示例，支持多表查询，例如有两个Log表，Level表，Log的LevelId字段引用Level的Id字段，通过以下的查询，可以实现多表查询的分页，排序，过滤：
首先是通过两次查询的示例（基本支持所有版本Sql Server）：

1 public Tuple< IEnumerable< Log> , int> Find(LogSearchCriteria criteria 2, int pageIndex 3, int pageSize 4, string[] asc 5, string[] desc) 6{ 7using (IDbConnection connection = base.OpenConnection()) 8{ 9const string countQuery = @"SELECT COUNT(1) 10FROM[Log] l 11INNER JOIN [Level] lv ON l.LevelId = lv.Id 12/**where**/"; 13 14const string selectQuery = @"SELECT* 15FROM( SELECTROW_NUMBER() OVER ( /**orderby**/ ) AS RowNum, l.*, lv.Name as [Level] 16FROM[Log] l 17INNER JOIN [Level] lv ON l.LevelId = lv.Id 18/**where**/ 19) AS RowConstrainedResult 20WHERERowNum > = (@PageIndex * @PageSize + 1 ) 21AND RowNum < = (@PageIndex + 1) * @PageSize 22ORDER BY RowNum"; 23 24SqlBuilder builder = new SqlBuilder(); 25 26var count = builder.AddTemplate(countQuery); 27var selector = builder.AddTemplate(selectQuery, new { PageIndex = pageIndex, PageSize = pageSize }); 28 29if (!string.IsNullOrEmpty(criteria.Level)) 30builder.Where("lv.Name= @Level", new { Level = criteria.Level }); 31 32if (!string.IsNullOrEmpty(criteria.Message)) 33{ 34var msg = "%" + criteria.Message + "%"; 35builder.Where("l.Message Like @Message", new { Message = msg }); 36} 37 38foreach (var a in asc) 39{ 40if(!string.IsNullOrWhiteSpace(a)) 41builder.OrderBy(a); 42} 43 44foreach (var d in desc) 45{ 46if (!string.IsNullOrWhiteSpace(d)) 47builder.OrderBy(d + " desc"); 48} 49 50var totalCount = connection.Query< int> (count.RawSql, count.Parameters).Single(); 51var rows = connection.Query< Log> (selector.RawSql, selector.Parameters); 52 53return new Tuple< IEnumerable< Log> , int> (rows, totalCount); 54} 55}

第二个示例是通过Offset/Fetch查询（支持Sql Server 2012+）

1 public Tuple< IEnumerable< Log> , int> FindWithOffsetFetch(LogSearchCriteria criteria 2, int pageIndex 3, int pageSize 4, string[] asc 5, string[] desc) 6{ 7using (IDbConnection connection = base.OpenConnection()) 8{ 9 10const string selectQuery = @" ; WITH _data AS ( 11SELECT l.*, lv.Name AS [Level] 12FROM[Log] l 13INNER JOIN [Level] lv ON l.LevelId = lv.Id 14/**where**/ 15), 16_count AS ( 17SELECT COUNT(1) AS TotalCount FROM _data 18) 19SELECT * FROM _data CROSS APPLY _count /**orderby**/ OFFSET @PageIndex * @PageSize ROWS FETCH NEXT @PageSize ROWS ONLY"; 20 21SqlBuilder builder = new SqlBuilder(); 22 23var selector = builder.AddTemplate(selectQuery, new { PageIndex = pageIndex, PageSize = pageSize }); 24 25if (!string.IsNullOrEmpty(criteria.Level)) 26builder.Where("lv.Name = @Level", new { Level = criteria.Level }); 27 28if (!string.IsNullOrEmpty(criteria.Message)) 29{ 30var msg = "%" + criteria.Message + "%"; 31builder.Where("l.Message Like @Message", new { Message = msg }); 32} 33 34foreach (var a in asc) 35{ 36if (!string.IsNullOrWhiteSpace(a)) 37builder.OrderBy(a); 38} 39 40foreach (var d in desc) 41{ 42if (!string.IsNullOrWhiteSpace(d)) 43builder.OrderBy(d + " desc"); 44} 45 46var rows = connection.Query< Log> (selector.RawSql, selector.Parameters).ToList(); 47 48if(rows.Count == 0) 49return new Tuple< IEnumerable< Log> , int> (rows, 0); 50 51 52return new Tuple< IEnumerable< Log> , int> (rows, rows[0].TotalCount); 53 54} 55}

谢谢
希望对大家有帮助:)
【基于Dapper的分页实现，支持筛选，排序，结果集总数，非存储过程】最后，我更新了本篇随便，增加了内容，希望不要再被撤下了（上次撤下说是因为篇幅太短。。。），因为个人觉得这个对大家应该还是会有用的。