The WebGrid - Efficient Paging And Sorting With SQL CE 4.0

A problem inherent in the existing version of the WebGrid is that it is not very efficient when it comes to paging data out of the box. If you have 10,000 rows of data, and you want to display 10 rows per page, all 10,000 rows are retrieved from the database for each page. The grid works out how to only show the current 10, and wastes the other 9,990 rows. That's a fair sized overhead on each page. Ideally, you should only retrieve the data you need for each page. This article examines how you can do that with the existing WebGrid and a SQL CE database, while still maintaining sorting capabilities.

Like other articles in this series, the sample code makes use of a SQL CE 4.0 version of the Northwind database. It is available as part of the download that accompanies this article, a link to which is provided at the end. The sample also makes use of the same layout page as other samples, which references jQuery, and includes a RenderSection call to an optional section called "script":

<!DOCTYPE html>

<html lang="en">
    <head>
        <meta charset="utf-8" />

        <title>@Page.Title</title>
        <script src="@Href("~/scripts/jquery-1.6.2.min.js")" type="text/javascript"></script>

        <link href="@Href("~/styles/site.css")" rel="stylesheet" />
        @RenderSection("script", required: false)
    </head>

    <body>
        @RenderBody()
    </body>
</html>

The key to efficient paging with SQL CE 4.0 is the introduction of the OFFSET and FETCH syntax, which enables you to specify how many records you want to retrieve (FETCH) and where the starting point is (OFFSET). For example, if you wanted to retrieve records 21 - 30 of a result (page 3 if there are 10 records per page), the syntax would look something like this:

SELECT col1, col2, col3, ..., coln FROM table ORDER BY Id OFFSET 20 FETCH NEXT 10 ROWS ONLY

Only 10 records are returned from the database, which is a lot more efficient than fetching all that match the criteria. The paging links on a WebGrid are generated from the actual data returned, so although you have paging and sorting enabled, no paging links appear because the grid can only "see" the first page of data, and thinks that is all there is. To get round this side effect, you have to implement your own paging, which isn't difficult to do. Here is the code that sets up the variables that are used to enable custom paging:

@{
    Page.Title = "Efficient Paging And Sorting";
    
    var pageSize = 10; 
    var totalPages = 0; 
    var count = 0;
    var page = UrlData[0].IsInt() ? UrlData[0].AsInt() : 1;
    var offset = (page -1) * pageSize;
    var db = Database.Open("Northwind");
    var sql = "SELECT COUNT (*) FROM Customers";
    count = db.QueryValue(sql);
    totalPages = count/pageSize;        
    if(count % pageSize > 0){
        totalPages += 1;
    }

At this point, the number of records per page has been decided (pageSize) at 10. Variables have been set up to hold the total number of pages that the data will require (totalPages) and the number of records in total (count). This is populated by a SELECT COUNT(*) query.The current page number is stored in the page variable. The value for this is obtained form UrlData. If there is no UrlData, the user must have just requested the page for the first time, so the page number is assumed to be 1. The offset value - the number of records to skip before taking any - is calculated by multiplying the current page number - 1 by 10. If the current page is 1, the result of multiplying that minus 1 by anything is zero, which is exactly how many records you want to skip - none. The total number of pages required is calculated by dividing the total number of records by the records per page (pageSize). If there is any remainder from the calculation, the total number of pages is incremented by one.

It really isn't as complicated as it first looks. Now onto the rest of the code block at the top of the page:

    var query = Request.Url.GetComponents(UriComponents.Query, UriFormat.Unescaped);
    var columns = new[]{"CustomerID", "CompanyName", "ContactName", "Address", "City", "Country", "Phone"};
    var orderBy = "CustomerID";
    if(!Request["sort"].IsEmpty()){
        if(columns.Contains(Request["sort"])){
            orderBy = Request["sort"];
            if(Request["sortdir"] == "DESC"){
                orderBy += " DESC"; 
            }
        }
    }
    sql = "SELECT * FROM Customers Order By " + orderBy + " OFFSET @0 ROWS FETCH NEXT @1 ROWS ONLY;";
    var data = db.Query(sql, offset, pageSize);
    var grid = new WebGrid(data, columnNames: columns);
    
}

This section declares a few more variables. The first is called query, and holds any query string that comes as part of the current URL. This is important for maintaining sorting functionality while paging. The next variable is an array of strings holding the column names that will be used in the WebGrid. It also serves another purpose, and that is to help validate sorting values taken from the query string. OFFSET and FETCH requires an ORDER BY clause. Initially, the data will be ordered by the CustomerID value, but from then on, it will be ordered by any column that the user has chosen to sort by. That means that the column to order by in the SQL needs to be dynamic. Seeing values concatenated into SQL strings should cause you to have the jitters due to potential SQL injection issues, but in this case you have a very defined list to validate the value by. If there is nothing in Request["sort"] (no query string value), the CustomerID value stands. Otherwise, the code checks first to ensure that whatever is in Request["sort"] is also in the list of columns that you created. If it is, it is assumed to be valid, and the value is passed into the ORDER BY clause. The code also checks to see the direction of the sort, and accommodates that as well.

The next part sets up the SQL, with the ORDER BY clause sorted (excuse the pun), and then executes the SQL against the database, using the calculated offset value to decide the point at which records will be picked from. The number of records to be picked is passed as a parameter value too, although it could have been hard coded into the SQL. However, you may decide to include a select list which allows the user to define their own pageSize value. Finally, the data is obtained and passed to the WebGrid.

