How to use Dapper for paging and sorting?

dapper orm vb.net

Question

I'm trying out the Dapper ORM. Using the code below, I can query data from a table:

Dim comments As List(Of Comment)
Using conn = New SqlConnection(ConnectionString)
    conn.Open()
    comments = conn.Query(Of Comment)("SELECT * from comments where userid = @commentid", New With {.userid= 1})
End Using

Return View(comments)

I'd want to learn how to use Dapper for paging and sorting. To assist with this, EF provides the commands "skip" and "take." I realize that a micro ORM does not have this capability, but I'd want to know how to go about doing it.

1
16
5/7/2011 1:43:48 AM

Accepted Answer

T-SQL is used to do this. if you wish to skip and take with Dapper.

SELECT *
FROM
(
SELECT tbl.*, ROW_NUMBER() OVER (ORDER BY ID) rownum
FROM comments as tbl
) seq
 WHERE seq.rownum BETWEEN @x AND @y
 AND userid = @commentid
 ORDER BY seq.rownum
19
5/23/2017 12:06:56 PM

Popular Answer

You may start right now.
All you have to do is build an extension method that accepts Query, PageSize, and PageNumber, and then add the results.

OFFSET @PageSize * (@PageNumber - 1) ROWS FETCH NEXT @PageSize ROWS ONLY;

Add a line to the query and run it.
(at least in T-SQL). It's worth noting that the query needs an ORDER-BY clause.
MS-SQL (2012+), PostgreSQL (8.4+), and Oracle (12c+) are all supported.
You'd have to add LIMIT offset, page size to MySQL.

LIMIT @PageSize * (@PageNumber - 1), @PageSize 

You'd have to add ROWS x TO y in Firebird.

ROWS (@PageSize * (@PageNumber - 1)) TO (@PageSize * @PageNumber -1) 

It would be from startoffset base1 to endoffset base1 for a base-1 index.

StartAt @PageSize * (pagenum - 1) + 1 EndAt @PageSize * (pagenum - 1) + @PageSize

Example:

DECLARE @PageSize int 
DECLARE @PageNumber int 
SET @PageSize = 5
SET @PageNumber = 2

SELECT * FROM T_Users
ORDER BY USR_ID 
-- Must contain "ORDER BY" 
OFFSET @PageSize * (@PageNumber - 1) ROWS FETCH NEXT @PageSize ROWS ONLY;

See the RDBMS Syntax page for further information.
http://www.jooq.org/doc/3.5/manual/sql-building/sql-statements/select-statement/limit-clause/

Verification:

DECLARE @PageSize int 
SET @PageSize = 5


;WITH CTE AS 
(
    SELECT 1 as pagenum 
    UNION ALL

    SELECT pagenum+1 AS pagenum 
    FROM CTE 
    WHERE CTE.pagenum < 100
)
SELECT 
     pagenum
    ,@PageSize * (pagenum - 1) AS StartOFFSETBase0
    --,@PageSize * (pagenum - 1) + @PageSize - 1 AS EndOFFSETBase0
    ,@PageSize * pagenum - 1 AS EndOFFSETBase0 -- Simplified

    ,@PageSize * (pagenum - 1) + 1 AS StartOFFSETBase1
    ,@PageSize * (pagenum - 1) + @PageSize AS EndOFFSETBase1
FROM CTE 


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow