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#?

What do you mean with count, the count of result sets or the count of each row count? — Tim Schmelter
– Tim Schmelter, Commented Aug 8, 2013 at 9:56
Prop the count of the result from all those select, like he said. — butterbox
– butterbox, Commented Aug 8, 2013 at 9:59
i mean count of result sets (above tree). each set can contains some rows — isxaker
– isxaker, Commented Aug 8, 2013 at 10:01
I don't think there's any way to count the number of result sets - you just have to enumerate them (using reader.GetNextResult()) and handle them. You cannot determine ahead of time how many result sets you'll be handling, though — marc_s
– marc_s, Commented Aug 8, 2013 at 10:36
Just to expand on marc_s comment. I would presume that moving the reader past a result would not allow me to go back - so I can't know ahead of time reliably in a dynamic way. Even if it worked today I could expect an implementation change tomorrow that would break my assumption or hack that made it work. — Sql Surfer
– Sql Surfer, Commented Oct 14, 2016 at 1:04

binki · Accepted Answer · 2016-10-14 00:53:56Z

11

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).

answered Oct 14, 2016 at 0:53

binki

8,4216 gold badges76 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Solomon Rutzky Over a year ago

Depending on the scenario, I have also used SqlDataReader.HasRows instead of FieldCount (I probably use HasRows more often than FieldCount). But yes, a do {...} while() is a far better (and cleaner) approach than the convoluted, and less flexible, approach shown in the accepted answer. (+1). And correct, a result set must have at least one column.

binki Over a year ago

@srutzky The code I've written will probably never be anything other than System.Data.SqlClient and I have no abstraction over raw T-SQL, but I still find it fun to try to restrict myself to the available interfaces (except to take advantage of the TPL compatible Async() variants for which I use DbCommand). FieldCount does feel hacky, but at least I can get “pure” interfacy code ;-)

Tim Schmelter · Accepted Answer · 2013-08-08 10:00:38Z

7

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 ...
                    }
                }
            }
        }
    }
}

answered Aug 8, 2013 at 10:00

Tim Schmelter

462k79 gold badges721 silver badges981 bronze badges

4 Comments

isxaker Over a year ago

if (rdr.NextResult()) count++; do you have any idea? method NextResult() move position the sqldatareader

Tim Schmelter Over a year ago

@Mikhail: Explain your question. Do you need the count of each table's rows or do you need the count of result sets or do you need the rows of each result set(as shown above)? If you just need the count of result sets: int count = 0; while(rdr.NextResult())count++; should work.

isxaker Over a year ago

Ok, if i use this method, how i can return position of sqldatareader before the first record after counting?

binki Over a year ago

@isxaker The point is that you can’t. You can read all of the data into a collection such as a List<T> (you’ll need something more complex if handling multiple result sets) and then you can both count data points and use the data.

dodexahedron · Accepted Answer · 2023-01-01 06:37:49Z

Another solution to be aware of, in addition to the manual SqlDataReader method, in the accepted answer, is to use a SqlDataAdapter and DataSets and DataTables.

When using those classes, the entire result set is retrieved from the server in one go, and you can iterate them at your leisure. Also, several other .net classes are aware of DataSets and DataTables and can be hooked up to them directly, for read-only or read-write data access, if you also set the DeleteCommand, InsertCommand, and UpdateCommand properties. What you get "for free," with that, is the ability to alter the data in the DataSet, and then simply call Update() to push your local changes to the database. You also gain the RowUpdated event handler, which you can use to make that happen automatically.

DataSets and DataTables retain the metadata from the database schema, as well, so you can still access columns by name or index, as you please.

Overall, they're a nice feature, though certainly heavier weight than a SqlDataReader is.

Documentation for SqlDataAdapter is here: https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqldataadapter

Collectives™ on Stack Overflow

Get number of result sets from a SqlDataReader

3 Answers 3

2 Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related