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.
