Implement paging effect based on dapper, support filtering, sorting, total number of result sets, etc

Time:2021-5-31

brief introduction

Previously, I searched the blog Garden for the implementation of dapper paging. There are some, but they are either based on stored procedures, or support paging instead of sorting, or the search criteria are not so easy to maintain.

code

Code first:https://github.com/jinweijie/Dapper.PagingSample

Method definition

The following is my implementation of paging. Although it is not generic (considering where conditions and the collocation of SQL statements), it should be 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 result, 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 records per page. In the current first page, the first value is 10 records, and the second value is 100)

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

1. The first one is based on 2. The total number is obtained for the first time, and the result set is obtained for the second time.

2. The second one is based on 1. It uses the offer / fetch of SQL server, so it only supports SQL Server 2012 +, so you can choose different implementations according to your version of SQL server. Of course, the second one is more efficient.

Running examples

1. After downloading GitHub’s repo or clone locally, go to the database directory and unzip 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 to run the program. In the example project, I used a simple WinForm program, but it should be able to demonstrate the paging effect better.

Multi table support

An example is added to support multi table query. For example, there are two log tables, level table. The levelid field of log refers to the ID field of level. Paging, sorting and filtering of multi table query can be realized through the following queries:

The first is the example of two queries (basically supporting 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 (support 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 whole content of this article, I hope to help you learn, and I hope you can support developer more.