Auto Filtering GridView Control

• Download source code with sample page and sample database - 1.7 MB
• Download DLL only - 9.6 KB

Introduction

I have only been working in ASP.NET for a couple of years now, having come from a Classic ASP background. I have had great need for a GridView control that has the ability to allow filtering on the columns. I searched for a simple and free control that would do what I needed, and mostly found people manually placing filters in the header or footer and writing code behind to handle the filtering. I ended up doing this myself, but always felt there should be an easier way than having to rewrite the code behind every page I made, or editing the GridView itself every page to get the filtering ability.

If you rather just try the control with your own project, download the Bin.zip, place the DLL in your project bin folder, add the reference to the DLL, register it on your aspx page and replace your GridView with the AutoFilterGridView.

Background

I was reusing the same code to add filtering in the headers of GridViews on far too many ASP.NET pages. It did work, but I was tired of having to rewrite the code behind every time I wanted filters. I finally decided to use some of my existing codebehind, some new code and make a new GridView control that I could simply drop on a page and have it work or replace the Microsoft GridView control on a page with my new Gridview and do no other special coding to add filtering to the columns. Then I realized that a standard filter was sometimes a drawback and decided to take it a bit further. I realized a minimum/maximum range would be needed for date/time, numeric and Boolean types, and so added that. In the end, I made a simple replacement for the GridView that will add filters when told to. No additional coding is required, just replace the GridView with the new one. I call it "AutoFilterGridView". I have tested the control with SQL Server 2005 & 2008. Keep in mind this is a basic component made to suit my needs at the time. There is a lot of room for improvement and personalization if you decide to use it..

Using the Code

To make the control, I inherited the System.Web.UI.WebControls.GridView and added a class named "AutoFilterGridView.vb" and then created a shell that would become the new component.

Imports System.Text.RegularExpressions
Imports System.Web.UI.HtmlControls

<Assembly: TagPrefix("MyCustomControl.CustomsControls", "asp")>

<ToolboxData("<{0}:AutoFilterGridView runat=""server""></{0}:AutoFilterGridView>")> _
Partial Public Class AutoFilterGridView
    Inherits GridView 

End Class

The control will need a location to store information on the fields it will filter as well. To get the component made quickly, I opted to store information in a private class and then store this class in the ViewState. I realize that it may not be the best way to store the information, but you are more than welcome to rewrite this component all you like.

   'used to hold information needed for filtering the fields
   ' needs to be serializable so we can store it in the viewstate
   <Serializable()> _
Private Class FilterInfo
       Public Name As String
       Public PlaceHolder As String
       Public DataFieldType As System.Type
       Public DataFieldName As String
       Public [Operator] As String
   End Class

   Private Filters As New List(Of FilterInfo)

Next, I needed a way to communicate with the component through properties. I wanted a way to have filters or not, where to place the apply filters button and remove filters button and if I wanted the control to supply basic validation on the filter fields. The validation is to prevent someone from trying to filter a date with "Frank" or a number with "@@#!". The properties will be:

• IncludeFilters: True to add filters, False to not add them.
• FilterButtonsColumnIndex: Tell the GridView what header column to place the apply and clear buttons in.
• ClientCalidateFilters: True to auto validate the filters, False to not (so you can do it yourself on the page).

   <Category("Behavior")> _
   <Description("Add filters the gridview.")> _
   <DefaultValue(False)> _
