ASP.NET + SQL Server 高效分頁完整實作：預存程序 + 自訂分頁控制項

資料庫端：游標式分頁預存程序

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(*) FROM [viewdatabase0716].[dbo].[view_tag];
    SET @Cursor = @MaxPageSize * (@PageNum - 1) + 1;

    -- 超出總筆數自動導向最後一頁
    IF @Cursor > @Count
    BEGIN
        IF @Count % @MaxPageSize = 0
            SET @Cursor = CASE WHEN @Cursor > @MaxPageSize THEN @Count - @MaxPageSize + 1 ELSE 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

關鍵技巧：SET ROWCOUNT 限制掃描筆數，配合鍵值定位避免掃描前 N 頁資料。

前端端：ExStringBuilder 分頁控制項 (C#)

public class ExStringBuilder
{
    private StringBuilder InsertString = new StringBuilder();
    private StringBuilder PageString = new StringBuilder();
    private int PageNum = 1, MaxPageSize = 25, MaxPages = 10, TotalCount, TotalPages;

    public string GetHtml => InsertString.ToString();
    public string GetPageHtml => PageString.ToString();

    public void Pagination()
    {
        PageString.Clear();
        TotalPages = (int)Math.Ceiling((decimal)TotalCount / MaxPageSize);
        PageNum = Math.Max(1, Math.Min(PageNum, TotalPages));

        int leftRange = (MaxPages + 1) / 2 - 1;
        int rightRange = (MaxPages + 1) / 2;
        int leftStart, rightEnd;

        if (MaxPages >= TotalPages) { leftStart = 1; rightEnd = TotalPages; }
        else if (PageNum <= leftRange) { leftStart = 1; rightEnd = MaxPages; }
        else if (TotalPages - PageNum < rightRange) { rightEnd = TotalPages; leftStart = TotalPages - MaxPages + 1; }
        else { leftStart = PageNum - leftRange; rightEnd = PageNum + rightRange; }

        // 生成上一頁、頁碼、下一頁、首尾頁邏輯（省略具體 HTML 拼接）
        // ...
    }

    public void TagTable(DataRow row)
    {
        InsertString.AppendFormat("{0}{1}", row["tag_Name"], row["tag_Id"]);
    }
}

頁面調用範例

var Tag = new ExStringBuilder();
Tag.MaxPageSize = Convert.ToInt32(Session["Tag_size"]);
Tag.MaxPages = 9;
Tag.PageNum = Convert.ToInt32(Session["Tag_page"]);

string[][] param = {
    new[] {"MaxPageSize","PageNum","Count"},
    new[] {Tag.MaxPageSize.ToString(), Tag.PageNum.ToString()}
};

DataTable dt = mySQL.BatchGetDB("Tag_Page_Name_Select", param, "Count");
Tag.TotalCount = (int)mySQL.OutputCommand.Parameters["@Count"].Value;
Tag.Pagination();

HeadPage.InnerHtml = FootPage.InnerHtml = Tag.GetPageHtml;
foreach (DataRow r in dt.Rows) Tag.TagTable(r);
TagBox.InnerHtml = Tag.GetHtml;

優化建議

• SQL Server 2012+ 建議改用 OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY，計畫更穩定。
• 分頁控制項可封裝為 Web User Control 或 TagHelper (ASP.NET Core) 復用。
• 大量資料建議增加「每頁筆數」下拉選單，配合 MaxPageSize 參數化。