The WebGrid - Efficient Paging And Sorting With SQL CE 4.0

5 (8 votes)

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">
        <meta charset="utf-8" />

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


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";
            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">
            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){
                <a href="/efficientPaging/@i@(query.Length > 0 ? "?" + query : "")">@i</a>

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.

You might also like...

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


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

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



- 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>
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)?

- Mike


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.

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

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

- Mike


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

- Alistair Davison

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

- Mike


You can use the ROW_NUMBER function in SQL Express.

- cogni

is there an equivalent for OFFSET & FETCH in MySQL?

- Mike


Yes, you need to look at OFFSET and LIMIT

- Chandra

It is really good. simple code. Perfectly working. Thanks for your code.

Recent Comments

Satyabrata Mohapatra 23/07/2017 16:43
In response to Razor Pages - The Elevator Pitch
@Dale Severin You can continue to build apps using web pages....

Satyabrata Mohapatra 23/07/2017 16:40
In response to Sending Email in Razor Pages
Thanks for sharing...learned a lot...

Gfw 22/07/2017 11:53
In response to Sending Email in Razor Pages
Question... Does System.Net.Mail support SSL?...

Dale Severin 20/07/2017 03:38
In response to Razor Pages - The Elevator Pitch
I work with razor web pages extensively. I appreciate the rapid development it permits me to I am as...

Obinna Okafor 14/07/2017 01:19
In response to Routing in Razor Pages
Thank you, Mike. Good post....

Satyabrata Mohapatra 11/07/2017 16:02
In response to Routing in Razor Pages
Very powerful routing system!!...

Cyrus 05/07/2017 03:41
In response to Razor Pages - Getting Started With The Preview
How can I trim packages and services as much as possible to use just razor pages? I don’t want to to...

Harris Boyce 04/07/2017 04:17
In response to Razor Pages - The Elevator Pitch
As a developer of a couple "trivial" web pages applications used by non-profits that wouldn't have I...

Cyrus 28/06/2017 20:25
In response to Razor Pages - Getting Started With The Preview
.net core 2.0 preview 2: <a...

ojorma 17/06/2017 09:24
In response to Razor Pages - The Elevator Pitch
Finally I can say goodbye to webforms...