search
HomeBackend DevelopmentC#.Net TutorialTutorial 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!

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
C# .NET Development: A Beginner's Guide to Getting StartedC# .NET Development: A Beginner's Guide to Getting StartedApr 18, 2025 am 12:17 AM

To start C#.NET development, you need to: 1. Understand the basic knowledge of C# and the core concepts of the .NET framework; 2. Master the basic concepts of variables, data types, control structures, functions and classes; 3. Learn advanced features of C#, such as LINQ and asynchronous programming; 4. Be familiar with debugging techniques and performance optimization methods for common errors. With these steps, you can gradually penetrate the world of C#.NET and write efficient applications.

C# and .NET: Understanding the Relationship Between the TwoC# and .NET: Understanding the Relationship Between the TwoApr 17, 2025 am 12:07 AM

The relationship between C# and .NET is inseparable, but they are not the same thing. C# is a programming language, while .NET is a development platform. C# is used to write code, compile into .NET's intermediate language (IL), and executed by the .NET runtime (CLR).

The Continued Relevance of C# .NET: A Look at Current UsageThe Continued Relevance of C# .NET: A Look at Current UsageApr 16, 2025 am 12:07 AM

C#.NET is still important because it provides powerful tools and libraries that support multiple application development. 1) C# combines .NET framework to make development efficient and convenient. 2) C#'s type safety and garbage collection mechanism enhance its advantages. 3) .NET provides a cross-platform running environment and rich APIs, improving development flexibility.

From Web to Desktop: The Versatility of C# .NETFrom Web to Desktop: The Versatility of C# .NETApr 15, 2025 am 12:07 AM

C#.NETisversatileforbothwebanddesktopdevelopment.1)Forweb,useASP.NETfordynamicapplications.2)Fordesktop,employWindowsFormsorWPFforrichinterfaces.3)UseXamarinforcross-platformdevelopment,enablingcodesharingacrossWindows,macOS,Linux,andmobiledevices.

C# .NET and the Future: Adapting to New TechnologiesC# .NET and the Future: Adapting to New TechnologiesApr 14, 2025 am 12:06 AM

C# and .NET adapt to the needs of emerging technologies through continuous updates and optimizations. 1) C# 9.0 and .NET5 introduce record type and performance optimization. 2) .NETCore enhances cloud native and containerized support. 3) ASP.NETCore integrates with modern web technologies. 4) ML.NET supports machine learning and artificial intelligence. 5) Asynchronous programming and best practices improve performance.

Is C# .NET Right for You? Evaluating its ApplicabilityIs C# .NET Right for You? Evaluating its ApplicabilityApr 13, 2025 am 12:03 AM

C#.NETissuitableforenterprise-levelapplicationswithintheMicrosoftecosystemduetoitsstrongtyping,richlibraries,androbustperformance.However,itmaynotbeidealforcross-platformdevelopmentorwhenrawspeediscritical,wherelanguageslikeRustorGomightbepreferable.

C# Code within .NET: Exploring the Programming ProcessC# Code within .NET: Exploring the Programming ProcessApr 12, 2025 am 12:02 AM

The programming process of C# in .NET includes the following steps: 1) writing C# code, 2) compiling into an intermediate language (IL), and 3) executing by the .NET runtime (CLR). The advantages of C# in .NET are its modern syntax, powerful type system and tight integration with the .NET framework, suitable for various development scenarios from desktop applications to web services.

C# .NET: Exploring Core Concepts and Programming FundamentalsC# .NET: Exploring Core Concepts and Programming FundamentalsApr 10, 2025 am 09:32 AM

C# is a modern, object-oriented programming language developed by Microsoft and as part of the .NET framework. 1.C# supports object-oriented programming (OOP), including encapsulation, inheritance and polymorphism. 2. Asynchronous programming in C# is implemented through async and await keywords to improve application responsiveness. 3. Use LINQ to process data collections concisely. 4. Common errors include null reference exceptions and index out-of-range exceptions. Debugging skills include using a debugger and exception handling. 5. Performance optimization includes using StringBuilder and avoiding unnecessary packing and unboxing.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
1 months agoBy尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. Best Graphic Settings
1 months agoBy尊渡假赌尊渡假赌尊渡假赌
Will R.E.P.O. Have Crossplay?
1 months agoBy尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

Powerful PHP integrated development environment

VSCode Windows 64-bit Download

VSCode Windows 64-bit Download

A free and powerful IDE editor launched by Microsoft

MantisBT

MantisBT

Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

SecLists

SecLists

SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.