<h1>Efficient Paging And Sorting</h1>
<div id="grid">
    <div id="table">
        @grid.GetHtml(    
            tableStyle : "table",
            alternatingRowStyle : "alternate",
            headerStyle : "header"
        )

There's not really much to see here, so we will move along to the paging:

    
        @for (var i = 1; i <= totalPages; i++){
            if(i == page){
                 @i
            }else{
                <a href="/efficientPaging/@i@(query.Length > 0 ? "?" + query : "")">@i</a>
            }
        }
    </div>
</div>

This little bit of code sets up a loop that iterates for as many pages of data exist. Starting at 1, if the loop counter is equal to the current page, just the number is rendered to the browser. Otherwise the code creates a link that includes the page number as UrlData, and any existing query string to maintain sorting data.

The pager code is simple for the purposes of this sample, but it should really be created as a helper. That way you can reuse it. You could also increase its complexity such as allowing first and last text, and specifying the number of links that will be rendered. At the moment, all pages are included in the links, which will get untidy if there are a lot of them. Perhaps a Paging helper will feature in a forthcoming article...

In the meantime, the code for this article as available as a GitHub repo.

Date Posted: Sunday, August 28, 2011 10:46 PM
Last Updated: Monday, December 17, 2012 10:12 PM
Posted by: Mikesdotnetting
Total Views to date: 15675

9 Comments

Monday, August 29, 2011 5:39 AM - Mike

Thanks again for another in a great line of articles, Mike. This site is fast becoming "the" goto site for authoritative articles on WebMatrix. Thanks for your continued efforts and help - both here and on the asp.net WebMatrix forum.

I look foreward to the possibility of an article about a paging helper.

Cheers

Mike

Monday, August 29, 2011 8:12 AM - reav

as always, good article, Mike!

For Pager helper, i highly recommend to use
<tfoot><ul class="pagination">
<li><a href="link"></a>
<li><a href="#" class="disabled"></a>
<li><a href="link"></a>
</ul></tfoot>
style of formatting, because of much easier css styling.

Maybe you will make helper to replace WebGrid? With total control over how data and table is displayed (maybe with some templates, provided into constructor or something like that)?

Monday, August 29, 2011 9:59 AM - Mike

@reav,

The problem with adding a tfoot is that your HTML won't validate. A tfoot must appear before the tbody, and they must both appear within a table element.

http://www.w3.org/TR/html4/struct/tables.html#h-11.2.3

I don't think there is too much wrong with the current WebGrid helper - at least not much you can't fix with jQuery.

Monday, August 29, 2011 3:57 PM - reav

yeah, and when i asked about how to modify some html with jquery on stackoverflow, my quesion got -30 in five minutes, with only one comment like "emit correct html noob"

Monday, August 29, 2011 6:46 PM - Mike

@reav,

That's stackoverflow for you - the new home for the Usenet Egos.

Wednesday, October 24, 2012 1:21 PM - Alistair Davison

OFFSET & FETCH only work with SQL CE, is there a way to adapt the code to work with SQL Express?

Wednesday, October 24, 2012 1:38 PM - Mike

@Alistair,

You can use the ROW_NUMBER function in SQL Express.

http://msdn.microsoft.com/en-us/library/ms186734.aspx

Monday, December 16, 2013 6:07 AM - cogni

is there an equivalent for OFFSET & FETCH in MySQL?

Monday, December 16, 2013 10:30 AM - Mike

@cogni,

Yes, you need to look at OFFSET and LIMIT
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.

Recent Comments

Allen Michaels 12/17/2014 4:37 PM
In response to Cascading DropDownLists with jQuery and ASP.NET
Fantastic thank you so much!...

Emily 12/17/2014 12:36 PM
In response to Parameterized IN clauses with ADO.NET and LINQ
Thanks, very helpful!!!! Can i use this for multiple in's ????? IN (.....) and IN(...) and IN...

sss 12/16/2014 3:06 PM
In response to Solving the Operation Must Use An Updateable Query error
good...

Gjuro 12/15/2014 10:30 PM
In response to Examining the Edit Methods and Edit View
You have one fromr (and it should be from, I suppose). :-)...

Gjuro 12/15/2014 10:27 PM
In response to Adding Search
Hi, thnx for all this C#->VB translations. Yet, the following code block is (slightly) in error it a...

Scot 12/14/2014 1:39 PM
In response to Entity Framework 6 Recipe - Alphabetical Paging In ASP.NET MVC
Thanks,Mike I found solution....

Gjuro 12/13/2014 10:52 PM
In response to Accessing Your Model's Data from a Controller
The article mentions "Creating an Entity Framework Data Model for an ASP.NET MVC Application" (at is...

Samuel 12/13/2014 8:40 AM
In response to Displaying The First n Characters Of Text
I have failed to use the extension because it throws an error that it doesn't recognise the chop be...

Ignas 12/12/2014 5:11 PM
In response to Cleaner Conditional HTML Attributes In Razor Web Pages
Any suggestions for Html Helper elements with HtmlAttributes, when you need to conditionally set it...

Gautam 12/11/2014 8:50 PM
In response to Validation In Razor Web Pages 2
Hi Mike Is this required for V3, non html helper input...