isxaker isxaker - 1 month ago 16
SQL Question

Get number of result sets from a SqlDataReader

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

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