[RESOLVED] Fastest way to dump data from an array(#,#) into a datagridview or datatable

Hi

just trying to find the fastest way to put a 2 dimensional array into a datatable or datagridview, the fast possible method is what i want.

What i have setup is a class to store a cache (which comes from a query) this cache is going to be used for sorting, filtering etc, the cache is built in the following way.

ADO.NET > Adapter > DataSet > Array (DataSet to Arrays takes 20ms for 4000 rows)

now i currently setup the class to extract the table into a 2D Array and the headers into a 1D Array, just because the DataSet and DataGridView also keep them separated.

now what i want is to do it backwards

Array > DataSet OR Array > DataGridView (i want it to be around 20ms too)

currently i tried putting the array into the DataGridView but its taking 700ms....

Code:

---

Private Sub PopulateDataGrid_FromCache(DataGrid As DataGridView, Data As Array, Headers As Array)
        Dim ColCount As Integer
        Dim RowCount As Integer
        Dim Row As Long
        Dim Col As Integer
        Dim DRow() As String

        RowCount = UBound(Data, 1)
        ColCount = UBound(Data, 2)

        With DataGrid
            '.Columns.Clear()
            For Col = 0 To ColCount
                .Columns.Add(columnName:="Column_" & Col, headerText:=Headers(Col))
            Next

            ReDim DRow(ColCount)

            For Row = 0 To RowCount
                For Col = 0 To ColCount
                    DRow(Col) = Data(Row, Col)
                Next
                .Rows.Add(DRow)
            Next

        End With
    End Sub

---

I though it was possible to do this by columns instead of rows which in theory should be faster since there is only 9 columns, looping the array happens in milliseconds so all this extra time must be caused by accessing and adding rows to the object so adding columns should be faster if that is possible.

I also saw another method where i can build an array with a structure that can be used directly as a datasource, but how can you define a structure at runtime? is that possible......... maybe a hackjob for this is to define a structure with the max amount of columns that would be needed and delete the excess.......that may work (im using this class on multiple tables....well i will be when its working fast enough)

any ideas?

ADDITIONAL: Its no problem to change the 2D Array into an Array of 1D Arrays or to even convert it into a list when i have my results if that would be better, just as long as the process from Cache to DataGridView is less than 100ms im good with that.

Why us the arrays at all? Why not just put the data into a DataTable, which you already have, and then work with that? Is it because accessing that is slower than an array elsewhere?

i just know arrays work and its what im familiar with, im not really familiar with whats happening behind the scenes with DataGridViews, DataSets etc as this is the first time i used them :).

so from a ADO.NET query im just dumping a DataTable into the DataGridView and then im actually just dropping the datatable from the then on, im assuming it still resides (or a copy of it) in the DataGridView DataSource........

so would you recommend trying to do the work from this dataset (maybe storing a copy as a cache) and then creating a new datatable with the results to use on the datagridview?

it does appear that the biggest problem i have is accessing the datagridview row by row. Do you think DataTable will be fast to work on??

Quote:

---

i just know arrays work and its what im familiar with

---

Thats no reason to use them, Arrays are old hat in .Net nobody uses them anymore when you have Lists which are like arrays on steroids.

Quote:

---

so from a ADO.NET query im just dumping a DataTable into the DataGridView and then im actually just dropping the datatable from the then on

---

So you actually have a DataTable to Begin with?? then just dont get rid of it!, they way most of use do it is to actually make all you changes to the DataTable and then you can just rebind it to your Grid and your Grid will show any changes.

Quote:

---

DataGridView.DataSource = DataTable

---

You can add rows to your DataTable like this -

Quote:

---

Dim R As DataRow = DataTable.NewRow
R("Name") = txtName.Text
DataTable.Rows.Add(R)
DataGridView.DataSource = DataTable

---

Obviously that example assumes you have a column called Name, but you get the idea.

Hi

@NeedSomeAnswers :)

Quote:

---

nobody uses them anymore when you have Lists which are like arrays on steroids.

---

Thats a perfect analogy and although they are very flexible Just like a big guy on steriods they are slower since they have overhead, and this overhead is what im removing as like i mentioned earlier i need the "Fastest" possible way to do this because my clients demand realtime action, i havent quite figured out how to make that happen in .NET yet unlike VBA which i can get this working on an excel sheet much faster.

Quote:

---

You can add rows to your DataTable like this

---

