首先是存儲過程,只取出我需要的那段數據,如果頁數超過數據總數,自動返回最后一頁的紀錄:
|
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; |
