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

SheoNarayan
Posted by in ASP.NET category on for Intermediate level | Points: 250 | Views : 11319 red flag
Rating: 5 out of 5  
 1 vote(s)

To do custom pagination in the GridView or to do SEO friendly pagination for the GridView, we can follow this approach.

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.


SQL SERVER STORED PROCEDURE

-- 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.

OUTPUT

Thanks for reading, hope you liked it.

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

Page copy protected against web site content infringement by Copyscape

About the Author

SheoNarayan
Full Name: Sheo Narayan
Member Level: HonoraryPlatinum
Member Status: Administrator
Member Since: 7/8/2008 6:32:14 PM
Country: India
Regards, Sheo Narayan http://www.dotnetfunda.com
http://www.snarayan.com
Ex-Microsoft MVP, Author, Writer, Mentor & architecting applications since year 2001. Connect me on http://www.facebook.com/sheo.narayan | https://twitter.com/sheonarayan | http://www.linkedin.com/in/sheonarayan

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)