Web pages-efficient Paging without the WebGrid

Web pages-efficient Paging without the WebGrid

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

Actually, there is another problem with paging within the WebGrid helper apart from being restricted to HTML tables, an D that's that ' s it's not particularly efficient out of the box. If you have a rows of data, and you want to display the rows per page, all of the rows is retrieved from the database For each page. The pager works out how to only show the current, 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 and you should already know that I had 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 is 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 had removed all the default markup so, which 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 what many records per page I want to display. I ' ve set it at 3. TotalPages'll be used to calculate how many pages of data there is in the database that match the selection criteria. Count would is used to store the total number of matching records that match the selection criteria. Page is used to identify. It would actually obtain its value from the URL of the page. I ' ve decided to take advantage of the built in routing, the comes with Web Pages, but more than that a bit later. The current page defaults to 1 if No. value is present. Finally, offset'll is used to specify how many rows of data within the database should be ignored before the current "PA GE "of data is retrieved. The calculation for offset ' s value is actually quite straightforward. If the current page is 1, offset would equal 1-1 * 3. Since that results in zero, no rows of data would be skipped. If The current page was determined to be 3, offsetwould equal 3-1 * 3 (or 6). In the 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 is displayed, first. This'll be used to calulcate the totalpages.

var db = Database.open ("Books"), var sql = "Select Count (*) from Books" +            "Inner Join Authors on books.authorid = Auth Ors. Authorid "+            " Inner Join Categories on books.categoryid = Categories.CategoryID "; count = (int) db. QueryValue (sql); totalpages = count/pagesize;        if (count% PageSize > 0) {    totalpages + = 1;}

If you have want a single value returned from the database query, Database.queryvalue () is the method of should be used. It returns something of type Object, which needs to is cast to the correct type (in this case a 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 was the total number of full pages of 3 records available. The modulus operator (%) is also used here to calculate whether there be still records left over after the previous divi Sion because it rounds down. If there is, another page is added to TotalPages to take account of these extra records.

Now we had all the elements required to calculate the pages of the data needed, and which page the user was on. All that's needed is a by-pass to passes this information to the SQL query which are responsible for getting the current page of Data

sql = "Select Title, ISBN, Description, FirstName, LastName, Category from Books" +            "Inner Join Authors on Books.auth Orid = Authors.authorid "+            " Inner Join Categories on Books.categoryid = Categories.CategoryID ' +           ' Order by Booki D OFFSET @0 rows FETCH NEXT @1 rows only  ; "; var result = db. Query (SQL, offset, pageSize);

This query shows the OFFSET and FETCH keywords. As I alluded to earlier, OFFSET was the starting point at which records should was retrieved, and FETCH dictates how many sh Ould be retrieved. OFFSET and FETCH need an ORDER BY clause to is 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 being passed in dynamically, and is the preferred-pass in variable of values to a SQL query.

I mentioned earlier the current page was calculated from the URL, and that I had taken advantage of the inbuilt Suppo RT that for Routing this comes with Web Pages. Now, the time to examine, in a bit more detail. Routing is a mechnism whereby URLS was 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 would map to www.mysite.com/Paging.cshtml. Equally, WWW.MYSITE.COM/PAGING/3 can used to request page 3 of the data. THE/3 part of the URL is available within Urldata, and which is a zero based collection. Referencing the element within the collection by it index is done by putting the index in square brackets:urldata[Indexvalue]. So urldata[0] In this case would return the current page number from the address in the browser. So, if we run the query has 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 is to is displayed, but on the top of this page, I had also added a bit of CO De which shows the user which page they is currently on, and how many pages there is in total:

<p>page @page of @totalPages </p> @foreach (var row in result) {   
Finally, we need some paging links. These'll be responsible for ensuring, the correct page number appears in the URL so that the user can navigate the R Ecords successfully.
@{for    (var i = 1; i < totalpages + 1; 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'll see the first 3 records in the Databas e appear, and you can also see that the URL in the browser address bar is paging.cshtml.
If you click on the second paging link, you should see this 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.
 Web pages-efficient Paging without the WebGrid