Handle pagination with Dapper ORM in C#

In this tutorial, I'll show you how to handle paging with Dapper ORM in C#. Using paging helps your system load faster. Especially when you need to load large amounts of data into a table.

public IPagedList<Company> GetPages(int pageIndex = 1, int pageSize = 25)
{
    using (IDbConnection db = new SqlConnection(Helper.ConnectionString))
    {
        if (db.State == ConnectionState.Closed)
            db.Open();
        var query = db.QueryMultiple("SELECT COUNT(*) FROM Company;SELECT* FROM Company ORDER BY Id OFFSET ((@PageNumber - 1) * @Rows) ROWS FETCH NEXT @Rows ROWS ONLY", new { PageNumber = pageIndex, Rows = pageSize }, commandType: CommandType.Text);
        var row = query.Read<int>().First();
        var pageResult = query.Read<Company>().ToList();
        return new StaticPagedList<Company>(pageResult, pageIndex, pageSize, row);
    }
}

You need to install PagedList from nuget package manager or you can download it directly from https://github.com/TroyGoode/PagedList. PagedList is a library that allows you to easily take an IEnumerable/IQueryable, chop it up into "pages", and grab a specific "page" by an index.

We will use Multiple Queries with Dapper ORM and Paging a query with SQL to handle paging. If you don't know how to use it you should read it