Dapper-based paging implementation, support for filtering, sorting, total number of result sets, non-stored procedures

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.

Code

First on the code: https://github.com/jinweijie/Dapper.PagingSample

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 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.

Thank you

Hope to be helpful to everyone:)

Dapper-based paging implementation, support for filtering, sorting, total number of result sets, non-stored procedures