如何创建服务器端分页、搜索和排序存储过程

https://www.c-sharpcorner.com/article/how-to-create-server-side-pagination-searching-and-sorting-stored-procedure/

数据效率 数据检索 SQL分页 SQL搜索 SQL服务器端 SQL排序

一.介绍

高效处理大型数据集对于 Web 应用程序至关重要。服务器端分页、搜索和排序有助于减少发送到客户端的数据量并提高性能。在本文中，我们将演示如何在 SQL Server 中创建存储过程来实现此目的。

二.创建服务器端分页、搜索和排序存储过程

ALTER PROCEDURE [dbo].[UserDetails_GetAll]
    @rowCount INT,
    @pageNo INT,
    @sortColumn NVARCHAR(128) = 'UserID',
    @sortDirection VARCHAR(4) = 'DESC',
    @searchText VARCHAR(100) = NULL
AS
BEGIN
    DECLARE @TotalRowCount INT = 0;
    DECLARE @LSearchText VARCHAR(100);
    SET @LSearchText = ISNULL(@searchText, '');
    -- Calculate the total number of rows
    SELECT @TotalRowCount = COUNT(1)
    FROM [dbo].[UserDetails] US
    WHERE (
        @LSearchText = ''
        OR US.[FirstName] LIKE '%' + @LSearchText + '%'
        OR US.[LastName] LIKE '%' + @LSearchText + '%'
    )
    -- Return the total row count
    SELECT @TotalRowCount AS TotalRows
    -- Fetch the paginated, sorted, and filtered data
    SELECT
        CONVERT(VARCHAR(50), US.[UserID]) AS UserID,
        CONVERT(VARCHAR(50), US.[ClientID]) AS ClientID,
        A.UserName,
        A.Email,
        US.FirstName,
        US.LastName,
        US.ContactNumber,
        FS.FileAppPath AS ProfileImagePath
    FROM
        [dbo].[UserDetails] AS US
    LEFT JOIN
        AspNetUsers AS A ON A.Id = US.UserID
    LEFT JOIN
        [dbo].[FileStore] FS ON FS.FileUID = US.ProfileImageFileUID
    WHERE
        @LSearchText = ''
        OR US.[FirstName] LIKE '%' + @LSearchText + '%'
        OR US.[LastName] LIKE '%' + @LSearchText + '%'
    ORDER BY
        CASE WHEN @sortColumn = 'UserName' AND @sortDirection = 'ASC' THEN A.[UserName] END ASC,
        CASE WHEN @sortColumn = 'UserName' AND @sortDirection = 'DESC' THEN A.[UserName] END DESC,
        CASE WHEN @sortColumn = 'FirstName' AND @sortDirection = 'ASC' THEN US.[FirstName] END ASC,
        CASE WHEN @sortColumn = 'FirstName' AND @sortDirection = 'DESC' THEN US.[FirstName] END DESC,
        CASE WHEN @sortColumn = 'LastName' AND @sortDirection = 'ASC' THEN US.[LastName] END ASC,
        CASE WHEN @sortColumn = 'LastName' AND @sortDirection = 'DESC' THEN US.[LastName] END DESC,
        CASE WHEN @sortColumn = 'ContactNumber' AND @sortDirection = 'ASC' THEN US.[ContactNumber] END ASC,
        CASE WHEN @sortColumn = 'ContactNumber' AND @sortDirection = 'DESC' THEN US.[ContactNumber] END DESC,
        CASE WHEN @sortColumn = 'Email' AND @sortDirection = 'ASC' THEN A.[Email] END ASC,
        CASE WHEN @sortColumn = 'Email' AND @sortDirection = 'DESC' THEN A.[Email] END DESC
    OFFSET ((@pageNo - 1) * @rowCount) ROWS
    FETCH NEXT @rowCount ROWS ONLY
END

解释

1. 参数

• @rowCount：每页的行数。
• @pageNo：当前页码。
• @sortColumn：排序所依据的列名。
• @sortDirection：排序方向（ASC 或 DESC）。
• @searchText：在 FirstName 和 LastName 中搜索的文本。

2. 总行数：使用 COUNT(1) 计算符合搜索条件的总行数。
3. 数据检索

• 将 UserDetails 与 AspNetUsers 和 FileStore 结合起来以获取所需的数据。
• 使用 ORDER BY 根据参数进行条件排序。
• 使用 OFFSET 和 FETCH NEXT 进行分页。

三.结论

在本文中，我们创建了一个存储过程来处理服务器端的分页、搜索和排序。这种方法可确保仅从服务器获取所需的数据，从而提高性能并减少加载时间。通过遵循这些步骤，您可以有效地管理应用程序中的大型数据集。