Fill DataTable from DataReader row by row

Question

Hello, i want to fill a Datatable with a Datareader row by row but get an exception because the reader's columns have a different order than the Datatable's columns.

Why and how does a Datatable loads itself with a Datareader correctly, even so it has a column collection with different order?

I need to calculate values for every row so i dont want to load the Datatable completely but row for row. It has 25 columns so i dont want to set it all manually.

There must be a chance to load/fill/add a new row from a datareader without having the same column order(reader.GetSchemaTable?). Thanks

Following is what i have but what doesnt work:

Using reader As System.Data.SqlClient.SqlDataReader = command.ExecuteReader
   'next line works(configInfo.DataSource is a Datatable)but is inappropriate 
    configInfo.DataSource.Load(reader) 
    While reader.Read
        Dim allCols(reader.FieldCount - 1) As Object
        reader.GetValues(allCols)
        'following both methods are throwing exception because of the wrong column order
        configInfo.DataSource.Rows.Add(allCols)
        configInfo.DataSource.LoadDataRow(allCols, True)
        'now i want to do some calculations on this row ....
    End While
End Using

Answer 1

Honestly, I would use the .Load method on the DataTable to load it fully and then iterate through (or use LINQ) the DataTable by row to do the calcualtions and make modifications. It is much cleaner than that cast to Object of the reader you are trying to do. So start with this:

configInfo.DataDource.Load(reader)

...and then iterate through like this:

For Each dr As DataRow in configInfo.DataSource
  'Do calculations and modify rows

Next

You might even prefer to create a separate DataTable 1st rather than working off of that DataSource DataTable property. Once the DataTable has been modified, re-assign it like so:

configInfo.DataSource = myModifiedDataTable