数据库分页技术

  当我们的网站需要列出大量数据时，能够提供按页浏览对我们的访问者来说就很有用了，每页会列出出预定义好了的一定数量的显示项目。对于不同的逻辑分页层，我们主要有两种技术区实现分页：

1.在数据库层实现分页：在这种情况下，数据库仅仅返回访问者想要看到的一页数据。

2.在应用程序层实现分页：本情况下，通常数据层会返回所有的数据到表示层，同时在表示层来对数据实现分页(例如：可以使用GridView控件)这种分页方法存在潜在的性能问题尤其是在需要处理大量数据的时候问题更加突出。附加的数据同时也需要存储在服务器内存中，造成了不必要的服务器资源浪费。

下面介绍下如何在数据库层实现分页功能：

现在我们来看看如何从数据库中获取一部分数据，假设现在我们已经知道了页数和每页的项目数。(显然，获取前n条记录可以使用TOP关键字来查询，但这并不能实现分页。)

SQL Server 2008(and 2005) 数据库给我们提供了一个ROW NUMBER函数分配行号，从1开始。在使用ROW NUMBER函数是我们也需要指定具体的某一列来进行编号：

SELECT  ROW_NUMBER() OVER (ORDER BY ProductID) AS Row，Name
FROM Product

执行结果如下：

Row Name

1 I Love You (Simon Elvin)

2 Elvis Hunka Burning Love

3 Funny Love

4 Today, Tomorrow & Forever

5 Smiley Heart Red Balloon

6 Love 24 Karat

7 Smiley Kiss Red Balloon

8 Love You Hearts

9 Love Me Tender

10 I Can't Get Enough of You Baby

……

为了能过显示5条数据，例如第六条到第十条数据，我们可以使用上面的查询语句并加上一个WHERE语句来实现。具体的语句如下：

SELECT Row, Name
FROM(    
   SELECT ROW NUMBER() OVER (ORDER BY ProductID) AS Row,Name
   FROM Product
)  AS ProductsWithRowNumbers
WHERE Row >=6 AND Row<=10

使用表变量：

如果你获得了一系列数据并需要对这些数据进行进一步的操作，那么你可以考虑吧这些数据存储到一个临时表或一个表变量中区。临时表和表变量都可以像使用正常的表那样来使用，这对使用存储过程来存储临时数据有很大的帮助。

在存储过程中会返回很多页的记录，你可以吧所有的记录都存放在一个表变量中去，这样你可以在返回具体某一页之前统计出记录的总条数。

下面的代码告诉你如何去创建一个名为@Products的表变量：

-- declare a new TABLE variable
DECLARE @Products TABLE
(RowNumber INT,
ProductID INT,
Name VARCHAR(50),
Description NVARCHAR(MAX)
)

定义完表变量以后，你就可以使用INSERT INTO语句来向其中插入数据：

-- populate the table variable with the complete list of products
INSERT INTO@Products
SELECT ROW NUMBER() OVER (ORDER BY Product.ProductID) AS Row,
      ProductID,Name,Description
FROM Product
然后你可以使用下面的方法来获取数据

 SELECT Name,Description FROM @Products
 WHERE RowNumber>=6 AND RowNumber<=10

现在我们来实现一个完整的数据库分页的功能，创建CatalogGetProductOnFrontPromo

CREATE PRODUCE  CatalogGetProcuctsOnFrontPromo
(@DescriptionLength INT,
@PageNumber INT,
@ProductsPerPage INT,
@HowManyProducts INT OUTPUT
)
AS
-- declare a new TABLE variable
DECLARE @ Products TABLE
(RowNumber INT,
ProductID INT,
Name NVARCHAR(50),
Description NVARCHAR(MAX),
Price MONEY,
Thumbnail NVARCHAR(50),
Image NVARCHAR(50),
PromoFront bit,
PromoDept bit
)


-- Populate the table variable with the complete list of products
INSERT INTO @Products
SELECT ROW NUMBER() OVER (ORDER BY Product.ProductId),
      ProductID,Name,
      CASE WHEN LEN(Description) <= @DescriptionLength THEN Description
           ELSE SUBSTRING(Description,1,@DescriptionLength) + ‘…’ END
      AS Description, Price, Thumbnail, Image, PromoFront, PromoDept
FROM Product
WHERE PromoFront = 1

-- return the total number of products using an OUTPUT variable
SELECT @HowManyProducts = COUNT(ProductID) FROM @Products

-- extract the requested page of products
SELECT ProductID, Name, Description, Price, Thumbnail,
      Image, PromoFront, PromoDept
FROM @Products
WHERE RowNumber > (@PageNumber - 1) * ProductsPerPage
  AND RowNumber <= @PageNumber * ProductsPerPage


以上就是在数据库层实现分页技术的介绍，在上面的基础上我们就可以在业务逻辑层进行存储过程调用来实现分页。

(文章翻译自beginning asp.net e-commerce in C# from novice to professional)
                                                                                Translation by: Snail