Public Property IncludeFilters() As Boolean
       Get
           If String.IsNullOrEmpty(ViewState("IncludeFilters")) Then
               Return False
           Else
               Return DirectCast(ViewState("IncludeFilters"), Boolean)
           End If
       End Get
       Set(ByVal Value As Boolean)
           ViewState("IncludeFilters") = Value
       End Set
   End Property

   <Category("Behavior")> _
    <Description("Add filter button to which column position (0=first empty)?")> _
    <DefaultValue(-1)> _
   Public Property FilterButtonsColumnIndex() As Integer
       Get
           If String.IsNullOrEmpty(ViewState("FilterButtonsColumnIndex")) Then
               Return 0
           Else
               Return DirectCast(ViewState("FilterButtonsColumnIndex"), Integer)
           End If
       End Get
       Set(ByVal Value As Integer)
           ViewState("FilterButtonsColumnIndex") = Value
       End Set
   End Property

   <Category("Behavior")> _
    <Description("Basic client validation on filters")> _
    <DefaultValue(True)> _
   Public Property ClientValidateFilters() As Boolean
       Get
           If String.IsNullOrEmpty(ViewState("ClientValidateFilters")) Then
               Return True
           Else
               Return DirectCast(ViewState("ClientValidateFilters"), Boolean)
           End If
       End Get
       Set(ByVal Value As Boolean)
           ViewState("ClientValidateFilters") = Value
       End Set
   End Property

Obviously, we cannot do basic clientside validation without some clientside code. I knew that some of my pages would have JQuery included and some would not, so I coded the needed clientside script to handle it either way on its own. I realize that was not needed since coding it to not use JQuery would be enough, but I wanted to show people how to do it either way. I am not going to go through this code since I am sure you will be able to read it. What I am going to show is how I included the file in the component.

' Adds the needed js file for validation and clearing the filters
Protected Overrides Sub OnPreRender(e As EventArgs)
    MyBase.OnPreRender(e)
    Dim resourceName As String = "MyCustomControls.AutoFilterGridView.js"
    Dim cs As ClientScriptManager = Me.Page.ClientScript
    cs.RegisterClientScriptResource(GetType(MyCustomControls.AutoFilterGridView), resourceName)
End Sub

Now we get to the main part. When the control is DataBound and IF we want filters added to the headers, then we will have work to do. The DataBound event is where creation of the filter controls happen and where the information on the field data is stored in the ViewState.

Private Sub AutoFilterGridView_DataBound(sender As Object, e As System.EventArgs) Handles Me.DataBound
   ' if the control is not told to add filters leave
   If Not IncludeFilters Then Return

   If Me.Controls.Count > 0 Then
      If Not AddFilterHeader() Then Return
   End If

   ' store the filter list in the viewstate
   Using sw As New IO.StringWriter()
      Dim los = New LosFormatter
      los.Serialize(sw, Filters)
      ViewState("CustomGridFilters") = sw.GetStringBuilder().ToString()
   End Using

   Return
End Sub

The AddFilterHeader routine is used to walk the columns of the gridview to determine the data type in them, create the column filters and map out the columns into the filters list. For each column, there are only 3 possible things that can be done.

1. Add filter fields
2. Add filter related buttons
3. Do nothing

The first thing the routine checks is if filter fields need to be added. Will happen IF the column is not designated to hold the filter buttons through the property added earlier, the header is visible, the column is visible and the field in the column is or related to a BoundField control. If the column is going to get a filter, then the MakeFilterCell function is called.

If the column is not going to get filters in the header, then the routine checks if the column is slated to receive the filter buttons. If so, then the buttons are added and a flag is set so buttons are no longer added.

If the column is not going to receive a filter or buttons, then an empty cell is created.

