Search Engine-Friendly Custom Paging with the GridView and LINQ to SQL

The main problem I find with the built-in paging functionality offered by the GridView control is that the paging links are managed by Javascript. This is a bit awkward for people who disable, or don't have Javascript in their browser, but more importantly, the links can't be triggered by search engines. If you rely on the GridView to paginate links to content, this can prove disastrous, and much of your content will be inaccessible to search engines. Motivated by Scott Guthrie's series of blog entries on LINQ to SQL, available in the .Net 3.5 framework, I set about getting some custom paging working with html paging links.

I won't bother with a detailed description of what LINQ to SQL is. Mr. Guthrie does an excellent job of that himself in his series of articles. Suffice to say that it allows us to query data sources (or data contexts) using a set of standard query operators in a .Net language of your choice. I will be using the Northwind Sql Server database as the basis for my data context for this example. At the moment, the Linq to Sql Classes designer only supports Sql Server 2000 and 2005 (including Express). To bring LINQ to SQL into play, just go to 'Add New Item...' after right-clicking on the name of your project in Solution Explorer, and select LINQ to SQL Classes. Change the name from DataClasses.dbml to Northwind.dbml.

Next, drag the Orders, Order Details, Categories and Products tables from the Server Explorer pane onto the designer. The result will appear as follows, and in the meantime, the IDE will be busy creating entity classes for each of the tables.

Now that the hard work is out of the way, create a new Web Form called CustomPaging.aspx, and drag a GridView and a Literal control onto it.

Now, the code-behind. we'll use the query that retrieves 4 columns of data (or propeties of the generated classes) that's provided in Scott Gu's blog entry:

 

NorthwindDataContext db = new NorthwindDataContext();

 

var query = from p in db.Products

            where p.Order_Details.Count > 2

            select new

            {

              ID = p.ProductID,

              Name = p.ProductName,

              NumOrders = p.Order_Details.Count,

              Revenue = p.Order_Details.Sum(o => o.UnitPrice * o.Quantity)

            };

 

The above LINQ Query Syntax uses the new standard LINQ query operators which are made available with the 2008 versions of Visual Studio and Visual Web Developer. It's a declarative shorthand which is compiled into explicit method invocation code against the entity classes created by the designer. The compiled code utilises the new 3.5 framework Extension Methods and Lambda Expressions. Much more about this can be read here.

The query syntax will be translated to an SQL statement, and without knowing too much about the LINQ standard operators or query syntax, you may already guess that it will return the ProductID under an alias of ID, the ProductName (Name), the total of Orders for each product (NumOrders) and the revenue per product calculated by multiplying the unit price per product by the quantity sold for all orders(Revenue). The result will be returned to an IEnumerable<Product> called query, which can be bound to any control that supports IEnumerable. At the moment, though, the code won't do anything, because execution is deferred until you actually try to do something with the results, such as iterate over them. Consequently, by adding the following lines:

 

GridView1.DataSource = query;

GridView1.DataBind();

 

The code will be executed at the point that DataBind() is called, and not before. However, that does not really serve our purpose. If the GridView is configured for paging, the default Javascript links will appear, and if it isn't, the entire results will appear. Our current GridView is not configured for anything. So we will implement the paging within the code-behind.

LINQ has many very useful operators, two of which are Skip() and Take(). These come into their own with scenarios like paging, because they are translated to the use of ROW_NUMBER with Sql Server 2005, which allows you to skip a certain number of rows, and only take a limited number from that point. This basically means that if you only want to show 10 records per page, you only need to select 10 records, and not the entire resultset as is the default for the GridView and a SqlDataSource, for example. So we need to add some code to calculate which page we are on so that we can tell the database how many rows to skip and take. We also need to know how many total pages there are in the results of the query. The following does that:

 

int totalRecords = query.Count();

int pageSize = 10;

int totalPages = totalRecords/pageSize;

 

totalRecords uses the Count() method. LINQ will actually translate that to a SELECT COUNT(*) query when it is run against the database. intPageSize sets the number of records to be displayed per page. intTotalPages calculates the total number of pages required to show all the records, and is simply the result of the number of records divided by the page size returned as a whole number.

