Efficient Server Side Paging with the ASP.NET GridView Control

Abstract: The following article demonstrates how to use the ObjectDataSource control with the GridView control and Linq To SQL to create efficient server side ASP.NET GridView Paging.

Efficient Server Side Paging with the ASP.NET GridView Control

When people talk about paging with the GridView control, they’re normally talking about the default, out of the box, vanilla paging that comes built in the GridView control. This is good for demos or small databases. But what happens to the performance of your website if you connect to a database with thousands of records? The application won’t fall over, but the decline in performance will be noticeable. That is because GridView paging retrieves all records from the SQL Server and displays only what is required. One of the best ways to handle this scenario is to use the GridView control with an ObjectDataSource control and specify the methods you will use for manual paging.

The following example will utilize Visual Studio 2008, LINQ to SQL and SQL Server 2008.

Add a new Linq To SQL file to the project and connect to the Northwind database. Drag the Customers table onto the designer. This is the table that will be referenced in the example.

Now that the database layer exists, let’s turn our attention to the ObjectDataSource. The ObjectDataSource has two methods that are required for efficient paging. These are SelectMethod and SelectCountMethod. The following parameters are required for both methods:

int maximumRows, int startPageIndex, int startRowIndex

These values will be passed by the ObjectDataSource to the methods we define.

Add a new Class file to the project and name it Northwind.cs or Northwind.vb. This will be a partial class as this example will extend the functionality of the Northwind class defined in the Linq To SQL file. The following code is required for paging:

The GetCustomers method accepts the arguments that are passed by the ObjectDataSource. It utilizes the Skip and Take methods to only return the rows needed for the GridView, not the whole contents of the table. The GetCustomerCount method returns a count of all the records in the table. That is all that is required for our methods. Now we need to connect this up to a GridView control and see the results.

Open the Default.aspx page. Drag a GridView control onto the page. Open the Smart Tag and select Choose Data Source. Select <New data source...>:

A wizard will open and guide you through the setup. The first screen defines the Data Source Type:

Select Object and click OK. The following screen asks you to choose a Business Object. Select Paging.Northwind and click Next:

The following screen asks you to choose a Data Method. Select GetCustomers and click Next:

The final screen asks you to define the parameters. Leave the Value blank for as it will be passed by the ObjectDataSource. Once this is done click Finish to close the wizard:

The final step is to select the GridView and set AllowPaging to true. Then select the ObjectDataSource and set EnablePaging to true. The hard work is complete. To test this you can use SQL Profiler to see the SQL that is executed on the database. The following code is a sample of what is executed on the SQL Server:

The code that is generated utilises the ROW_NUMBER() function which limits the numbers of rows that are returned.

By handling the paging as shown in this example, your ASP.NET website will be able to display thousands of records efficiently in a GridView and let the user page through data without delays that are common when dealing with large data sets. This will not only improve performance on the web server, but it will also decrease the amount of data that is sent to the client. The source code of the article can be downloaded from here.

Was this article worth reading? Share it with fellow developers too. Thanks!

Malcolm Sheridan is a Microsoft awarded MVP in ASP.NET, a Telerik Insider and a regular presenter at conferences and user groups throughout Australia and New Zealand. Being an ASP.NET guy, his focus is on web technologies and has been for the past 10 years. He loves working with ASP.NET MVC these days and also loves getting his hands dirty with jQuery and JavaScript. He also writes technical articles on ASP.NET for SitePoint and other various websites. Follow him on twitter @malcolmsheridan

Feedback - Leave us some adulation, criticism and everything in between!

@Faith
Yes that is true. The LinqDataSource does server side paging out of the box. The downside to this is if you have existing business objects which return collections, they're not available to the LinqDataSource unless they return the System.Data.Linq.Table<> type. The ObjectDataSource however does allow this and it more flexible in that scenario.

@Faith
Yes that is true. The LinqDataSource does server side paging out of the box. The downside to this is if you have existing business objects which return collections, they're not available to the LinqDataSource unless they return the System.Data.Linq.Table<> type. The ObjectDataSource however does allow this and it more flexible in that scenario.

Comment posted by
babon
on Thursday, February 5, 2009 10:08 AM

I was looking out for this functionality and found your article. Many thanks to you.

