Query Paging

There are two main methods used for paging.

Page<T>

IDatabase db = new Database("connStringName");
Page<T> pagedUsers = db.Page<User>(2, 10, "select u.* from users u order by userid");

where Page<T> is defined by:

public class Page<T> 
{
    public long CurrentPage { get; set; }
    public long TotalPages { get; set; }
    public long TotalItems { get; set; }
    public long ItemsPerPage { get; set; }
    public List<T> Items { get; set; }
}

Note: You must provide an order by statement in your SQL statement so that the query knows in which order you want your data to be paged.

The first parameter to the Page<T> method is the page number. This number begins at 1 for the first page. The second parameter is the size of the page. In the example above, the second page with 10 users in it will be returned.

SkipTake<T>

The SkipTake<T> method is very similar to the Skip and Take methods in LINQ. It has the same number of parameters as the Page<T> method, but instead of the first parameter being the page number, it is the number of records to skip. The second parameter is the number of records to return after x number of records have been skipped. To return the same results as the Page<T> method, the query would be as follows:

List<User> users = db.SkipTake<User>(10, 10, "select u.* from users u order by userid");

The difference between the two methods is that SkipTake<T> returns a List<T> whereas Page<T> returns a Page<T> object, which has extra properties convenient for rendering a pager.

There is also an overload of Fetch<T> which has the same parameter signature of the Page<T> method but returns a List<T>.