Skip to main content

Custom paging in using stored procedure in gridview

When we need to display only some of the record in page and table in database contains lacks of rows then   we should have to use custom paging instead of default paging.

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 (i.e query    execution time in SQL Server or any database management system).
  •   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 :

      @StartIndex int,
      @PageSize int,
      @TotalCount int OutPut

select @TotalCount=count(1) from mstrDeals;
   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;

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

   _TotalRowCount = Convert.ToInt32(parTotalCount.Value);

   grdCustomPagging.DataSource = ds;

   generatePager(_TotalRowCount, pageSize, currentPage);


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

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;

protected void dlPager_ItemCommand(object source, DataListCommandEventArgs e)
   if (e.CommandName == "PageNo")

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

Custom paging in using stored procedure in gridview Demo

Custom paging in c# store procedure with pagination
Custom paging in using stored procedure in gridview

Popular posts from this blog

Merging multiple PDFs using iTextSharp in c#

In this article i will show you how to merge multiple pdfs into one using ITextSharp below is the two approach one is to pass your input files path, output file path (will be created if not exist) and another is pass direct input stream, output stream and it will write the merge files into output stream.

how $document.ready() is different from window.onload()

we often use window.onload() in javascript and $document.ready() in jquery and assume that both are same just jquery library wrap up javascript window.onload() and introduce $document.ready() but no there is much difference between them below is the explanation -

Uploading large file in chunks in Mvc c# from Javascript ajax

Often we have a requirement to upload files in, Mvc c# application but when it comes to uploading larger file, we always think how to do it as uploading large file in one go have many challenges like UI responsiveness, If network fluctuate for a moment in between then uploading task get breaks and user have to upload it again etc.

Scrollable Gridview with fixed headers in

horizontal and vertical scrollable Gridview with static header row.fixed header in gridview in header in gridview through java script.I was looking for a solution for this for a long time and found most of the answers are not working or not suitable for my situation i also find most of the java script code for that they worked but only with the vertical scroll not with the horizontal scroll and also combination of header and rows doesn't match.

How to handle click event of linkbutton inside gridview

Recently I have posted how to sort only current page of gridview, Scrollble gridview with fixed header through javascript, File upload control inside gridview during postback and now i am going to explain how to handle click event of linkbutton or any button type control inside gridview.

We can handle click event of any button type control inside gridview by two way first is through event bubbling and second one is directly (in this type of event handling we need to access current girdviewrow container)