Read multiple values returned by multiple SELECT from a SP

Question

I have this SP (extremply simplified for exhibit)

SELECT colA, colB, colC
INTO #MyTable

select COUNT(*) from #MyTable

On the C# side, I have

int total = 0;
var command = _context.CreateStoreCommand(
    "dbo.mySP",
    CommandType.StoredProcedure,
    new SqlParameter("Param1", parameters.Param1),
    //...
);

using (command.Connection.CreateConnectionScope())
{
    using (var reader = command.ExecuteReader())
    {
        result = new Materializer<MyType>()
            .Materialize(reader)
            .ToList();

            if (reader.NextResult() && reader.Read())
            {
                // This returns the total number of elements, 
                // (select COUNT(*) from #MyTable, ~15000 in my case)
                total = reader.GetInt32(0); 
            }
        }
    }

Because the SP may result a huge amount of data, .Materialize(reader) only returns the 10 firsts elements into the result variable.
There's absolutely no way to change this rule for some reasons I can't explain here.

In the sample above, however, total is the total number of results of the SP, which may be greater than 10 (in my case ~15000) and it works well.

I now need to return another result, let's say

SELECT COUNT(*) from #MyTable WHERE colA = 'sample value'

Obviously, I can't do it C# side, like var x = result.Where(x => x.Prop = "sample value").Count() because it would search only on the 10 first results, whereas I want it to search on the whole dataset, which is ~15000 here.

I tried to add

var total2 = reader.GetInt32(1);

but reader.GetInt32(1) systematically fired a System.OutOfRangeException

How can I read, C# side, another result returned by a new SELECT statement on my SP ?

Answer 1

To receive a third resultset, you need to cal NextResult again:

if (reader.NextResult() && reader.Read())
{
    total2 = reader.GetInt32(0); 
}

---

Although I would advise you to just change your second query to use conditional aggregation (this is much more performant to do it together):

select COUNT(*), COUNT(CASE WHEN colA = 'sample value' THEN 1 END)
from #MyTable

You can then use your original code:

if (reader.NextResult() && reader.Read())
{
    total = reader.GetInt32(0);
    total2 = reader.GetInt32(1); 
}

---

You could also entirely remove the temp table and just use a windowed count

SELECT colA, colB, colC,
    COUNT(*) OVER (),
    COUNT(CASE WHEN colA = 'sample value' THEN 1 END) OVER ()
FROM Whatever

You would now only have one resultset, where each row has the total for the whole set.