Auto Filtering GridView Control
A component replacement for the standard GridView that adds filters to the header and does not require code changes on your page.
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:Trueto add filters,Falseto not add them.FilterButtonsColumnIndex: Tell theGridViewwhat header column to place the apply and clear buttons in.ClientCalidateFilters:Trueto auto validate the filters,Falseto 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.
- Add filter fields
- Add filter related buttons
- 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.