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 5 · Accepted Answer · edited May 23 '17 at 12:17

5

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

edited May 23 '17 at 12:17

Community

1
1

answered Mar 24 '17 at 18:31

Martin_W

1,582
1
19
24

1

Reference: https://learn.microsoft.com/en-us/dotnet/api/system.data.datatable.load?view=net-6.0 Under the second "Remarks" section: `The Load method consumes the first result set from the loaded IDataReader, and after successful completion, sets the reader's position to the next result set, if any.` Instead of a for loop, I might recommend a `do...while (!reader.IsClosed)` – jwatts1980 Oct 05 '22 at 22:38

Populate multiple datatables from DataReader. Is it even possible?

1 Answers1