Filtering a datagrid view

I would recommend following pattern to apply multiple filters based instant search.

1- Create a master data source (a copy of your data source which you have set into binding source)
2- Create a data source/binding source from master by applying filter.
3- Bind the filtered data to Gridview.

Because you have multiple filters, when one of them is applied, then your data source only contains filtered data and subsequent filters cannot be applied on that hence its better to persist the master copy of data source.

Here is pseudo code:

List<UserDTO> masterList=new List<UserDTO>();

//Search by user ID
List<UserDTO> users =masterList.Where(u=>u.UserID > 1).ToList();

//Bind to gridview
gridview.datasource=users;

//Apply fiter on name and age
List<UserDTO> users =masterList.Where(u=>u.age > 18 && u.Name.contains("Erik")).ToList();

//Bind to gridview
gridview.datasource=users;

Regards,

Dim filterString As String = ""
If TextBox1.Text<>"" Then 'or your condition here
     filterString = "(ColumnName1 = '" & TextBox1.Text & ")"
End If
If TextBox2.Text<>"" Then 'or your condition here
     If filterString <>"" then filterString = filterString & " AND "
     filterString &= "(ColumnName2 LIKE '%" & TextBox2.Text & ")"
End If
If TextBox3.Text<>"" Then 'or your condition here
     If filterString <>"" then filterString = filterString & " AND "
     filterString &= "(ColumnName3 = '" & TextBox3.Text & ")"
End If
If TextBox4.Text<>"" Then 'or your condition here
     If filterString <>"" then filterString = filterString & " AND "
     filterString &= "(ColumnName4 = '" & TextBox4.Text & ")"
End If
If filterString<>"" Then bs.Filter = filterString Else bs.RemoveFilter

Select allOpen in new window

And yet another implementation using dynamic expression based filtering -

Imports System.ComponentModel
Imports System.Globalization
Imports System.Linq.Expressions
Imports System.Runtime.CompilerServices

Public Class Form1
    Private IsLoading As Boolean
    Private filter As Func(Of Person, Boolean)
    Private data As List(Of Person)

    Private Sub OnLoad(sender As Object, e As EventArgs) Handles MyBase.Load
        IsLoading = True
        data = New List(Of Person)(From i In Enumerable.Range(0, 20) Select New Person() With {.ID = i, .FirstName = String.Format("{0}{1}", If(i Mod 5 = 0, "Paul", If(i Mod 4 = 0, "Larry", If(i Mod 3 = 0, "Susan", If(i Mod 2 = 0, "Mary", "Peter")))), i), .LastName = String.Format("LastName{0}", i), .BirthDate = Today.AddYears(-(9 * i)), .IsWorking = i Mod 2 = 0})
        BindingSource1.DataSource = data
        DataGridView1.DataSource = BindingSource1
        ComboBox1.Items.AddRange(New Object() {"None", "False", "True"})
        ComboBox1.SelectedIndex = 0
        IsLoading = False
    End Sub

    Private Function GetFilter() As Func(Of Person, Boolean)
        GetFilter = Nothing
        Dim filters As New List(Of Filter)
        Try
            If Not String.IsNullOrWhiteSpace(TextBox1.Text) Then
                filters.Add(New Filter() With {.PropertyName = "FirstName", .Operation = Operation.Contains, .Value = TextBox1.Text})
            End If

            If Not String.IsNullOrWhiteSpace(TextBox2.Text) Then
                filters.Add(New Filter() With {.PropertyName = "LastName", .Operation = Operation.Contains, .Value = TextBox2.Text})
            End If

            If Not String.IsNullOrWhiteSpace(MaskedTextBox1.Text) Then
                filters.Add(New Filter() With {.PropertyName = "BirthDate", .Operation = Operation.Equals, .Value = Date.ParseExact(MaskedTextBox1.Text, "ddMMyyyy", CultureInfo.InvariantCulture)})
            End If

            If Not String.IsNullOrWhiteSpace(ComboBox1.Text) AndAlso Not ComboBox1.Text.Equals("none", StringComparison.OrdinalIgnoreCase) Then
                filters.Add(New Filter() With {.PropertyName = "IsWorking", .Operation = Operation.Equals, .Value = Convert.ToBoolean(ComboBox1.Text)})
            End If
        Catch ex As Exception
        Finally
            GetFilter = filters.GetExpression(Of Person)()?.Compile()
            ' This is using the null-conditional operator - '?.'; using it is the same as
            ' GetFilter = If(filters.Count = 0, Nothing, filters.GetExpression(Of Person)().Compile())
        End Try
        Return GetFilter
    End Function

    Private Sub OnTextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged, TextBox2.TextChanged, MaskedTextBox1.TextChanged
        If Not IsLoading Then
            filter = GetFilter()
            If filter Is Nothing Then
                BindingSource1.DataSource = data
            Else
                BindingSource1.DataSource = data.Where(filter)
            End If
        End If
    End Sub

    Private Sub OnSelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
        If Not IsLoading Then
            filter = GetFilter()
            If filter Is Nothing Then
                BindingSource1.DataSource = data
            Else
                BindingSource1.DataSource = data.Where(filter)
            End If
        End If
    End Sub
