Save dataset contents to variant array – VBA vs VB.net

Question

In VBA it is very simple to define an array, read a recordeset with ADO (a simple accdb table) and then populate the array with the info from the recordset. Say I have a table with 3 columns, A is dates, B is people names and C is the number of steps each person made during a day. Because we deal with numbers and strings, it's simple to just define a variant array:

Dim RS_CON As Object, RS_DATA As Object
Dim  connString as String, queryString as string
Dim OutputArr() as variant
Set RS_CON = CreateObject("ADODB.Connection")
Set RS_DATA = CreateObject("ADODB.Recordset")
RS_CON.Open connString
queryString="SELECT * FROM [TableName]"
RS_DATA.Open queryString, RS_CON, adOpenStatic, adLockOptimistic, adCmdText
OutputArr =RS_DATA.GetRows(RS_DATA.RecordCount)

The resulting array has 3 columns and all the rows of table, without even specifying the dimensions of the array. In addition, the array holds various data types. But who do I do the exact same thing in VB.NET?

    RS_CON = New OleDbConnection With {
        .ConnectionString = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" &
        sourceFld & dbFileName & "; Persist Security Info=False;")
    }
    Try
        dtSet = New DataSet
        dtTables = dtSet.Tables
        queryString = "SELECT * FROM [TableName];"
        RS_CON.Open() '---> Open Connection To The DataBase File.
        dtAdapt = New OleDbDataAdapter(queryString, RS_CON)
        dtAdapt.Fill(dtSet)

        Dim Y() As Array '---> DOES NOT WORK!!

        RS_CON.Close()

Thank you for any hints/clues/guidance. Stefan

Answer 1

You can use the DataRow.ItemArray property to get your row as an array.

Variants don't exist in VB.NET, use Object instead.

I'd also recommend filling a DataTable instead of a DataSet.

(NOTE: for MS Access you will need to change to ODBC objects, the code below is for Sql Server)

Dim connectionString = "Data Source=localhost;Timeout=10;Database=Junk;Integrated Security=True;"
Dim strSql As String = "SELECT [Id], [Code], [Value] FROM [Table1]"
Dim dtb As New DataTable
Using cnn As New SqlConnection(connectionString)
  cnn.Open()
  Using dad As New SqlDataAdapter(strSql, cnn)
    dad.Fill(dtb)
  End Using
  cnn.Close()
End Using
Dim y() As Object = dtb.Rows(0).ItemArray

Answer 2

In the end, the solution that I found and works for me is quite simple and executes fast. I simply declared an array AS OBJECT, called RawArr (from 'raw data array') and dimmed it accordingly.

        Dim nrRows As Integer = (dtTables(0).Rows.Count - 1)
        Dim nrCols As Integer = (dtTables(0).Columns.Count - 1)
        Dim RawArr(nrRows, nrCols) As Object
        For i As Integer = 0 To nrRows
            For j As Integer = 0 To nrCols
                RawArr(i, j) = dtTables(0).Rows(i).Item(j)
            Next
        Next

This works very fast. The size of the dataset is roughly 7300 rows x 9 columns and it takes just a second to read it from Access and populate the RawArr.