您现在的位置：程式碼之家 >> 編程語言 > >> .NET 網頁程式設計 >> ASP.NET 基礎 >> ASP.NET 基礎

DataList 控件分頁實戰：存儲過程分頁 + 完整前後台代碼實現

編輯：ASP.NET 基礎

架構概覽

前台： DataList 顯示/編輯/刪除 + 分頁導航按鈕（首頁/上頁/下頁/尾頁/跳轉）
後台： 存儲過程分頁查詢 + ViewState 保存頁碼/總頁數 + 事件處理
資料庫： SQL Server 存儲過程使用 ROW_NUMBER() 分頁

前台完整標記


<asp:DataList ID="dlProducts" runat="server" 
    OnItemCommand="dlProducts_ItemCommand"
    OnEditCommand="dlProducts_EditCommand"
    OnUpdateCommand="dlProducts_UpdateCommand"
    OnCancelCommand="dlProducts_CancelCommand"
    OnDeleteCommand="dlProducts_DeleteCommand"
    OnItemDataBound="dlProducts_ItemDataBound">

    <ItemTemplate>
        產品名: <asp:Label ID="lblName" runat="server" Text='<%# Eval("ProductName") %>' /><br/>
        規格: <asp:Label ID="lblSpec" runat="server" Text='<%# Eval("ProductStandard") %>' /><br/>
        包裝率: <asp:Label ID="lblRatio" runat="server" Text='<%# Eval("PackagingRatio") %>' /><br/>
        條碼: <asp:Label ID="lblBarcode" runat="server" Text='<%# Eval("ArticleNum") %>' /><br/>
        價格: <asp:Label ID="lblPrice" runat="server" Text='<%# Eval("Price") %>' /><br/>
        <asp:Button ID="btnEdit" runat="server" CommandName="Edit" Text="編輯" />
        <asp:Button ID="btnDelete" runat="server" CommandName="Delete" CommandArgument='<%# Eval("PId") %>' Text="刪除" />
        <asp:Button ID="btnBuy" runat="server" CommandName="Buy" CommandArgument='<%# Eval("PId") %>' Text="加入購物車" />
    </ItemTemplate>

    <EditItemTemplate>
        <table>
            <tr><td>商品名:</td><td><asp:TextBox ID="txtName" runat="server" Text='<%# Eval("ProductName") %>' /></td></tr>
            <tr><td>規格:</td><td><asp:TextBox ID="txtSpec" runat="server" Text='<%# Eval("ProductStandard") %>' /></td></tr>
            <tr><td>包裝率:</td><td><asp:TextBox ID="txtRatio" runat="server" Text='<%# Eval("PackagingRatio") %>' /></td></tr>
            <tr><td>條碼:</td><td><asp:TextBox ID="txtBarcode" runat="server" Text='<%# Eval("ArticleNum") %>' /></td></tr>
            <tr><td>價格:</td><td><asp:TextBox ID="txtPrice" runat="server" Text='<%# Eval("Price") %>' /></td></tr>
            <tr><td><asp:Button ID="btnUpdate" runat="server" CommandName="Update" CommandArgument='<%# Eval("PId") %>' Text="更新" /></td>
                <td><asp:Button ID="btnCancel" runat="server" CommandName="Cancel" Text="取消" /></td></tr>
        </table>
    </EditItemTemplate>
</asp:DataList>

