Web Pages - Efficient Paging Without The WebGrid

If you want to display your data over a number of pages using WebMatrix Beta1, you have two options. One is to use the built-in paging support that comes with the WebGrid helper. But that means that your data will be displayed within an HTML table. If that is not your preferred layout choice, you need to write your own paging code. Let's look at how you can do that.

Actually, there is another problem with paging within the WebGrid helper apart from being restricted to html tables, and that is that's it's not particularly efficient 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 pager 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. Sql Server CE 4.0 offers OFFSET and FETCH, which enable paging queries.

I'm going to add a page to the Books application I introduced in my first look at WebMatrix. I've called it Paging.cshtml. If you followed the second of my WebMatrix articles, you should already know that I have fiddled around with the original download and added some support for layout pages and so on to create a consistent look and feel. So if you are using the download as a basis to add paging functionality, you may want to quickly look at what I described in the second article, or just go to the bottom of this article to get the updated download. Back to Paging.cshtml, I have removed all the default markup so that this page becomes a content page. At the top of the page, I declare a number of variables:

var pageSize = 3; 
var totalPages = 0; 
var count = 0;
var page = UrlData[0].AsInt(1);
var offset = (page -1) * pageSize;

The first variable is pageSize. This dictates how many records per page I want to display. I've set it at 3. totalPages will be used to calculate how many pages of data there are in the database that match the selection criteria. count will be used to store the total number of matching records that match the selection criteria. page is used to identify what the current page is. It will actually obtain its value from the URL of the page. I've decided to take advantage of the built in routing support that comes with Web Pages, but more of that a bit later. The current page defaults to 1 if no value is present. Finally, offset will be used to specify how many rows of data within the database should be ignored before the current "page" of data is retrieved. The calculation for offset's value is actually quite straightforward. If the current page is 1, offset will equal 1 - 1 * 3. Since that results in zero, no rows of data will be skipped. If the current page is determined to be 3, offset will equal 3 - 1 * 3 (or 6). In other words, we want to ignore rows 1 - 6 and show rows 7, 8 and 9 on page 3.

We need to get the total number of records that can be displayed, first. This will be used to calulcate the totalPages.

var db = Database.Open("Books");
var sql = "Select Count(*) From Books " + 
           "Inner Join Authors on Books.AuthorId = Authors.AuthorId " + 
           "Inner Join Categories on Books.CategoryId = Categories.CategoryId";
count = (int)db.QueryValue(sql);
totalPages = (int)Math.Ceiling((double)count/pageSize);        

If you only want a single value returned from the database query, Database.QueryValue() is the method that should be used. It returns something of type object, which needs to be cast to the correct type (in this case an int) for you to be able to work with it. The code above simply returns a number, containing the COUNT of all matching rows in the database. That's divided by the number of records per page, and the result is the total number of full pages of 3 records available. The modulus operator (%) is also used here to calculate whether there are still records left over after the previous division because it rounds down. If there are, another page is added to totalPages to take account of these extra records.

Now we have all the elements required to calculate the pages of data needed, and which page the user is on. All that's needed is a way to pass this information to the SQL query which is responsible for getting the current page of data:

sql = "Select Title, ISBN, Description, FirstName, LastName, Category From Books " + 
           "Inner Join Authors on Books.AuthorId = Authors.AuthorId " + 
           "Inner Join Categories on Books.CategoryId = Categories.CategoryId " +
           "Order By BookId OFFSET @0 ROWS FETCH NEXT @1 ROWS  ONLY;";

var result = db.Query(sql, offset, pageSize);

This query shows the OFFSET and FETCH keywords in use. As I alluded to earlier, OFFSET is the starting point at which records should be retrieved, and FETCH dictates how many should be retrieved. OFFSET and FETCH need an ORDER BY clause to be able to work, so I decided to order the results by the BookId value. @0 and @1 are parameter markers. This allows for values to be passed in dynamically, and is the preferred way to pass in variable values to a SQL query.

