Populate multiple datatables from DataReader. Is it even possible?

Question

I have a stored procedure which returns two resultsets which i capture in a DataReader.

dr = returnData(Id,frmDate, toDate);

dt1 = new DataTable();
dt2 = new DataTable();

dt1.Load(dr);
dr.NextResult(); // Proceed to next resultset
dt2.Load(dr);

DataTable dt1 gets successfully populated but DataTable dt2 remains empty. How to do it. Is it even possible ? Does DataReader get destroyed the first time it is used ?

Answer 1

Very old question, but in case someone else stumbles upon this... Found the answer in this SO answer: If you use DataTable.Load() then you should not use rdr.NextResult(), as that is taken care implicitly. Simply call .Load() for your next table. So, for example, if you have an expectedTableCount:

ds = new DataSet();
ds.Tables.Add("Table1");
ds.Tables[0].Load(reader);
for (int ii = 1; ii < expectedTableCount; ii++)
{
    ds.Tables.Add("Table" + (ii + 1));
    ds.Tables[ii].Load(reader);
}