Dapper. Paging

Question

I am trying Dapper ORM and I am querying a a Posts table.

But I would like to get paged results ...

1 - How can I do this? Isn't there a helper for this?

2 - Can Dapper Query return an IQueryable?

Thank You, Miguel

Answer 1

You didn't specify a database or version. If you're lucky enough to be able to use the brand new SQL Server 2012 and have access to MSDN, you can use the shiny new OFFSET and FETCH keywords. The following query will skip 20 records and return the next 5.

SELECT * FROM [Posts]
ORDER BY [InsertDate]
OFFSET 20 ROWS
FETCH NEXT 5 ROWS ONLY

Check out http://msdn.microsoft.com/en-us/library/ms188385(v=sql.110).aspx#Offset for more info.

Also, it's easy enough to copy the way Massive does it and write your own extension method for IDbConnection. Here's Massive's code.

var query = string.Format("SELECT {0} FROM (SELECT ROW_NUMBER() OVER (ORDER BY {2}) AS Row, {0} FROM {3} {4}) AS Paged ", columns, pageSize, orderBy, TableName, where);

Answer 2

1) Dapper doesn't have a built-in pagination feature. But its not too hard to implement it directly in the query. Example:

SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY InsertDate) AS RowNum, *
          FROM      Posts
          WHERE     InsertDate >= '1900-01-01'
        ) AS result
WHERE   RowNum >= 1 // *your pagination parameters
    AND RowNum < 20  //*
ORDER BY RowNum

Requires SQL Server 2005+

2) Dapper returns an IEnumerable<T>.

Answer 3

Here is a full working version using C# and Dapper.

