ASP.NET 高性能分页

首先是存储过程,只取出我需要的那段数据,如果页数超过数据总数,自动返回最后一页的纪录:

程序代码

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Clear
-- Create date: 2007-01-30
-- Description: 高性能分页
-- =============================================
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);
//生成中间页
        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[i]));
        }
        TagBox.InnerHtml = Tag.GetHtml;

处理页码到Session的方法就不提供了,没有很大关.调用存储过程返回参数和纪录的方法和之前我写的批量数据操作方法差不多的,只需要定义一个输出方式.

目前我想这些代码还会有瑕疵,等项目后期代码审查的时候再强化吧,我想说的一点就是不要被那些拖来拖去的东西迷惑了,那样对自己永远都没有提高,要抱着知其然,知其所以然的态度去做一件事情,对自己的帮助才会明显.