這篇文章主要為大家詳細介紹了基於Dapper實現分頁效果,支援篩選,排序,結果集總數,多表查詢,非預存程序,具有一定的參考價值,感興趣的小夥伴們可以參考一下
簡介
之前事先搜尋了下部落格園上關於Dapper分頁的實現,有是有,但要麼是基於預存程序,要麼支援分頁,而不支援排序,或者搜尋條件不是那麼容易維護。
方法定義
以下是我的一個分頁的實現,雖然不是泛型(因為考慮到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); } }