Kỹ thuật phân trang trong trang asp.net - Dùng Store

--CREATE BY hungbv.com.vn
CREATE PROCEDURE [dbo].[spCustomers_PhanTrang]
@currPage int,
@recodperpage int,
@Pagesize int
AS
Begin
    Begin
    WITH s AS
    (
        SELECT ROW_NUMBER() 
   OVER(ORDER BY CustomerID, 
   CompanyName) AS RowNum, 
   CustomerID, 
   CompanyName, 
   ContactName, City 
        FROM Customers  
    )
    Select * From s 
    Where RowNum Between 
  (@currPage - 1)*@recodperpage+1 
   AND @currPage*@recodperpage
    END
    -- Tính tổng số bản ghi
    DECLARE @Tolal int
    SELECT @Tolal=Count(*) FROM Customers
    
    EXEC spPhanTrangSQL 
   @Tolal, 
   @currPage, 
   @Pagesize, 
   @recodperpage
END

<table cellpadding="0" cellspacing="0" width="620">
    <tr>
        <td style="width: 100%">
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" BackColor="White"
        BorderColor="#CC9966" BorderStyle="None" BorderWidth="1px" CellPadding="4" Width="620px">
        <RowStyle BackColor="White" ForeColor="#330099" />
        <Columns>
            <asp:BoundField DataField="RowNum" HeaderText="RowNum" SortExpression="RowNum" />
            <asp:BoundField DataField="CustomerID" HeaderText="CustomerID" SortExpression="CustomerID" />
            <asp:BoundField DataField="CompanyName" HeaderText="CompanyName" SortExpression="CompanyName" />
            <asp:BoundField DataField="ContactName" HeaderText="ContactName" SortExpression="ContactName" />
            <asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />
        </Columns>
        <FooterStyle BackColor="#FFFFCC" ForeColor="#330099" />
        <PagerStyle BackColor="#FFFFCC" ForeColor="#330099" HorizontalAlign="Center" />
        <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="#663399" />
        <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="#FFFFCC" />
    </asp:GridView>
        </td>
    </tr>
    <tr>
        <td style="width: 100%">
            <asp:Literal ID="ltlPhanTrang" runat="server"/></td>
    </tr>
</table>

1. Hàm thực thi một Store đổ dữ liệu vào DataSet

private DataSet StoreToDataSet(
        int currPage, 
        int recodperpage, 
        int Pagesize)
    {
        DataSet dts = new DataSet();
        SqlParameter[] arrParam = {
                new SqlParameter("@currPage", SqlDbType.Int),
                new SqlParameter("@recodperpage", SqlDbType.Int),
                new SqlParameter("@Pagesize", SqlDbType.Int)
                };
        arrParam[0].Value = currPage;
        arrParam[1].Value = recodperpage;
        arrParam[2].Value = Pagesize;
        return ThucThiStore_DataSet("spCustomers_PhanTrang", arrParam);
    }

2. Hàm lấy dữ liệu từ Store trả về một DataSet

3. Hàm nạp dữ liệu để hiển thị

 private void NapDuLieu(int currPage, int recodperpage, int Pagesize)
    {
        DataSet ds = StoreToDataSet(currPage, recodperpage, Pagesize);
        DataTable dtbData = ds.Tables[0];
        DataTable dtbPhanTrang = ds.Tables[1];
        if (dtbData.Rows.Count > 0)
        {
            GridView1.DataSource = dtbData;
            GridView1.DataBind();
            if (dtbPhanTrang.Rows.Count > 0)
            {
                ltlPhanTrang.Text = dtbPhanTrang.Rows[0]["PhanTrang"] + "";
            }
        }
    }

protected void Page_Load(object sender, EventArgs e)
    {
        int page = int.Parse("0" + Request.QueryString["page"]);
 if (page == 0) page = 1;
        if (!IsPostBack)
        {
            NapDuLieu(page, 10, 5);
        }
    }