-
Nov 1st, 2016, 03:44 PM
#1
Thread Starter
Lively Member
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()
-
Nov 1st, 2016, 04:04 PM
#2
Re: Need to speed up record selection in a datagridview
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
-
Nov 1st, 2016, 06:12 PM
#3
Thread Starter
Lively Member
Re: Need to speed up record selection in a datagridview
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?
Last edited by Christhemist; Nov 1st, 2016 at 06:26 PM.
-
Nov 2nd, 2016, 08:50 AM
#4
Re: Need to speed up record selection in a datagridview
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.
-
Nov 2nd, 2016, 09:27 AM
#5
Re: Need to speed up record selection in a datagridview
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.
My usual boring signature: Nothing
-
Nov 2nd, 2016, 10:54 AM
#6
Re: Need to speed up record selection in a datagridview
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
Tags for this Thread
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
|