在读取大量数据的时候我们可以通过DataReader对数据进行分页以提高性能,还有一个更好的方法就是在存储过程中对数据进行分页。
假设有一个Products表字段有(ProductID,Name,Description, Price)
以下方法只支持SQLServer 2005 因为ROW_NUMBER()函数是SQLServer 2005新增的。
1
CREATE PROCEDURE GetProducts
2![](/Images/OutliningIndicators/None.gif)
3
(@DescriptionLength INT, --定义参数:描述长度
4![](/Images/OutliningIndicators/None.gif)
5
@PageNumber INT, --页码
6![](/Images/OutliningIndicators/None.gif)
7
@ProductsPerPage INT, --每页产品数
8![](/Images/OutliningIndicators/None.gif)
9
@HowManyProducts INT OUTPUT) --产品总数
10![](/Images/OutliningIndicators/None.gif)
11
AS
12![](/Images/OutliningIndicators/None.gif)
13
-- 定义一个Table变量
14![](/Images/OutliningIndicators/None.gif)
15
DECLARE @Products TABLE
16![](/Images/OutliningIndicators/None.gif)
17
(RowNumber INT,
18![](/Images/OutliningIndicators/None.gif)
19
ProductID INT,
20![](/Images/OutliningIndicators/None.gif)
21
Name VARCHAR(50),
22![](/Images/OutliningIndicators/None.gif)
23
Description VARCHAR(5000)
24![](/Images/OutliningIndicators/None.gif)
25
Price MONEY)
26![](/Images/OutliningIndicators/None.gif)
27
-- 把数据读到刚定义的@Products 中
28![](/Images/OutliningIndicators/None.gif)
29
INSERT INTO @Products
30![](/Images/OutliningIndicators/None.gif)
31
SELECT ROW_NUMBER() OVER (ORDER BY Product.ProductID),
32![](/Images/OutliningIndicators/None.gif)
33
ProductID, Name,
34![](/Images/OutliningIndicators/None.gif)
35
SUBSTRING(Description, 1, @DescriptionLength) + '
' AS Description, Price,
36![](/Images/OutliningIndicators/None.gif)
37
FROM Product
38![](/Images/OutliningIndicators/None.gif)
39
-- 返回产品数
40![](/Images/OutliningIndicators/None.gif)
41
SELECT @HowManyProducts = COUNT(ProductID) FROM @Products
42![](/Images/OutliningIndicators/None.gif)
43
-- 返回请求页面的数据
44![](/Images/OutliningIndicators/None.gif)
45
SELECT ProductID, Name, Description, Price
46![](/Images/OutliningIndicators/None.gif)
47
FROM @Products
48![](/Images/OutliningIndicators/None.gif)
49
WHERE RowNumber > (@PageNumber - 1) * @ProductsPerPage
50![](/Images/OutliningIndicators/None.gif)
51
AND RowNumber <= @PageNumber * @ProductsPerPage
52![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
2
![](/Images/OutliningIndicators/None.gif)
3
![](/Images/OutliningIndicators/None.gif)
4
![](/Images/OutliningIndicators/None.gif)
5
![](/Images/OutliningIndicators/None.gif)
6
![](/Images/OutliningIndicators/None.gif)
7
![](/Images/OutliningIndicators/None.gif)
8
![](/Images/OutliningIndicators/None.gif)
9
![](/Images/OutliningIndicators/None.gif)
10
![](/Images/OutliningIndicators/None.gif)
11
![](/Images/OutliningIndicators/None.gif)
12
![](/Images/OutliningIndicators/None.gif)
13
![](/Images/OutliningIndicators/None.gif)
14
![](/Images/OutliningIndicators/None.gif)
15
![](/Images/OutliningIndicators/None.gif)
16
![](/Images/OutliningIndicators/None.gif)
17
![](/Images/OutliningIndicators/None.gif)
18
![](/Images/OutliningIndicators/None.gif)
19
![](/Images/OutliningIndicators/None.gif)
20
![](/Images/OutliningIndicators/None.gif)
21
![](/Images/OutliningIndicators/None.gif)
22
![](/Images/OutliningIndicators/None.gif)
23
![](/Images/OutliningIndicators/None.gif)
24
![](/Images/OutliningIndicators/None.gif)
25
![](/Images/OutliningIndicators/None.gif)
26
![](/Images/OutliningIndicators/None.gif)
27
![](/Images/OutliningIndicators/None.gif)
28
![](/Images/OutliningIndicators/None.gif)
29
![](/Images/OutliningIndicators/None.gif)
30
![](/Images/OutliningIndicators/None.gif)
31
![](/Images/OutliningIndicators/None.gif)
32
![](/Images/OutliningIndicators/None.gif)
33
![](/Images/OutliningIndicators/None.gif)
34
![](/Images/OutliningIndicators/None.gif)
35
![](/Images/OutliningIndicators/None.gif)
![](/Images/dot.gif)
36
![](/Images/OutliningIndicators/None.gif)
37
![](/Images/OutliningIndicators/None.gif)
38
![](/Images/OutliningIndicators/None.gif)
39
![](/Images/OutliningIndicators/None.gif)
40
![](/Images/OutliningIndicators/None.gif)
41
![](/Images/OutliningIndicators/None.gif)
42
![](/Images/OutliningIndicators/None.gif)
43
![](/Images/OutliningIndicators/None.gif)
44
![](/Images/OutliningIndicators/None.gif)
45
![](/Images/OutliningIndicators/None.gif)
46
![](/Images/OutliningIndicators/None.gif)
47
![](/Images/OutliningIndicators/None.gif)
48
![](/Images/OutliningIndicators/None.gif)
49
![](/Images/OutliningIndicators/None.gif)
50
![](/Images/OutliningIndicators/None.gif)
51
![](/Images/OutliningIndicators/None.gif)
52
![](/Images/OutliningIndicators/None.gif)
在SQLServer 2000中可以用以下的方法:
1
CREATE PROCEDURE GetProducts
2![](/Images/OutliningIndicators/None.gif)
3
(@DescriptionLength INT, --定义参数:描述长度
4![](/Images/OutliningIndicators/None.gif)
5
@PageNumber INT, --页码
6![](/Images/OutliningIndicators/None.gif)
7
@ProductsPerPage INT, --每页产品数
8![](/Images/OutliningIndicators/None.gif)
9
@HowManyProducts INT OUTPUT) --产品总数
10![](/Images/OutliningIndicators/None.gif)
11
AS
12![](/Images/OutliningIndicators/None.gif)
13
-- 定义一个Table变量
14![](/Images/OutliningIndicators/None.gif)
15
DECLARE #Products TABLE --这里一定要用‘#’(声明为本地临时表)
16![](/Images/OutliningIndicators/None.gif)
17
(RowNumber SMALLINT NOT NULL IDENTITY(1,1), --类型一定要自动递增
18![](/Images/OutliningIndicators/None.gif)
19
ProductID INT,
20![](/Images/OutliningIndicators/None.gif)
21
Name VARCHAR(50),
22![](/Images/OutliningIndicators/None.gif)
23
Description VARCHAR(5000)
24![](/Images/OutliningIndicators/None.gif)
25
Price MONEY)
26![](/Images/OutliningIndicators/None.gif)
27
-- 把数据读到刚定义的#Products 中
28![](/Images/OutliningIndicators/None.gif)
29
INSERT INTO #Products (ProductID, Name, Description, Price)
30![](/Images/OutliningIndicators/None.gif)
31
SELECT
32![](/Images/OutliningIndicators/None.gif)
33
ProductID, Name,
34![](/Images/OutliningIndicators/None.gif)
35
SUBSTRING(Description, 1, @DescriptionLength) + '
' AS Description, Price,
36![](/Images/OutliningIndicators/None.gif)
37
FROM Product
38![](/Images/OutliningIndicators/None.gif)
39
-- 返回产品数
40![](/Images/OutliningIndicators/None.gif)
41
SELECT @HowManyProducts = COUNT(ProductID) FROM #Products
42![](/Images/OutliningIndicators/None.gif)
43
-- 返回请求页面的数据
44![](/Images/OutliningIndicators/None.gif)
45
SELECT ProductID, Name, Description, Price
46![](/Images/OutliningIndicators/None.gif)
47
FROM #Products
48![](/Images/OutliningIndicators/None.gif)
49
WHERE RowNumber > (@PageNumber - 1) * @ProductsPerPage
50![](/Images/OutliningIndicators/None.gif)
51
AND RowNumber <= @PageNumber * @ProductsPerPage
52![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
2
![](/Images/OutliningIndicators/None.gif)
3
![](/Images/OutliningIndicators/None.gif)
4
![](/Images/OutliningIndicators/None.gif)
5
![](/Images/OutliningIndicators/None.gif)
6
![](/Images/OutliningIndicators/None.gif)
7
![](/Images/OutliningIndicators/None.gif)
8
![](/Images/OutliningIndicators/None.gif)
9
![](/Images/OutliningIndicators/None.gif)
10
![](/Images/OutliningIndicators/None.gif)
11
![](/Images/OutliningIndicators/None.gif)
12
![](/Images/OutliningIndicators/None.gif)
13
![](/Images/OutliningIndicators/None.gif)
14
![](/Images/OutliningIndicators/None.gif)
15
![](/Images/OutliningIndicators/None.gif)
16
![](/Images/OutliningIndicators/None.gif)
17
![](/Images/OutliningIndicators/None.gif)
18
![](/Images/OutliningIndicators/None.gif)
19
![](/Images/OutliningIndicators/None.gif)
20
![](/Images/OutliningIndicators/None.gif)
21
![](/Images/OutliningIndicators/None.gif)
22
![](/Images/OutliningIndicators/None.gif)
23
![](/Images/OutliningIndicators/None.gif)
24
![](/Images/OutliningIndicators/None.gif)
25
![](/Images/OutliningIndicators/None.gif)
26
![](/Images/OutliningIndicators/None.gif)
27
![](/Images/OutliningIndicators/None.gif)
28
![](/Images/OutliningIndicators/None.gif)
29
![](/Images/OutliningIndicators/None.gif)
30
![](/Images/OutliningIndicators/None.gif)
31
![](/Images/OutliningIndicators/None.gif)
32
![](/Images/OutliningIndicators/None.gif)
33
![](/Images/OutliningIndicators/None.gif)
34
![](/Images/OutliningIndicators/None.gif)
35
![](/Images/OutliningIndicators/None.gif)
![](/Images/dot.gif)
36
![](/Images/OutliningIndicators/None.gif)
37
![](/Images/OutliningIndicators/None.gif)
38
![](/Images/OutliningIndicators/None.gif)
39
![](/Images/OutliningIndicators/None.gif)
40
![](/Images/OutliningIndicators/None.gif)
41
![](/Images/OutliningIndicators/None.gif)
42
![](/Images/OutliningIndicators/None.gif)
43
![](/Images/OutliningIndicators/None.gif)
44
![](/Images/OutliningIndicators/None.gif)
45
![](/Images/OutliningIndicators/None.gif)
46
![](/Images/OutliningIndicators/None.gif)
47
![](/Images/OutliningIndicators/None.gif)
48
![](/Images/OutliningIndicators/None.gif)
49
![](/Images/OutliningIndicators/None.gif)
50
![](/Images/OutliningIndicators/None.gif)
51
![](/Images/OutliningIndicators/None.gif)
52
![](/Images/OutliningIndicators/None.gif)
大家都清楚了吧,这种方法比在DataReader中速度高效。