<!-- 分頁導航 -->
<asp:Button ID="btnFirst" runat="server" Text="|<" OnClick="btnFirst_Click" />
<asp:Button ID="btnPrev" runat="server" Text="<" OnClick="btnPrev_Click" />
<asp:Button ID="btnNext" runat="server" Text=">" OnClick="btnNext_Click" />
<asp:Button ID="btnLast" runat="server" Text=">|" OnClick="btnLast_Click" />
<asp:Label ID="lblPageInfo" runat="server" />
<asp:TextBox ID="txtPageNo" runat="server" Width="43px" />
<asp:CompareValidator ID="cvPage" runat="server" ControlToValidate="txtPageNo" Operator="DataTypeCheck" Type="Integer" ErrorMessage="必須為整數" />
<asp:RangeValidator ID="rvPage" runat="server" ControlToValidate="txtPageNo" MinimumValue="1" ErrorMessage="頁碼超出範圍" />
<asp:Button ID="btnGo" runat="server" Text="Go" OnClick="btnGo_Click" />
<asp:HiddenField ID="hfPageCount" runat="server" />
<asp:HiddenField ID="hfPageIndex" runat="server" />

後台核心邏輯

分頁綁定方法


private void BindProducts(int pageIndex) {
    string connStr = ConfigurationManager.ConnectionStrings["studentConnectionString"].ConnectionString;
    using (SqlConnection conn = new SqlConnection(connStr)) {
        conn.Open();
        using (SqlCommand cmd = conn.CreateCommand()) {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "sp_Product_Select_by_Page_rowNumber";
            cmd.Parameters.AddWithValue("@pageSize", 3); // 每頁 3 條
            cmd.Parameters.Add("@pageCount", SqlDbType.Int).Direction = ParameterDirection.Output;
            cmd.Parameters.AddWithValue("@pageIndex", pageIndex);
            
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            
            dlProducts.DataSource = dt;
            dlProducts.DataBind();
            
            int pageCount = (int)cmd.Parameters["@pageCount"].Value;
            hfPageCount.Value = pageCount.ToString();
            hfPageIndex.Value = pageIndex.ToString();
            
            // 更新 RangeValidator 最大值
            rvPage.MaximumValue = pageCount.ToString();
        }
    }
}

頁面載入與導航事件


protected void Page_Load(object sender, EventArgs e) {
    if (!IsPostBack) BindProducts(1);
}

protected void btnFirst_Click(object s, EventArgs e) => BindProducts(1);

protected void btnPrev_Click(object s, EventArgs e) {
    int idx = int.Parse(hfPageIndex.Value);
    if (idx > 1) BindProducts(idx - 1);
}

protected void btnNext_Click(object s, EventArgs e) {
    int idx = int.Parse(hfPageIndex.Value);
    int count = int.Parse(hfPageCount.Value);
    if (idx < count) BindProducts(idx + 1);
}

protected void btnLast_Click(object s, EventArgs e) => BindProducts(int.Parse(hfPageCount.Value));

protected void btnGo_Click(object s, EventArgs e) {
    if (int.TryParse(txtPageNo.Text, out int page) && page >= 1 && page <= int.Parse(hfPageCount.Value))
        BindProducts(page);
    else
        Response.Write("頁碼超出範圍");
}

protected void dlProducts_ItemDataBound(object s, DataListItemEventArgs e) {
    lblPageInfo.Text = $"第 {hfPageIndex.Value} 頁，共 {hfPageCount.Value} 頁";
}

編輯/更新/刪除/購買事件


protected void dlProducts_EditCommand(object s, DataListCommandEventArgs e) {
    dlProducts.EditItemIndex = e.Item.ItemIndex;
    BindProducts(1); // 編輯時重置首頁，或保持當前頁
}

protected void dlProducts_UpdateCommand(object s, DataListCommandEventArgs e) {
    string name = (e.Item.FindControl("txtName") as TextBox).Text;
    string spec = (e.Item.FindControl("txtSpec") as TextBox).Text;
    string ratio = (e.Item.FindControl("txtRatio") as TextBox).Text;
    string barcode = (e.Item.FindControl("txtBarcode") as TextBox).Text;
    string price = (e.Item.FindControl("txtPrice") as TextBox).Text;
    string pid = e.CommandArgument.ToString();
    
    string sql = @"UPDATE Product SET ProductName=@n, ProductStandard=@s, PackagingRatio=@r, ArticleNum=@b, Price=@p WHERE PId=@id";
    SqlParameter[] ps = {
        new SqlParameter("@n", name), new SqlParameter("@s", spec),
        new SqlParameter("@r", ratio), new SqlParameter("@b", barcode),
        new SqlParameter("@p", price), new SqlParameter("@id", pid)
    };
    SqlHelper.ExecuteNonQuery(sql, ps);
    dlProducts.EditItemIndex = -1;
    BindProducts(int.Parse(hfPageIndex.Value));
}

