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.

score 4 · Answer 1 · answered Mar 29 '16 at 16:05

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();
   ...
}

score 3 · Accepted Answer · answered Mar 30 '16 at 07:53

You may want to approach this issue differently, since the requirement is to get a Result set and a count, then even though QueryMultiple helps, but that's better used for the multiple result sets, in this case you can just plan to use Dynamic Parameters, which can help you add the OutputParameter, not just the InputParameter as you have done by default by using Anonymous type and on execution of conn.Query<ProductPreview>, which will help receive the Result set of type IEnumerable<ProductPreview>, can fetch the output parameter value to fetch the Count as shown underneath using your code:

public IHttpActionResult Test()
    {
        List<ProductPreview> gridLines;
        var cs = ConfigurationManager.ConnectionStrings["eordConnection"].ConnectionString;
        using (SqlConnection conn = new SqlConnection(cs))
        {
            DynamicParameters dynamicParameters = new DynamicParameters();

            dynamicParameters.Add("UserID",1,ParameterDirection.Input);

            // Fill the Count in this Parameter
            dynamicParameters.Add("Count",0,ParameterDirection.Output); 

            gridLines = conn.Query<ProductPreview>("dbo.myStoredProcedure", dynamicParameters,
             commandType: CommandType.StoredProcedure).ToList();

             var totalCount = dynamicParameters.Get<int>("Count");
        }

        ....
        return Ok(gridLines);
    }

score 1 · Answer 3 · answered Oct 07 '21 at 11:45

I found an "ok" way to do this, tricking dapper a bit:

Create a PagingColumns class:

public class PagingColumns{public int Id { get; set; }public int? TotalCount {get;set;}}

On your query, add those 2 columns:

" -99 as Id, TotalCount = COUNT(1) OVER() "

"Id" will be faked to Split the Column on mappings.
TotalCount will give you the total count of records of your query.

Add paging options at the bottom of your query:

"ORDER BY OneOfYourTables.Id DESC OFFSET (0) ROWS FETCH NEXT (5) ROWS ONLY "

Query/mapping time:

     PagingColumns pagingColumns;
     conn.Query<YourModelA, PagingColumns, YourModelA>(sql, (m, p) =>
     {
         yourModelsList.Add(m);
         pagingColumns = p;
         return m;
     });

PagingColumns will have now your totalCount.

As a trick, you can fake the "TotalCount" value when paging is not really required.

TotalCount for paging with dapper

3 Answers3