Dapper ORM paging and sorting

I am giving the Dapper ORM a try. I am able to query data from a table using the code below:

Dim开发者_如何学JAVA 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 am interested to learn how to do paging/sorting using Dapper. EF has "skip" and "take" to help with this. I understand that a micro ORM does not have this built in but would like to know the best way to accomplish this.

If you want to do skip and take with Dapper, you do it with T-SQL.

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

You could do it now.
All you'd need to do is write an extension method that takes Query and PageSize and PageNumber, then you need to append the

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

line to the query and execute.
Note that the query absolutely requires an ORDER-BY clause (at least in T-SQL).
This would work for MS-SQL (2012+), PostgreSQL (8.4+), and Oracle (12c+).
For MySQL, you'd have to append LIMIT offset, page_size.

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

For Firebird, you'd have to append ROWS x TO y

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

For a base-1 index, it would be from startoffset_base1 to endoffset_base1

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;

For the syntax on various different RDBMS, see
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