数据库中的数据分页（ASP.NET 方法）

• 下载源代码 + SQL 脚本 - 10 Kb

引言

当您需要显示大量记录时，通用的做法是使用数据分页，这样信息将以更友好的方式呈现。有许多解决方案可用于实现这样的系统，它们各有优缺点。在本文中，我将介绍一种数据分页解决方案，并解释它是如何工作的，以及使用这种技术您能获得什么和失去什么。

此解决方案与其他解决方案不同之处在于，分页是在数据库（数据层）而不是中间层进行的。因此，当您在页面之间导航时，只有当前页的记录会从数据库中检索。当您在中间层进行数据分页时，需要从数据库检索所有记录，然后只显示其中一部分，当记录数量巨大时，这听起来并不太理想。

工作原理

您需要在数据库中有一个“容器”表，该表将保存所有将要在页面中显示的记录的“键”。此外，该表必须包含一个用于标识用户的键，在本例中我使用了 ASP.NET 会话 ID。
您需要两个存储过程：一个用于初始化页面，仅在用户首次进入我们示例中的 .aspx 页面时调用；第二个存储过程用于检索要显示的记录。当用户在页面之间导航时，将仅使用第二个存储过程。

数据库

在数据库中，我有两个表 `Members` 和 `TempPages`。`Members` 表包含要显示的信息，而 `TempPages` 是我将用于保存访问网站的每个用户的页面的容器表。

我示例中的存储过程是 `InitPages`（初始化存储过程）和 `GetPage`（用于检索记录的 SP）。

`Members` 表

我在脚本中展示了我的示例表。该表将保存要分页显示的信息。