:) Nooo please dont let me add objects 1 row at a time, there must be a faster way to do it......is there?

Quote:

---

So you actually have a DataTable to Begin with??

---

i actually think i should be keeping this somewhere thats a good idea thanks.

@everyone

actually i think i jumped the gun a little on the last post, im going to give the addrows method a try on the dataTable and see if its faster than the DataGridView if not im going to have to either find a way to use this Typed Array method that i saw somewhere which looks good, or maybe use another control............ or maybe just tell the client to press enter when done so its not calculating so damn much haha..

The main reason i need this all to work ultra fast is because another process (a filter) which should be simple,..... and is just making it happen fast enough is a pain. i know i can process an array in a split second with multiple filters on thousands of rows so i just need the start and end parts to be a little faster so i can do it as the user types (user request but its good practice :))

OK i just tested the DataTable and it started off taking 1100ms but then it started running very fast indeed at around 80ms.


its almost exactly the same as the DataGridView code so it looks like the DataTable is going to be the one to use
im still going to stick with the arrays since i already set that up :)

Code:

---

Private Sub PopulateDataGrid_FromCache(ByRef DataGrid As DataGridView, Data As Array, Headers As Array)
        Dim ColCount As Integer
        Dim RowCount As Integer
        Dim Row As Long
        Dim Col As Integer
        Dim DRow() As String
        Dim dt As New DataTable

        RowCount = UBound(Data, 1)
        ColCount = UBound(Data, 2)

        With dt
 
            .Columns.Clear()

            For Col = 0 To ColCount
                .Columns.Add(Headers(Col))
                '.Columns.Add(columnName:="Column_" & Col, headerText:=Headers(Col))
            Next

            ReDim DRow(ColCount)

            For Row = 0 To RowCount
                For Col = 0 To ColCount
                    DRow(Col) = Data(Row, Col)
                Next
                .Rows.Add(DRow)
            Next

        End With

        DataGrid.DataSource = dt
    End Sub

---

so the total time after the query returns its results is

DataTable > Array 20ms
Array > DataTable > DataGridView 80ms

which gives me around 20-50ms to play with on the filter which shouldnt be a problem.....

Are you aware that a DataTable has filtering built in? When you bind a DataTable, the data you see comes from its DefaultView property, which is a DataView. That DataView has a RowFilter property that you set and the DataView then exposes only those rows that satisfy the filter. A bound grid will reflect that automatically.

Quote:

---

Originally Posted by GBeats

OK i just tested the DataTable and it started off taking 1100ms but then it started running very fast indeed at around 80ms.


its almost exactly the same as the DataGridView code so it looks like the DataTable is going to be the one to use
im still going to stick with the arrays since i already set that up :)

Code:

---

Private Sub PopulateDataGrid_FromCache(ByRef DataGrid As DataGridView, Data As Array, Headers As Array)
        Dim ColCount As Integer
        Dim RowCount As Integer
        Dim Row As Long
        Dim Col As Integer
        Dim DRow() As String
        Dim dt As New DataTable

        RowCount = UBound(Data, 1)
        ColCount = UBound(Data, 2)

        With dt
 
            .Columns.Clear()

            For Col = 0 To ColCount
                .Columns.Add(Headers(Col))
                '.Columns.Add(columnName:="Column_" & Col, headerText:=Headers(Col))
            Next

            ReDim DRow(ColCount)

            For Row = 0 To RowCount
                For Col = 0 To ColCount
                    DRow(Col) = Data(Row, Col)
                Next
                .Rows.Add(DRow)
            Next

        End With

        DataGrid.DataSource = dt
    End Sub

---

so the total time after the query returns its results is

DataTable > Array 20ms
Array > DataTable > DataGridView 80ms

which gives me around 20-50ms to play with on the filter which shouldnt be a problem.....

---

Help me here... I thought the data was in a datatable to begin with... is that not the case?

This from post 1 implies that's what you're doing:
ADO.NET > Adapter > DataSet > Array (DataSet to Arrays takes 20ms for 4000 rows)


so why are you using an array to load a datatable?
You should be doing this:
ADO.NET > Adapter > DataSet > Bind to dataGridView

and done... nothing more, nothing less.

your shuttling data in and out of the array is highly inefficient and there's no need to do so.

-tg

@jmcilhinney, i did not know that :), i will have a quick look at this filter....

