-
Nov 22nd, 2016, 04:31 PM
#1
Thread Starter
Member
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.
-
Nov 22nd, 2016, 04:38 PM
#2
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
-
Nov 22nd, 2016, 04:45 PM
#3
Thread Starter
Member
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
-
Nov 22nd, 2016, 05:06 PM
#4
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
-
Nov 22nd, 2016, 05:25 PM
#5
Thread Starter
Member
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
-
Nov 22nd, 2016, 08:13 PM
#6
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|