首先是存儲過程,只取出我需要的那段數據,如果頁數超過數據總數,自動返回最後一頁的紀錄:
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代碼方法):
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(...); }
調用方法:
//得到分頁設置並放入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;