Private Function AddFilterHeader() As Boolean
   ' get a view of the table
   Dim myTable = DirectCast(Me.Controls(0), Table)
   Dim myNewRow = New GridViewRow(0, -1, DataControlRowType.Header, DataControlRowState.Normal)
   Dim boolFilterDropped As Boolean = False
   Filters.Clear()
   ' Get a view of the data columns
   Dim columns As DataColumnCollection
   If Not IsNothing(Me.DataSource) AndAlso Me.DataSource.GetType() Is GetType(DataTable) Then
     columns = DirectCast(Me.DataSource, DataTable).Columns
   ElseIf Not IsNothing(Me.DataSourceObject) AndAlso Me.DataSourceObject.GetType() Is GetType(SqlDataSource) Then
     columns = CType(CType(Me.DataSourceObject, SqlDataSource).Select(DataSourceSelectArguments.Empty), DataView).Table.Columns
   Else
     Return False
   End If

   'For each column, process it
   For x = 1 To Me.Columns.Count
      With Me.Columns(x - 1)
         If (FilterButtonsColumnIndex <> x) AndAlso _
         (.ShowHeader AndAlso .Visible AndAlso GetType(BoundField).IsAssignableFrom(.GetType()) _
         AndAlso Not String.IsNullOrEmpty(CType(Me.Columns(x - 1), BoundField).DataField)) Then
         Using tc As New TableHeaderCell
            AddFilterControls(tc, columns(CType(Me.Columns(x - 1), _
            BoundField).DataField.ToString).DataType, CType(Me.Columns(x - 1), _
            BoundField).DataField.ToString, .GetType())
            tc.CssClass = "filterHeader"
         End Using
         ElseIf FilterButtonsColumnIndex = x OrElse (FilterButtonsColumnIndex = 0 _
         AndAlso .Visible And Not boolFilterDropped) Then
            Using tc As New TableHeaderCell
               tc.CssClass = "filterButtons"
               tc.Controls.Add(New Button() With {.ID = "btnApplyFilters", _
               .CommandName = "ApplyFilters", _
               .Text = "Filter", .CssClass = "filterButton"})
               Using b As New Button
                  b.ID = "btnClearFilters"
                  b.CommandName = "ClearFilters"
                  b.Text = "Clear"
                  b.CssClass = "filterButton"
                  b.Attributes.Add("onclick", "ClearAllFilters()")
                  tc.Controls.Add(b)
               End Using
               myNewRow.Cells.Add(tc)
            End Using
            boolFilterDropped = True
         ElseIf .Visible Then   ' just make an empty cell
            myNewRow.Cells.Add(New TableHeaderCell())
         End If
      End With

   Next

   myTable.Rows.AddAt(0, myNewRow)
   Return True
End Sub

The AddFilterControls routine is where the filters are created and added to the header cell. In order to decide what type of filter is needed, the routine needs to look at the DataFieldType associated to the DataField type in the BoundField in the column and then handle them as required. The information needed to properly filter the data with this field will also be stored in the filters list for use later.

The first type it handles is Boolean. Since the component will allow boolean data as a checkbox or other types, we have to check the control being used in the column. If it is a checkbox, then a tri-state checkbox will be used for filtering (checked, unchecked, indeterminate).
The checkbox state will be handled by the JavaScript code we dropped earlier.
The checkbox is also given an id, name, data-indeterminate attribute, checked if we see it has been checked prior and then a class so we can style it as needed on the client-side.

   Private Sub AddFilterControls(ByRef hc As TableHeaderCell, DataFieldType As System.Type, _
DataFieldName As String, BoundFieldType As System.Type)
      ' Based on the datatype we will need to make different controls and set the values
      Select Case Type.GetTypeCode(DataFieldType)
         Case TypeCode.Boolean
            If BoundFieldType Is GetType(CheckBoxField) Then
               ' create a tristate checkbox
               Using i As New HtmlGenericControl("input")
                  i.Attributes.Add("id", "filter1_" & DataFieldName)
                  i.Attributes.Add("name", i.Attributes("id"))
                  i.Attributes.Add("type", "checkbox")
                  i.Attributes.Add("data-indeterminate", _
                  String.IsNullOrEmpty(If(Page.Request(i.Attributes("name")), String.Empty)))
                  If String.Compare(If(Page.Request(i.Attributes("name")), _
                  String.Empty), "True", True) = 0 Then
                     i.Attributes.Add("checked", String.Compare_
                     (If(Page.Request(i.Attributes("name")), String.Empty), "True", True) = 0)
                  End If
                  i.Attributes.Add("class", "autoFilter tri " & DataFieldType.Name.ToLower)

                  hc.Controls.Add(i)

                  Filters.Add(New FilterInfo() With {.Name = i.Attributes("name"), _
                  .DataFieldType = DataFieldType, .DataFieldName = DataFieldName, .Operator = "="})
               End Using

If the Boolean is not a checkbox type, then we will make it a select with options for true, false and empty.
The select is given an id, name and a class so we can style it as needed on the client-side. One of the options will have selected set is needed.

