Custom paging in asp.net using stored procedure in gridview

Custom paging in asp.net using stored procedure in gridview

 Why to avoid default paging and use custom paging reason -

     In default paging we are fetching all rows in database that takes more execution time.
      In default paging all rows (records of table) load in memory so memory consumption
      In default paging all rows fetch form back end to front end but we are displaying only some of the rows i.e page-size.


How to do custom paging -

    Need to customize you store procedure
    No Change in binding Gridview or Datalist or Repeater whatever you are using
    Need to populate ( Generate ) Pager (Pagination ) manually ( Code also given below).

 Store Procedure Code :

CREATE PROCEDURE getDeals
      @StartIndex int,
      @PageSize int,
      @TotalCount int OutPut
as

select @TotalCount=count(1) from mstrDeals;
WITH CTE AS
(
   select top(@startIndex+@PageSize-1) ROW_NUMBER() OVER(ORDER BY creationdate) RowNumber,dealid,dealTitle
   from mstrDeals
)
select * from CTE where RowNumber between @startIndex and (@startIndex+@PageSize-1)


Binding Grid Control Code :

public void bindGrid(int currentPage)
{
 int pageSize = 10;
 int _TotalRowCount = 0;

 string _ConStr = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
 using (SqlConnection con = new SqlConnection(_ConStr))
 {

   SqlCommand cmd = new SqlCommand("getDeals", con);
   cmd.CommandType = CommandType.StoredProcedure;

   int startRowNumber = ((currentPage - 1) * pageSize) + 1;
     
   cmd.Parameters.AddWithValue("@StartIndex", startRowNumber);
   cmd.Parameters.AddWithValue("@PageSize", pageSize);

   SqlParameter parTotalCount = new SqlParameter("@TotalCount", SqlDbType.Int);
   parTotalCount.Direction = ParameterDirection.Output;
   cmd.Parameters.Add(parTotalCount);

   SqlDataAdapter da = new SqlDataAdapter(cmd);
   DataSet ds = new DataSet();
   da.Fill(ds);

   _TotalRowCount = Convert.ToInt32(parTotalCount.Value);

   grdCustomPagging.DataSource = ds;
   grdCustomPagging.DataBind();

   generatePager(_TotalRowCount, pageSize, currentPage);

 }
}


<asp:GridView Width="500" runat="server" ID="grdCustomPagging">
</asp:GridView>


Then we need to generate pager with total no of rows, page size and current page, below is the sample code that populating pager here total number of pager links is 5 that can be customize further by resetting local variable named totalLinkInPage and have first and last button also which automatic enable or disable when current page is first or last accordingly.
Generate Pager Code :

public void generatePager(int totalRowCount, int pageSize, int currentPage)
{
  int totalLinkInPage = 5;
  int totalPageCount = (int)Math.Ceiling((decimal)totalRowCount / pageSize);

  int startPageLink = Math.Max(currentPage - (int)Math.Floor((decimal)totalLinkInPage / 2), 1);
  int lastPageLink = Math.Min(startPageLink + totalLinkInPage - 1, totalPageCount);

  if ((startPageLink + totalLinkInPage - 1) > totalPageCount)
  {
      lastPageLink = Math.Min(currentPage + (int)Math.Floor((decimal)totalLinkInPage / 2), totalPageCount);
      startPageLink = Math.Max(lastPageLink - totalLinkInPage + 1, 1);
  }

  List<ListItem> pageLinkContainer = new List<ListItem>();

  if (startPageLink != 1)
      pageLinkContainer.Add(new ListItem("First", "1", currentPage != 1));
  for (int i = startPageLink; i <= lastPageLink; i++)
  {
      pageLinkContainer.Add(new ListItem(i.ToString(), i.ToString(), currentPage != i));
  }
  if (lastPageLink != totalPageCount)
      pageLinkContainer.Add(new ListItem("Last", totalPageCount.ToString(), currentPage != totalPageCount));

  dlPager.DataSource = pageLinkContainer;
  dlPager.DataBind();
}

protected void dlPager_ItemCommand(object source, DataListCommandEventArgs e)
{
   if (e.CommandName == "PageNo")
   {
       bindGrid(Convert.ToInt32(e.CommandArgument));
   }
}


<asp:DataList CellPadding="5" RepeatDirection="Horizontal" runat="server" ID="dlPager"
    onitemcommand="dlPager_ItemCommand">
    <ItemTemplate>
       <asp:LinkButton Enabled='<%#Eval("Enabled") %>' runat="server" ID="lnkPageNo" Text='<%#Eval("Text") %>' CommandArgument='<%#Eval("Value") %>' CommandName="PageNo"></asp:LinkButton>
    </ItemTemplate>
</asp:DataList>