Dapper ORM and Paging and Sorting Extension

Question

I am using Dapper for a Generic DAL that can be used for both Oracle and SQL server. What would be the best way to provide Paging and Sorting methods so that it works both for SQL and Oracle without manually creating/changing the SQL statments? Something like:

var users= Dapper
           .Query<User>(sqlStatment
                        .Skip(10)
                        .Take(10)); // where sqlStatment string

I don't know if you're doing yourself a favor with a generic method. Oracle has ROWNUM while SQL Server has TOP / ROW_NUMBER(). I'd implement a specification for each of the two. — Alex

Robert Koritnik Robert Koritnik · Accepted Answer · 2012-04-03T09:58:05

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.

Dapper ORM and Paging and Sorting Extension

1 Answers