Custom Page Size And Paging For ASP.NET Grid View

Introduction

• Here, I will explain how to set custom page size and paging for ASP.NET GridView.

Why We Need To Use Custom Page Size And Paging

• We utilize default GridView paging, which means we are getting all the records from the database but we use Custom paging, which means we are getting a small amount of records (like 10 records).
  
• For example, we have 20000 record in the database. We assume that 20000 data binds to GridView, then we set page size as 10. if we are not using custom paging GridView it means that the full 20000 data binds to GridView, but this process takes a lot of time because the data volume is high, so we decrease fetching time by using custom paging GridView.

Stored Procedure For Paging

• The procedure, mentioned below, is used to fetch the page size, which is based on the records.
• I set default page size as 10 so 10 records only return this procedure.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- === === === === === === === === === === === === === === === CREATE PROCEDURE GetCustomersPageWise
@PageIndex INT = 1,
@PageSize INT = 10,
@RecordCount INT OUTPUT

AS
BEGIN
   SET NOCOUNT ON;

   SELECT
      ROW_NUMBER() OVER(ORDER BY[CustomerID] ASC) AS RowNumber,
      [CustomerID],
      [CompanyName],
      [ContactName]
      INTO #Results FROM[Customers]

   SELECT @RecordCount = COUNT( * )
   FROM #Results

   SELECT *
   FROM #Results
      WHERE RowNumber BETWEEN(@PageIndex - 1) * @PageSize + 1
         AND(((@PageIndex - * @PageSize + 1) + @PageSize) - 1

   DROP TABLE #Results;
END
GO

HTML code

Sample ASP.NET Front end code for custom grid is given below.

• HTML given below has One DropDownList, GridView and Repeater.
  DropDownList is used to set Custom page size(Like 10,25,50). If you set custom page size to 10, only 10 pieces of data are fetched from the database then that data is shown on the grid.
  
• Repeater control is used for pagination.
  

  <div> PageSize:
      <asp:DropDownList ID="ddlPageSize" runat="server" AutoPostBack="true" OnSelectedIndexChanged="PageSize_Changed">
          <asp:ListItem Text="10" Value="10" />
          <asp:ListItem Text="25" Value="25" />
          <asp:ListItem Text="50" Value="50" /> </asp:DropDownList>
      <hr />
      <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
          <Columns>
              <asp:BoundField HeaderText="CustomerId" DataField="CustomerId" />
              <asp:BoundField HeaderText="ContactName" DataField="ContactName" /> .
              <asp:BoundField HeaderText="CompanyName" DataField="CompanyName" /> </Columns>
      </asp:GridView> <br />
      <asp:Repeater ID="rptPager" runat="server">
          <ItemTemplate>
              <asp:LinkButton ID="lnkPage" runat="server" Text='<%#Eval("Text") %>' CommandArgument='<%# Eval("Value") %>' Enabled='<%# Eval("Enabled") %>' OnClick="Page_Changed"></asp:LinkButton>
          </ItemTemplate>
      </asp:Repeater>
  </div>

C# Code

private void GetGridDataPageWise(int pageIndex) {
    string constring = ConfigurationManager.ConnectionStrings["constring"].ConnectionString;
    using(SqlConnection con = new SqlConnection(constring)) {
        using(SqlCommand cmd = new SqlCommand("GetGridDataPageWise", con)) {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
            cmd.Parameters.AddWithValue("@PageSize", int.Parse(ddlPageSize.SelectedValue));
            cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4);
            cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
            con.Open();
            IDataReader idr = cmd.ExecuteReader();
            GridView1.DataSource = idr;
            GridView1.DataBind();
            idr.Close();
            con.Close();
            int recordCount = Convert.ToInt32(cmd.Parameters["@RecordCount"].Value);
            this.GetGridDataBasedonPageIndex(recordCount, pageIndex);
        }
    }
}
private void GetGridDataBasedonPageIndex(int recordCount, int currentPage) {
    double dblPageCount = (double)((decimal) recordCount / decimal.Parse(ddlPageSize.SelectedValue));
    int pageCount = (int) Math.Ceiling(dblPageCount);
    List < ListItem > pages = new List < ListItem > ();
    if (pageCount > 0) {
        pages.Add(new ListItem("First", "1", currentPage > 1));
        for (int i = 1; i <= pageCount; i++) {
            pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
        }
        pages.Add(new ListItem("Last", pageCount.ToString(), currentPage < pageCount));
    }
    rptPager.DataSource = pages;
    rptPager.DataBind();
}
protected void PageSize_Changed(object sender, EventArgs e) {
    this.GetGridDataPageWise(1);
}
protected void Page_Changed(object sender, EventArgs e) {
    int pageIndex = int.Parse((sender as LinkButton).CommandArgument);
    this.GetGridDataPageWise(pageIndex);
}

Example



Advantage

• We decrease data fetching time by using Custom Grid Paging.