Else
   ' create a true/false/any dropdownlist
   Using i As New HtmlGenericControl("select")
      i.Attributes.Add("id", "filter1_" & DataFieldName)
      i.Attributes.Add("name", i.Attributes("id"))
      Using o As New HtmlGenericControl("option")
         o.Attributes.Add("value", "")
         o.InnerText = ""
         If (If(Page.Request(i.Attributes("name")), String.Empty)) = o.Attributes("value") Then
            o.Attributes.Add("selected", "selected")
         End If
         i.Controls.Add(o)
      End Using
      Using o As New HtmlGenericControl("option")
         o.Attributes.Add("value", "false")
         o.InnerText = "False"
         If (If(Page.Request(i.Attributes("name")), String.Empty)) = o.Attributes("value") Then
            o.Attributes.Add("selected", "selected")
         End If
         i.Controls.Add(o)
      End Using
      Using o As New HtmlGenericControl("option")
         o.Attributes.Add("value", "true")
         o.InnerText = "True"
         If (If(Page.Request(i.Attributes("name")), String.Empty)) = o.Attributes("value") Then
            o.Attributes.Add("selected", "selected")
         End If
         i.Controls.Add(o)
      End Using

      i.Attributes.Add("class", "autoFilter " & DataFieldType.Name.ToLower)

      hc.Controls.Add(i)

      Filters.Add(New FilterInfo() With {.Name = i.Attributes("name"), _
      .DataFieldType = DataFieldType, .DataFieldName = DataFieldName, .Operator = "="})
   End Using
End If

The next data type the routine handles are the number types. Since numeric fields are given a range to filter with, 2 input fields will be dropped. The first is for the minimum value and the second is for the maximum value.
The 2 input fields will be given attributes: id, name, class, placeholder (so users know what the field does), maxlength and if ClientValidateFilters is set an onblur event call to basic validation is added.

         Case TypeCode.Byte, TypeCode.Decimal, TypeCode.Double, TypeCode.Int16, _
TypeCode.Int32, TypeCode.Int64, TypeCode.SByte, TypeCode.Single, TypeCode.UInt16, TypeCode.UInt32, TypeCode.UInt64
            ' This is a range control, add min then max
            Dim mm As String() = {"min", "max", ">=", "<="}
            For x = 1 To 2
               Using i As New HtmlGenericControl("input")
                  i.Attributes.Add("id", "filter" & x.ToString & "_" & DataFieldName)
                  i.Attributes.Add("name", i.Attributes("id"))
                  i.Attributes.Add("placeholder", mm(x - 1))
                  If Type.GetTypeCode(DataFieldType) = TypeCode.Byte Then
                     i.Attributes.Add("maxlength", 4)
                  Else
                     i.Attributes.Add("maxlength", 20)
                  End If
                  i.Attributes.Add("class", "autoFilter " & mm(x - 1) & _
                  "Value numericValue " & DataFieldType.Name.ToLower)
                  If ClientValidateFilters Then
                     Select Case Type.GetTypeCode(DataFieldType)
                        Case TypeCode.Byte, TypeCode.Int16, TypeCode.Int32, TypeCode.Int64, TypeCode.SByte
                           i.Attributes.Add("onblur", "ValidateAutoFilter(this,/^([\-+]?\d+)?$/)")

                        Case TypeCode.UInt16, TypeCode.UInt32, TypeCode.UInt64
                           i.Attributes.Add("onblur", "ValidateAutoFilter(this,/^(\d+)?$/)")

                        Case TypeCode.Decimal, TypeCode.Double, TypeCode.Single
                           i.Attributes.Add("onblur", "ValidateAutoFilter(this,/^([-+]?[0-9]*\.?[0-9]+)?$/)")
                     End Select
                  End If

                  i.Attributes.Add("value", If(Page.Request(i.Attributes("id")), String.Empty))
                  hc.Controls.Add(i)

                  Filters.Add(New FilterInfo() With {.Name = i.Attributes("id"), _
                  .DataFieldType = DataFieldType, .DataFieldName = DataFieldName, .Operator = mm(x + 1), _
                  .PlaceHolder = i.Attributes("placeholder")})
               End Using
            Next