Now that we have that, we just need to create the html for the paging:

 

if (totalRecords % 10 > 0)

{

    totalPages += 1;

}

StringBuilder sb = new StringBuilder();

for(int i = 0; i < totalPages; i++)

{

    int pageNo = i + 1;

    sb.Append("<a href=\"CustomPaging.aspx?StartRow=" + pageSize * i + "\">" + pageNo.ToString() + "</a>&nbsp;");

}

 

If the modulo of the total number of records divided by the number of pages is greater than 0, we need to add a page to the total to cater for the extra records. For example, assume the totalRecords value is 73. 73 mod 10 is 3, and those extra 3 records need to be shown on the 8th page, so that needs to be added to the total because totalRecords/pageSize returned 7, being the whole number part of the result from the calculation. From there, a StringBuilder is used within a loop to build the html that will contain the paging links. I know there are only 70-odd records in this example, so I have not catered for a situation where large numbers of pages could be returned. Additional logic could be added for this, ensuring that a vast number of paging links don't spread across the page.

You will notice that the paging links include a query string value, StartRow, and each time the loop runs, the value of this is set to pageSize(10) * the value of the loop counter. This provides the method with the row number to start taking records from.

Now, the records need to be bound to the GridView, and the paging links applied to the Literal control:

 

Literal1.Text = "Page: " + sb.ToString();

GridView1.DataSource = query.Skip(startRow).Take(10);

GridView1.DataBind();

 

The Take() method is passed the value of pageSize, in this case 10, which tells LINQ to fetch 10 rows at a time. The Skip method is passed another variable - startRow which is the QueryString["StartRow"] value. So all that remains to be done is to wrap this code in a method which accepts an int, and then to call the method during Page_Load(). Here's the final code:

 

NorthwindDataContext db = new NorthwindDataContext();

 

var query = from p in db.Products

            where p.Order_Details.Count > 2

            select new

            {

              ID = p.ProductID,

              Name = p.ProductName,

              NumOrders = p.Order_Details.Count,

              Revenue = p.Order_Details.Sum(o => o.UnitPrice * o.Quantity)

            };

 

int totalRecords = query.Count();

int pageSize = 10;

int totalPages = totalRecords / pageSize;

if (totalRecords % 10 > 0)

{

  totalPages += 1;

}

StringBuilder sb = new StringBuilder();

for (int i = 0; i < totalPages; i++)

{

  int pageNo = i + 1;

  sb.Append("<a href=\"CustomPaging.aspx?StartRow=" + pageSize * i + "\">" + pageNo.ToString() + "</a>&nbsp;");

}

Literal1.Text = "Page: " + sb.ToString();

GridView1.DataSource = query.Skip(startRow).Take(10);

GridView1.DataBind();

Command.Text = "<p>" + db.GetCommand(query.Skip(startRow).Take(10)).CommandText + "</p>";

 

This is a nice efficient way of paging results, which is also search engine friendly. However, there is still one small problem. Two queries are executed against the database: one to get the 10 rows of data, and another to get the total number of records. This is no worse than using the new LinqDataSource and built-in paging with the GridView, which does exactly the same thing. But if you are concerned with minimising database calls, it is a simple matter to store the value of totalRecords in an Application level variable, and use that instead. Any code that might alter the totalRecords value, such as an Insert, Update or Delete can be finished off with setting the value of the Application variable to 0, and the next time the page is run, it will have the job of repopulating the variable:

 

int totalRecords;

if (Convert.ToInt32(Application["count"]) == 0)

{

  totalRecords = query.Count();

  Application["count"] = totalRecords;

}

else

{

  totalRecords = Convert.ToInt32(Application["count"]);

}

 

 

Date Posted:
Last Updated:
Posted by:
Total Views to date: 51839

15 Comments

- skyhook

What does "command" reference in "command.text"?

- Mike

@skyhook

