首先是存储过程,只取出我需要的那段数据,如果页数超过数据总数,自动返回最后一页的纪录:
set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGO-- =============================================-- Author: Clear-- Description: 高性能分页-- http://www.VEVb.com/roucheng/-- =============================================Alter PROCEDURE [dbo].[Tag_Page_Name_Select]-- 传入最大显示纪录数和当前页码 @MaxPageSize int, @PageNum int,-- 设置一个输出参数返回总纪录数供分页列表使用 @Count int outputASBEGIN 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 0END
然后是分页控件(... 为省略的生成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.VEVb.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;
新闻热点
疑难解答