Filtering a datagrid view

I have a datagridview  bound to binding source. i am using bindingsource.filter to filter for a search string in a column. My requirement is as below

I have 4 search textboxes. department, sercode, sername, bedtype.

I enter  a search string in department textbox.  it filters for the entered department in the grid. when i next enter  sercode, it has to filter for the text in sercode in already filtered data grid view for the deparment
vensaliAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Munawar HussainPrincipal Software EngineerCommented:
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,
ArkCommented: 
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

Open in new window

it_saigeDeveloperCommented:
*No Points*

There is no need to maintain separate lists.  Filtering does not change the contents of the source, simply what is presented to the UI.

Proof of concept for Ark's recommendation:

Form1.vb - 
Imports System.ComponentModel
Imports System.Globalization
Imports System.Runtime.CompilerServices

Public Class Form1
    Private IsLoading As Boolean
    Private filterString As String

    Private Sub OnLoad(sender As Object, e As EventArgs) Handles MyBase.Load
        IsLoading = True
        BindingSource1.DataSource = (From i In Enumerable.Range(0, 20) Select New 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}).ConvertToDataTable()
        DataGridView1.DataSource = BindingSource1
        ComboBox1.Items.AddRange(New Object() {"None", "False", "True"})
        ComboBox1.SelectedIndex = 0
        IsLoading = False
    End Sub

    Private Function GetFilter() As String
        GetFilter = String.Empty
        Dim filters As New List(Of String)
        Try
            If Not String.IsNullOrWhiteSpace(TextBox1.Text) Then
                filters.Add(String.Format("(FirstName LIKE '%{0}%')", TextBox1.Text))
            End If

            If Not String.IsNullOrWhiteSpace(TextBox2.Text) Then
                filters.Add(String.Format("(LastName LIKE '%{0}%')", TextBox2.Text))
            End If

            If Not String.IsNullOrWhiteSpace(MaskedTextBox1.Text) Then
                filters.Add(String.Format("(BirthDate = '{0}')", 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(String.Format("(IsWorking = {0})", Convert.ToBoolean(ComboBox1.Text)))
            End If
        Catch ex As Exception
        Finally
            GetFilter = String.Join(" AND ", filters)
        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
            filterString = GetFilter()
            If String.IsNullOrWhiteSpace(filterString) Then
                BindingSource1.RemoveFilter()
            Else
                BindingSource1.Filter = filterString
            End If
        End If
    End Sub

    Private Sub OnSelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
        If Not IsLoading Then
            filterString = GetFilter()
            If String.IsNullOrWhiteSpace(filterString) Then
                BindingSource1.RemoveFilter()
            Else
                BindingSource1.Filter = filterString
            End If
        End If
    End Sub
End Class

Module Extensions
    <Extension()>
    Public Function ConvertToDataTable(Of T)(ByVal source As IEnumerable(Of T)) As DataTable
        Dim properties As PropertyDescriptorCollection = TypeDescriptor.GetProperties(GetType(T))
        Dim table As DataTable = New DataTable()

        For i As Integer = 0 To properties.Count - 1
            Dim [property] As PropertyDescriptor = properties(i)
            If [property].PropertyType.IsGenericType AndAlso [property].PropertyType.GetGenericTypeDefinition().Equals(GetType(Nullable)) Then
                table.Columns.Add([property].Name, [property].PropertyType.GetGenericArguments()(0))
            Else
                table.Columns.Add([property].Name, [property].PropertyType)
            End If
        Next

        Dim values(properties.Count - 1) As Object
        For Each item As T In source
            For i As Integer = 0 To properties.Count - 1
                values(i) = properties(i).GetValue(item)
            Next
            table.Rows.Add(values)
        Next

        Return table
    End Function
End Module

Open in new window

Form1.Designer.vb - 
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Partial Class Form1
    Inherits System.Windows.Forms.Form

    'Form overrides dispose to clean up the component list.
    <System.Diagnostics.DebuggerNonUserCode()> _
    Protected Overrides Sub Dispose(ByVal disposing As Boolean)
        Try
            If disposing AndAlso components IsNot Nothing Then
                components.Dispose()
            End If
        Finally
            MyBase.Dispose(disposing)
        End Try
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    <System.Diagnostics.DebuggerStepThrough()> _
    Private Sub InitializeComponent()
        Me.components = New System.ComponentModel.Container()
        Me.Label1 = New System.Windows.Forms.Label()
        Me.Label2 = New System.Windows.Forms.Label()
        Me.Label3 = New System.Windows.Forms.Label()
        Me.Label4 = New System.Windows.Forms.Label()
        Me.TextBox1 = New System.Windows.Forms.TextBox()
        Me.TextBox2 = New System.Windows.Forms.TextBox()
        Me.MaskedTextBox1 = New System.Windows.Forms.MaskedTextBox()
        Me.ComboBox1 = New System.Windows.Forms.ComboBox()
        Me.DataGridView1 = New System.Windows.Forms.DataGridView()
        Me.BindingSource1 = New System.Windows.Forms.BindingSource(Me.components)
        CType(Me.DataGridView1, System.ComponentModel.ISupportInitialize).BeginInit()
        CType(Me.BindingSource1, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'Label1
        '
        Me.Label1.AutoSize = True
        Me.Label1.Location = New System.Drawing.Point(9, 9)
        Me.Label1.Name = "Label1"
        Me.Label1.Size = New System.Drawing.Size(60, 13)
        Me.Label1.TabIndex = 0
        Me.Label1.Text = "First Name:"
        '
        'Label2
        '
        Me.Label2.AutoSize = True
        Me.Label2.Location = New System.Drawing.Point(160, 9)
        Me.Label2.Name = "Label2"
        Me.Label2.Size = New System.Drawing.Size(61, 13)
        Me.Label2.TabIndex = 1
        Me.Label2.Text = "Last Name:"
        '
        'Label3
        '
        Me.Label3.AutoSize = True
        Me.Label3.Location = New System.Drawing.Point(313, 9)
        Me.Label3.Name = "Label3"
        Me.Label3.Size = New System.Drawing.Size(52, 13)
        Me.Label3.TabIndex = 2
        Me.Label3.Text = "Birthdate:"
        '
        'Label4
        '
        Me.Label4.AutoSize = True
        Me.Label4.Location = New System.Drawing.Point(479, 9)
        Me.Label4.Name = "Label4"
        Me.Label4.Size = New System.Drawing.Size(61, 13)
        Me.Label4.TabIndex = 3
        Me.Label4.Text = "Is Working:"
        '
        'TextBox1
        '
        Me.TextBox1.Location = New System.Drawing.Point(12, 25)
        Me.TextBox1.Name = "TextBox1"
        Me.TextBox1.Size = New System.Drawing.Size(100, 20)
        Me.TextBox1.TabIndex = 4
        '
        'TextBox2
        '
        Me.TextBox2.Location = New System.Drawing.Point(163, 25)
        Me.TextBox2.Name = "TextBox2"
        Me.TextBox2.Size = New System.Drawing.Size(100, 20)
        Me.TextBox2.TabIndex = 5
        '
        'MaskedTextBox1
        '
        Me.MaskedTextBox1.Location = New System.Drawing.Point(316, 25)
        Me.MaskedTextBox1.Mask = "00/00/0000"
        Me.MaskedTextBox1.Name = "MaskedTextBox1"
        Me.MaskedTextBox1.Size = New System.Drawing.Size(100, 20)
        Me.MaskedTextBox1.TabIndex = 6
        Me.MaskedTextBox1.TextMaskFormat = System.Windows.Forms.MaskFormat.ExcludePromptAndLiterals
        Me.MaskedTextBox1.ValidatingType = GetType(Date)
        '
        'ComboBox1
        '
        Me.ComboBox1.Location = New System.Drawing.Point(482, 25)
        Me.ComboBox1.Name = "ComboBox1"
        Me.ComboBox1.Size = New System.Drawing.Size(100, 21)
        Me.ComboBox1.TabIndex = 7
        '
        'DataGridView1
        '
        Me.DataGridView1.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize
        Me.DataGridView1.Location = New System.Drawing.Point(12, 52)
        Me.DataGridView1.Name = "DataGridView1"
        Me.DataGridView1.Size = New System.Drawing.Size(570, 263)
        Me.DataGridView1.TabIndex = 8
        '
        'Form1
        '
        Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!, 13.0!)
        Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
        Me.ClientSize = New System.Drawing.Size(594, 327)
        Me.Controls.Add(Me.DataGridView1)
        Me.Controls.Add(Me.ComboBox1)
        Me.Controls.Add(Me.MaskedTextBox1)
        Me.Controls.Add(Me.TextBox2)
        Me.Controls.Add(Me.TextBox1)
        Me.Controls.Add(Me.Label4)
        Me.Controls.Add(Me.Label3)
        Me.Controls.Add(Me.Label2)
        Me.Controls.Add(Me.Label1)
        Me.Name = "Form1"
        Me.Text = "Form1"
        CType(Me.DataGridView1, System.ComponentModel.ISupportInitialize).EndInit()
        CType(Me.BindingSource1, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)
        Me.PerformLayout()

    End Sub

    Friend WithEvents Label1 As Label
    Friend WithEvents Label2 As Label
    Friend WithEvents Label3 As Label
    Friend WithEvents Label4 As Label
    Friend WithEvents TextBox1 As TextBox
    Friend WithEvents TextBox2 As TextBox
    Friend WithEvents MaskedTextBox1 As MaskedTextBox
    Friend WithEvents ComboBox1 As ComboBox
    Friend WithEvents DataGridView1 As DataGridView
    Friend WithEvents BindingSource1 As BindingSource
End Class

Open in new window

Produces the following output -
Initial load -Capture.PNGFiltering on First Name using 'ar' -Capture.PNGFiltering on Last Name using 14 -Capture.PNG
-saige-

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Learn More!
it_saigeDeveloperCommented:
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

Open in new window

Produces the same output as above.

-saige-
ArkCommented:
>>*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
Munawar HussainPrincipal Software EngineerCommented:
@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.
it_saigeDeveloperCommented:
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-
vensaliAuthor Commented:
Thank you very much. excellent explanation
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.