ASP.NET 分頁三大方案深度解析：GridView 內建、AspNetPager 控件、存儲過程分頁

方案一：GridView 內建分頁（最簡單）

適合資料量小（< 1 萬筆）、不需自訂分頁 UI 的場景。

<asp:GridView ID="GridView1" AllowPaging="true" PageSize="3" OnPageIndexChanging="GridView1_PageIndexChanging" runat="server"></asp:GridView>

Protected Sub Page_Load(...)
    If Not IsPostBack Then BindData()
End Sub
Protected Sub BindData()
    Dim dt = GetData() ' 取回全量資料
    GridView1.DataSource = dt : GridView1.DataBind()
End Sub
Protected Sub GridView1_PageIndexChanging(...)
    GridView1.PageIndex = e.NewPageIndex : BindData()
End Sub

缺點：每次換頁都重查全量資料，大數據量極其低效。

方案二：AspNetPager + PagedDataSource（中型資料量）

引用 Wuqi.Webdiyer.AspNetPager.dll，配合 PagedDataSource 在記憶體中分頁。

<asp:GridView ID="GridView1" runat="server"></asp:GridView>
<webdiyer:AspNetPager ID="AspNetPager1" runat="server" PageSize="4" 
    OnPageChanging="AspNetPager1_PageChanging" ... ></webdiyer:AspNetPager>

Protected Sub BindData()
    Dim dt = GetData() ' 全量
    Dim ds = New DataSet() : ds.Tables.Add(dt)
    Pager(GridView1, AspNetPager1, ds)
End Sub

Protected Sub Pager(ByVal gv As GridView, ByVal anp As AspNetPager, ByVal ds As DataSet)
    Dim pds = New PagedDataSource() With {.DataSource = ds.Tables(0).DefaultView, .AllowPaging = True, .PageSize = anp.PageSize, .CurrentPageIndex = anp.CurrentPageIndex - 1}
    anp.RecordCount = ds.Tables(0).Rows.Count
    gv.DataSource = pds : gv.DataBind()
End Sub

Protected Sub AspNetPager1_PageChanging(...)
    AspNetPager1.CurrentPageIndex = e.NewPageIndex : BindData()
End Sub

仍需查全量進記憶體，適合中等資料量（萬~十萬筆）。

方案三：AspNetPager + 存儲過程真分頁（大數據量）

只取當頁資料，配合 ROW_NUMBER() 窗口函數，效能最佳。

前台

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" ... ></asp:GridView>
<webdiyer:AspNetPager ID="AspNetPager1" runat="server" OnPageChanged="AspNetPager1_PageChanged" ... ></webdiyer:AspNetPager>

後台

Protected Sub Page_Load(...)
    If Not IsPostBack Then
        ' 首次載入：取總筆數
        Dim dsTotal = ReportQueryBLL.GetDetail(...)
        AspNetPager1.RecordCount = dsTotal.Tables(0).Rows.Count
        BindDetailReportToGv()
    End If
End Sub

Protected Sub BindDetailReportToGv()
    Dim ds = ReportQueryBLL.GetTcikDetailReport(..., AspNetPager1.StartRecordIndex, AspNetPager1.EndRecordIndex)
    GridView1.DataSource = ds : GridView1.DataBind()
End Sub

Protected Sub AspNetPager1_PageChanged(...)
    BindDetailReportToGv()
End Sub

核心存儲過程（SQL Server 2005+）

CREATE PROCEDURE [dbo].[P_GetPagedOrders2005]
    @startIndex INT, @endIndex INT
AS
SELECT * FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY IPid DESC) AS rownum,
           [IPid],[IPFrom],[IPTo],[IPLocation],[IPCity],[IPToNumber],[IPFromNumber]
    FROM IPInfo
) AS U
WHERE rownum BETWEEN @startIndex AND @endIndex
GO

複雜動態欄位存儲過程範例

動態 PIVOT 轉換票券類型為欄位，再套用分頁：

CREATE PROCEDURE [dbo].[pro_pager]
    @startIndex INT, @endIndex INT, @strWhere VARCHAR(200)
AS
-- 1. 明細暫存表 #temp
SELECT ... INTO #temp FROM ... WHERE 1=1 + @strWhere GROUP BY ...
-- 2. 動態構建 PIVOT SQL
DECLARE @sql VARCHAR(8000)
SET @sql = 'SELECT CONVERT(varchar(12), On_Tick_SellDatetime_Dtm, 111) AS On_Tick_SellDatetime_Dtm, ...'
SELECT @sql = @sql + ',SUM(CASE tickettype_name_dec WHEN ''' + tickettype_name_dec + ''' THEN expr2 ELSE 0 END) [' + tickettype_name_dec + ']'
FROM (SELECT DISTINCT tickettype_name_dec FROM tb_Ticket_Type) AS a
SET @sql = @sql + ' ,expr3,Expr1,On_Tick_TicketsNum_Str,Dept_ID_Int,On_Tick_ID_Int INTO ##t FROM #temp GROUP BY ... ORDER BY On_Tick_SellDatetime_Dtm'
EXEC(@sql)
-- 3. 套用分頁
SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY on_tick_id_int DESC) AS rownum, * FROM ##t) AS U
WHERE rownum BETWEEN @startIndex AND @endIndex
DROP TABLE ##t

三方案對比總結

選型建議

• 後台管理、資料量可控 → 方案二。
• 前台高併發、百萬級資料 → 方案三，並配合適當索引與快取。
• SQL Server 2012+ 可直接使用 OFFSET ... FETCH 簡化存儲過程。