End Class

Class Person
    Public Property ID() As Integer
    Public Property FirstName() As String
    Public Property LastName() As String
    Public Property BirthDate() As Date
    Public Property IsWorking() As Boolean
End Class

Class Filter
    Public Property PropertyName() As String
    Public Property Operation() As Operation
    Public Property Value() As Object
End Class

Enum Operation
    Equals
    GreaterThan
    LessThan
    GreaterThanOrEqual
    LessThanOrEqual
    Contains
    StartsWith
    EndsWith
End Enum

Module Extensions
    <Extension()>
    Public Function GetExpression(Of T)(param As ParameterExpression, filter As Filter)
        Dim member As MemberExpression = Expression.Property(param, filter.PropertyName)
        Dim constant As ConstantExpression = Expression.Constant(filter.Value)

        Select Case filter.Operation
            Case Operation.Equals
                Return Expression.Equal(member, constant)
            Case Operation.GreaterThan
                Return Expression.GreaterThan(member, constant)
            Case Operation.GreaterThanOrEqual
                Return Expression.GreaterThanOrEqual(member, constant)
            Case Operation.LessThan
                Return Expression.LessThan(member, constant)
            Case Operation.LessThanOrEqual
                Return Expression.LessThanOrEqual(member, constant)
            Case Operation.Contains
                Return Expression.Call(member, GetType(String).GetMethod("Contains"), constant)
            Case Operation.StartsWith
                Return Expression.Call(member, GetType(String).GetMethod("StartsWith", New Type() {GetType(String)}), constant)
            Case Operation.EndsWith
                Return Expression.Call(member, GetType(String).GetMethod("EndsWith", New Type() {GetType(String)}), constant)
            Case Else
                Return Nothing
        End Select
    End Function

    <Extension()>
    Public Function GetExpression(Of T)(filters As IList(Of Filter)) As Expression(Of Func(Of T, Boolean))
        If filters.Count = 0 Then Return Nothing

        Dim param As ParameterExpression = Expression.Parameter(GetType(T), "t")
        Dim exp As Expression = Nothing

        If filters.Count = 1 Then
            exp = param.GetExpression(Of T)(filters(0))
        ElseIf (filters.Count = 2) Then
            exp = param.GetExpression(Of T)(filters(0), filters(1))
        Else
            While filters.Count > 0
                Dim f1 = filters(0)
                Dim f2 = filters(1)
                If exp Is Nothing Then
                    exp = param.GetExpression(Of T)(filters(0), filters(1))
                Else
                    exp = Expression.AndAlso(exp, param.GetExpression(Of T)(filters(0), filters(1)))
                End If

                filters.Remove(f1)
                filters.Remove(f2)

                If filters.Count = 1 Then
                    exp = Expression.AndAlso(exp, param.GetExpression(Of T)(filters(0)))
                    filters.RemoveAt(0)
                End If
            End While
        End If
        Return Expression.Lambda(Of Func(Of T, Boolean))(exp, param)
    End Function

    <Extension()>
    Private Function GetExpression(Of T)(param As ParameterExpression, f1 As Filter, f2 As Filter)
        Return Expression.AndAlso(param.GetExpression(Of T)(f1), param.GetExpression(Of T)(f2))
    End Function
End Module

Select allOpen in new window

Produces the same output as above.

-saige-

>>*No Points*
it_sage, you desire them!
One thing though - I suggest using
"(BirthDate = Convert('{0}','System.DateTime'))"
or format DateTime using standard SQL ANSI "yyyy-MM-dd" for date data type
or "yyyy-MM-dd HH:mm:ss" for datetime/datetime2
due to possible difference in system and SQL formats

@it_saige, how would you say, there is no need to maintain master? in case of list, I named it master but in your case data is basically master and you apply filters to master to pull the filtered results.

I was without visual studio at the time of writing answer so, I written pseudo code however, the logic remains same.

thanks for working demo, definitely its more helpful.

I didn't say that you would not need to maintain a *master* list.  I had stated that you don't need to maintain *separate* (or multiple) lists which is what your psuedo code alludes to.

-saige-

Thank you very much. excellent explanation