TotalCount for paging with dapper

Question

I'm using dapper to get result set from stored procedure into object list and return it to the client as json:

public IHttpActionResult Test()
    {
        List<ProductPreview> gridLines;
        var cs = ConfigurationManager.ConnectionStrings["eordConnection"].ConnectionString;
        using (SqlConnection conn = new SqlConnection(cs))
        {
            gridLines = conn.Query<ProductPreview>("dbo.myStoredProcedure", new { userID = 1 },
             commandType: CommandType.StoredProcedure).ToList();
        }
        var totalCount = gridLines[0].MaxCount;//I need to know total count
        ....
        return Ok(gridLines);
    }

It works. The last property of object of type ProductPreview is TotalCount, since stored procedure returns total count as column of every row. (second option is that stored procedure returns two recordsets, but I'm not sure how to change dapper to work with two recordsets). Having two separate queries is not an option.

What would be the best way to return gridLines json object to client without totalCount property(since it is overhead) and read total count from stored procedure to some variable? Copy gridLines object to some other object without totalCount property would be also unnecessary overhead.

Answer 1

Dapper allows you to process multiple result grids in a single query.

Example:

var sql = 
@"
select * from Customers where CustomerId = @id
select * from Orders where CustomerId = @id
select * from Returns where CustomerId = @id";

using (var multi = connection.QueryMultiple(sql, new {id=selectedId}))
{
   var customer = multi.Read<Customer>().Single();
   var orders = multi.Read<Order>().ToList();
   var returns = multi.Read<Return>().ToList();
   ...
}