SqlDataReader, DataTable and Multiple Resultsets


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.

Published by

Phil Harding

SharePoint Consultant, Developer, Father, Husband and Climber.

12 thoughts on “SqlDataReader, DataTable and Multiple Resultsets

  1. Thanks a million for the tip…searched for a couple of hours and your tip solved my problem.

  2. I searched for “datatable.load multiple results” and your listing came up first. Your information on how to use the datatable.load() method with multiple results without loosing results was just what I was looking for. Keep up the good work!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s