I mentioned earlier that the current page is calculated from the URL, and that I have taken advantage of the inbuilt support that for Routing that comes with Web Pages. Now is the time to examine that in a bit more detail. Routing is a mechnism whereby URLS are mapped to resources on the web server. The whole topic deserves more explanation than I am going to give here, but fundamentally, a URL of www.mysite.com/Paging will map to www.mysite.com/Paging.cshtml. Equally, www.mysite.com/Paging/3 can be used to request page 3 of the data. The /3 part of the url is available within UrlData, which is a zero based collection. Referencing the element within the collection by its index is done by putting the index in square brackets: UrlData[indexvalue]. So UrlData[0] in this case will return the current page number from the address in the browser. So, if we run the query having requested page 3. it translates to "Get me these records, offsetting the first 6 rows, and only fetch the next 3 rows".

The next bit of code shows how the records are to be displayed, but at the top of this page, I have also added a bit of code which shows the user which page they are currently on, and how many pages there are in total:

<p>Page @page of @totalPages</p>

@foreach(var row in result){
   <h2>@row.Title</h2>
   <p><strong>Author:</strong> @row.FirstName @row.LastName<br />
   <strong>ISBN:</strong> @row.ISBN <br/>
   <strong>Description:</strong> @row.Description <br />
   <strong>Category: </strong> @row.Category</p>
}

Finally, we need some paging links. These will be responsible for ensuring that the correct page number appears in the URL so that the user can navigate the records successfully.

 @{
    for (var i = 1; i <= totalPages; i++){
         <a href="/Paging/@i">@i</a>
    }   
}

This code sets up a counter to loop through from 1 to the total number of pages, and writes a link for each page of data. When you first run the page, by hitting F12 or clicking the Launch button, you will see the first 3 records in the database appear, and you can also see that the URL in the browser address bar is Paging.cshtml.

If you click the second paging link, you should see that change to Paging/2, and routing come into play.

Download the code from here. To run it, simply unzip the folder, and then use the Create Site From Folder option in WebMatrix.

 

Date Posted: Saturday, July 17, 2010 10:48 AM
Last Updated: Wednesday, November 5, 2014 2:05 PM
Posted by: Mikesdotnetting
Total Views to date: 27463

12 Comments

Wednesday, August 11, 2010 9:32 PM - dotnetcoder

This is so much easier to implement than the ROW_NUMBER() OVER method in TSQL. Thanks Mike, this article is worth gold!

Saturday, March 17, 2012 1:32 PM - cusman

I find your article-tutorials to be one of the best resources in terms of useful information on how to use WebMatrix. Greatly appreciated.

Sunday, December 30, 2012 2:15 AM - Robert

everything is ok, but when I clicked on the other side of this page loses CSS style sheet, why?

Tuesday, January 1, 2013 8:17 PM - Mike

@Robert,

I don't entirely understand what you are saying, but if it is anything to do with the article's sample site, I can't replicate anything similar to what you describe. Perhaps you could provide some more detail?

Thursday, May 30, 2013 4:30 PM - VoidKing

You seem to have a knack for writing easily understandable and highly useful articles on the best practices of using WebMatrix as a single developer. I am very grateful for this!

I do have a question concerning this article, however.

I have a site that is in desperate need of pagination like your examples above, however, as it stands I have a standard print button that prints (after being re-styled by a print.css file) all the results on the page. If I use pagination, not all results will show up on the page, so not all results can get printed with one click.

Is there an easy way to set up both?

Thursday, May 30, 2013 7:01 PM - Mike

@Voidking

If people hit Ctrl + P then the browser will print whatever it contains. If that consists of paged results, that's what gets printed. You could perhaps intercept that with Javascript and provide an alternative version of the page with no pagination and print that, perhaps.

Friday, July 26, 2013 11:17 AM - Geert Kamphuis

I am using Webmatrix for accessing an mdb-database, usung Oledb.
The following code gives the correct results:

