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: Tuesday, November 20, 2007 10:54 PM
Last Updated: Saturday, May 22, 2010 7:49 PM
Posted by: Mikesdotnetting
Total Views to date: 45424

15 Comments

Wednesday, March 4, 2009 2:24 PM - skyhook

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

Wednesday, March 4, 2009 9:02 PM - 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.

Saturday, March 7, 2009 5:06 PM - 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

Saturday, March 7, 2009 5:14 PM - Mike

@Andy

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

Wednesday, July 22, 2009 7:45 AM - Piyush

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

Wednesday, July 22, 2009 11:43 AM - Nilkanth

thanks

Sunday, August 30, 2009 12:55 PM - raj

dear !

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

Tuesday, November 3, 2009 12:47 PM - 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.

Thursday, December 24, 2009 7:14 AM - Jeevan Baby

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

Thursday, December 24, 2009 7:44 AM - Mike

@Jeevan

I have no idea what you are talking about.

Tuesday, January 26, 2010 6:12 PM - Tomy

Great! many thanks

Wednesday, May 12, 2010 7:41 PM - Dung

What is Command !

Thursday, May 13, 2010 7:16 PM - Mike

@Dung

Asked and answered in the first two comments.

Monday, August 9, 2010 6:18 PM - 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!

Wednesday, August 11, 2010 6:30 PM - 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 some may not be published. The kind of things that will ensure your comment is deleted without ever seeing the light of day are as follows:

  • Not relevant to the article
  • Gratuitous links to your own site or product
  • Anything abusive or libellous
  • Spam
  • Anything in a language I don't understand including gibberish.

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