一個基於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 {
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
14 const string selectQuery = @" SELECT *
15 FROM ( SELECT ROW_NUMBER() OVER ( /**orderby**/ ) AS RowNum, l.*, lv.Name as [Level]
16 FROM [Log] l
17 INNER JOIN [Level] lv ON l.LevelId = lv.Id
18 /**where**/
19 ) AS RowConstrainedResult
20 WHERE RowNum >= (@PageIndex * @PageSize + 1 )
21 AND RowNum <= (@PageIndex + 1) * @PageSize
22 ORDER BY RowNum";
23
24 SqlBuilder builder = new SqlBuilder();
25
26 var count = builder.AddTemplate(countQuery);
27 var selector = builder.AddTemplate(selectQuery, new { PageIndex = pageIndex, PageSize = pageSize });
28
29 if (!string.IsNullOrEmpty(criteria.Level))
30 builder.Where("lv.Name= @Level", new { Level = criteria.Level });
31
32 if (!string.IsNullOrEmpty(criteria.Message))
33 {
34 var msg = "%" + criteria.Message + "%";
35 builder.Where("l.Message Like @Message", new { Message = msg });
36 }
37
38 foreach (var a in asc)
39 {
40 if(!string.IsNullOrWhiteSpace(a))
41 builder.OrderBy(a);
42 }
43
44 foreach (var d in desc)
45 {
46 if (!string.IsNullOrWhiteSpace(d))
47 builder.OrderBy(d + " desc");
48 }
49
50 var totalCount = connection.Query<int>(count.RawSql, count.Parameters).Single();
51 var rows = connection.Query<Log>(selector.RawSql, selector.Parameters);
52
53 return 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 {
7 using (IDbConnection connection = base.OpenConnection())
8 {
9
10 const string selectQuery = @" ;WITH _data AS (
11 SELECT l.*, lv.Name AS [Level]
12 FROM [Log] l
13 INNER JOIN [Level] lv ON l.LevelId = lv.Id
14 /**where**/
15 ),
16 _count AS (
17 SELECT COUNT(1) AS TotalCount FROM _data
18 )
19 SELECT * FROM _data CROSS APPLY _count /**orderby**/ OFFSET @PageIndex * @PageSize ROWS FETCH NEXT @PageSize ROWS ONLY";
20
21 SqlBuilder builder = new SqlBuilder();
22
23 var selector = builder.AddTemplate(selectQuery, new { PageIndex = pageIndex, PageSize = pageSize });
24
25 if (!string.IsNullOrEmpty(criteria.Level))
26 builder.Where("lv.Name = @Level", new { Level = criteria.Level });
27
28 if (!string.IsNullOrEmpty(criteria.Message))
29 {
30 var msg = "%" + criteria.Message + "%";
31 builder.Where("l.Message Like @Message", new { Message = msg });
32 }
33
34 foreach (var a in asc)
35 {
36 if (!string.IsNullOrWhiteSpace(a))
37 builder.OrderBy(a);
38 }
39
40 foreach (var d in desc)
41 {
42 if (!string.IsNullOrWhiteSpace(d))
43 builder.OrderBy(d + " desc");
44 }
45
46 var rows = connection.Query<Log>(selector.RawSql, selector.Parameters).ToList();
47
48 if(rows.Count == 0)
49 return new Tuple<IEnumerable<Log>, int>(rows, 0);
50
51
52 return new Tuple<IEnumerable<Log>, int>(rows, rows[0].TotalCount);
53
54 }
55 }
謝謝
希望對大家有幫助:)
最後，我更新了本篇隨便，增加了內容，希望不要再被撤下了（上次撤下說是因為篇幅太短。。。），因為個人覺得這個對大家應該還是會有用的。
