set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGO-- =============================================-- Author: Clear-- Description: 高性能分頁(yè)-- http://www.cnblogs.com/roucheng/-- =============================================Alter PROCEDURE [dbo].[Tag_Page_Name_Select]-- 傳入最大顯示紀(jì)錄數(shù)和當(dāng)前頁(yè)碼@MaxPageSize int,@PageNum int,-- 設(shè)置一個(gè)輸出參數(shù)返回總紀(jì)錄數(shù)供分頁(yè)列表使用@Count int outputASBEGINSET NOCOUNT ON;DECLARE-- 定義排序名稱參數(shù)@Name nvarchar(50),-- 定義游標(biāo)位置@Cursor int-- 首先得到紀(jì)錄總數(shù)Select @Count = count(tag_Name)FROM [viewdatabase0716].[dbo].[view_tag];-- 定義游標(biāo)需要開(kāi)始的位置Set @Cursor = @MaxPageSize*(@PageNum-1)+1-- 如果游標(biāo)大于紀(jì)錄總數(shù)將游標(biāo)放到最后一頁(yè)開(kāi)始的位置IF @Cursor > @CountBEGIN-- 如果最后一頁(yè)與最大每次紀(jì)錄數(shù)相等，返回最后整頁(yè)IF @Count % @MaxPageSize = 0BEGINIF @Cursor > @MaxPageSizeSet @Cursor = @Count - @MaxPageSize + 1ELSESet @Cursor = 1END-- 否則返回最后一頁(yè)剩下的紀(jì)錄ELSESet @Cursor = @Count - (@Count % @MaxPageSize) + 1END-- 將指針指到該頁(yè)開(kāi)始Set Rowcount @Cursor-- 得到紀(jì)錄開(kāi)始的位置Select @Name = tag_NameFROM [viewdatabase0716].[dbo].[view_tag]orDER BY tag_Name;-- 設(shè)置開(kāi)始位置Set Rowcount @MaxPageSize-- 得到該頁(yè)紀(jì)錄Select * From [viewdatabase0716].[dbo].[view_tag]Where tag_Name >= @Nameorder By tag_NameSet Rowcount 0END 

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>/// 擴(kuò)展連接字符串/// </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>/// 得到生成的分頁(yè)HTML/// </summary>public string GetPageHtml{get{return PageString.ToString();}}/// <summary>/// 設(shè)置或獲取目前頁(yè)數(shù)/// </summary>public int PageNum{get{return PrivatePageNum;}set{if (value >= 1){PrivatePageNum = value;}}}/// <summary>/// 設(shè)置或獲取最大分頁(yè)數(shù)/// </summary>public int MaxPageSize{get{return PrivateMaxPageSize;}set{if (value >= 1){PrivateMaxPageSize = value;}}}/// <summary>/// 設(shè)置或獲取每次顯示最大頁(yè)數(shù)/// </summary>public int MaxPages{get{return PrivateMaxPages;}set{PrivateMaxPages = value;}}/// <summary>/// 設(shè)置或獲取數(shù)據(jù)總數(shù)/// </summary>public int DateCount{get{return PrivateCount;}set{PrivateCount = value;}}/// <summary>/// 獲取數(shù)據(jù)總頁(yè)數(shù)/// </summary>public int AllPage{get{return PrivateAllPage;}}/// <summary>/// 初始化分頁(yè)/// </summary>public void Pagination(){PageString = new StringBuilder("");//得到總頁(yè)數(shù)PrivateAllPage = (int)Math.Ceiling((decimal)PrivateCount / (decimal)PrivateMaxPageSize);//防止上標(biāo)或下標(biāo)越界if (PrivatePageNum > PrivateAllPage){PrivatePageNum = PrivateAllPage;}//滾動(dòng)游標(biāo)分頁(yè)方式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;}}//生成頁(yè)碼列表統(tǒng)計(jì)PageString.Append(...);StringBuilder PreviousString = new StringBuilder("");//如果在第一頁(yè)if (PrivatePageNum > 1){...}else{...}//如果在第一組分頁(yè)if (PrivatePageNum > PrivateMaxPages){...}else{...}PageString.Append(PreviousString);//生成中間頁(yè) http://www.cnblogs.com/roucheng/for (int i = LeftStart; i <= RightEnd; i++){//為當(dāng)前頁(yè)時(shí)if (i == PrivatePageNum){...}else{...}}StringBuilder LastString = new StringBuilder("");//如果在最后一頁(yè)if (PrivatePageNum < PrivateAllPage){...}else{...}//如果在最后一組if ((PrivatePageNum + PrivateMaxPages) < PrivateAllPage){...}else{...}PageString.Append(LastString);}/// <summary>/// 生成Tag分類表格/// </summary>public void TagTable(ExDataRow myExDataRow){InsertString.Append(...);} 

//得到分頁(yè)設(shè)置并放入SessionExRequest myExRequest = new ExRequest();myExRequest.PageSession("Tag_", new string[] { "page", "size" });//生成Tag分頁(yè)ExStringBuilder Tag = new ExStringBuilder();//設(shè)置每次顯示多少條紀(jì)錄Tag.MaxPageSize = Convert.ToInt32(Session["Tag_size"]);//設(shè)置最多顯示多少頁(yè)碼Tag.MaxPages = 9;//設(shè)置當(dāng)前為第幾頁(yè)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()}};//調(diào)用存儲(chǔ)過(guò)程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;