A Filter Dialog for a DataGridView

• Download source files with a small demo and sample database - 1.20 MB
• Download source files - 56.8 Kb

Introduction

When creating an interface to filter data, the question arises, which boolean operator do I use (AND, or OR) to join multiple filters? If you allow only one, inevitably there will be cases where the other will be needed. However, if you allow both 'AND' and 'OR', then you must decide which is to be interpreted first. 'AND' is generally interpreted first, but there are certainly cases where this would not be desirable. The other option is to allow parentheses, but the interface becomes complicated, and if the user doesn't understand boolean logic, it can become too confusing, even for the most basic filters. When searching around, I found many solutions that allowed for basic filtering, but none of them accomodated more complex filter expressions.

Code Usage

To accomplish this task, I created a dialog for filtering. To use it, all you need to do is instantiate the dialog, show it, and then retrieve the filter from the dialog's 'filter' property.

Instantiation

There are two constructors of interest, one that accepts a DataGridView, and one that accepts a list of fields.

Constructor 1

When filtering on different types of columns (strings, numbers, dates), different operators need to be used, and the criteria needs to be constructed differently. I have created a class called genericExpression which is nothing more than a dropdownlist of operators, plus a control (usually a textbox) for entering the criteria. A single filter item, then, is nothing more than a list of fields and a placeholder for an expression that changes whenever a field is selected. This first constructor takes a DataGridView as a parameter, and will try to build this list of fields by checking the datatype of each column and adding the appropriate expression type to the list. Note that this constructor will only work if the DataGridView is ultimately tied to a DataTable. By ultimately, the datasource can be a DataSet, DataTable, DataView, or a bindingsource whose datasource is one of the previous.

Dim fd as Filter
fd = new Filter(myDataGridView, [distinctDisplayLevel], _
                [useDistinctDisplayLevel])

Distinct Display Level

I know the name of this parameter is confusing, but I honestly couldn't think of what to call it. Sometimes, a data column will only have a small number of distinct values, like when the field is a foreign key. In those cases, rather than displaying a textbox, it might be more useful to display a list of all the distinct values of that column in a combobox or dropdownlist.

When generating the list of fields from the source table, how do we decide when to display a column like this (in a dropdown list) or to display a textbox? This constructor will count the number of distinct entries in each column, and use that number to make the decision.

The 'DistinctDisplayLevel' is the number that the constructor uses to compare against. This parameter can take on multiple meanings...

• If between 0 and 1, the DistinctDisplayLevel is interpreted as a percentage. The constructor, for each column, calculates the percentage of distinct values vs. total rows. If this percentage is less than the DistinctDisplayLevel, the column will be filtered using a dropdownlist.
• If greater than 1, the DistinctDisplayLevel is interpreted as a count of distinct values. For each column, if the number of distinct values is less than the DistinctDisplayLevel, then the column will be filtered using a dropdownlist.

UseDistinctDisplayLevel

The third parameter of this constructor is a boolean value indicating whether or not to use the DistinctDisplayLevel in deciding how to filter each column

Constructor 2

The second constructor takes nothing more than the list of type 'FilterItemField'. This list is exactly what the previous constructor generates, only this allows you to build the list yourself, if you want to filter some other datasource (like XML) or simply don't like the way the fields are generated.

Dim fields As List(Of FilterItemField)

' Generate list of fields

Dim filterDialog As Filter
filterDialog = New Filter(fields)

FilterItemField

The FilterItemField is a custom class that contains all the data needed for each field. The constructor takes three parameters, and an optional fourth.

• Value - The name of the column (field) that will be put into the actual filter string that is built.
• <bold>Display - A readable name for the column. This is what will be displayed in the list of fields.
• <bold>Type - The type of field that it is. The options are NumberExpression, StringExpression, DateExpression, and DropDownExpression.
• <bold>DistinctValues - In the case that the type is 'DropDownExpression', this extra parameter will be needed. This is basically a List(Of String) that will populate the dropdownlist of distinct values for that field.

The code might look like this...

Dim field As FilterItemField
Dim fields As New List(Of FilterItemField)

for each ...

     ' some code to determine the type of field it is

     field = New FilterItemField("columnName", "columnHeader", _
             FilterItem.FieldTypes.StringExpression)
     fields.Add(field)

next

Using the Filter Dialog

Usage is fairly simple, just show the dialog, and read the result.

Dim newFilter As String

If filterDialog.ShowDialog() = Windows.Forms.DialogResult.Ok Then

    newFilter = filterDialog.Filter

End If

' Do something with the filter

Implementation

The code is commented pretty heavily, so anything not covered here should be in the comments in the code.

The dialog was designed after Groupwise' email filter dialog. I used almost the same style for each filter item, but I didn't like the way that Groupwise handled groups, so that is a little different.

IFilterItem

This is a sample to encapsulate the basic properties I wanted for each filter item. Because I wanted to allow unlimited nested parentheses, each group contains basic filter items and subgroups, both of which implement the IFilterItem interface. The interface looks like this...

Public Interface IFilterItem
    ' The objects selected item in its menu
    Property SelectedMenuItem() As FilterItemMenuButton.Items
    ' The filter string for the object
    ReadOnly Property Filter() As String
    ' The readable filter for the object
    ReadOnly Property ReadableFilter() As String
    ' The conjunction following the object
    ReadOnly Property Conjunction() As String
End Interface