/// <summary>
    /// Gets All People
    /// </summary>
    /// <returns>List of People</returns>
    public IEnumerable<Person> GetAllPeople(Pager pager)
    {
        var sql = (@" select * from [dbo].[Person]
                      order by [PeplNo]
                      OFFSET      @Offset ROWS 
                      FETCH NEXT  @Next   ROWS ONLY");

        using (IDbConnection cn = Connection)
        {
            cn.Open();

            var results = cn.Query<Person>(sql,pager);

            return results;
        }
    }


public class Pager
{
    public int Page { get; set; }
    public int PageSize { get; set; }

    public int Offset { get; set; }
    public int Next { get; set; }

    public Pager(int page, int pageSize = 10)
    {
        Page = page < 1 ? 1 : page;
        PageSize = pageSize < 1 ? 10 : pageSize;

        Next = pageSize;
        Offset = (Page - 1) * Next;
    }

}

Answer 4

public async Task<IEnumerable<Blog>> GetBlogs(int pageNo = 1, int pageSize = 10)
{
    int skip = (pageNo - 1) * pageSize;

    using (var db = _context.GetOpenConnection())
    {
       var query = string.Format(@"SELECT * FROM [blog] ORDER BY [Id] OFFSET {0} ROWS FETCH NEXT {1} ROWS ONLY", skip, pageSize);
       
       var result = await db.QueryAsync<Blog>(query);

       return result;
    }
}

Answer 5

I created a generic method with strongly type arguments to get a reusable solution. This relies on FETCH NEXT and OFFSET, which means you need SQL Server 2012 or more recent.

    /// <summary>
    /// Fetches page with page number <paramref name="pageNumber"/> with a page size set to <paramref name="pageSize"/>.
    /// Last page may contains 0 - <paramref name="pageSize"/> items. The page number <paramref name="pageNumber"/> is 0-based,
    /// i.e starts with 0. The method relies on the 'FETCH NEXT' and 'OFFSET' methods
    /// of the database engine provider.
    /// Note: When sorting with <paramref name="sortAscending"/> set to false, you will at the first page get the last items.
    /// The parameter <paramref name="orderByMember"/> specified which property member to sort the collection by. Use a lambda.
    /// </summary>
    /// <typeparam name="T">The type of ienumerable to return and strong type to return upon</typeparam>
    /// <param name="connection">IDbConnection instance (e.g. SqlConnection)</param>
    /// <param name="orderByMember">The property to order with</param>
    /// <param name="sql">The select clause sql to use as basis for the complete paging</param>
    /// <param name="pageNumber">The page index to fetch. 0-based (Starts with 0)</param>
    /// <param name="pageSize">The page size. Must be a positive number</param>
    /// <param name="sortAscending">Which direction to sort. True means ascending, false means descending</param>
    /// <returns></returns>
    public static IEnumerable<T> GetPage<T>(this IDbConnection connection, Expression<Func<T, object>> orderByMember,
        string sql, int pageNumber, int pageSize, bool sortAscending = true)
    {
        if (string.IsNullOrEmpty(sql) || pageNumber < 0 || pageSize <= 0)
        {
            return null;
        }
        int skip = Math.Max(0, (pageNumber)) * pageSize;
        if (!sql.Contains("order by", StringComparison.CurrentCultureIgnoreCase))
        {
            string orderByMemberName = GetMemberName(orderByMember);
            sql += $" ORDER BY [{orderByMemberName}] {(sortAscending ? "ASC": " DESC")} OFFSET @Skip ROWS FETCH NEXT @Next ROWS ONLY";
            return connection.ParameterizedQuery<T>(sql, new Dictionary<string, object> { { "@Skip", skip }, { "@Next", pageSize } });
        }
        else
        {
            sql += $" OFFSET @Skip ROWS FETCH NEXT @Next ROWS ONLY";
            return connection.ParameterizedQuery<T>(sql, new Dictionary<string, object> { { "@Skip", skip }, { "@Next", pageSize } });
        }

    }

Supportive methods below, fetch property name and running parameterized query.

 private static string GetMemberName<T>(Expression<Func<T, object>> expression)
    {
        switch (expression.Body)
        {
            case MemberExpression m:
                return m.Member.Name;
            case UnaryExpression u when u.Operand is MemberExpression m:
                return m.Member.Name;
            default:
                throw new NotImplementedException(expression.GetType().ToString());
        }
    }

 public static IEnumerable<T> ParameterizedQuery<T>(this IDbConnection connection, string sql,
        Dictionary<string, object> parametersDictionary)
    {
        if (string.IsNullOrEmpty(sql))
        {
            return null;
        }
        string missingParameters = string.Empty;
        foreach (var item in parametersDictionary)
        {
            if (!sql.Contains(item.Key))
            {
                missingParameters += $"Missing parameter: {item.Key}";
            }
        }
        if (!string.IsNullOrEmpty(missingParameters))
        {
            throw new ArgumentException($"Parameterized query failed. {missingParameters}");
        }
        var parameters = new DynamicParameters(parametersDictionary);
        return connection.Query<T>(sql, parameters);
    }

Example usage using the Northwind DB:

        var sql = $"select * from products";
        var productPage = connection.GetPage<Product>(m => m.ProductID, sql, 0, 5, sortAscending: true);

Sample POCO look like this:

public class Product
{
    public int ProductID { get; set; }
    public string ProductName { get; set; }
    public int? SupplierID { get; set; }
    public int? CategoryID { get; set; }
    public string QuantityPerUnit { get; set; }
    public decimal? UnitPrice { get; set; }
    public short? UnitsInStock { get; set; }
    public short? UnitsOnOrder { get; set; }
    public short? ReorderLevel { get; set; }
    public bool? Discontinued { get; set; }
}

A more entensive variant of this approach could also build up a method to get an IEnumerable of IEnumerables using a DB Cursor and wrapping the logic used here, but my approach is a basic demonstration of a sturdy predictable type-safe solution, and not relying on the more flexible dynamic approach. The down-side is that since we have generic arguments, we must also write POCOs for our DB classes and not all developers like to spend time on that.

Answer 6

I created a sample project to demo the Dapper custom paging, support sorting, criteria and filter:

https://github.com/jinweijie/Dapper.PagingSample

Basically, the method looks like this:

 Tuple<IEnumerable<Log>, int> Find(LogSearchCriteria criteria
        , int pageIndex
        , int pageSize
        , string[] asc
        , string[] desc);

The first return value is the item list. The second return value is the total count.

Hope it helps.

Thanks.

Answer 7

I find this solution to work for me. It also adds sorting.

public async Task<PagedCustomerResult> GetPagedCustomersAsync(string? sortBy = null, string? sortOrder = null, string pageNumber = 0, string pageSize = 0)
{
  var param = new
  { 
    offSet = (pageNumber -1) * pageSize, 
    pageSize 
  };

  // First query to get total number of records
  StringBuilder sb = new StringBuilder(@"SELECT COUNT(0) [Count] from Customers;");

  // Second query to get the data
  sb.AppendLine(@" SELECT * from Customers c");
  
  if (sortBy != null && sortOrder != null)
  {
    sb.AppendLine($" ORDER BY {BuildOrderBySqlUsingIntepolation(sortBy, sortOrder)}");
  }

  if (pageNumber > 0 && pageSize > 0)
  {
    sb.AppendLine(@" OFFSET @OffSet ROWS FETCH NEXT @PageSize ROWS ONLY");
  }
  
  var multi = await sqlConnection.QueryMultipleAsync(sb.ToString(), param);
  var totalRowCount = await multi.ReadSingleAsync<int>();
  var customers = await multi.ReadAsync<Customer>();

  return new PagedCustomerResult(customers, pageNumber, pageSize, totalRowCount);
  
} 

public class PagedCustomerResult
{
    private IEnumerable<Customer> _data;
    private int _currentPage;
    private int _pageSize;
    private int _totalRecords;
    private int _totalPages;
        
    public PagedCustomerResult(IEnumerable<Customer> data, int page, int pageSize, int totalRecords)
    {
        _data = data;
        _currentPage = page;
        _pageSize = pageSize;
        _totalRecords = totalRecords;
        _totalPages = (int)Math.Ceiling(totalRecords / (double)pageSize);
    }

    public IEnumerable<Customer> Data => _data;

    public int CurrentPage => _currentPage;

    public int PageSize => _pageSize;

    public int TotalRecords => _totalRecords;

    public int TotalPages => _totalPages;
}

private static string BuildOrderBySqlUsingIntepolation(string sortOrderColumn, string sortOrderDirection)
{
   string orderBy;
   switch (sortOrderColumn)
   {
     case "name":
        orderBy = "c.[Name]";
        break;
     default:
        orderBy = "c.[CreatedDateTime]";
        break;
   }
   if (!string.IsNullOrEmpty(sortOrderDirection))
   {
      var sortOrder = "asc";
      if (sortOrderDirection == "desc")
      {
         sortOrder = "desc";
      }
      orderBy = $"{orderBy} {sortOrder}";
   }
   return orderBy;
}

Answer 8

If you want use pagination in dapper, You can use OFFSET and FETCH. I use of stored procedure. first type like this query in SQL and create your procedure:

CREATE PROCEDURE SpName    
    @OFFSET int
AS
BEGIN
    SELECT * 
    FROM TableName
    WHERE (if you have condition)
    ORDER BY columnName
        OFFSET @OFFSET ROWS  
        FETCH NEXT 25 (display 25 rows per page) ROWS ONLY  
END;  

Then you have create your method in your code for get data with Dapper and stored procedure :

public async Task<List<T>> getAllData<T>(string spName, DynamicParameters param)
{
    try
    {
        using (var con = new SqlConnection(_connections.DefaultConnection))
        {
            var result = await con.QueryAsync<T>(spName, param, commandType: System.Data.CommandType.StoredProcedure);
            return result.ToList();
        }
    }
    catch (Exception ex)
    {
        //
    }
}

Then eventually you call the stored procedure and set parameters (@OFFSET) in every method you want use dapper :

public async Task<List<YourModel>> methodName (int offset)
{
    var param = new DynamicParameters();
    param.Add("@OFFSET" , offset);

    var data = await getAllData<yourModel>("spName", param);
    var result =  _mapper.Map<List<yourModel>>(data);

    return result;
}

Answer 9

If you do not have Sql Server 2012 or you have other DBMS, one way to do paging is to split the processing between the DBMS and the web server or client. ---this is recommended only for small set size. You can use the 'TOP' keyword in Sql Server or LIMIT in MySql or ROWNUM in Oracle to get the top number of rows in the data set. The number of rows that you would fetch is equals to the number that you would skip plus the number that you would take:

top = skip + take;

for instance, you would want to skip 100 rows and take the next 50:

top = 100 + 50

So your SQL statement would look like this (SQL server flavor)

SELECT    TOP 150 Name, Modified, content, Created
FROM      Posts
WHERE     Created >= '1900-01-01'

On the Client: if you are using a .NET language like C# and using Dapper, you can use linq to skip a number of rows and take a number of rows like so:

var posts = connection.Query<Post>(sqlStatement, dynamicParameters);
return posts?.ToList().Skip(skipValue).Take(takeValue);