@techgnome, i really wanted to keep everything in place and filter from the datagrid (i have another post on that) basically i did it with the rows visible property but it was just taking too long to run, until just now i didnt realize a DataTable had a filter which i need to look at.

so i turned too building my own method which i have done many times in VBA and its very fast, i just needed a good way to transfer the data back into the DataGridView that didnt resort to looping and adding rows 1 by 1 which is the purpose of this particular thread.

Another reason for all the messing around is i dont want to keep any connections open, and i dont want to be requerying the database on every action as some of the queries (probably not the most efficient) are quite large and complex spanning over multiple tables with several conditions for each table. its not a simple customer table im gathering data for unfortunately, and its not my database so i cannot change that :) although i would love to haha.

so in this light, i decided as of now to run the query once, and store all the data into my own class via a datatable (into arrays in the class)........ this process only happens once so it doesnt actually matter if this part takes time although after the query its only 20ms to populate this class for 4000 rows (the current test data im working with).

once i have all the data cached i can then do whatever i want with it (NOTE this data is for viewing only, any edits or changes are done via forms and updated via Stored Procedures so i dont need to worry about all of that with this table) all i need to worry about is how is displayed and what to display and allow the user to interact with it in this regard.

Also this is just a test which im trying to get right on 1 part of this project so i can apply it to the rest of the datagridviews that need the same functionality.......

Quote:

---

Another reason for all the messing around is i dont want to keep any connections open, and i dont want to be requerying the database on every action as some of the queries (probably not the most efficient) are quite large and complex spanning over multiple tables with several conditions for each table. its not a simple customer table im gathering data for unfortunately, and its not my database so i cannot change that although i would love to haha.

so in this light, i decided as of now to run the query once, and store all the data into my own class via a datatable (into arrays in the class)........ this process only happens once so it doesnt actually matter if this part takes time although after the query its only 20ms to populate this class for 4000 rows (the current test data im working with).

once i have all the data cached i can then do whatever i want with it (NOTE this data is for viewing only, any edits or changes are done via forms and updated via Stored Procedures so i dont need to worry about all of that with this table) all i need to worry about is how is displayed and what to display and allow the user to interact with it in this regard.

Also this is just a test which im trying to get right on 1 part of this project so i can apply it to the rest of the datagridviews that need the same functionality.......

---

Ungh... ok. all that right there just goes to show that you don't really know or understand datatables... the good news (for you) is that datatables do not have a connection... only the dataadaptor does... so even using the filter does not re-run the query or anything... it just filters the data in the datatable. Period. So that first paragraph is a non issue.

"store all the data into my own class via a datatable (into arrays in the class)........ " .. huh? what? Store the data in the datatable... doesn't matter if it's in the form or a class, what ever makes sense. Stop with the arrays. Let. Them. Go. And lo, MINCE said unto the programmer "Programmer, let my arrays go." And he threw down his division line, where upon striking the ground it turned into an ASP.

Seriously... there's no reason to keep using arrays... if the data is in a datatable, bind it to the grid and boom! you're done... want to hide some rows? Create a binding context, assign the datatable to it, then set the binding context as the DataSource of the grid... then you can set the .Filter property to any SQL-like statement similar to a Where clause... and boom! suddenly your grid is filtered... it's not that hard, and was built for these kinds of situations, and is really efficient.

-tg

hmm, that also sounds good, so many ways to achieve a result.

so this binding context looks like you can set it up at design time looking at msdn?

and figuratively speaking the datatable will be my cache, the binding context in this case is my filter which limits whats going to the DataGridView?

OK i like this idea sounds just like what i had in mind. good thing i haven't changed any of my main code yet regarding this issue so i should be able to hook this up quickly and test it out.

:), just need to find out how to do that ... where is google.



Sorry about all this array stuff, im so used to working in VBA right now that all my .NET love has been replaced with VBA problems and excel haha

Now you're getting it...

Arrays are fine... for what they are... even I still use them... but as others have mentioned in the other thread, they are a tool that should be used for the right case. Sure I could use the butt of a screwdriver to pound a nail into the wall, but that doesn't mean it's a good idea.

In this case, the method you were using to shuttle the data into/out of the grid wasn't quite the right tool... especially since it went through the right tool (datatable) in the first place on its way to the array.

So, yes, the datatable essentially becomes your cache (using your parlance) ... the BindingContext becomes a malleable glue that allows the datatable to be tied to the grid, while at the same time allowing you to filter the data - and I think I misspoke, I think the property is RowFilter, not Filter... or it's possible both exist, but they work a little differently... so be sure to look it up on MSDN.