Good spot! That was left in there by mistake. When I was testing the code, I had a Literal control called Command. The GetCommand() method actually translates the LINQ query to its T-SQL counterpart. I was writing the result to the Literal control.

I'm leaving it in the article text, and hope that anyone else who sees this will find it of interest.

- Andy

Hi great tutorial but is there any way you can some rought ideas how to help a newbie use this with VB? Also is there any way of showing us how to do simialr using a SQL stored procedure as I am struggling to recoe your stuff to get what i want? many thanks in advance

- Mike

@Andy

If you need to re-code C# samples to VB, try using the translator at www.codechanger.com.

- Piyush

i like this article if you need to re-code C# smaple to J# try using the trasaltor at www.codechager.com

- Nilkanth

thanks

- raj

dear !

I want to impalement search option for my database in sql server 2005

- EltonLiu

Great tutorial!

However you forgot to put

int startRow = Convert.ToInt32(Request.QueryString["StartRow"]);

For those who are following this tutorial you need this to get the starRow variable from the URL.

- Jeevan Baby

Gridview does not accepts anything other than a ObjectDataSource.Plz check this problem

- Mike

@Jeevan

I have no idea what you are talking about.

- Tomy

Great! many thanks

- Dung

What is Command !

- Mike

@Dung

Asked and answered in the first two comments.

- Sarang

Hi, Thanks for the great post..I am wondering how can you limit the literal count?....as you have mentioned, you knew the records were less so you didn't include the code to restrict the number of pages....is is possible for you to include the code, assuming there are about 20000 + records?. Appreciate your help!

- Mike

@Sarang

I would never put my application is a position where it could conceivably return 20,000 records. I would provide a series of filters for user to limit the number of records to a more manageable number. You see this on used car sites where they ask you to select a make and model before you get the records returned.
Add your comment

If you have any comments to make about this article, please use this form to do so. Make sure that your comment relates specifically to the article above. More general comments can be posted through the form on the Contact page.

Please note, all comments are moderated, and I end up deleting quite a lot. The kind of things that will ensure your comment is deleted without ever seeing the light of day are as follows:

  • Requests to fix your code (post a question to forums.asp.net instead, please)
  • Gratuitous links to your own site or product
  • Anything abusive or libellous
  • Spam

I do not pass email addresses on to spammers, so a valid one will assist me in responding to you personally if required.

Recent Comments

Lorenzo 3/26/2015 8:21 AM
In response to iTextSharp - Introducing Tables
Hi Mike How can I add padding to all cells in the table? Kind Regards Lorenzo...

Satyabrata Mohapatra 3/25/2015 8:11 AM
In response to How To Send Email In ASP.NET MVC
Great article. Simple and up to the point....

Afzaal Ahmad Zeeshan 3/24/2015 8:17 PM
In response to How To Send Email In ASP.NET MVC
A great way to teach the MVC framework for sending the emails... Also, what I found helpful was the...

Jim H 3/24/2015 2:32 PM
In response to Migrating From Razor Web Pages To ASP.NET MVC 5 - Model Binding And Forms
Thank you. This helps....

wazz 3/22/2015 5:48 AM
In response to Posting Data With jQuery AJAX In ASP.NET Razor Web Pages
great info!!...

rael 3/21/2015 8:53 PM
In response to Getting the identity of the most recently added record
I spent hours trying to figure how to achieve this in C#. This article helped me. Thanks a lot...

Stephen 3/21/2015 8:48 PM
In response to Ajax with Classic ASP using jQuery
This was very helpful, thanks:)...

patrick voes 3/19/2015 10:19 AM
In response to iTextSharp - Introducing Tables
Thank you! very helpfull....

Bigmachini 3/19/2015 6:13 AM
In response to ASP.NET MVC DropDownLists - Multiple Selection and Enum Support
This just made my day, afternoon, evening, night... was looking for a way of doing this without to a...

Bobbyg 3/19/2015 4:14 AM
In response to HTML Helpers For Forms In Razor Web Pages
Nice article. There are bugs in MVC 5 with dropdowns retaining values from other screens and them. I...