三層架構 + 存儲過程實現 GridView 分頁完整案例

數據庫層：存儲過程分頁

CREATE PROCEDURE usp_GetRolesByPage
    @PageSize INT,
    @PageIndex INT
AS
BEGIN
    SELECT * FROM (
        SELECT *, ROW_NUMBER() OVER (ORDER BY Role_ID) AS RowNum FROM Roles
    ) AS T
    WHERE RowNum BETWEEN (@PageSize * (@PageIndex - 1) + 1) AND @PageSize * @PageIndex;
END

數據訪問層

public DataTable GetRolesPage(int pageSize, int pageIndex)
{
    SqlParameter[] pars = {
        new SqlParameter("@PageSize", pageSize),
        new SqlParameter("@PageIndex", pageIndex)
    };
    return DbHelperSQL.RunProcedureDataTable("usp_GetRolesByPage", pars);
}

// DbHelperSQL 核心方法
public static DataTable RunProcedureDataTable(string procName, IDataParameter[] pars)
{
    using (SqlConnection conn = new SqlConnection(connStr))
    using (SqlCommand cmd = new SqlCommand(procName, conn) { CommandType = CommandType.StoredProcedure })
    {
        if (pars != null) cmd.Parameters.AddRange(pars);
        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
        {
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
    }
}

業務邏輯層

public DataTable GetRolesPage(int pageSize, int pageIndex) => dal.GetRolesPage(pageSize, pageIndex);
public int GetTotalCount() => (int)dal.ExecuteScalar("SELECT COUNT(*) FROM Roles");

表現層：前台 GridView + 分頁導航

<asp:GridView ID="gvRoles" runat="server" AutoGenerateColumns="False" AllowPaging="False">
    <Columns>
        <asp:BoundField DataField="Role_ID" HeaderText="編號" />
        <asp:BoundField DataField="Role_Name" HeaderText="名稱" />
    </Columns>
</asp:GridView>

<asp:LinkButton ID="btnFirst" runat="server" OnClick="btnFirst_Click">首頁</asp:LinkButton>
<asp:LinkButton ID="btnPrev" runat="server" OnClick="btnPrev_Click">上一頁</asp:LinkButton>
<asp:LinkButton ID="btnNext" runat="server" OnClick="btnNext_Click">下一頁</asp:LinkButton>
<asp:LinkButton ID="btnLast" runat="server" OnClick="btnLast_Click">尾頁</asp:LinkButton>
<asp:TextBox ID="txtPage" runat="server" Width="40px" />
<asp:LinkButton ID="btnGo" runat="server" OnClick="btnGo_Click">GO</asp:LinkButton>

後台分頁邏輯

private int PageSize = 10;
private int PageIndex { get => (int)(ViewState["PageIndex"] ?? 1); set => ViewState["PageIndex"] = value; }
private int TotalPages { get => (int)(ViewState["TotalPages"] ?? 1); set => ViewState["TotalPages"] = value; }

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        PageIndex = 1;
        LoadTotalPages();
        BindGrid();
    }
}

private void LoadTotalPages()
{
    int total = new RoleBLL().GetTotalCount();
    TotalPages = (total + PageSize - 1) / PageSize;
}

private void BindGrid()
{
    var dt = new RoleBLL().GetRolesPage(PageSize, PageIndex);
    gvRoles.DataSource = dt;
    gvRoles.DataBind();
}

protected void btnFirst_Click(object s, EventArgs e) { PageIndex = 1; BindGrid(); }
protected void btnPrev_Click(object s, EventArgs e) { if (PageIndex > 1) { PageIndex--; BindGrid(); } }
protected void btnNext_Click(object s, EventArgs e) { if (PageIndex < TotalPages) { PageIndex++; BindGrid(); } }
protected void btnLast_Click(object s, EventArgs e) { PageIndex = TotalPages; BindGrid(); }
protected void btnGo_Click(object s, EventArgs e)
{
    if (int.TryParse(txtPage.Text, out int p) && p >= 1 && p <= TotalPages)
    { PageIndex = p; BindGrid(); }
    else txtPage.Text = "頁碼錯誤";
}

優化建議

• 緩存總數：角色表變化頻率低，總記錄數可緩存 5-10 分鐘。
• Keyset 分頁：大數據量用 WHERE Role_ID > @LastId ORDER BY Role_ID OFFSET 0 ROWS FETCH NEXT @PageSize ROWS ONLY，避免 ROW_NUMBER 全表掃描。
• ObjectDataSource：配合 SelectCountMethod 可自動處理分頁 UI，減少手工代碼。