Comment posted by
Brock Babuškov
on Friday, February 6, 2009 8:56 PM

Best to read! I remember reading scott mitchell article where he propos solution using stored procedures. With just SQL and LINQ as your sample what additional advantage does I get?

Then you'll need to update the GetCustomerCount method to filter the same data. That's it!

Comment posted by
ssss
on Friday, May 15, 2009 3:02 AM

sssss

Comment posted by
sumeet
on Tuesday, June 16, 2009 7:13 AM

good article! one query can we paginate without using ROW_NUMBER() in query? How?

Comment posted by
alice
on Friday, June 26, 2009 8:13 AM

Nice introduction! Being a freshman in blogging, I read lots of literature as far as what and how to write. http://www.ebook-search-queen.com/ helps me with the books, and your post with inspiration! Thanks!

Comment posted by
bill
on Wednesday, July 1, 2009 6:44 AM

I have a problem with the above. In SQL profiler the first sql code which is execute is "SELECT TOP(x)" where x is the number of paging records. Later on it then does the ROWNUMBER query and count query. Why is it doing a select top every time the page is loaded? Has anyone else tested this using the SQL profiler to see the exectued query?

Comment posted by
bill
on Wednesday, July 1, 2009 8:48 AM

Just to add - I am using listview and datapager for this - is this the problem?

Comment posted by
Malcolm Sheridan
on Wednesday, July 1, 2009 9:12 PM

@bill
That is what the IQueryable Skip and Take methods. They use the ROWNUMBER so only the records you want to display on that page are returned, not the entire table.

Comment posted by
bill
on Thursday, July 2, 2009 7:54 AM

@ Malcolm - I understand that, but every time i go to a page its doing a SELECT TOP, SELECT COUNT and A SELECT ROW query. IE, three different queries where it should only be 2 - the count and the efficient t-sql. Any ideas?

Comment posted by
Sobin
on Monday, August 24, 2009 1:52 AM

Sir,
What if I want to customize my select query? My select query is now created dynamically based on the selection of the user in the UI(The query includes table joining also).Can I pass this as the select query to the object datasource at run time and fetch values and then bind to the gridview? Also the paging should work as in your great article....... Thanks

Comment posted by
Guhan
on Wednesday, September 9, 2009 1:14 AM

i will bind image in Gridview for Ex(three rows records)
Mr.Raja,Mr.bill,Mr.Dipak
Mr.BB,Mr.CC,Mr.DD
Mr.AB,Mr.AC,Mr.AD
how to bind in Gridview including Paging..

Comment posted by
amigoface
on Thursday, September 10, 2009 6:39 AM

hi

great article (short and efficient )

now i am highely interested to make some filters in my application
for example a date range filter at runtime
and some other filters

@amigoface,@Sobin
Yes you should be able to add an extra filter in the GetCustomers method to allow for searching by say the contact name. You should try it and let me know.

Comment posted by
Joe
on Friday, November 6, 2009 11:46 AM

With enterprise level systems I really try to avoid Linq like it's the black plague. Stick with DAL->SP->DB and your application won't have as many scalability issues. If you don't believe me, just run the profiler against your typical Linq application and watch in horror :)

@Joe
I will disagree with you on avoiding LINQ to SQL in the enterprise. You can use stored procedures with LINQ to SQL, so if you have issues with the generated T-SQL, then use them. I am a big fan of stored procedures, and I'm a big fan of LINQ to SQL.

Comment posted by
Kostya Batanin
on Monday, November 16, 2009 7:45 PM

Nice article, thank you guys.

I find it very confusing that the SelectCount method has to take the same SelectParameters as the Select method since the SelectCount method does not use any of those parameters at all. But I think this is Microsoft's fault.

This way there is no reason to specify unnecessary parameters in the signatures of the methods. I've also removed the startPageIndex parameter from the Select method as well, as far as I understand it's not needed for this particular example?

Cheers!

Comment posted by
fdfsfdfdsfs
on Thursday, January 5, 2012 7:23 AM

fvfsaf

Comment posted by
vxvxvx
on Thursday, January 5, 2012 7:24 AM

dsadsadasdasdasdasdasdasda

Comment posted by
Sushant
on Wednesday, August 21, 2013 5:10 AM

hi, i want to if i'm using DTO how can I implement this, since I need to format the data . please help