protected void dlProducts_CancelCommand(object s, DataListCommandEventArgs e) {
    dlProducts.EditItemIndex = -1;
    BindProducts(int.Parse(hfPageIndex.Value));
}

protected void dlProducts_DeleteCommand(object s, DataListCommandEventArgs e) {
    string sql = "DELETE FROM Product WHERE PId=@id";
    SqlHelper.ExecuteNonQuery(sql, new SqlParameter("@id", e.CommandArgument));
    BindProducts(int.Parse(hfPageIndex.Value));
}

protected void dlProducts_ItemCommand(object s, DataListCommandEventArgs e) {
    if (e.CommandName == "Buy") Response.Write($"購買商品 ID: {e.CommandArgument}");
}

存儲過程：ROW_NUMBER() 高效分頁


CREATE PROCEDURE sp_Product_Select_by_Page_rowNumber
    @pageSize INT,      -- 每頁記錄數
    @pageCount INT OUTPUT, -- 總頁數
    @pageIndex INT      -- 當前頁碼
AS
BEGIN
    DECLARE @totalRecords INT;
    SELECT @totalRecords = COUNT(PId) FROM Product;
    
    -- 計算總頁數
    IF (@totalRecords % @pageSize = 0)
        SET @pageCount = @totalRecords / @pageSize;
    ELSE
        SET @pageCount = @totalRecords / @pageSize + 1;
    
    -- CTE + ROW_NUMBER 分頁
    WITH Temp AS (
        SELECT ROW_NUMBER() OVER (ORDER BY PId) AS RowNum, * FROM Product
    )
    SELECT * FROM Temp 
    WHERE RowNum BETWEEN (@pageIndex - 1) * @pageSize + 1 AND @pageIndex * @pageSize;
    
    RETURN @totalRecords;
END

配置與輔助類

Web.config 連接字符串


<connectionStrings>
    <add name="studentConnectionString" 
         connectionString="Data Source=.;Initial Catalog=student;User ID=sa;Password=111111" 
         providerName="System.Data.SqlClient" />
</connectionStrings>

SQLHelper 通用執行類


public static class SqlHelper {
    static string ConnStr = ConfigurationManager.ConnectionStrings["studentConnectionString"].ConnectionString;
    
    public static int ExecuteNonQuery(string sql, params SqlParameter[] ps) {
        using (var conn = new SqlConnection(ConnStr))
        using (var cmd = new SqlCommand(sql, conn)) {
            if (ps != null) cmd.Parameters.AddRange(ps);
            conn.Open();
            return cmd.ExecuteNonQuery();
        }
    }
    
    public static DataTable ExecuteDataTable(string sql, params SqlParameter[] ps) {
        var dt = new DataTable();
        using (var da = new SqlDataAdapter(sql, ConnStr)) {
            if (ps != null) da.SelectCommand.Parameters.AddRange(ps);
            da.Fill(dt);
        }
        return dt;
    }
}

關鍵設計點總結

設計點	實現方式
分頁邏輯下推	存儲過程 RETURN 總頁數，OUTPUT 參數返回總頁數
頁狀態保持	HiddenField 存儲 PageIndex/PageCount，跨 PostBack 保持
編輯狀態管理	EditItemIndex + 重新綁定，CommandArgument 傳遞主鍵
參數化查詢	全程 SqlParameter 防注入
驗證控件聯動	RangeValidator.MaximumValue 動態綁定總頁數