Tutorial on how to use Dapper to achieve paging effects

This article mainly introduces the paging effect based on Dapper in detail, supports filtering, sorting, total number of result sets, multi-table queries, and non-stored procedures. It has certain reference value. Interested friends can refer to it.

Introduction

I searched the blog in advance about the implementation of Dapper paging. There are some, but they are either based on stored procedures or support paging, but not Sorting is supported, or search criteria are not so easy to maintain.

Method definition

The following is my paging implementation. Although it is not generic (because of the where conditions and sql statement combinations), it should be considered It is more general. The method definition is 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 querying Log. In the returned results, the first value of Tuple is the result set, and the second value is the total number of rows (for example, There are 100 records in total, 10 per page, and the current first page, then the first value is 10 records, and the second value is 100)

In the sample project, I use two methods to achieve it Pagination:

1. The first one is based on 2 queries. The first query gets the total number, and the second query gets the result set.

2. The second one is based on 1 this query, using Offest/Fetch of SqlServer, so it only supports Sql Server 2012+, so you can choose different implementations according to the Sql Server version you use. Here is of course The second implementation is more efficient.

Run the example

1. After downloading or Clone the Github Repo locally, go to the Database directory and decompress Database.7z

2. Attach to Sql Server . By default, I use Sql Server LocalDB, and the connection string is Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=DapperPagingSample;integrated security=True; If you are not using LocalDB, please modify the connection string of App.Config as appropriate.

3. Ctrl+F5 runs the program. In the sample project, I used a simple WinForm program, but it should be able to better demonstrate the paging effect.

Multiple table support

Added examples to support multi-table query, for example, there are two Log tables, Level table, Log The LevelId field refers to the Level Id field. Through the following query, you can realize paging, sorting, and filtering of multi-table queries:

The first is an example of two queries (basically supports 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);
        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);
      }
    }

The second example is through Offset/Fetch query (supports 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);
        
      }
    }

The above is the detailed content of Tutorial on how to use Dapper to achieve paging effects. For more information, please follow other related articles on the PHP Chinese website!