Generic pagination system

I have to develop a layer to retrieve data from a database (can be SQL Server, Oracle or IBM DB2). Queries (which are generic) are written by developers, but i can can modify them in my layer. The tables can be huge (say > 1 000 000 rows), and they are a lot of joins (for example, I have a query with 35 joins - no way to reduce).

So, I have to de开发者_C百科velop a pagination system, to retreive a "page" (say, 50 rows).

The layer (which is in a dll) is for desktop applications.

Important fact : queries are never ordered by ID.

The only way I found is to generate a unique row number (with MSSQL ROW_NUMBER() function) but won't work with Oracle because there are too much joins.

Does anyone know another way ?

There are only two ways to do pagination code.

The first is database specific. Each of those databases have very different best practices with regards to paging through result sets. Which means that your layer is going to have to know what the underlying database is.

The second is to execute the query as is then just send the relevant records up the stream. This has obvious performance issues in that it would require your data layer to essentially grab all the records all of the time.

This is, IMHO, the primary reason why people shouldn't try to write database agnostic code. At the end of the day there are enough differences between RDBMs that it makes sense to have a pluggable data layer architecture which can take advantage of the specific RDBMs it works with.

In short, there is no ANSI standard for this. For example:

MySql uses the LIMIT keyword for paging.
Oracle has ROWNUM which has to be combined with subqueries. (not sure when it was introduced)
SQL Server 2008 has ROW_NUMBER which should be used with a CTE.
SQL Server 2005 had a different (and very complicated) way entirely of paging in a query which required several different procs and a function.
IBM DB2 has rownumber() which also must be implemented as a subquery.

You can do LINQ on your object collection, if you want to do that in the web side.

list.Skip(numPages * itemsPerPage).Take(itemsPerPage)

Lets you skip to the specified page (aka numPages = 0 is page 1).