基于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):
public tuple<ienumerable<log>, int> find(logsearchcriteria criteria , int pageindex , int pagesize , string[] asc , string[] desc) { using (idbconnection connection = base.openconnection()) { const string countquery = @"select count(1) from [log] l inner join [level] lv on l.levelid = lv.id /**where**/"; const string selectquery = @" select * from ( select row_number() over ( /**orderby**/ ) as rownum, l.*, lv.name as [level] from [log] l inner join [level] lv on l.levelid = lv.id /**where**/ ) as rowconstrainedresult where rownum >= (@pageindex * @pagesize + 1 ) and rownum <= (@pageindex + 1) * @pagesize order by rownum"; sqlbuilder builder = new sqlbuilder(); var count = builder.addtemplate(countquery); var selector = builder.addtemplate(selectquery, new { pageindex = pageindex, pagesize = pagesize }); if (!string.isnullorempty(criteria.level)) builder.where("lv.name= @level", new { level = criteria.level }); if (!string.isnullorempty(criteria.message)) { var msg = "%" + criteria.message + "%"; builder.where("l.message like @message", new { message = msg }); } foreach (var a in asc) { if(!string.isnullorwhitespace(a)) builder.orderby(a); } foreach (var d in desc) { if (!string.isnullorwhitespace(d)) builder.orderby(d + " desc"); } var totalcount = connection.query<int>(count.rawsql, count.parameters).single(); var rows = connection.query<log>(selector.rawsql, selector.parameters); return new tuple<ienumerable<log>, int>(rows, totalcount); } }
第二个示例是通过offset/fetch查询(支持sql server 2012+)
public tuple<ienumerable<log>, int> findwithoffsetfetch(logsearchcriteria criteria , int pageindex , int pagesize , string[] asc , string[] desc) { using (idbconnection connection = base.openconnection()) { const string selectquery = @" ;with _data as ( select l.*, lv.name as [level] from [log] l inner join [level] lv on l.levelid = lv.id /**where**/ ), _count as ( select count(1) as totalcount from _data ) select * from _data cross apply _count /**orderby**/ offset @pageindex * @pagesize rows fetch next @pagesize rows only"; sqlbuilder builder = new sqlbuilder(); var selector = builder.addtemplate(selectquery, new { pageindex = pageindex, pagesize = pagesize }); if (!string.isnullorempty(criteria.level)) builder.where("lv.name = @level", new { level = criteria.level }); if (!string.isnullorempty(criteria.message)) { var msg = "%" + criteria.message + "%"; builder.where("l.message like @message", new { message = msg }); } foreach (var a in asc) { if (!string.isnullorwhitespace(a)) builder.orderby(a); } foreach (var d in desc) { if (!string.isnullorwhitespace(d)) builder.orderby(d + " desc"); } var rows = connection.query<log>(selector.rawsql, selector.parameters).tolist(); if(rows.count == 0) return new tuple<ienumerable<log>, int>(rows, 0); return new tuple<ienumerable<log>, int>(rows, rows[0].totalcount); } }
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持。