首先是存储过程,只取出我需要的那段数据,如果页数超过数据总数,自动返回最后一页的纪录:
|
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 ONset QUOTED_IDENTIFIER ONGO-- =============================================-- Author: Clear-- Description: 高性能分页-- http://www.cnblogs.com/roucheng/-- =============================================Alter PROCEDURE [dbo].[Tag_Page_Name_Select]-- 传入最大显示纪录数和当前页码@MaxPageSize int,@PageNum int,-- 设置一个输出参数返回总纪录数供分页列表使用@Count int outputASBEGINSET 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 > @CountBEGIN-- 如果最后一页与最大每次纪录数相等，返回最后整页IF @Count % @MaxPageSize = 0BEGINIF @Cursor > @MaxPageSizeSet @Cursor = @Count - @MaxPageSize + 1ELSESet @Cursor = 1END-- 否则返回最后一页剩下的纪录ELSESet @Cursor = @Count - (@Count % @MaxPageSize) + 1END-- 将指针指到该页开始Set Rowcount @Cursor-- 得到纪录开始的位置Select @Name = tag_NameFROM [viewdatabase0716].[dbo].[view_tag]orDER BY tag_Name;-- 设置开始位置Set Rowcount @MaxPageSize-- 得到该页纪录Select * From [viewdatabase0716].[dbo].[view_tag]Where tag_Name >= @Nameorder By tag_NameSet Rowcount 0END |
然后是分页控件(... 为省略的生成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
|
//得到分页设置并放入SessionExRequest 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; |
