Introduction

As a web developer, you should know by now that using the default paging capabilities of ASP.NET Web Controls like DataGrid and GridView is not recommended, simply because with every roundtrip to the data-server, you get all the records all of the time. This is fine perhaps for very small databases (the kind, say, a Microsoft programmer would use to develop a new feature), but in the real, ugly world outside, databases tend to have tables with several ten-thousands of records.

Hence the need for custom paging in a Web Control.

Custom paging various databases

Custom paging poses two problems:

How to get a page of records from a database (I use "a page of records" or "paged records" to indicate a limited recordset, consisting of only those records that are needed to be displayed by the ASP.NET Web Control). Getting these records however depends on the data-server, so you need to know the various methods. This is what this article is about.

The second problem is how to display only the limited recordset in the Web Control, but yet let it automatically show a page-row. With the DataGrid (ASP.NET 1.x), that was rather easy, but with the GridView (ASP.NET 2.0), it's a whole other story, especially when the binding has to be done not at design time, i.e., declaratively, but at run time (programmatically). I did not find anything useful on the Web to solve this problem, so I came up with one of my own. You'll find it in the previous article I wrote (Custom Paging and the GridView ASP.NET 2.0).

However, as far as the first problem is concerned (getting paged records from a database), there are lots of solutions on the web. Most of this article will just point to those solutions and give some examples (using the Northwind sample database).

Let's start with the simplest solution, provided by MySQL.

MySQL

MySQL comes with a LIMIT clause to be used in the SELECT statement.

Example

SELECT * FROM Products LIMIT 0, 10

The first argument denotes the offset from the first record found, and the second argument indicates how many records should be returned. So, the previous example limits the recordset to the first 10 records in the table Products.

To find the next batch, you use "SELECT * FROM Products LIMIT 10, 10", and then LIMIT 20, 10 etc..

SQL Server 2005, SQL Server 2000, SQL Server 7, and MS Access

There are several solutions for paging records if you work with the database-servers listed above. But many of them involve working with temporary tables or cursors, which of course takes a heavy toll, performance-wise.

However, there is one solution that consists only of one SQL-statement, and so is efficient and quick, even with large databases. The good news is: it will work! Always! But on the other hand, it's rather complicated.

Legend

fields = Name of the field(s) you want to display in the Web Control. They should include the primary key field(s) of the table/view (whether it's displayed or not).

x = Rows per page (number of rows you want to have returned).

id_field = Primary key field(s) of the table/view.

sort_field = Fields on which you want to sort the table (optional). Notice however that the sort fields always include the primary key field(s)! This is to avoid ambiguous results.

y = The maximum number of records required. This requires a little calculation: y = pagenumber * records_per_page. So if you want to display the third page, and there are 10 records per page, then y = 3 x 10 = 30. (However, the SQL statement will only return 10 records, but y has to be the maximum number of records required.)

table = Name of the table/view from which to get the recordset.

conditions = Usual WHERE-clauses go here.

foo = Just a name, you can use it or choose another one.

bar = Just a name, idem.

Example

This will give you the first 10 products from the table Products of which the UnitsInStock is less than 200, ordered by ProductName. Fields you want to display in the GridView are ProductID, ProductName, UnitPrice, and UnitsInStock:

How does this work?

1. The innermost SELECT statement

The innermost SELECT statement just gives you the primary key fields (+ optionally the sort fields) of the relevant records (so, the WHERE clause is placed in the innermost SELECT statement).

These records are sorted in ASCending order (see remark 2 if you want it in DESCending order).

Also, the number of rows are limited to the maximum rows required. Assuming that your page contains 10 rows, the number of records required for the first page = 10, the second = 20, the third = 30, etc. This means that if you have a table with 10,000 records, and you want to have the last 10 records, the innermost SELECT statement will indeed retrieve 10,000 records. This, of course, has its ramifications on performance, but since it's only the primary key fields (which are indexed) and optionally some sort fields, the impact will be minimal. Also, the WHERE clause will limit the number of the base recordset.

This recordset is named 'foo'.

2. The middle SELECT statement

From the 'foo' recordset, the middle SELECT statement only selects the records to display. The number of rows to display is set by the TOP x clause (where x = the number of rows to display). It will be the last 'x' records because of the reverse sorting order.

This gives you a recordset, named 'bar'.

This recordset also consists of only two kind of fields: the primary key fields and the sort fields.

3. The outer SELECT statement

In the outer SELECT statement, the primary key field(s) of 'bar' is short-circuited with the primary key field(s) of the source table/view (with the INNER JOIN clause), so now it is possible to retrieve other fields (like UnitPrice and UnitsInStock). Again, this recordset has to be sorted in ascending order.

Conclusion

The SQL statement plays with the sorting orders to limit the records, thus resulting in paged recordsets.

This means that there should be at least one field to be sorted. If there isn't any, sort on the primary key field(s)!

Remarks

When sorting, put the primary key fields after the sorting fields.

If you want to have the results in DESCending order, then you have to change all the ASC keywords to DESC, and all the DESC to ASC, and there you have it!

A problem arises when you come to the end of the table. Say you have 84 records in your table, and you want to have the last page, there should only be four records returned. Unlike the LIMIT statement in MySQL, you have to do the calculation yourself. This means that you need to keep track if the last page is requested, and if so, you need to calculate the number of records returned. This then should be the value of x in the SQL statement.

Share

About the Author

I'm a self-made developer. And after years of writing code, I think I don't suck at everything ! When I stumble on a problem, I can't help but to figure it out. In some cases, I even write an article about it !Also, in my quest to write better code, I have obtained a certificate as UML Professional, and I'm also a Certified ScrumMaster.

Comments and Discussions

This was a reply from Dmeert to a post from Priddy, so you can find it in the thread "Hello", but I thought it deserved to be posted as a new thread.

Hi Erwin,You're absolutely right that Microsoft needed to make an effort to get near the Limit construction. And they have done so in SQLserver 2005 where they introduced Row_number and Commontable expressions (using the With ... As (select...))

This allows you to rewrite the query to this

WITH IDOrdered as(SELECT id_field, R=ROW_NUMBER() OVER (ORDER BY sort_field ASC)WHERE conditionsFROM table),MyPage AS (SELECT id_field FROM IDOrdered WHERE R between Y and Y+10)SELECT t.* FROM TABLE tINNER JOIN MyPage p ON p.id_field=T.id_fieldORDER BY T.id_field ASC

It is slightly more readable (but the Limit syntax still remains far better )I hope this contributes to your topic,Dirk