Get number of result sets from a SqlDataReader

Question

I have an SQL Server stored procedure that returns multiple results. The body of the stored procedure might look like this:

SELECT * FROM tableA;
SELECT * FROM tableB;
SELECT * FROM tableC;

In that case, the stored procedure returns 3 result sets. Other stored procedures might return, e.g., 1, 0, or any number of result sets. Each result set might contain 0 or more rows in it. When loading these, I will need to call IDataReader.NextResult() to navigate between result sets.

How can I reliably get the count of result sets (not row counts) in C#?

Answer 1

There seems to be no property or method that directly calculates the result count in IDataReader. This interface rather intends to be consumed in an incremental/streaming fashion. So, to count the number of result sets returned, increment a counter every time you call IDataReader.NextResult() and it returns true while consuming the data.

However, there is a catch. The The documentation for IDataReader.NextResult() states:

By default, the data reader is positioned on the first result.

Consider the following scenarios:

• The command returned 0 result sets. Your first call to IDataReader.NextResult() returns false.
• The command returned 1 result set. Your first call to IDataReader.NextResult() returns false.
• The command returned 2 result sets. Your second call to IDataReader.NextResult() returns false.

You can see that we have enough information to count the number of result sets as long as there is at least one result set. That would be the number of times that IDataReader.NextResult() returned true plus one.

To detect whether or not there are 0 result sets, we use another property from the reader: IDataRecord.FieldCount. The documentation for this property states:

When not positioned in a valid recordset, 0; otherwise, the number of columns in the current record. The default is -1.

Thus, we can read that field when first opening the reader to determine if we are in a valid result set or not. If the command generates no result sets, the value of IDataRecord.FieldCount on the reader will initially be less than 1. If the command generates at least one result set, the value will initially be positive. This assumes that it is impossible for a result set to have 0 columns (which I think you can assume with SQL, not sure).

So, I would use something like the following to count the number of result sets. If you also need to save the data, that logic must be inserted into this:

using (var reader = command.ExecuteReader())
{
    var resultCount = 0;
    do
    {
        if (reader.FieldCount > 0)
            resultCount++;

        while (reader.Read())
        {
            // Insert logic to actually consume data here…
            // HandleRecordByResultIndex(resultCount - 1, (IDataRecord)reader);
        }
    } while (reader.NextResult());
}

I’ve tested this with System.Data.SqlClient and the commands PRINT 'hi' (0 result sets), SELECT 1 x WHERE 1=0 (1 result set), and SELECT 1 x WHERE 1=0; SELECT 1 x WHERE 1=0 (2 result sets).

Answer 2

Use DataReader.NextResult to advance the reader to the next result set.:

using (var con = new SqlConnection(Properties.Settings.Default.ConnectionString))
{
    using (var cmd = new SqlCommand("SELECT * FROM TableA; SELECT * FROM TableB; SELECT * FROM TableC;", con))
    {
        con.Open();
        using (IDataReader rdr = cmd.ExecuteReader())
        {
            while (rdr.Read())
            {
                int firstIntCol = rdr.GetInt32(0); // assuming the first column is of type Int32
                // other fields ...
            }
            if (rdr.NextResult())
            {
                while (rdr.Read())
                {
                    int firstIntCol = rdr.GetInt32(0); // assuming the first column is of type Int32
                    // other fields ...
                }
                if (rdr.NextResult())
                {
                    while (rdr.Read())
                    {
                        int firstIntCol = rdr.GetInt32(0); // assuming the first column is of type Int32
                        // other fields ...
                    }
                }
            }
        }
    }
}