[轉貼] ASP.NET 撘配 SQL Server 快速使用分頁
出處：http://www.cnblogs.com/roucheng/p/3519180.html
首先是存儲過程,只取出我需要的那段數據,如果頁數超過數據總數,自動返回最後一頁的紀錄:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Clear
-- Create date: 2007-01-30
-- 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 using System.Data;
2 using System.Configuration;
3 using System.Web;
4 using System.Web.Security;
5 using System.Web.UI;
6 using System.Web.UI.WebControls;
7 using System.Web.UI.WebControls.WebParts;
8 using System.Web.UI.HtmlControls;
9 using System.Text;
10
11 /// <summary>
12 /// 擴展連接字符串
13 /// </summary>
14 public class ExStringBuilder
15 {
16 private StringBuilder InsertString;
17 private StringBuilder PageString;
18 private int PrivatePageNum = 1;
19 private int PrivateMaxPageSize = 25;
20 private int PrivateMaxPages = 10;
21 private int PrivateCount;
22 private int PrivateAllPage;
23 public ExStringBuilder()
24 {
25 InsertString = new StringBuilder("");
26 }
27 /// <summary>
28 /// 得到生成的HTML
29 /// </summary>
30 public string GetHtml
31 {
32 get
33 {
34 return InsertString.ToString();
35 }
36 }
37 /// <summary>
38 /// 得到生成的分頁HTML
39 /// </summary>
40 public string GetPageHtml
41 {
42 get
43 {
44 return PageString.ToString();
45 }
46 }
47 /// <summary>
48 /// 設置或獲取目前頁數
49 /// </summary>
50 public int PageNum
51 {
52 get
53 {
54 return PrivatePageNum;
55 }
56 set
57 {
58 if (value >= 1)
59 {
60 PrivatePageNum = value;
61 }
62 }
63 }
64 /// <summary>
65 /// 設置或獲取最大分頁數
66 /// </summary>
67 public int MaxPageSize
68 {
69 get
70 {
71 return PrivateMaxPageSize;
72 }
73 set
74 {
75 if (value >= 1)
76 {
77 PrivateMaxPageSize = value;
78 }
79 }
80 }
81 /// <summary>
82 /// 設置或獲取每次顯示最大頁數
83 /// </summary>
84 public int MaxPages
85 {
86 get
87 {
88 return PrivateMaxPages;
89 }
90 set
91 {
92 PrivateMaxPages = value;
93 }
94 }
95 /// <summary>
96 /// 設置或獲取數據總數
97 /// </summary>
98 public int DateCount
99 {
100 get
101 {
102 return PrivateCount;
103 }
104 set
105 {
106 PrivateCount = value;
107 }
108 }
109 /// <summary>
110 /// 獲取數據總頁數
111 /// </summary>
112 public int AllPage
113 {
114 get
115 {
116 return PrivateAllPage;
117 }
118 }
119 /// <summary>
120 /// 初始化分頁
121 /// </summary>
122 public void Pagination()
123 {
124 PageString = new StringBuilder("");
125 //得到總頁數
126 PrivateAllPage = (int)Math.Ceiling((decimal)PrivateCount / (decimal)PrivateMaxPageSize);
127 //防止上標或下標越界
128 if (PrivatePageNum > PrivateAllPage)
129 {
130 PrivatePageNum = PrivateAllPage;
131 }
132 //滾動游標分頁方式
133 int LeftRange, RightRange, LeftStart, RightEnd;
134 LeftRange = (PrivateMaxPages + 1) / 2-1;
135 RightRange = (PrivateMaxPages + 1) / 2;
136 if (PrivateMaxPages >= PrivateAllPage)
137 {
138 LeftStart = 1;
139 RightEnd = PrivateAllPage;
140 }
141 else
142 {
143 if (PrivatePageNum <= LeftRange)
144 {
145 LeftStart = 1;
146 RightEnd = LeftStart + PrivateMaxPages - 1;
147 }
148 else if (PrivateAllPage - PrivatePageNum < RightRange)
149 {
150 RightEnd = PrivateAllPage;
151 LeftStart = RightEnd - PrivateMaxPages + 1;
152 }
153 else
154 {
155 LeftStart = PrivatePageNum - LeftRange;
156 RightEnd = PrivatePageNum + RightRange;
157 }
158 }
159
160 //生成頁碼列表統計
161 PageString.Append(...);
162
163 StringBuilder PreviousString = new StringBuilder("");
164 //如果在第一頁
165 if (PrivatePageNum > 1)
166 {
167 ...
168 }
169 else
170 {
171 ...
172 }
173 //如果在第一組分頁
174 if (PrivatePageNum > PrivateMaxPages)
175 {
176 ...
177 }
178 else
179 {
180 ...
181 }
182 PageString.Append(PreviousString);
183 //生成中間頁 http://www.cnblogs.com/roucheng/
184 for (int i = LeftStart; i <= RightEnd; i++)
185 {
186 //為當前頁時
187 if (i == PrivatePageNum)
188 {
189 ...
190 }
191 else
192 {
193 ...
194 }
195 }
196 StringBuilder LastString = new StringBuilder("");
197 //如果在最後一頁
198 if (PrivatePageNum < PrivateAllPage)
199 {
200 ...
201 }
202 else
203 {
204 ...
205 }
206 //如果在最後一組
207 if ((PrivatePageNum + PrivateMaxPages) < PrivateAllPage)
208 {
209 ...
210 }
211 else
212 {
213 ...
214 }
215 PageString.Append(LastString);
216 }
217 /// <summary>
218 /// 生成Tag分類表格
219 /// </summary>
220 public void TagTable(ExDataRow myExDataRow)
221 {
222 InsertString.Append(...);
223 }
調用方法:
1 //得到分頁設置並放入Session
2 ExRequest myExRequest = new ExRequest();
3 myExRequest.PageSession("Tag_", new string[] { "page", "size" });
4 //生成Tag分頁
5 ExStringBuilder Tag = new ExStringBuilder();
6 //設置每次顯示多少條紀錄
7 Tag.MaxPageSize = Convert.ToInt32(Session["Tag_size"]);
8 //設置最多顯示多少頁碼
9 Tag.MaxPages = 9;
10 //設置當前為第幾頁
11 Tag.PageNum = Convert.ToInt32(Session["Tag_page"]);
12 string[][] myNamenValue = new string[2][]{
13 new string[]{"MaxPageSize","PageNum","Count"},
14 new string[]{Tag.MaxPageSize.ToString(),Tag.PageNum.ToString()}
15 };
16 //調用存儲過程
17 DataTable myDataTable = mySQL.BatchGetDB("Tag_Page_Name_Select", myNamenValue, "Count");
18 Tag.DateCount = (int)mySQL.OutputCommand.Parameters["@Count"].Value;
19 Tag.Pagination();
20
21 HeadPage.InnerHtml = FootPage.InnerHtml = Tag.GetPageHtml;
22
23 for (int i = 0, j = myDataTable.Rows.Count; i < j; i++)
24 {
25 Tag.TagTable(new ExDataRow(myDataTable.Rows));
26 }
27 TagBox.InnerHtml = Tag.GetHtml;
留言
張貼留言