Dapper. Paging

Question

I am trying Dapper ORM and I am querying a a Posts table.

But I would like to get paged results ...

1 - How can I do this? Isn't there a helper for this?

2 - Can Dapper Query return an IQueryable?

Thank You, Miguel

Answer 1

You didn't specify a database or version. If you're lucky enough to be able to use the brand new SQL Server 2012 and have access to MSDN, you can use the shiny new OFFSET and FETCH keywords. The following query will skip 20 records and return the next 5.

SELECT * FROM [Posts]
ORDER BY [InsertDate]
OFFSET 20 ROWS
FETCH NEXT 5 ROWS ONLY

Check out http://msdn.microsoft.com/en-us/library/ms188385(v=sql.110).aspx#Offset for more info.

Also, it's easy enough to copy the way Massive does it and write your own extension method for IDbConnection. Here's Massive's code.

var query = string.Format("SELECT {0} FROM (SELECT ROW_NUMBER() OVER (ORDER BY {2}) AS Row, {0} FROM {3} {4}) AS Paged ", columns, pageSize, orderBy, TableName, where);

Answer 2

If you do not have Sql Server 2012 or you have other DBMS, one way to do paging is to split the processing between the DBMS and the web server or client. ---this is recommended only for small set size. You can use the 'TOP' keyword in Sql Server or LIMIT in MySql or ROWNUM in Oracle to get the top number of rows in the data set. The number of rows that you would fetch is equals to the number that you would skip plus the number that you would take:

top = skip + take;

for instance, you would want to skip 100 rows and take the next 50:

top = 100 + 50

So your SQL statement would look like this (SQL server flavor)

SELECT    TOP 150 Name, Modified, content, Created
FROM      Posts
WHERE     Created >= '1900-01-01'

On the Client: if you are using a .NET language like C# and using Dapper, you can use linq to skip a number of rows and take a number of rows like so:

var posts = connection.Query<Post>(sqlStatement, dynamicParameters);
return posts?.ToList().Skip(skipValue).Take(takeValue);

Answer 3

1) Dapper doesn't have a built-in pagination feature. But its not too hard to implement it directly in the query. Example:

SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY InsertDate) AS RowNum, *
          FROM      Posts
          WHERE     InsertDate >= '1900-01-01'
        ) AS result
WHERE   RowNum >= 1 // *your pagination parameters
    AND RowNum < 20  //*
ORDER BY RowNum

Requires SQL Server 2005+

2) Dapper returns an IEnumerable<T>.

Answer 4

I created a sample project to demo the Dapper custom paging, support sorting, criteria and filter:

https://github.com/jinweijie/Dapper.PagingSample

Basically, the method looks like this:

 Tuple<IEnumerable<Log>, int> Find(LogSearchCriteria criteria
        , int pageIndex
        , int pageSize
        , string[] asc
        , string[] desc);

The first return value is the item list. The second return value is the total count.

Hope it helps.

Thanks.