How to do custom pagination in the GridView to achieve better performance in case we have large number of data to display?

GridvIew control is a powerful data grid control that allows us to display the data in tabular format with sorting and pagination. It also allows us to manipulate the data.

Get hundreds of ASP.NET Tips and Tricks and ASP.NET Online training here.

In the previous article we learnt about How to delete multiple selected records from the GridView?In this article we shall learn how to do custom pagination in the GridView or to do SEO friendly pagination for the GridView, we can
follow this approach.

ASPX PAGE

<asp:gridview id="GridView1" runat="server" autogeneratecolumns="false" cellpadding="4"
width="100%" datakeynames="AutoId" allowpaging="false" enableviewstate="false">
<Columns>
<asp:BoundField HeaderText="Auto Id" DataField="AutoId" />
<asp:BoundField HeaderText="First Name" DataField="FirstName" />
<asp:BoundField HeaderText="Last Name" DataField="LastName" />
<asp:BoundField HeaderText="Age" DataField="Age" />
<asp:BoundField HeaderText="Active" DataField="Active" />
</Columns>
</asp:gridview>
<hr />
<b>Navigate to: </b>
<asp:literal id="litPaging" runat="server" enableviewstate="false" />

In the above code snippet, we have a GridView with AllowPaging=false so that GridView inbuilt paging mechanism doesn’t work or it doesn’t limit the number of rows to display. We have kept a asp:Literal control that is used to populate the custom page numbers from the code behind.

CODE BEHIND

string _ConnStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
int _startRowIndex = 0;
int _pageSize = 2;
int _thisPage = 0;
 
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
if (!string.IsNullOrWhiteSpace(Request.QueryString["startIndex"]))
{
_startRowIndex = int.Parse(Request.QueryString["startIndex"]);
_thisPage = int.Parse(Request.QueryString["page"]);
}
this.BindTheGrid();
}
}
 
private void BindTheGrid()
{
var totalCount = 0;
DataTable table = new DataTable();
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = _ConnStr;
using (SqlCommand cmd = new SqlCommand("LoadPersonalDetails", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@startRowIndex", _startRowIndex);
cmd.Parameters.AddWithValue("@pageSize", _pageSize);
SqlParameter prm = new SqlParameter("@totalCount", SqlDbType.Int);
prm.Direction = ParameterDirection.Output;
cmd.Parameters.Add(prm);
using (SqlDataAdapter ad = new SqlDataAdapter(cmd))
{
ad.Fill(table);
}
totalCount = int.Parse(prm.Value.ToString());
}
}
GridView1.DataSource = table;
GridView1.DataBind();
litPaging.Text = DoPagiation(totalCount);
}
 
private string DoPagiation(int totalCount)
{
var pages = totalCount / _pageSize;
int holder = 0;
StringBuilder strB = new StringBuilder();
for (int i = 0; i < pages; i++)
{
if (!holder.Equals(_thisPage))
{
strB.Append("<a href=\"CustomPagination.aspx?startIndex=" + (holder *
_pageSize) + "&page=" + holder + "\">Page - " + (holder + 1) + "</a> | ");
}
else
{
strB.Append("Page " + (holder + 1) + " | ");
}
holder++;
}
return strB.ToString();
}
 

BindTheGrid() method

In the code behind we have a BindTheGrid method that is used to populate the data into the GridView. BindTheGrid method uses “LoadPersonalDetails” stored procedure that accepts two parameters (startRowIndex – the row number after which records to fetch from the database, and pageSize – number of records to fetch from the database). This stored procedure also has an output parameter that is the total number of records exists into that table that is eligible for the listing (in case you want to place where clause in the SELECT statement, it must be placed both in the sub query of the first select statement as well as the in the second (total count sql). 

The stored procedure (that is written below) gives records based on the startRowIndex and pageSize parameters those are filled into the DataTable. This DataTable is used to populate the GridView.

-- EXEC LoadPagedArticles 10, 5
CREATE PROCEDURE [dbo].[LoadPersonalDetails]
-- Add the parameters for the stored procedure here
@startRowIndex int,
@pageSize int,
@totalCount int out
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- increase the startRowIndex by 1 to avoid returning the last record again
SET @startRowIndex = @startRowIndex + 1
BEGIN
SELECT * FROM (
Select *,
ROW_NUMBER() OVER (ORDER BY AutoID ASC) as RowNum
FROM PersonalDetail -- put where clause here if any
) as People
WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @pageSize)
- 1
ORDER BY AutoID ASC
SELECT @totalCount = COUNT(AutoId) FROM PersonalDetail 
-- put where clause here if any
END
END

In case we have to do the custom pagination with where clause (filtering the record based on certain condition), we need to put the where clause in the Subquery of the above stored procedure as well as in the total count SELECT statement.

DoPagination() method

DoPagination method accepts totalCount as parameter and divides it by the page size to get the number of pages to display and writes the appropriate links with “startIndex” and “page” as querystring in the asp:Literal control.

The scope of _startRowIndex and _pageSize are of page level and its default values are specified so when this page is loaded for the first time, the first page of the GridView is shown and next time onwards based on the page number clicked _startRowIndex and _pageSize variables are set using the querystring values into the Page_Load event that is used to fetch the records from the database.

Thanks for reading, hope you liked it.

Keep reading my forth coming articles. To read my series of articles on ASP.NET,click here.