Paging with Oracle and SQL Server and Generic Paging Method

How to implement paging mechanism in generic way for SQL standards?

As far as I know there is no generic functionality to implement the pagining mechanism for the all the database.

The syntax to implement the pagination may also change with the database, so it is hard to say that there is a genric functionality to implement it across all the database.

You can refer There are a method to paging using ANSI Sql only? where the accepted answer refers to a link which says to use it like

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownum,
    columns
  FROM tablename
) AS foo
WHERE rownum > skip AND rownum <= (n+skip)

Generic pagination system

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.

Dapper ORM and Paging and Sorting Extension

As @Alex pointed out, paging is done differently on the two databases of your choice so your best best for having most optimized queries is to write separate queries for each.

It would probably be best to create two data provider assemblies each one serving each database:

• Data.Provider.Sql
• Data.Provider.Oracle

And then configure your application for one of the two. I've deliberately also created Data.Provider namespace (which can be part of some Data assembly and defines all data provider interfaces (within Data.Provider) that upper couple of providers implement.

There are a method to paging using ANSI SQL only?

See Limit—with offset section on this page: http://troels.arvin.dk/db/rdbms/

BTW, Firebird also supports ROWS clause since version 2.0

Generic pagination with the dapper

It looks currently as though you are planning to retrieve all of the rows in the table and then select from them the page of data you actually require. It would likely be quicker to just select the page you need straight from the database, unless you do actually need all of the rows for some reason.

Assuming that your table names are always going to match exactly with their respective class/entity names, the following will give you a paged result (using postgres):

public class GenericRepository<T> : IGenericRepository<T> where T : class
        {
        public async Task<IEnumerable<T>> GetAllPagedAsync(int limit, int offset)
            {
                var tableName = typeof(T).Name;             
                // assuming here you want the newest rows first, and column name is "created_date"
                // may also wish to specify the exact columns needed, rather than *
                var query = "SELECT * FROM @TableName ORDER BY created_date DESC Limit @Limit Offset @Offset";
                var results = Connection.QueryAsync<T>(query, new {Limit = limit, Offset = offset});
                return results;
            }
    }

A note regarding this. I am obviously not familiar with the structure or size of your database, however for most general purposes the limit/offset approach to paging shown here will most probably be sufficient. There are however some potential issues you may wish to consider:

1. When the offset value gets very large performance may suffer.
2. Paging tables with a high frequency of inserts in this fashion may cause results to be duplicated/ appear on multiple pages as the offset values does not take into account new rows added to the table since the last retrieval.

Whether or not these are likely to cause issues to your particular case, these potential drawbacks, as well as some alternatives solutions are outlined here.

What's the best method to do paging in my ASP page

Whats the best method to do paging in my ASP page when displaying a list of items ?

I just want to add one more feature to Mr. Wheat's answer. Why not u are trying to use the Take () and Skip() feature of linq(obviously if u are using dotnet framework 3.5+)

It is indeed helpful while working with large datasets.

Have a look
Using Take and Skip method in LINQ queries

I knew that there is no MySQL LIMIT clause present in SQL server(both 2000 and 2005 should support).how can i retrieve some particular records (Record 20 -30) from DB ?

You can do this in SQLSERVER 2005+ by using ranking function Row_Number() among other alternatives. A sample example is included herewith

First I am creating a dummy table and inserting some 50 records

declare @tbl table(name varchar(50),age int)
;with num_cte as
(   select 1 as rn
    union all
    select rn+1 from num_cte where rn<50
)  
insert @tbl
select  names ,rn + 20 ageval                
from num_cte
cross apply( select  'name' + CAST(rn as varchar(2))  AS names) names
select * from @tbl

Now by using the Row_Number() function I am picking up records between 20 & 30

select name,age from(
select ROW_NUMBER()over (order by age) as rownum,name,age from @tbl) X
where X.rownum between 20 and 30

However, for achieving the same in SQL SERVER 2000 the below query will help

select name,age from(
select t1.name,t1.age,
(select count(*)+1 from @tbl where name<>t1.name and age<=t1.age) rownum
from @tbl t1
)X(name,age,rownum)
where rownum between 20 and 30

Pagination with the stored procedure

One way (possibly not the best) to do it is to use dynamic SQL

CREATE PROCEDURE [sp_Mk]
 @page INT,
 @size INT,
 @sort nvarchar(50) ,
 @totalrow INT  OUTPUT
AS
BEGIN
    DECLARE @offset INT
    DECLARE @newsize INT
    DECLARE @sql NVARCHAR(MAX)

    IF(@page=0)
      BEGIN
        SET @offset = @page
        SET @newsize = @size
       END
    ELSE 
      BEGIN
        SET @offset = @page*@size
        SET @newsize = @size-1
      END
    SET NOCOUNT ON
    SET @sql = '
     WITH OrderedSet AS
    (
      SELECT *, ROW_NUMBER() OVER (ORDER BY ' + @sort + ') AS ''Index''
      FROM [dbo].[Mk] 
    )
   SELECT * FROM OrderedSet WHERE [Index] BETWEEN ' + CONVERT(NVARCHAR(12), @offset) + ' AND ' + CONVERT(NVARCHAR(12), (@offset + @newsize)) 
   EXECUTE (@sql)
   SET @totalrow = (SELECT COUNT(*) FROM [Mk])
END

Here is SQLFiddle demo

---

Related Topics