We help IT Professionals succeed at work.

Filtering a datagrid view

vensali
vensali asked
on
519 Views
Last Modified: 2017-04-08
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
Comment
Watch Question

Munawar HussainPrincipal Software Engineer
CERTIFIED EXPERT

Commented:
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,
Ark
CERTIFIED EXPERT

Commented:
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

Developer
CERTIFIED EXPERT
Distinguished Expert 2019
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
it_saigeDeveloper
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
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-
Ark
CERTIFIED EXPERT

Commented:
>>*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 Engineer
CERTIFIED EXPERT

Commented:
@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_saigeDeveloper
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
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-

Author

Commented:
Thank you very much. excellent explanation

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions