ASP.NET和MSSQL高性能分页实例代码

时间:2022-09-17 17:05:00

首先是存储过程,只取出我需要的那段数据,如果页数超过数据总数,自动返回最后一页的纪录:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Clear
-- Description: 高性能分页
-- http://www.cnblogs.com/roucheng/
-- =============================================
Alter PROCEDURE [dbo].[Tag_Page_Name_Select]
-- 传入最大显示纪录数和当前页码
@MaxPageSize int,
@PageNum int,
-- 设置一个输出参数返回总纪录数供分页列表使用
@Count int output
AS
BEGIN
SET NOCOUNT ON;
DECLARE
-- 定义排序名称参数
@Name nvarchar(50),
-- 定义游标位置
@Cursor int
-- 首先得到纪录总数
Select @Count = count(tag_Name)
FROM [viewdatabase0716].[dbo].[view_tag];
-- 定义游标需要开始的位置
Set @Cursor = @MaxPageSize*(@PageNum-1)+1
-- 如果游标大于纪录总数将游标放到最后一页开始的位置
IF @Cursor > @Count
BEGIN
-- 如果最后一页与最大每次纪录数相等,返回最后整页
IF @Count % @MaxPageSize = 0
BEGIN
IF @Cursor > @MaxPageSize
Set @Cursor = @Count - @MaxPageSize + 1
ELSE
Set @Cursor = 1
END
-- 否则返回最后一页剩下的纪录
ELSE
Set @Cursor = @Count - (@Count % @MaxPageSize) + 1
END
-- 将指针指到该页开始
Set Rowcount @Cursor
-- 得到纪录开始的位置
Select @Name = tag_Name
FROM [viewdatabase0716].[dbo].[view_tag]
orDER BY tag_Name;
-- 设置开始位置
Set Rowcount @MaxPageSize
-- 得到该页纪录
Select *
From [viewdatabase0716].[dbo].[view_tag]
Where tag_Name >= @Name
order By tag_Name
Set Rowcount 0
END

然后是分页控件(... 为省略的生成HTML代码方法):

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Text;
/// <summary>
/// 扩展连接字符串
/// </summary>
public class ExStringBuilder
{
private StringBuilder InsertString;
private StringBuilder PageString;
private int PrivatePageNum = 1;
private int PrivateMaxPageSize = 25;
private int PrivateMaxPages = 10;
private int PrivateCount;
private int PrivateAllPage;
public ExStringBuilder()
{
InsertString = new StringBuilder("");
}
/// <summary>
/// 得到生成的HTML
/// </summary>
public string GetHtml
{
get
{
return InsertString.ToString();
}
}
/// <summary>
/// 得到生成的分页HTML
/// </summary>
public string GetPageHtml
{
get
{
return PageString.ToString();
}
}
/// <summary>
/// 设置或获取目前页数
/// </summary>
public int PageNum
{
get
{
return PrivatePageNum;
}
set
{
if (value >= 1)
{
PrivatePageNum = value;
}
}
}
/// <summary>
/// 设置或获取最大分页数
/// </summary>
public int MaxPageSize
{
get
{
return PrivateMaxPageSize;
}
set
{
if (value >= 1)
{
PrivateMaxPageSize = value;
}
}
}
/// <summary>
/// 设置或获取每次显示最大页数
/// </summary>
public int MaxPages
{
get
{
return PrivateMaxPages;
}
set
{
PrivateMaxPages = value;
}
}
/// <summary>
/// 设置或获取数据总数
/// </summary>
public int DateCount
{
get
{
return PrivateCount;
}
set
{
PrivateCount = value;
}
}
/// <summary>
/// 获取数据总页数
/// </summary>
public int AllPage
{
get
{
return PrivateAllPage;
}
}
/// <summary>
/// 初始化分页
/// </summary>
public void Pagination()
{
PageString = new StringBuilder("");
//得到总页数
PrivateAllPage = (int)Math.Ceiling((decimal)PrivateCount / (decimal)PrivateMaxPageSize);
//防止上标或下标越界
if (PrivatePageNum > PrivateAllPage)
{
PrivatePageNum = PrivateAllPage;
}
//滚动游标分页方式
int LeftRange, RightRange, LeftStart, RightEnd;
LeftRange = (PrivateMaxPages + 1) / 2-1;
RightRange = (PrivateMaxPages + 1) / 2;
if (PrivateMaxPages >= PrivateAllPage)
{
LeftStart = 1;
RightEnd = PrivateAllPage;
}
else
{
if (PrivatePageNum <= LeftRange)
{
LeftStart = 1;
RightEnd = LeftStart + PrivateMaxPages - 1;
}
else if (PrivateAllPage - PrivatePageNum < RightRange)
{
RightEnd = PrivateAllPage;
LeftStart = RightEnd - PrivateMaxPages + 1;
}
else
{
LeftStart = PrivatePageNum - LeftRange;
RightEnd = PrivatePageNum + RightRange;
}
}
//生成页码列表统计
PageString.Append(...);
StringBuilder PreviousString = new StringBuilder("");
//如果在第一页
if (PrivatePageNum > 1)
{
...
}
else
{
...
}
//如果在第一组分页
if (PrivatePageNum > PrivateMaxPages)
{
...
}
else
{
...
}
PageString.Append(PreviousString);
//生成中间页 http://www.cnblogs.com/roucheng/
for (int i = LeftStart; i <= RightEnd; i++)
{
//为当前页时
if (i == PrivatePageNum)
{
...
}
else
{
...
}
}
StringBuilder LastString = new StringBuilder("");
//如果在最后一页
if (PrivatePageNum < PrivateAllPage)
{
...
}
else
{
...
}
//如果在最后一组
if ((PrivatePageNum + PrivateMaxPages) < PrivateAllPage)
{
...
}
else
{
...
}
PageString.Append(LastString);
}
/// <summary>
/// 生成Tag分类表格
/// </summary>
public void TagTable(ExDataRow myExDataRow)
{
InsertString.Append(...);
}

调用方法:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
//得到分页设置并放入Session
ExRequest myExRequest = new ExRequest();
myExRequest.PageSession("Tag_", new string[] { "page", "size" });
//生成Tag分页
ExStringBuilder Tag = new ExStringBuilder();
//设置每次显示多少条纪录
Tag.MaxPageSize = Convert.ToInt32(Session["Tag_size"]);
//设置最多显示多少页码
Tag.MaxPages = 9;
//设置当前为第几页
Tag.PageNum = Convert.ToInt32(Session["Tag_page"]);
string[][] myNamenValue = new string[2][]{
new string[]{"MaxPageSize","PageNum","Count"},
new string[]{Tag.MaxPageSize.ToString(),Tag.PageNum.ToString()}
};
//调用存储过程
DataTable myDataTable = mySQL.BatchGetDB("Tag_Page_Name_Select", myNamenValue, "Count");
Tag.DateCount = (int)mySQL.OutputCommand.Parameters["@Count"].Value;
Tag.Pagination();
HeadPage.InnerHtml = FootPage.InnerHtml = Tag.GetPageHtml;
for (int i = 0, j = myDataTable.Rows.Count; i < j; i++)
{
Tag.TagTable(new ExDataRow(myDataTable.Rows));
}
TagBox.InnerHtml = Tag.GetHtml;