ericdc ericdc - 1 month ago 23
Vb.net Question

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 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.

Answer

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