tutorial on how to use dapper to achieve pagination effect

This article is mainly for you to introduce the implementation of pagination based on dapper, support filtering, sorting, the total number of result sets, multi-table query, non-stored procedures, with a certain reference value, interested in small partners can refer to

Brief introduction

The previous search for the next blog on the dapper paging implementation, there are, but either based on stored procedures, or support paging, not support sorting, or search conditions are not so easy to maintain.

Method definition

The following is an implementation of my paging, though not generic (because of the Where condition and the collocation of the SQL statement), but it should be considered more generic, and the method is defined as follows:

public tuple<ienumerable<log>, int> Find (logsearchcriteria criteria      , int pageIndex      , int pageSize      , string[] ASC      , string[] desc);

The above function definition is an example of a query log, the return result, the first value of a tuple is the result set, the second value is the total number of rows (for example, there are 100 records, each page 10, the current first page, then the first value is 10 records, the second value is 100)

In the example project, I implemented the paging in two ways:

1. The first is based on 2 of this query, the first time to get the total, the second query to get the result set.

2. The second is based on 1 of this query, using SQL Server's Offest/fetch, so only support for 2012+, so people based on the version of SQL Server you use to choose different implementations, here is of course the second implementation of the higher efficiency.

Run the sample

1. Download or clone the GitHub repo to local, and then unzip the database directory to extract the database.7z

2. Attach to SQL Server. By default I use SQL Server LocalDB, the connection string is Data source= (LocalDB) \mssqllocaldb;initial catalog=dapperpagingsample;integrated Security=true; If you are not using LOCALDB, modify the connection string for app. Config as appropriate.

3. Ctrl+f5 run the program, in the example project, I used a simple WinForm program, but it should be better to demonstrate the paging effect.

Multi-table Support

Added examples, support multi-table query, such as two log table, level table, log Levelid field reference level ID field, through the following query, you can implement multi-table query paging, sorting, filtering:

The first is an example of two queries (basic support for all versions of 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); Varselector = 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.        (a); } foreach (var d in Desc) {if (!string. Isnullorwhitespace (d)) builder.        (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); }    }

The second example is a query through Offset/fetch (SQL Server 2012+ support)

public tuple<ienumerable<log>, int> Findwithoffsetfetch (Logsearchcriteria criteria, in T 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 C Ount (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.        (a); } foreach (var d in Desc) {if (!string. Isnullorwhitespace (d)) builder.        (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); }    }