The base filter dialog is nothing more than a panel with a single filter group (class name 'FilterItemGroup').

FilterItem

A filter item is nothing more than a list of fields, a placeholder for an expression, which changes whenever a field is selected, and a button. The list of fields is tied to a List(Of FilterItemField) which is built when the filter dialog is instantiated.

GenericExpression

GenericExpression is a class from which each expression class (StringExpression, NumberExpression, DateExpression, DropDownExpression) inherits. It has the properties common to an expression, an empty dropdownlist for the operators, and a place for the criteria control.

To add an expression type, you would need to..

• Create a class that inherits from GenericExpression
• Add the operators you want to the dropdownlist
• Add a control for editing the criteria
• Handle the change events of both controls, and set the values of the various properties of generic expression
• Add the type to the enumeration of expression types in the FilterItem class
• Add code to the set method of the FieldTypes property of FilterItem to handle the type being set to your new expression.

The code for StringExpression looks like this...

Public Class StringExpression

    ''' <summary>
    '''   An enumeration of the Operators available
    '''   and their indices in the dropdownlist
    ''' </summary>
    ''' <remarks></remarks>
    Private Enum Operators As Integer
        Contains = 0
        DoesNotContain = 1
        BeginsWith = 2
        EndsWith = 3
        Matches = 4
        DoesNotMatch = 5
    End Enum

    ''' <summary>
    '''   Handles the changing of the Operator
    ''' </summary>
    Public Sub OperatorsList_SelectedIndexChanged(ByVal sender _
           As Object, ByVal e As EventArgs) _
           Handles OperatorsList.SelectedIndexChanged
        SetValues()
    End Sub

    ''' <summary>
    '''  Handles whenever the criteria is changing
    ''' </summary>
    Private Sub TextBox1_TextChanged(ByVal sender As Object, _
            ByVal e As System.EventArgs) _
            Handles TextBox1.TextChanged
        SetValues()
    End Sub

    ''' <summary>
    '''   Sets the values of the Operator, the criteria,
    '''   and whether the expression should be inverted,
    '''   for use in building the filter string
    '''   for this particular expression
    ''' </summary>
    Private Sub SetValues()
        Select Case OperatorsList.SelectedIndex
            Case Operators.Contains
                Me.RealOperator = "Like"
                Me.Inverse = False
                Me.Criteria = "'*" & _
                  CleanCriteriaForFilter(TextBox1.Text) & "*'"
            Case Operators.DoesNotContain
                Me.RealOperator = "Like"
                Me.Inverse = True
                Me.Criteria = "'*" & _
                  CleanCriteriaForFilter(TextBox1.Text) & "*'"
            Case Operators.BeginsWith
                Me.RealOperator = "Like"
                Me.Inverse = False
                Me.Criteria = "'" & _
                  CleanCriteriaForFilter(TextBox1.Text) & "*'"
            Case Operators.EndsWith
                Me.RealOperator = "Like"
                Me.Inverse = False
                Me.Criteria = "'*" & _
                  CleanCriteriaForFilter(TextBox1.Text) & "'"
            Case Operators.Matches
                Me.RealOperator = "="
                Me.Inverse = False
                Me.Criteria = "'" & _
                  CleanCriteriaForFilter(TextBox1.Text) & "'"
            Case Operators.DoesNotMatch
                Me.RealOperator = "<>"
                Me.Inverse = False
                Me.Criteria = "'" & _
                  CleanCriteriaForFilter(TextBox1.Text) & "'"
        End Select

        '' Replaces the Criteria if nothing is entered into the textbox
        If TextBox1.Text.Length = 0 Then
            Me.Criteria = ""
        End If

        '' Set the readable criteria and readable Operator,
        '' and raise a filter changed event
        Me.ReadableOperator = OperatorsList.Text
        If TextBox1.Text.Length > 0 Then
            Me.ReadableCriteria = "'" & TextBox1.Text & "'"
        Else
            Me.ReadableCriteria = ""
        End If
        Me.RaiseFilterChanged()

    End Sub

End Class

Problems

There was one kind of stupid problem that I ran into. I wanted the dialog to have a maximum size, and to have scroll bars if it expanded beyond that size. However, I could not figure out how to do this with an autosizing FlowLayoutPanel. With AutoSize set to True and the maximum size set, it works, but everytime a control is added, even if the form is below the maximum size, the scrollbars flash briefly, and it looks terrible. I tried setting the scroll bar visibility in code myself, but I just couldn't get it to look right. Due to time constraints, I eventually gave up. If anyone has any suggestions on this, I'd be more than happy to hear them.

Updates

09-26-06

There was a bug when trying to filter all items on a specific date. My code before was using 'date = mm/dd/yyyy', however, this only worked if there wasn't a time listed as well. However, Microsoft developed their filter expressions, there is no simple way to do this filter. So, 'date = mm/dd/yyyy' had to be changed to 'date >= #mm/dd/yyyy 12:00am# and date <= #mm/dd/yyyy/ 11:59pm#'. Now, technically, this could be problematic if the time listed is 11:59: and some number of seconds, however, for my purposes, it was worth the time to write logic to figure out what the next day was.

This was more of a problem, because initially each filter item returned its expression as a concatenation of its operator and criteria. Thus, the 'filter' property was not in the Expression control itself, but in the FilterItem Control, which contained one of various types of expressions (date, number, string, etc...). Because the date expression could not be represented with a single operator and criteria, the Filter property had to be moved to the Expression controls.