CREATE TABLE [Members] (
    [UserID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
    [UserName] [varchar] (25) ,
    [Password] [varchar] (25) ,
    [FullName] [varchar] (25) ,
    [Email] [varchar] (25) 
) ON [PRIMARY]
GO

`TempPages` 表

`TempPages` 表有一个复合键，由 `SessionID`（用于标识用户会话）和 `RecordNo`（用于编号记录，从 1 到 RecordCount）组成。`User_ID` 列将保存 `Members.UserID`。

CREATE TABLE [TempPages] (
    [SessionID] [varchar] (24)  NOT NULL ,
    [RecordNo] [int] NOT NULL ,
    [User_ID] [int] NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [TempPages] WITH NOCHECK ADD 
    CONSTRAINT [PK_TempPages] PRIMARY KEY  CLUSTERED 
    (
        [SessionID],
        [RecordNo]
    )  ON [PRIMARY] 
GO

`InitPages` 存储过程

为了初始化要显示的记录，`InitPages` 只会在开始时运行一次。该存储过程将从 `Members` 表中选择 UserIDs 并将它们插入 `TempPages`。在我的示例中，此存储过程只有一个参数，即 `@SessionID`，会话标识符。您可以添加更多参数来过滤要分页的记录，在这种情况下，我选择 `Members` 中的所有记录。

`InitPages` 的工作原理如下：

首先，它将从 `TempPages` 中删除所有 `SessionID` 等于当前 `@SessionID` 的记录。如果进行新的初始化，我们会删除先前初始化的记录。

清空表后，我选择所有的 `Members.UserID` 并创建一个临时表 `#TempPages`，该表将保存所有的 `Members.UserID` 以及一个 IDENTITY 字段，该字段稍后将成为 `TempPages` 中的 `RecordNo`。

然后，我选择 `#TempPages` 中的所有行，并使用当前会话 ID（`@SessionID`）将它们插入 `TempPages`。我选择 `@@ROWCOUNT`，然后删除 `#TempPages`。

`InitPages` 存储过程的代码如下所示：

CREATE PROCEDURE InitPages 
    @SessionID varchar(24)
AS

SET NOCOUNT ON

DECLARE @RecordCount int
SET @RecordCount = 0

--Deletes the previous records associated with the current session 

DELETE TempPages WHERE SessionID = @SessionID

-- Create a temporary table with all the UserIDs from Members.

SELECT 
    IDENTITY(int, 1, 1) AS RecordNo,
    CONVERT(int, UserID) AS UserID
INTO #TempPages  
FROM Members
ORDER BY UserID

-- Insert into TempPages the records from #TempPages and with the @SessionID

INSERT INTO TempPages( SessionID, RecordNo, User_ID )
SELECT @SessionID, RecordNo, UserID
FROM #TempPages
ORDER BY RecordNo

SELECT @RecordCount = @@ROWCOUNT

-- Drop the temporary table #TempPages

DROP TABLE #TempPages

-- Return the record count.

SELECT @RecordCount AS RecordCount
GO

`GetPage` 存储过程

这个存储过程非常简单。它只需要 3 个参数：`@SessionID`、`@PageNo` 和 `@RowsPerPage`。`@SessiondID` 用于标识会话。`@PageNo` 是页码，`@RowsPerPage` 是每页显示的最大记录数。

此存储过程的代码如下所示：

CREATE PROCEDURE GetPage
    @SessionID    varchar(24),
    @PageNo       int,
    @RowsPerPage  int
AS
SET NOCOUNT ON
DECLARE
  @Start      int,
  @End        int

-- Calculate the boundaries between which I will select 

SET @Start = (@PageNo - 1) * @RowsPerPage + 1
SET @End = @PageNo * @RowsPerPage

-- Select all the records from Members and join TempPages

SELECT
  Members.*
FROM 
  TempPages TP INNER JOIN Members ON TP.User_ID = Members.UserID
WHERE
  TP.SessionID = @SessionID AND
  TP.RecordNo >= @Start AND
  TP.RecordNo <= @End
ORDER BY RecordNo
GO

ASP.NET 实现

在 ASP.NET 中，我使用了一个数据网格来显示从数据库返回的记录。数据网格不参与分页。

对于数据库访问和与存储过程的交互，我使用了 Microsoft Data Access Application Block for .NET，这是一个独立的 .NET 程序集。它包含了 Microsoft SQL Server 数据访问所需的所有功能。使用 Microsoft Data Access Application Block (mdaab) 的优点是您可以编写更少的数据访问代码，并且数据访问得到了优化。

您可以在 Data Access Application Block for .NET 上阅读更多相关信息。您可以在那里下载安装程序。我也将 DLL 附加到了源代码中。

演示程序很简单。第一次请求不带查询字符串的 `default.aspx` 时，将执行 `InitPages`，然后记录初始化后，将执行 `GetPage` 存储过程。当您在页面之间导航时，PageNo 和 PageCount 会通过 QueryString 传递，如果这些值不为 `null`，则只执行 `GetPage` SP。有关更多详细信息，请参见源代码。

此外，在 `Session_End` 中，我执行了一个清理存储过程，该过程会从 `TempPages` 中删除与当前 `Session.SessionID.ToString()` 匹配的记录。

概述

使用此方法的优点

• 非常适合处理大量记录集。通过仅从数据库检索当前页的记录，大大降低了 IIS 的负载。如果您的记录集有 100,000 条记录，并且您希望每页显示 20 条，那么此解决方案比 ADO/ADO.NET 中的分页更优，因为数据库只会选择 20 条记录。
• 如果结果需要复杂的 SQL 代码，那么这些代码只会在初始化时执行一次，而在您在页面之间导航时，只会执行简单而快速的 SELECT（`GetPage` SP）。

缺点

• 如果更多并发用户对分页记录执行 ADD、EDIT、DELETE 操作，那么正在分页的用户不会受到 ADD 操作的影响。用户将在记录重新初始化后才能看到数据库中新添加的记录。EDIT 和 DELETE 操作没有问题，因为我进行了 `TempPages.User_ID = Members.UserID` 的 JOIN。
• 需要在数据库中另创建一个表（`TempPages`）并实施清理方案。

备注

如果您的应用程序需要显示不同类型的记录，那么您可以在 `TempPages` 中添加一个新列，用于标识记录的类型以及从何处选择。例如，在您的网站上，您希望分页显示成员和文章。

欢迎发表您的评论和问题 :)

编程愉快！