Results 1 to 6 of 6

Thread: populate a multi element data array from a data table

  1. #1

    Thread Starter
    Member
    Join Date
    Jan 2016
    Posts
    34

    populate a multi element data array from a data table

    I am trying to populate a multi element data array from a data table. The data ultimately populates an Excel file.

    My reason for trying this approach is that I am invoking a data reader. From it, I loop thru it to count the rows. Using that I can accurately dim my data array. To populate the data array, I have to invoke the data reader a second time. The data reader takes a few minutes to run and I am trying to prevent having to invoke it twice.

    So, I thought about populating the data table from the data reader and then populate the data array from the data table, which causes me to only invoke the data reader once. The problem with this approach is that I have to loop thru the data table row by row and column by column to populate the data array. This takes just about as long as invoking the data reader twice. There may be better ways to do all the above from the approach I have taken but I have not found one yet.

    Ultimately, I am looking for the most efficient way to populate the data array by invoking the data reader only once. From it I can populate the excel file, which happens very fast.

    I am open to any ideas or suggestions.

    Thanks in advance for any guidance and happy thanksgiving to all.

  2. #2
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,715

    Re: populate a multi element data array from a data table

    We have come a long way since fixed length collections. The most practical approach would be use to use the .NET dynamic length collection, List(Of T).

    Declare a List(Of String), loop through your DataReader once, and add the items to the List(Of String). Without knowing your data schema, take a look at this example:
    Code:
        'Declare a dynamic sized collection
        Dim items As New List(Of String)
    
        'Declare the connection object
        Dim con As OleDbConnection
    
        'Wrap code in Try/Catch
        Try
            'Set the connection object to a new instance
            'TODO: Change "My Connection String Here" with a valid connection string
            con = New OleDbConnection("My Connection String Here")
    
            'Create a new instance of the command object
            'TODO: Change [ID] to a valid column and [MyTable] to a valid table
            Using cmd As OleDbCommand = New OleDbCommand("SELECT [ID] FROM [MyTable]", con)
    
                'Open the connection
                con.Open()
    
                'Use a DataReader to read all rows
                Using reader As OleDbDataReader = cmd.ExecuteReader()
                    'Loop through each row
                    While reader.Read(
                        'Add the item to the collection
                        'TODO: Change ID to a valid column name
                        items.Add(reader("ID"))
                    End While
                End Using
    
                'Close the connection
                con.Close()
            End Using
        Catch ex As Exception
            'Display the error
            Console.WriteLine(ex.Message)
        Finally
            'Check if the connection object was initialized
            If con IsNot Nothing Then
                If con.State = ConnectionState.Open Then
                    'Close the connection if it was left open(exception thrown)
                    con.Close()
                End If
    
                'Dispose of the connection object
                con.Dispose()
            End If
        End Try
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  3. #3

    Thread Starter
    Member
    Join Date
    Jan 2016
    Posts
    34

    Re: populate a multi element data array from a data table

    dday9:
    Thanks for the reply. Do you have some sample code you could post (translated, could you hold my hand a little bit that would help me see how it works? Thanks

  4. #4
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: populate a multi element data array from a data table

    What's the point of the data array anyways? You can populate a datatable and send that directly to Excel, if you wanted to, so what does the array give you that you don't already have?
    My usual boring signature: Nothing

  5. #5

    Thread Starter
    Member
    Join Date
    Jan 2016
    Posts
    34

    Re: populate a multi element data array from a data table

    Shaggy:
    Thanks. This was just the way I was approaching it and wa looking for a better way. Do you have some sample code you could post that populates an excel file from a datareader? If so, thanks very much. That would be what I need and want, I believe

  6. #6
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: populate a multi element data array from a data table

    If you search the .NET CodeBank section for threads started by me, you will find one with Excel in the title. That holds a class I wrote for exporting a datatable to a spreadheet. I doubt it's the most efficient approach, but it's an example.
    My usual boring signature: Nothing

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width