如何在C#循环/列表中处理SQL Server分页查询结果

当前做法的问题 #

你的T-SQL脚本通过循环返回多个独立的结果集（每页一个结果集），但默认的.NET数据访问逻辑只会读取第一个结果集，导致currentItemVersion和itemVersionHistory只拿到第一页数据。同时，数据库端循环分页的方式会增加数据库与应用端的交互次数，效率较低，不是最优方案。

解决方案 #

根据数据量大小，提供两种可行方案：

方案一：一次性获取所有数据（小数据量场景） #

如果数据总量不大，直接取消分页逻辑，一次性查询所有数据：

SELECT FruitName, Price 
FROM SampleFruits
ORDER BY Price

.NET端直接读取全量数据到列表，后续处理逻辑保持不变即可。

方案二：.NET端控制分页加载（大数据量场景） #

将分页逻辑移到应用端，循环调用单页查询接口，合并所有页数据：

1. 修改T-SQL为参数化单页查询

-- 接收.NET传入的分页参数
DECLARE @PageNumber INT = @InputPageNumber
DECLARE @RowsOfPage INT = @InputRowsPerPage

-- 返回当前页数据
SELECT FruitName, Price 
FROM SampleFruits
ORDER BY Price 
    OFFSET (@PageNumber-1) * @RowsOfPage ROWS
    FETCH NEXT @RowsOfPage ROWS ONLY

-- 返回总页数，用于应用端判断循环次数
SELECT CEILING(COUNT(*) / CAST(@RowsOfPage AS FLOAT)) AS TotalPages
FROM SampleFruits

2. .NET端实现分页循环加载

int rowsPerPage = 4;
int currentPage = 1;
int totalPages = 0;

List<Item> currentItemVersion = new List<Item>();
List<Item> itemVersionHistory = new List<Item>();

// 工具方法：封装分页查询逻辑
List<Item> GetPageData(int pageNum, int pageSize)
{
    var pageItems = new List<Item>();
    using (var conn = new SqlConnection("你的数据库连接字符串"))
    {
        conn.Open();
        var cmd = new SqlCommand("你的分页SQL语句", conn);
        cmd.Parameters.AddWithValue("@InputPageNumber", pageNum);
        cmd.Parameters.AddWithValue("@InputRowsPerPage", pageSize);
        
        using (var reader = cmd.ExecuteReader())
        {
            // 读取当前页数据
            while (reader.Read())
            {
                pageItems.Add(new Item
                {
                    FruitName = reader["FruitName"].ToString(),
                    Price = (decimal)reader["Price"],
                    // 映射其他字段
                });
            }
            // 读取总页数（仅第一页需要）
            if (pageNum == 1 && reader.NextResult() && reader.Read())
            {
                totalPages = (int)reader["TotalPages"];
            }
        }
    }
    return pageItems;
}

// 加载第一页并获取总页数
currentItemVersion.AddRange(GetPageData(1, rowsPerPage));

// 循环加载剩余页数
for (int page = 2; page <= totalPages; page++)
{
    currentItemVersion.AddRange(GetPageData(page, rowsPerPage));
}

// 用同样逻辑加载itemVersionHistory列表
// ...

// 原有处理逻辑保持不变
foreach (Item myItem in currentItemVersion)
{
    if (myItem.IsGood) 
    {
        var goodItems = itemVersionHistory.Where(x => x.Item_ID == myItem.Item_ID).ToList();                
        foreach (Item itemVersions in goodItems)
        {
            // 执行你的业务逻辑
        }
    }           
}

3. ORM简化方案（如Entity Framework）

如果使用ORM框架，可直接用Skip+Take实现分页：

int rowsPerPage = 4;
var dbContext = new YourDbContext();
int totalItems = dbContext.SampleFruits.Count();
int totalPages = (int)Math.Ceiling(totalItems / (double)rowsPerPage);

List<Item> allItems = new List<Item>();
for (int page = 0; page < totalPages; page++)
{
    var pageItems = dbContext.SampleFruits
                            .OrderBy(f => f.Price)
                            .Skip(page * rowsPerPage)
                            .Take(rowsPerPage)
                            .Select(f => new Item
                            {
                                FruitName = f.FruitName,
                                Price = f.Price
                            })
                            .ToList();
    allItems.AddRange(pageItems);
}

内容的提问来源于stack exchange，提问作者StackTrace