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 ?

`NextResult()` should do the trick. Can you possibly show us the SPROC which is being called? — rossipedia, Oct 14 '13 at 05:38
The stored procedure is successfully returning two resultsets , I have checked by executing directly on Sql Server Management Studio. — Mudassir Hasan, Oct 14 '13 at 05:41
Does `NextResult` actually return `true`? The code you've shown ignores the return value. — Jon Skeet, Oct 14 '13 at 05:47
@JonSkeet ok , so I checked with `if(dr.NextResult())`...it is returning false..even though there are two resultsets — Mudassir Hasan, Oct 14 '13 at 05:51
I have edited your title. Please see, "[Should questions include “tags” in their titles?](http://meta.stackexchange.com/questions/19190/)", where the consensus is "no, they should not". — John Saunders, Oct 14 '13 at 05:53
please refer this http://stackoverflow.com/questions/12969318/multiples-table-in-datareader — Sain Pradeep, Oct 14 '13 at 06:16

score 4 · Accepted Answer · edited May 23 '17 at 12:17

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);
}

Populate multiple datatables from DataReader. Is it even possible?

1 Answers1