Using a SqlDataReader for accessing a resultset returned from a DB is common practice, however, sometimes it’s convenient or neccesary however to use a DataTable/DataSet.

When faced with this, I commmonly load a Datatable from a DataReader, like so

var dt = new DataTable();
 

// I've got my sqldatareader from somewhere, now I'll use it to load the DataTable

dt.Load(dr);

This all works quite nicely. Now, I recently discovered that when working with multiple resultsets returned from the DB, the DataTable.Load method does something, well sneaky IMHO.

Ok, given a SQL command that returns multiple resultsets, you can access them using the SqlDataReader, as normal, except that once the .Read() method returns false, you can call the .NextResult() method to advance the SqlDataReader onto the next resultset, and continue calling .Read() untill it returns false etc.

If the .NextResult() method returns false, then there are no more resultsets. Now, after I called DataTable.Load() I called .NextResult() which returned false, which was wrong since I knew my SQL batch was returning multiple resultsets. Anyway, after much investigation and quick look with Reflector, I discover why it’s not working (and this is the sneaky bit) – DataTable.Load() calls the .NextResult() method on the supplied SqlDataReader for you, as you can see from the code snip below;

...
if (!reader.IsClosed && !reader.NextResult())
{
 reader.Close();
}
...

Ok, so then after calling DataTable.Load(), we just need to check the .HasRows property to see if there are any rows on the newly active resultset.