Dbdatareader, Nextresult() And Filling More Than One Table

This question is continuation of my previous one. Without going into too much details, I'm filling dataset with 2 related 1-to-many tables. So, my question now is - why this code w

Solution 1:

DataTable.Load automatically advances the reader to the next result. So you should remove your explicit call to NextResult.

Meaning:

using (DbDataReader reader = command.ExecuteReader())
{
    result.t0_DataAgency_R.Load(reader);
    result.t01_ChoiceParam_R.Load(reader);
}

Solution 2:

Adding a DataSet to the mix... we used to use SqlDataAdapter and returned a DataSet but didn't take advantage of any of the offline features, etc., so a SqlDataReader is a better fit. Here's code to fill a DataSet. Found this was about 10% faster overall.

Dim s As DataSet = New DataSet()

                Using reader As SqlDataReader = command.ExecuteReader()
                    Dim tables AsNew List(Of DataTable)
                    DoDim table AsNew DataTable()
                        table.Load(reader)
                        tables.Add(table)
                        s.Tables.Add(table)
                    LoopWhileNot reader.IsClosed

                    s.Load(reader, LoadOption.OverwriteChanges, tables.ToArray())
                EndUsing