search

brucezhang80 / dapper-dot-net

Automatically exported from code.google.com/p/dapper-dot-net
Other
0 stars 0 forks source link

Are pagination queries are possible with Dapper? #20

Open GoogleCodeExporter opened 9 years ago

GoogleCodeExporter commented 9 years ago 
This is noway a bug, but rather a question: is pagination on SO is done with 
Dapper as well?

Does it explicitly provide any means for that? Or should one resort to 
low-level t-sql practices?

Original issue reported on code.google.com by estee...@gmail.com on 6 May 2011 at 12:31

GoogleCodeExporter commented 9 years ago 
The trouble with the pagination stuff is that introduces a db dependent layer. 

for sql server you use the nested query row_number trick, in denali we have 
offset which simplifies 

oracle and mysql have offset and limit 

my current recommendation would be for us to ship a contrib library with 
helpers for this, but it will not be in core dapper

Original comment by sam.saff...@gmail.com on 7 May 2011 at 10:37

GoogleCodeExporter commented 9 years ago 
dapper contrib anyone? will need multiple providers for each db connection type

Original comment by sam.saff...@gmail.com on 23 May 2011 at 12:32

GoogleCodeExporter commented 9 years ago 
Here's the code I'm using for MSSQL:

    int pageIndex = 0;
    int pageSize = 2;

    int lower = (pageIndex * pageSize) + 1;
    int upper = (pageIndex * pageSize) + pageSize;

    string query =
        @"
        select * from (select p.*, row_number() over (order by Title) as rownum, count(*) over() as TotalRows 
        from portfolioprojects as p where p.Deleted = 0) seq 
        where seq.rownum between @x and @y";

    var results =
        cn.Query(query, new { x = lower, y = upper });

It would be nice to have a helper for this (for each provider).

Original comment by b...@planetcloud.co.uk on 29 May 2011 at 11:48

GoogleCodeExporter commented 9 years ago

Original comment by sam.saff...@gmail.com on 1 Jun 2011 at 9:06

GoogleCodeExporter commented 9 years ago 
You should checkout https://github.com/TroyGoode/PagedList it's awesome
and works pretty great combined with dapper.

Original comment by peter.su...@gmail.com on 4 Jul 2013 at 1:13