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