EDIT: Note. I should have mentioned I'm not interested in using the .Select, DataRowView, RowFind, etc. Thank you for those suggestions but the code as it stands, I was hoping to optimize.
I wrote a function to search a datatable for terms in an array. I then return the datatable as a dataset so it can be bound to a DataGridView. Does anyone have any optimization ideas? Since this is a real-time search (every term typed instantly searches) I'd like to make the function as fast as possible. The speed is actually pretty good already but I figured I'd be worth asking you all if there was anything else I could change about it.
- VB.NET
- .NET 2.0
Public Function SearchEngine(ByVal Terms() As String, ByRef ResidentTBL As DataTable) As DataSet
Dim newdts As New DataSet("Users")
Dim newtable, temptable As New DataTable
Dim residentPassCeiling, tempPassCeiling, termsceiling As Integer
residentPassCeiling = ResidentTBL.Rows.Count – 1
termsceiling = Terms.GetUpperBound(0)
newtable = ResidentTBL.Clone
For j = 0 To termsceiling
If j < 1 Then
'######################## BEGIN First Pass ########################################################################
For i = 0 To residentPassCeiling
If ResidentTBL.Rows(i).Item(TheColumns.AccountName - 1).ToString.ToUpper = Terms(j).ToUpper Then
'we have an excact match on account skip further additions
newtable.Rows.Clear()
newtable.ImportRow(ResidentTBL.Rows(i))
newdts.Tables.Add(newtable)
Return newdts
Else
If _
ResidentTBL.Rows(i).Item(TheColumns.AccountName - 1).ToString.ToUpper Like Terms(j).ToUpper & "*" Or _
ResidentTBL.Rows(i).Item(TheColumns.displayName - 1).ToString.ToUpper Like Terms(j).ToUpper & "*" Or _
ResidentTBL.Rows(i).Item(TheColumns.sn - 1).ToString.ToUpper Like Terms(j).ToUpper & "*" Or _
ResidentTBL.Rows(i).Item(TheColumns.givenName - 1).ToString.ToUpper Like Terms(j).ToUpper & "*" Or _
ResidentTBL.Rows(i).Item(TheColumns.l - 1).ToString.ToUpper Like Terms(j).ToUpper & "*" Or _
ResidentTBL.Rows(i).Item(TheColumns.department - 1).ToString.ToUpper Like Terms(j).ToUpper & "*" Or _
ResidentTBL.Rows(i).Item(TheColumns.telephoneNumber - 1).ToString.ToUpper Like Terms(j).ToUpper & "*" Or _
ResidentTBL.Rows(i).Item(TheColumns.building - 1).ToString.ToUpper Like Terms(j).ToUpper & "*" Then
'then add to the row
newtable.ImportRow(ResidentTBL.Rows(i))
End If
End If
Next i
'######################## END First Pass ########################################################################
Else
'######################## BEGIN Second.. Pass(es) ########################################################################
'Further searches we want to search the already filtered datatable from each term
temptable = newtable.Clone
temptable = newtable.Copy
newtable.Clear()
tempPassCeiling = temptable.Rows.Count - 1
For i = 0 To tempPassCeiling
If temptable.Rows(i).Item(TheColumns.AccountName - 1).ToString.ToUpper = Terms(j).ToUpper Then
'we have an excact match on account skip further additions
newtable.Rows.Clear()
newtable.ImportRow(temptable.Rows(i))
newdts.Tables.Add(newtable)
Return newdts
Else
If _
temptable.Rows(i).Item(TheColumns.AccountName - 1).ToString.ToUpper Like Terms(j).ToUpper & "*" Or _
temptable.Rows(i).Item(TheColumns.sn - 1).ToString.ToUpper Like Terms(j).ToUpper & "*" Or _
temptable.Rows(i).Item(TheColumns.givenName - 1).ToString.ToUpper Like Terms(j).ToUpper & "*" Or _
temptable.Rows(i).Item(TheColumns.l - 1).ToString.ToUpper Like Terms(j).ToUpper & "*" Or _
temptable.Rows(i).Item(TheColumns.department - 1).ToString.ToUpper Like Terms(j).ToUpper & "*" Or _
temptable.Rows(i).Item(TheColumns.telephoneNumber - 1).ToString.ToUpper Like Terms(j).ToUpper & "*" Or _
temptable.Rows(i).Item(TheColumns.building - 1).ToString.ToUpper Like Terms(j).ToUpper & "*" Then
'then add to the row
newtable.ImportRow(temptable.Rows(i))
End If
End If
Next i
temptable = Nothing
'######################## END Second.. Pass(es) ########################################################################
End If
Next j
newdts.Tables.Add(newtable)
ResidentTBL = Nothing
Return newdts
newdts = Nothing
End Function
Best Answer
You can generate a DataTable filter string to select rows from a data table.
http://msdn.microsoft.com/en-us/library/system.data.datatable.select.aspx
Here is docs on the filter string
http://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression.aspx