C中常用的分页存储过程有哪些？

在C#开发中，分页技术是处理大量数据时不可或缺的一部分，分页存储过程通过将大数据集分成更小、更易管理的块来提高性能和用户体验，下面将对C#中常用的分页存储过程进行详细归纳：

1、基本分页存储过程

简单分页：最基本的分页存储过程使用OFFSET和FETCH子句来获取特定页的数据，以下SQL语句用于从第2页开始获取5条记录：

     SELECT * FROM Employees
     ORDER BY EmployeeID
     OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;

参数化分页：为了提高灵活性和安全性，通常会使用参数化查询。

     CREATE PROCEDURE GetEmployeesByPage
         @PageNumber INT,
         @PageSize INT
     AS
     BEGIN
         DECLARE @Offset INT = (@PageNumber 1) * @PageSize;
         SELECT * FROM Employees
         ORDER BY EmployeeID
         OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY;
     END;

2、高级分页存储过程

带过滤条件的分页：在实际项目中，经常需要在分页的基础上添加过滤条件，按部门筛选员工并分页显示：

     CREATE PROCEDURE GetEmployeesByDepartmentAndPage
         @Department NVARCHAR(50),
         @PageNumber INT,
         @PageSize INT
     AS
     BEGIN
         DECLARE @Offset INT = (@PageNumber 1) * @PageSize;
         SELECT * FROM Employees
         WHERE Department = @Department
         ORDER BY EmployeeID
         OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY;
     END;

动态排序的分页：允许用户指定排序字段和排序方向。

     CREATE PROCEDURE GetEmployeesByPageWithDynamicSort
         @PageNumber INT,
         @PageSize INT,
         @SortColumn NVARCHAR(50),
         @SortDirection NVARCHAR(4) -'ASC' or 'DESC'
     AS
     BEGIN
         DECLARE @Offset INT = (@PageNumber 1) * @PageSize;
         IF @SortDirection = 'DESC'
            SET @SortDirection = '';
         ELSE
            SET @SortDirection = 'ASC';
         
         DECLARE @SQL NVARCHAR(MAX);
         SET @SQL = 'SELECT * FROM Employees
         ORDER BY ' + QUOTENAME(@SortColumn) + ' ' + @SortDirection + '
         OFFSET ' + CAST(@Offset AS NVARCHAR) + ' ROWS FETCH NEXT ' + CAST(@PageSize AS NVARCHAR) + ' ROWS ONLY';
         EXEC sp_executesql @SQL;
     END;

总记录数计算：为了显示分页信息（如总页数），通常需要先计算总记录数，可以在一个存储过程中同时返回数据和总记录数：

     CREATE PROCEDURE GetEmployeesByPageWithCount
         @PageNumber INT,
         @PageSize INT,
         @TotalRecords INT OUTPUT
     AS
     BEGIN
         DECLARE @Offset INT = (@PageNumber 1) * @PageSize;
         -Calculate total records
         SELECT @TotalRecords = COUNT(*) FROM Employees;
         -Select paged data
         SELECT * FROM Employees
         ORDER BY EmployeeID
         OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY;
     END;

3、优化分页性能

索引优化：确保对排序和过滤字段建立适当的索引，以提高查询性能。

避免SELECT：尽量选择需要的列而不是所有列，减少数据传输量。

缓存结果：对于频繁访问的分页数据，可以考虑使用缓存技术来减少数据库负载。

4、错误处理和事务管理：在存储过程中加入错误处理和事务管理，确保数据的一致性和完整性。

   CREATE PROCEDURE SafeGetEmployeesByPage
       @PageNumber INT,
       @PageSize INT,
       @TotalRecords INT OUTPUT
   AS
   BEGIN
       BEGIN TRY
           BEGIN TRANSACTION;
           DECLARE @Offset INT = (@PageNumber 1) * @PageSize;
           -Calculate total records
           SELECT @TotalRecords = COUNT(*) FROM Employees;
           -Select paged data
           SELECT * FROM Employees
           ORDER BY EmployeeID
           OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY;
           COMMIT TRANSACTION;
       END TRY
       BEGIN CATCH
           ROLLBACK TRANSACTION;
           THROW;
       END CATCH;
   END;

分页存储过程在C#开发中扮演着重要角色，通过合理设计和优化，可以显著提高数据处理效率和用户体验。