OleDbDataReader results DIRECTLY to an array

Question

Currently I have a class that I created that will allow a user to input SQL code and then the class returns the results to an array in which they can further use. Most methods use a loop to transfer the data from the OleDbDataReader object to an array. This can be very slow when dealing with a large number of items.

Current method:

Dim SQLdr As OleDbDataReader  'SQL reader
Dim SQLCmd As New OleDbCommand() 'The SQL Command
Dim firstline As Boolean
SQLCmd.Connection = SQLConn 'Sets the Connection to use with the SQL Command
SQLCmd.CommandText = SQLStr 'Sets the SQL String
SQLdr = SQLCmd.ExecuteReader 'Gets Data

And then later..

While (SQLdr.Read)
  If firstline = True Then
    'fill headers
    Do Until j = SQLdr.FieldCount
      result(j, i) = SQLdr.GetName(j)
      j = j + 1
    Loop
    firstline = False
    j = 0
    i = 1
  End If

  j = 0
  Do Until j = SQLdr.FieldCount
    ReDim Preserve result(result.GetUpperBound(0), result.GetUpperBound(1) + 1)
    If display = True Then
      MsgBox(j & ":" & i & SQLdr(j).ToString)
    End If
    result(j, i) = SQLdr(j).ToString
    j = j + 1
  Loop

  i = i + 1
End While

I want to know if there is a more direct way out there to output the results into an array.. I am sorry but I do not have any idea where to start for this, if it is even possible, or if anyone has tried this before.

Answer 1

Thank you to Steve for initially pointing me in the right direction and also thank you Tim. I searched and found the solution here:

http://www.vbforums.com/showthread.php?381224-Filling-a-DataTable-using-a-DataReader

I used that method modified slightly so it includes headers and also to output it as an array as I had done previously.

For anyone in the future here is my finished code which runs a TON faster for loading results:

  load_sql(username_, password_, conn_string)

    If SQLConn.State = ConnectionState.Open Then
        Dim myDataTable As New DataTable
        Try
            Using con As New Odbc.OdbcConnection(conn_string)
                ' Dim command As New Odbc.OdbcCommand(SQLConn, con)
                Dim SQLCmd As New OleDbCommand()
                SQLCmd.Connection = SQLConn 'Sets the Connection to use with the SQL Command
                SQLCmd.CommandText = SQLStr 'Sets the SQL String
                Using dr As OleDbDataReader = SQLCmd.ExecuteReader
                    myDataTable.Load(dr)
                End Using
            End Using
        Catch ex As Exception
            myDataTable = Nothing
        End Try


        Dim total_rows As Integer
        Dim total_columns As Integer
        total_rows = myDataTable.Rows.Count
        total_columns = myDataTable.Columns.Count
        Dim result(total_columns, total_rows) As String
        Dim i As Integer = 0
        Dim j As Integer = 0
        Do Until j = total_columns 'add column headers first
            result(j, 0) = myDataTable.Columns(j).Caption
            j = j + 1
        Loop

        Do Until i = total_rows 'load data to array
            RaiseEvent progress(i, total_rows)
            j = 0
            Do Until j = total_columns
                result(j, i + 1) = myDataTable.Rows(i)(j)
                j = j + 1
            Loop

            i = i + 1
        Loop
        RaiseEvent progress(total_rows, total_rows)
        RaiseEvent query_finished(result, queryindex) 'display results

    End If

Answer 2

Is this really VB.NET? However, you should not use ReDim Preserve to resize your array. Instead use a generic List and it's Add method. You should also use a custom class for your data, that increases readability, makes it more reusable and less error-prone. It's also faster when you don't use Object everywhere since it doesn't need to box/unbox.

Here's an example with a List(Of User) where Ùser is a custom class with two properties.

Dim users = New List(Of User)
Using con = New OleDb.OleDbConnection(connectionString)
    Using cmd = New OleDb.OleDbCommand("SELECT UserID, UserName FROM dbo.User ORDER BY UserName", con)
        con.Open()
        Using rdr = cmd.ExecuteReader()
            While rdr.Read()
                Dim user = New User()
                user.UserID = rdr.GetInt32(0)
                user.UserName = rdr.GetString(1)
                users.Add(user)
            End While
        End Using
    End Using
End Using 

Here the simple class:

Class User
    Public Property UserID As Int32
    Public Property UserName As String
End Class

If you want to leave your code dynamic you could also use a DataAdapter to fill a DataTable/DataSet. That would simplify the code lot and would also be more efficient.

Dim table = New DataTable()
Using con = New OleDb.OleDbConnection(connectionString)
    Using da = New OleDb.OleDbDataAdapter("SELECT UserID, UserName FROM dbo.User ORDER BY UserName", con)
        da.Fill(table)
    End Using
End Using