Custom DataTable searches

Question

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

If you don't want to consider changing it you probably already have your answer about any optimizations available. — Ian Jacobs

according2me according2me · Accepted Answer · 2009-04-14T15:02:15

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

Custom DataTable searches

2 Answers