Read each row in a single column in a DataReader

Question

I use while(dr.Read()){...}. But I have a problem with reading data. I have a table like this:

Column1

value1
value2
value3

I want to get Value1, Value2 and Value3

I did this like so:

Using myDataReader As OracleDataReader = myCommand.ExecuteReader
    While (myDataReader.Read())

        Value1= myDataReader.GetValue(0).ToString().Trim()
        Value2 = myDataReader.GetValue(1).ToString().Trim()
        Value3 = myDataReader.GetValue(2).ToString().Trim()

    End While
End Using

I can retrieve Value1, however I get the following error when trying to retrieve Value2 and Value3:

Invalid column index specified

Answer 1

Instead of using and OracleDataReader you should consider using a DataTable to load the data into. To do this use the following code:

Dim dt As New DataTable
dt.Load(myCommand.ExecuteReader)

The DataTable will now contain a collection of DataRows. You can use these to assign the values to your variables like so:

Value1 = dt.Rows(0).Item(0).ToString().Trim()
Value2 = dt.Rows(1).Item(0).ToString().Trim()
Value3 = dt.Rows(2).Item(0).ToString().Trim()

This may fix your initial problem but it may lead to further problems. As suggested by ADyson in his comment:

@EmmaW. you could add a counter to your loop, so you know what row you're on and can populate the property accordingly. Bugs' suggestion is also sensible if you have a smallish number of properties. Sounds like your in-code object model doesn't really match your database though (vertical DB record structure vs horizontal object structure), which could be a sign of a conceptual design problem