如何使用Dapper.NET实现高效的分页查询？

dapper.net 是一个轻量级的 ORM，支持分页查询。可以使用 Query 方法结合 SQL 的 LIMIT 和 OFFSET 子句实现。

一、简介

在现代Web应用开发中，分页查询是一个常见的需求，通过分页，可以有效地减少一次性加载的数据量，提高系统性能和用户体验，Dapper是一款轻量级的ORM（对象关系映射）工具，以其简单易用和高性能而广受欢迎，本文将详细介绍如何使用Dapper实现分页查询，涵盖基本概念、实现方法、示例代码及常见问题解答。

二、什么是分页查询

分页查询是一种数据库操作技术，用于将大数据集分成多个小块（即“页”），然后按需检索这些小块数据，通常用于处理大量数据时，避免一次性加载过多数据导致的性能问题。

三、为什么使用分页查询

1、性能优化：减少内存占用和网络传输时间，提升系统响应速度。

2、用户体验：用户可以通过翻页查看数据，无需等待长时间加载。

3、资源管理：有效管理服务器资源，防止因大数据量导致的系统崩溃。

四、Dapper简介

Dapper是一个简单的DO（DataOriented）库，专为.NET设计，允许开发者以最接近手写SQL的方式与数据库交互，它支持多种数据库，并且易于集成和使用。

五、实现分页查询的方法

1. 基于两次查询的实现

这种方法首先获取总记录数，然后根据当前页码和页面大小计算偏移量，最后获取对应的数据记录。

public IEnumerable<T> GetPageList<T>(string sql, string orderBy, int pageIndex, int pageSize, out int total)
{
    int skip = (pageIndex 1) * pageSize + 1;
    using (var connection = OpenConnection())
    {
        // 获取总数
        total = connection.Query<int>($"SELECT COUNT(1) FROM ({sql}) AS Result").FirstOrDefault();
        
        // 获取分页数据
        return connection.Query<T>(
            $@"SELECT * 
               FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY {orderBy}) AS RowNum FROM ({sql}) AS Temp) AS Result
               WHERE RowNum >= @Skip AND RowNum <= @End", 
            new { Skip = skip, End = pageIndex * pageSize }, buffered: false);
    }
}

2. 基于Offset/Fetch的实现（仅适用于SQL Server 2012+）

这种方法使用SQL Server的OFFSET和FETCH子句，更加简洁高效。

public IEnumerable<T> GetPageList<T>(string sql, string orderBy, int pageIndex, int pageSize, out int total)
{
    using (var connection = OpenConnection())
    {
        // 获取总数
        total = connection.Query<int>($"SELECT COUNT(1) FROM ({sql}) AS Result").FirstOrDefault();
        
        // 获取分页数据
        return connection.Query<T>(
            $@"{sql} ORDER BY {orderBy} OFFSET @Skip ROWS FETCH NEXT @Size ROWS ONLY", 
            new { Skip = (pageIndex 1) * pageSize, Size = pageSize }, buffered: false);
    }
}

3. 多表查询示例

当涉及多表查询时，可以使用JOIN子句连接多个表，并结合上述分页方法进行分页查询。

public Tuple<IEnumerable<Log>, int> Find(LogSearchCriteria criteria, int pageIndex, int pageSize, string[] asc, string[] desc)
{
    using (var connection = OpenConnection())
    {
        const string countQuery = @"SELECT COUNT(1) FROM [Log] l INNER JOIN [Level] lv ON l.LevelId = lv.Id";
        const string selectQuery = @"SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY {orderby}) AS RowNum, l.*, lv.Name as [Level] FROM [Log] l INNER JOIN [Level] lv ON l.LevelId = lv.Id) AS RowConstrainedResult WHERE RowNum >= @Skip AND RowNum <= @End";
        
        // 根据排序条件动态生成SQL
        string orderBy = BuildOrderBy(asc, desc);
        
        // 执行查询
        int total = connection.Query<int>(countQuery).FirstOrDefault();
        var data = connection.Query<Log>(selectQuery, new { Skip = (pageIndex 1) * pageSize + 1, End = pageIndex * pageSize, orderby = orderBy }, buffered: false);
        
        return Tuple.Create(data, total);
    }
}

六、单元表格示例

方法	SQL Server版本	优点	缺点
基于两次查询	所有版本	通用性强，适用于各种数据库	需要执行两次SQL语句，性能稍低
基于Offset/Fetch	SQL Server 2012+	语法简洁，效率高	仅支持SQL Server 2012及以上版本