The next data type the routine handles are the date/time types. Since these field types are given a range to filter with, 2 input fields will be dropped. The first is for the minimum value and the second is for the maximum value.

The input fields will be given attributes: id, name, class, placeholder (so users know what the field does), maxlength and if ClientValidateFilters is set an onblur event call to basic validation is added.

Case TypeCode.DateTime
   ' This is a range control, add min then max
   Dim mm As String() = {"min", "max", ">=", "<="}
   For x = 1 To 2
      Using i As New HtmlGenericControl("input")
         i.Attributes.Add("id", "filter" & x.ToString & "_" & DataFieldName)
         i.Attributes.Add("name", i.Attributes("id"))
         i.Attributes.Add("placeholder", mm(x - 1))
         i.Attributes.Add("maxlength", 22)
         i.Attributes.Add("class", "autoFilter " _
         & mm(x - 1) & "Value " & DataFieldType.Name.ToLower)

         If ClientValidateFilters Then
            i.Attributes.Add("onblur", "ValidateAutoFilter_
            (this, /^((0?[1-9]|1[012])[- /.](0?[1-9]|[12][0-9]|3[01])[- /.](19|20)[0-9]{2}_
            ( ((2[0-3]|[0-1]?[0-9]):[0-5][0-9](:[0-5][0-9])?|(1[0-2]|[1-9]):[0-5][0-9]_
            (:[0-5][0-9])?( (am|pm))?))?)?$/i)")
         End If

         i.Attributes.Add("value", If(Page.Request(i.Attributes("id")), String.Empty))
         hc.Controls.Add(i)

         Filters.Add(New FilterInfo() With {.Name = i.Attributes("id"), _
         .DataFieldType = DataFieldType, .DataFieldName = DataFieldName, _
         .Operator = mm(x + 1), .PlaceHolder = i.Attributes("placeholder")})
      End Using
   Next

The routine handles the remaining types as text. These types are given a single input only.
The input field is then given attributes: id, name, class, placeholder (so users know what the field does) and maxlength.

      Case Else
         Using i As New HtmlGenericControl("input")
            i.Attributes.Add("name", "filter_" & DataFieldName)
            i.Attributes.Add("id", "filter_" & DataFieldName)
            i.Attributes.Add("placeholder", "contains")
            If DataFieldType.Name = "Char" Then
               i.Attributes.Add("maxlength", 1)
            Else
               i.Attributes.Add("maxlength", 255)
            End If

            i.Attributes.Add("class", "autoFilter textValue " & DataFieldType.Name.ToLower)

            i.Attributes.Add("value", If(Page.Request(i.Attributes("id")), String.Empty))
            hc.Controls.Add(i)

            Filters.Add(New FilterInfo() With {.Name = i.Attributes("id"), _
            .DataFieldType = DataFieldType, .DataFieldName = DataFieldName, _
            .Operator = "LIKE", .PlaceHolder = i.Attributes("placeholder")})
         End Using

   End Select

End Sub

The next important logic branch happens when the page is loaded. This is where the data will be filtered. If the control is set to include filtering and it is Page.IsPostBack is true then the filtering routine is called.

Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
    If IncludeFilters AndAlso Page.IsPostBack Then
        FilterTheData()
    End If
End Sub

The filtering is fairly basic and simply needs to decide how apply the filters. The only special code here is on the date filterers and only on the max value when it does not contain a time. Since the max value is inclusive and no time assumes midnight, we have to adjust the value by adding a day to it. For each filter we have from the webpage with a value a FormParameter is created and added to the FilterParameters of the SqlDataSource. Then the filter expression that will be used is added to a list of string. At the end the list of filter expressions is build up and assigned to the SqlDataSource.FilterExpression and then .DataBind is called on the datasource and the component.

Private Sub FilterTheData()
    ' retrieve the latest filter information from the viewstate
    Filters = (New LosFormatter()).Deserialize(ViewState("CustomGridFilters"))
    Using ds As SqlDataSource = CType(Me.DataSourceObject, SqlDataSource)
        Dim FilterExpressions As New List(Of String)()

        With ds
            ' remove parameters and recreate the needed ones
            .FilterParameters.Clear()
            ' cycle through each filterable column set in the filters list
            For Each filter As FilterInfo In Filters
                ' if there is a request value for the column
                '(that is not equal to the placeholder) then add the filter to the datasource
                If Not String.IsNullOrEmpty(If(Page.Request(filter.Name), String.Empty)) _
                AndAlso Page.Request(filter.Name) <> If(filter.PlaceHolder, String.Empty) Then
                    Dim p As New FormParameter(filter.Name, filter.Name)
                    With p
                        .Type = System.Type.GetTypeCode(filter.DataFieldType)
                        .DefaultValue = Page.Request(filter.Name)
                        ' If this is a datetime filter and the max value,
                        ' we need to make an adjustment if there is no time.
                        ' Since the filter should be =< the date and a date only
                        ' is treated as midnight, add a dat to the value
                        If .Type = TypeCode.DateTime AndAlso Regex.IsMatch(filter.Name, "^filter2_") _
                        AndAlso Regex.IsMatch(.DefaultValue, "^((0?[1-9]|1[012])[- /.]_
                        (0?[1-9]|[12][0-9]|3[01])[- /.](19|20)[0-9]{2})?$") Then
                            .DefaultValue = DateAdd(DateInterval.Second, -1, _
                            DateAdd(DateInterval.Day, 1, DateTime.Parse(.DefaultValue)))
                        End If
                    End With
                    .FilterParameters.Add(p)

                    ' create each expression for the parameter
                    Select Case System.Type.GetTypeCode(filter.DataFieldType)
                        Case TypeCode.Boolean
                            FilterExpressions.Add(filter.DataFieldName & _
                            filter.Operator & " '{" & (.FilterParameters.Count - 1) & "}'")

                        Case TypeCode.Byte, TypeCode.Decimal, TypeCode.Double, TypeCode.Int16, _
                        TypeCode.Int32, TypeCode.Int64, TypeCode.SByte, TypeCode.Single, _
                        TypeCode.UInt16, TypeCode.UInt32, TypeCode.UInt64
                            FilterExpressions.Add(filter.DataFieldName & filter.Operator & _
                            "{" & (.FilterParameters.Count - 1) & "}")

                        Case TypeCode.DateTime
                            FilterExpressions.Add(filter.DataFieldName & filter.Operator & _
                            "#{" & (.FilterParameters.Count - 1) & "}#")

                        Case Else
                            FilterExpressions.Add(filter.DataFieldName & _
                            " " _& filter.Operator & " '%{" & _
                            (.FilterParameters.Count - 1) & "}%'")

                    End Select

                End If
            Next

            ' convert filter expressions into one expression and assign it to the filterexpression
            .FilterExpression = [String].Join(" AND ", FilterExpressions.ToArray())
            .DataBind()
        End With

        Me.DataBind()
    End Using
End Sub

Variations I Forgot to Mention

Based on the message left below by Simon, I suppose I should have explained about the "Edit" button and filter buttons in the image of the control. The "Edit" column was used to edit the record associated to the row. The codebehind receives the record ID and you can process it as you like. It has an added benefit of leaving an empty header cell that I told the control to use for the filter buttons. If you do not want a command column in the GridView OR simply do not have an empty header area to tell the control to place the filter buttons in, you can just set the FilterButtonsColumnIndex of the AutoFilterGridView to a value of -1 (a column that cannot exist) and the control won't drop the buttons. Then somewhere on your page, add 2 buttons yourself. They do not even have to be an ASP.NET button. You can place this after the gridview or above it:

<input class="filterButton" type="submit" value="Filter">
<input class="filterButton" onclick="ClearAllFilters();" type="submit" value="Clear">

and they will function just fine.