-tg

You should absolutely add the BindingSource to the form in the designer. If you have a typed DataSet then you can even bind at design time and you'll see the appropriate columns in the DataGridView. If you don't have a typed DataSet then don't bind until run time because otherwise the columns won't be created automatically. Once you have your objects set up, getting the data into the grid is as simple as this:

vb.net Code:

myDataAdapter.Fill(myDataTable)
myBindingSource.DataSource = myDataTable
myDataGridView.DataSource = myBindingSource

The user can then sort the grid by clicking on a column header if you allow it and you can filter in code using what is basically a SQL WHERE clause, e.g.

vb.net Code:

myBindingSource.Filter = String.Format("MyColumn LIKE '%{0}%'", myTextBox.Text)

OK this is perfect...... i have choice right now but to eats all those words back up and say.....this is super fast haha, i havent tested it on all my tables yet, i just added a BindingSource to the form and at run time assigned my DT to it and it to the DataGridView.

im hoping i can just swap the data in here when i load another table by replacing the BindingSource.DataSource to a new DT and it will handle it..... but first

Houston i have a problem, i'm hoping i can fix it here rather than anywhere else although its not a major problem, when im pulling the data through a query im renaming some columns, some columns actually dont have a name just whitespace characters, some have spaces some have the hash sign, the usual method for encapsulating object names isnt working [] or ', is there another way to do this

Ex. "Column #" in sql i would usually use [Column #]

Code:

---

BindingSource1.Filter = " Column 1 Like '%" & cmbSearch.Text & "%'"

---

im super happy now :)

Quote:

---

Originally Posted by GBeats

im hoping i can just swap the data in here when i load another table by replacing the BindingSource.DataSource to a new DT and it will handle it

---

The problem there will be the columns in the grid. They get created automatically when you set the DataSource of the grid and they don't get removed automatically. With that in mind, your sequence should be:

vb.net Code:

myDataGridView.DataSource = Nothing
myDataGridView.Columns.Clear()
myBindingSource.DataSource = myNewDataTable
myDataGridView.DataSource = myBindingSource

Quote:

---

Originally Posted by GBeats

Houston i have a problem, i'm hoping i can fix it here rather than anywhere else although its not a major problem, when im pulling the data through a query im renaming some columns, some columns actually dont have a name just whitespace characters, some have spaces some have the hash sign, the usual method for encapsulating object names isnt working [] or ', is there another way to do this

Ex. "Column #" in sql i would usually use [Column #]

Code:

---

BindingSource1.Filter = " Column 1 Like '%" & cmbSearch.Text & "%'"

---

im super happy now :)

---

According to the documentation, both [brackets] and `graves` should work. The column names must match the values of the ColumnName properties of the DataColumns in the DataTable and I'd expect that a blank name wouldn't work. I'll test myself when I get the chance but I suspect that you're doing something wrong.

Thats straight forward, thanks.

Oh its working now, i must have done something wrong earlier

it also works on spaces [ ], [ ] which i used for columns containing controls

It doesnt like the single quote works though, it just filters everything out.



EDIT...

Quote:

---

`graves`

---

thats probably why single quotes never work for me anywhere :/ there not quotes haha

OK thanks guys You both resolved my initial problem.

but for anyone else looking for this particular fix (Fastest way to put an array into a DataGridView)

the fastest way i found from the various things i tried was this.

Bare in mind i have a separate header array here and i just got a ByRef of the DataGridView for no apparent reason i just tested it that way :) so adjust as needed

This ran very quickly

Code:

---

Private Sub PopulateDataGrid_FromArray(ByRef DataGrid As DataGridView, Data As Array, Headers As Array)
        Dim ColCount As Integer
        Dim RowCount As Integer
        Dim Row As Long
        Dim Col As Integer
        Dim DRow() As String
        Dim dt As New DataTable

        RowCount = UBound(Data, 1)
        ColCount = UBound(Data, 2)

        With dt
            .Columns.Clear()

            For Col = 0 To ColCount
                .Columns.Add(Headers(Col))
            Next

            ReDim DRow(ColCount)

            For Row = 0 To RowCount
                For Col = 0 To ColCount
                    DRow(Col) = Data(Row, Col)
                Next
                .Rows.Add(DRow)
            Next

        End With

        DataGrid.DataSource = dt
    End Sub

---