Need to speed up record selection in a datagridview

I have roughly 100,000 records in a datagridview that I am selecting records using a user input criteria in another search form. But its taking way to long to loop through each record and select them. Is there a faster way of doing this? Here is my code:

Code:

---

For Each strLine As String In TextBox2.Text.Split(vbNewLine)
        Dim SearchFor As String = strLine.ToString
        If SearchFor = vbLf & "AND" Or SearchFor = vbLf & "OR" Then
        Else
            If EquationVariable = "=" Then
                ItemToFind = SearchFor.Substring(SearchFor.LastIndexOf("=") + 1).TrimStart
                For Each R0w In DataGridView1.Rows
                    NewCount = R0w.index
                    ProgressBar1.Value = NewCount
                    If DataGridView1(0, R0w.index).Value = True Then
                    Else
                        ColumnName = SearchFor.Substring(0, SearchFor.IndexOf("=") - 1)
                        ColumnNameSearch = ColumnName
                        DataGridView1.CurrentCell = DataGridView1.Item(ColumnName, R0w.index)
                        If Not IsDBNull(DataGridView1.CurrentCell.Value) AndAlso DataGridView1.CurrentCell.Value = ItemToFind Then
                            DataGridView1.CurrentRow.DefaultCellStyle.ForeColor = Color.Red
                            DataGridView1(0, R0w.index).Value = True
                        Else
                        End If
                    End If
                Next
            Else
                MsgBox(ListBox1.SelectedItem.ToString & " Doest not = " & ItemToFind)
            End If
        End If
    Next

---

Here is How I am importing the data:

Code:

---

MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\Work\Import.xlsx;Extended Properties=Excel 12.0;")
MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection)
MyCommand.TableMappings.Add("Table", "Net-informations.com")
DtSet = New System.Data.DataSet
MyCommand.Fill(DtSet)
tab = DtSet.Tables(0)
DataGridView1.DataSource = tab
MyConnection.Close()

---

You're doing it too hard...
Since the DGV is bound to a datatable, filtering the view is easy. Every datatable contains a .DefaultView that you can manipulate... through the .RowFilter property...

Code:

---

dt.DefaultView.RowFilter ="Col1=123 and Col2='Some Text'"

---

Manipulating the DefaultView then affects the grid. All you need to do is boil it down to a SQL-compatible WHERE statement ...

-tg

I'm not sure I follow... I'm to manipulate the data within a DataTable, but where does the code you provide come in handy and what to you mean about the SQL statement? And how would I get these records to turn red like I am doing in my code above?

Doing what tg suggested would display only the "selected" rows but it sounds like you simply want the "selected" rows to be red while still displaying all the other rows? If that is the case then I'd try tying in your filtering code with the RowPrePaint event.

If you have 100,000 records, is turning red really sufficient? I'd think that asking the user to scroll through 100,000 records looking for highlighted records would likely turn them plenty red. What TG was suggesting would allow you to reduce the records in the DGV down to just the ones selected. That would tame the mass rather than just color it.

What he meant about the SQL, I believe, is that the .RowFilter for the default view looks very much like the WHERE clause in a SQL statement. They are largely built the same way.

I would also be inclined to follow tg's suggestion, as it would be far easier for the user, and should be dramatically quicker to run (it is also likely to be less code).


However, if you decide not to, the code you posted can be improved (whether or not you follow topshot's suggestion).

There are places where you do unnecessary work (eg: you copy strLine to SearchFor, but could just use SearchFor instead), and several places where you access properties when you could use a variable (eg: DataGridView1.CurrentRow when you have R0w), and things you currently do inside a loop that could be outside it (eg: setting ColumnName), and you select items in the grid when you don't really need to.

Here is a modified version that should be a bit faster:

Code:

---

    For Each SearchFor As String In TextBox2.Text.Split(vbNewLine)
        If Not(SearchFor = vbLf & "AND" Or SearchFor = vbLf & "OR") Then
            If EquationVariable = "=" Then
                ItemToFind = SearchFor.Substring(SearchFor.LastIndexOf("=") + 1).TrimStart
                ColumnName = SearchFor.Substring(0, SearchFor.IndexOf("=") - 1)
                For Each R0w as DataGridViewRow In DataGridView1.Rows
                    ProgressBar1.Value = R0w.index
                    If R0w.Cells(0).Value <> True Then
                        Dim cellValue = R0w.Cells(ColumnName).Value
                        If Not IsDBNull(cellValue) AndAlso cellValue = ItemToFind Then
                            R0w.DefaultCellStyle.ForeColor = Color.Red
                            R0w.Cells(0).Value = True
                        End If
                    End If
                Next
            Else
                MsgBox(ListBox1.SelectedItem.ToString & " Doest not = " & ItemToFind)
            End If
        End If
    Next

---