查看: 2228|回复: 0

[.NET开发] 基于Dapper实现分页效果 支持筛选、排序、结果集总数等

发表于 2018-1-2 08:00:00

简介

之前事先搜索了下博客园上关于Dapper分页的实现,有是有,但要么是基于存储过程,要么支持分页,而不支持排序,或者搜索条件不是那么容易维护。

代码

首先先上代码: https://github.com/jinweijie/Dapper.PagingSample

方法定义

以下是我的一个分页的实现,虽然不是泛型(因为考虑到where条件以及sql语句的搭配),但是应该可以算是比较通用的了,方法定义如下:

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

第二个示例是通过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. {
  7. using (IDbConnection connection = base.OpenConnection())
  8. {
  9. const string selectQuery = @" ;WITH _data AS (
  10. SELECT l.*, lv.Name AS [Level]
  11. FROM [Log] l
  12. INNER JOIN [Level] lv ON l.LevelId = lv.Id
  13. /**where**/
  14. ),
  15. _count AS (
  16. SELECT COUNT(1) AS TotalCount FROM _data
  17. )
  18. SELECT * FROM _data CROSS APPLY _count /**orderby**/ OFFSET @PageIndex * @PageSize ROWS FETCH NEXT @PageSize ROWS ONLY";
  19. SqlBuilder builder = new SqlBuilder();
  20. var selector = builder.AddTemplate(selectQuery, new { PageIndex = pageIndex, PageSize = pageSize });
  21. if (!string.IsNullOrEmpty(criteria.Level))
  22. builder.Where("lv.Name = @Level", new { Level = criteria.Level });
  23. if (!string.IsNullOrEmpty(criteria.Message))
  24. {
  25. var msg = "%" + criteria.Message + "%";
  26. builder.Where("l.Message Like @Message", new { Message = msg });
  27. }
  28. foreach (var a in asc)
  29. {
  30. if (!string.IsNullOrWhiteSpace(a))
  31. builder.OrderBy(a);
  32. }
  33. foreach (var d in desc)
  34. {
  35. if (!string.IsNullOrWhiteSpace(d))
  36. builder.OrderBy(d + " desc");
  37. }
  38. var rows = connection.Query<Log>(selector.RawSql, selector.Parameters).ToList();
  39. if(rows.Count == 0)
  40. return new Tuple<IEnumerable<Log>, int>(rows, 0);
  41. return new Tuple<IEnumerable<Log>, int>(rows, rows[0].TotalCount);
  42. }
  43. }
复制代码

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持程序员之家。



回复

使用道具 举报