var myQuery = "SELECT * FROM HC WHERE Bruidegom LIKE ? AND Bruid LIKE ?" +
" AND Regest LIKE ? AND Bron LIKE ? " +
" AND Fonds LIKE ? AND Datum > ? AND Datum < ?" +
" ORDER BY Datum";

OleDbCommand thisCommand = new OleDbCommand(myQuery);
thisCommand.Parameters.AddWithValue("?",zman);
thisCommand.Parameters.AddWithValue("?",zvrouw);
thisCommand.Parameters.AddWithValue("?",ztekst);
thisCommand.Parameters.AddWithValue("?",zbron);
thisCommand.Parameters.AddWithValue("?",zarchief);
thisCommand.Parameters.AddWithValue("?",zldatum);
thisCommand.Parameters.AddWithValue("?",zhdatum);

thisCommand.Connection = thisConnection;
OleDbDataAdapter HCadapter = new OleDbDataAdapter();
HCadapter.SelectCommand = thisCommand;
DataSet HCdataset = new DataSet();
HCadapter.Fill(HCdataset,"HC");
After enhancing the query with your paging solution:
var myQuery = "SELECT * FROM HC WHERE Bruidegom LIKE ? AND Bruid LIKE ?" +
" AND Regest LIKE ? AND Bron LIKE ? " +
" AND Fonds LIKE ? AND Datum > ? AND Datum < ?" +
" ORDER BY Datum OFFSET ? ROWS FETCH NEXT ? ROWS ONLY";

OleDbCommand thisCommand = new OleDbCommand(myQuery);
thisCommand.Parameters.AddWithValue("?",zman);
thisCommand.Parameters.AddWithValue("?",zvrouw);
thisCommand.Parameters.AddWithValue("?",ztekst);
thisCommand.Parameters.AddWithValue("?",zbron);
thisCommand.Parameters.AddWithValue("?",zarchief);
thisCommand.Parameters.AddWithValue("?",zldatum);
thisCommand.Parameters.AddWithValue("?",zhdatum);
thisCommand.Parameters.AddWithValue("?",rowoffs);
thisCommand.Parameters.AddWithValue("?",pagSize);

thisCommand.Connection = thisConnection;
OleDbDataAdapter HCadapter = new OleDbDataAdapter();
HCadapter.SelectCommand = thisCommand;
DataSet HCdataset = new DataSet();
HCadapter.Fill(HCdataset,"HC");

there is a runtime error caused by HCadapter.Fill(HCdataset,"HC");

Although I realize this is well outside the scope of “Beginning ASP.NET Web Pages with WebMatrix”, which I acquired last week, I woul nevertheless very much appreciate if you coul help me solve this problem.

Regards,

Geert Kamphuis

Friday, July 26, 2013 11:42 AM - Geert Kamphuis

Ref my earlier comment:
Forgot to mention the error message:
IErrorInfo.GetDescription failed with E_FAIL(0x80004005).
This error message is often cause by a naming conflict in the sql statement, but using constant number in the statement instead of placeholders causes the same message.

regards

Geert Kamphuis

Friday, July 26, 2013 11:49 AM - Mike

@Geert,

Jet databases (mdb files) do not support OFFSET and FETCH. In fact, they offer no paging mechanism at all.

Saturday, October 19, 2013 6:11 PM - Ross

Just in case it helps anyone, I modified the page links to highlight the page you're on to make navigation a little easier on the eye:


@{
for (var i = 1; i < totalPages + 1; i++){
if (@page!=@i){
<a href="/Articles/@i" class="pagelink">@i</a>
}else{
<div class="pagelink-on">@i</div>
}
}


Thanks for the help Mike, excellent code.

Friday, November 14, 2014 10:16 AM - Gautam

to get the count can we use only the below sql, why to join category and author table
var sql = Select Count(*) From Books

Friday, November 14, 2014 1:25 PM - Mike

@Gautam

Your SQL will give you the total for all books in the database, which may well be what you want. However, I only want to show books that have a category and an author, so my SQL gets the total of books that have a category and an author.
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...