DataGrid with built-in filter functionality






4.92/5 (148 votes)
A reusable component which extends the DataGrid and adds functionality for real time filtering.
- Download DataGrid demo project - 357 Kb
- Download DataGrid demo project including sample database - 874 Kb
- Download DataGrid source - 110 Kb
- Download DataGridView demo project - 61.4 Kb
- Download DataGridView demo project including sample database - 577 Kb
- Download DataGridView source - 102 Kb
- Download Northwind sample database on MSDN - 476 Kb
Note: The Visual Studio solution contains a project named GridExtensionUnitTests which requires NUnit to compile. If you don't have NUnit then just remove it from the solution - it is not required if you just want to use the grid.
Contents
Introduction
With the DataGrid
, Microsoft has provided a very mighty grid control which has some excellent features. With the possibility to easily add DataView
s and customize visualization, the DataGrid
is for sure one of the most important controls in the .NET Framework.
This component further extends the functionality of the DataGrid
by adding filtering capabilities in an easy, automated and customizable way. The functionality goes beyond the requirements I had in my special case. But I thought it might be a good idea to make something sophisticated which can be used at various places.
Background
Before reading this article, you should know the basics of working with the DataGrid
. That's all you need to use this article. To better understand the technical background, you should also be familiar with the concepts of DataView
s, RowFilter
and DataGridTableStyle
s.
Using the code
There are three ways of using this component:
- The
FilterableDataGrid
control can be used like any other control. It supports design time configuration. In order to display data, first set theDataSource
property. Note that this property (unlike the one in the originalDataGrid
) takes only aDataView
. As a second step aDataGridTableStyle
must be added to theDataGrid
which controls the visualization of data. Normally, theDataGrid
provides default visualization, but in this case a real table style is needed. To make the use of this control easier, I have provided theDataGrid
with some extra functionality to create the needed table styles and column styles as and when needed. The minimum code to get the control to work is://create an instance FilterableDataGrid grid = new FilterableDataGrid(); //styles should be generated automatically grid.EmbeddedGrid.AutoCreateTableStyles = true; //bind the data source grid.DataSource = myDataTable.DefaultView;
That's it!
- If you already have a
DataGrid
with all the properties set and events bound, the exchange might get difficult. In such cases, there is another possibility to add the filtering capabilities. Add theDataGridFilterExtender
component to the form and set itsDataGrid
property to your instance ofDataGrid
. You will see an immediate change in the designer which will show you some dummy filter boxes. You probably have to reposition the grid a bit so that the added control fits onto the form. - If you still want the benefit of automated creation of table styles you also have the option of exchanging your
DataGrid
with anExtendedDataGrid
. As this class derives fromDataGrid
and only adds some new features there shouldn't be any problem doing this.
All the three ways are shown in the included examples.
Samples
I have put much effort in supplying good samples in the downloads. They cover most of the functionality of this component, so if you want to know what this grid is capable of then play around with them. They depend on the MSAccess Northwind database which can be freely downloaded from Microsoft. At the top of the article you will find a direct download link to this. For easier usage it is also contained in one of the demo project downloads.
Architecture
Overview
Internally the most important class is the GridFiltersControl
. It builds up the filter criteria and contains the filter controls (unless they are customized). Most of the functionality is placed within it. Then there is the DataGridFilterExtender
which binds a DataGrid
or ExtendedDataGrid
to a GridFiltersControl
. How the columns are filtered and what GUI has to be shown is defined in the IGridFilter
implementations which are created by an IGridFilterFactory
. The FilterableDataGrid
class is finally a composition of all those classes which allows a quick start.
The following sections contain some more information on the classes within this component. For a more detailed information just look at the fully commented code or have a look into the compiled help file contained in the downloads.
ExtendedDataGrid
This class is derived from DataGrid
. Its main extension is that it provides a property AutoCreateTableStyles
to automatically create table styles. This is done whenever the DataSourceChanged
event occurs and no appropriate table style is found. Furthermore, it publishes some protected
properties from the DataGrid
which are normally not accessible from outside.
IGridFilter
This is the basic interface describing how a column can be filtered. For this it defines the GUI elements and the textual representations of the filter criteria.
IGridFilter implementations
All the given implementations of IGridFilter
derive from the abstract
class GridFilterBase
, which is like all the concrete implementations located in the GridExtensions.GridFilters
namespace. When making your own filters I would recommend using this base class as it reduces the amount of work to be done to get a working filter.
I have provided the following implementations of the IGridFilter
:
TextGridFilter
TextGridFilter
can filter about every column. It uses alike
criterion to filter rows beginning with a specified text. The filter is set with a simpleTextBox
control.BoolGridFilter
BoolGridFilter
is an implementation for boolean columns. The filter is set with aCheckBox
control with three states. The intermediate state means no filter is applied. The other states will filter out the rows not having the specified value.NullGridFilter
NullGridFilter
is an implementation which works on columns of any type. The filter is set with aCheckBox
control with three states. The intermediate state means no filter is applied. The other states will filter out the rows which contain / not containDbNull
.Implementing this one was a bit tricky as there is no direct filter expression to filter out rows with
DbNull
s. The filter expression I generate for the columns look like the following:Convert(ISNULL([ColumnName], 'a§df43dj§öap'), System.String) = 'a§df43dj§öap'
The weird
string
is just made up by me. I hope no one will use this one actually as a value in a grid, because this would result in wrong results (it would be shown as aDbNull
value).NumericGridFilter
The
NumericGridFilter
is an implementation for numeric columns. It uses aNumericGridFilterControl
which consists of aComboBox
containing * and several comparison operators (>, =, ...) and aTextBox
. When theComboBox
is set to *, the filter behaves like aTextGridFilter
. Otherwise the text in theTextBox
will be converted to a numeric value and the data source will be filtered to match the comparison criterion. If no valid number is given this filter will filter out all the rows.DateGridFilter
Nearly the same as the
NumericGridFilter
class, but it builds the filter criteria forDateTime
columns and has aDateGridFilterControl
consisting of aDateTimePicker
instead of a normalTextBox
control.EnumerationGridFilter
EnumerationGridFilter
is an implementation for columns containing a list of distinct values. The filter is set with aComboBox
control which is filled with those values. Which values are set to the list and how to build a filter for them is defined by a customizableIEnumerationSource
implementation. I have provided two default implementations:TypeEnumerationSource
This implementation will get all the members of a given enumeration type and fill the
ComboBox
with them. For this to work, theDataColumn
must have the same type as the enumeration type given here.IntStringMapEnumerationSource
Here, a user defined mapping between the string values (which are shown in the
ComboBox
) and the integer values contained in the data source can be defined.
DistinctValuesGridFilter
The
DistinctValuesGridFilter
analyzes a column for its contained values, and fills aComboBox
with these along with a (*) and a (null) (only if the column containsDbNull
) entry. The user can then select from any of those distinct values to filter accordingly. Using this type ofIGridFilter
with large data sources might lead to a performance loss at startup. Look into the description of theDefaultGridFilterFactory
for further explanations and restrictions.EmptyGridFilter
This one is just a dummy implementation to allow switching off the filter functionality for certain columns.
IGridFilterFactory
This is a simple interface which provides a method to create an IGridFilter
for a specified column. I included this interface to provide maximum flexibility. It could, for example, be used to specify special filters for each column separately.
Instead of having to implement your own IGridFilterFactory
for every special case, it is also possible to extend an existing implementation by binding its GridFilterCreated
event. It provides information about the table, name and the type of the column for which an IGridFilter
is being created and also the instance provided by the factory. The handler of the event can then exchange the IGridFilter
s for the needed columns. This is, for example, useful when you want to define an EnumerationGridFilter
with a customized IntStringMapEnumerationSource
to a special column while leaving all other columns as they are.
IGridFilterFactory implementations
Three implementations of this interface are provided within this component (all are located in the GridExtensions.GridFilterFactories
namespace). Another one can be found in the samples. They not only provide how the columns are filtered but also where the filtering GUI is located. The included implementations are:
DefaultGridFilterFactory
This implementation of
IGridFilterFactory
will be automatically generated and used by theFilterableDataGrid
. It automatically provides valid implementations for the various data types which can be contained in aDataTable
.The creation process consists of these steps:
- If the column data type is an enumeration and
HandleEnumerationTypes
is set totrue
, then anEnumerationGridFilter
is created. - If
CreateDistinctGridFilters
is set totrue
, then it is analyzed if the column contains less or equal distinct values than specified byMaximumDistinctValues
. If yes, then aDistinctValuesGridFilter
is created. TheMaximumDistinctValues
property is not only important to reduce the maximum number of entries theComboBox
gets filled with, but also to improve performance because the analysis of the columns data will be stopped immediately when more values are found than specified by it, and thus the analysis doesn't have to search through the whole data source. - If a grid filter type is specified for the data type of the column, then this one will be created. The data type to grid filter type matching can be altered by calls to
AddGridFilter
andRemoveGridFilter
. Note that, only grid filter types which implementIGridFilter
and which have an emptypublic
constructor are allowed.With this little example, one could switch off the filter functionality for columns of type
DateTime
:myDefaultGridFilter.RemoveGridFilter(typeof(DateTime));
- If still no filter was created, then the filter specified by
DefaultGridFilterType
will be created. By default, this is theTextGridFilter
. Note that, again only grid filter types which implementIGridFilter
and which have an emptypublic
constructor are allowed.
- If the column data type is an enumeration and
NullGridFilterFactory
This implementation of
IGridFilterFactory
always generatesNullGridFilter
s no matter what column was specified.DistinctValuesGridFilterFactory
This implementation of
IGridFilterFactory
always generatesDistinctValuesGridFilter
s no matter what column was specified.FullTextSearchGridFilterFactoryTextBox
This implementation derives from
TextBox
and builds aTextGridFilter
for each column supplying itself as theTextBox
to fetch the criterion from. The effect is that all the columns are filtered by the contents of just one singleTextBox
. This is even possible in several grids simultaneously. To use it just drag it onto the form with theFilterableDataGrid
orGridFilterExtender
and set theFilterFactory
property via designer to this class.LayoutedFridFilterFactoryControl
This implementation is build upon the
DefaultGridFilterFactory
or any otherIGridFilterFactory
implementation. What kind ofIGridFilter
s are created is determined by this inner factory but this implementation customizes the location of the filter GUI in a layouted way - outside of the actual grid. To use it just drag it onto the form with theFilterableDataGrid
orGridFilterExtender
and set theFilterFactory
property via designer to this class.
GridFiltersControl
This internal
class should be of no matter to you if you are only using this library. This is the control which is placed above, beyond or within the DataGrid
which is extended. It holds all the extra controls from the current IGridFilter
s which have not set its UseCustomPlacement
property to true
. It also contains most of the filter building logic of this component.
DataGridFilterExtender
Now we come to the core class of the library. I have documented all the public
properties and methods very well so I won't go too far into the details here. The most important properties are:
DataSource
This property has to be used to set the initial
DataView
. The grid is not limited to that one. If theDataView
is, for example, part of a complexDataSet
, it is possible (like in the normalDataGrid
) to navigate through its relations. The only limitation (as mentioned above) is that an appropriateDataGridTableStyle
must exist.EmbeddedDataGrid
With this property, the underlying grid can be accessed. With this, all the properties of the normal
DataGrid
can be altered. This is not possible with the designer (here, you will have to use the extended grid itself). The extra propertyAutoCreateTableStyles
can also be set here (if you are too lazy to define your own).FilterBoxPosition
Use this property to specify where the filter GUI should appear.
FilterFactory
If you want, you can specify your own implementation of the
IGridFilterFactory
with this property.AutoAdjustGridPosition
This property will adjust the position of the grid depending on where the filters are displayed. This will not work if the grid is docked in any way (anchors are fine).
All other properties can be explored by yourselves.
FilterableDataGrid
This control is nothing more than a UserControl
which binds the ExtendedDataGrid
and the DataGridFilterExtender
together. For every new filterable grid you want to create, this should do the job without having to handle several components.
DataGridView
I finally made a DataGridView
version of this component which is called DataGridViewExtensions
. The solution is logically a Visual Studio 2005 solution while the DataGrid
solution is still 2003. It has most of the functionality of the original component. The missing parts and known issues are:
- The highlighting is not yet contained.
- NDoc documentation is not yet contained.
- Because the
DataGridView
doesn't have a header, the corresponding value in theFilterPosition
enumeration has been removed. - Changing
RightToLeft
while the filters are visible will result in heavy flickering. This shouldn't (hopefully) be a problem because I assume this property is normally not changed while the grid is shown. - Startup performance might be bad when setting the
DataSource
in the constructor of theForm
/Control
theDataGridView
is contained in. A simple workaround is to set theDataSource
in the overriddenOnLoad
method after the base call.
ToDo's
- As you can see in the history section I'm eager to extend this component with useful features requested here. So, if anyone has further suggestions, please feel free to post them here.
History
- March 26th, 2005
- Initial release.
- March 26th, 2005
- (Some hours later) Refactored the library so that a component can be used to easily extend the existing grids. This will solve the problem of having to manually set the properties and bind the events in the nested
DataGrid
.
- (Some hours later) Refactored the library so that a component can be used to easily extend the existing grids. This will solve the problem of having to manually set the properties and bind the events in the nested
- March 31st, 2005 - Several enhancements and fixes were made:
- The component can now work with normal
DataGrid
s. Thus the use of theExtendedDataGrid
is no longer necessary. The only requirement is that an appropriateDataGridTableStyle
must be created and added to the grid. For this, the new helper classDataGridStyleCreator
has been added. - New property
AutoAdjustGridPosition
for automated repositioning of the grid added. - Fixed bug in the property
AutoCreateTableStyle
. - Fixed bug when removing the
DataGridExtender
component from its assignedForm
. - Added automated change of colors of the filter GUI when the colors of the underlying controls or the grid change.
- Added handling of the
CaptionVisible
property of theDataGrid
. This is important as a caption is needed in some display modes. - Added handling of change events from the style collections to immediately reflect changes in the filter GUI.
- The component can now work with normal
- April 9th, 2005 - Some enhancements and fixes:
- Added the
DateGridFilterControl
to allow better filtering ofDateTime
columns. - Corrected some minor mistakes in my comments.
- Added some attributes for (a bit) clearer design time support.
- Added the
- April 11th, 2005 - Some enhancements and fixes:
- Added the
EnumerationGridFilter
to allow user friendly filtering of columns containing enumeration types. - Fixed an error regarding criteria string building. Thanks to Juergen Posny for pointing this out.
- Moved all the
IGridFilter
implementations to a separate namespace (GridExtensions.GridFilters
). - Added a
ComboBox
to the main form to allow the user to select what data should be presented from the Northwind database. Also, added an enumeration type column programmatically to show the functionality of the newly addedEnumerationGridFilter
.
- Added the
- April 15th, 2005 - Some enhancements:
- Added a property
Operator
to allow definition if the criteria is combined with a logicalAND
or a logicalOR
. - Added a method
ClearFilters
to clear all the set filters to their initial state.
- Added a property
- April 16th, 2005 - Some enhancements and fixes:
- Corrected the wrong appearance of the filters when the
RowHeadersVisible
property of theDataGrid
was set tofalse
. Thanks to Dean_DWD for pointing out this. - Added two properties
MessageErrorMode
andConsoleErrorMode
that allow configuring the kind of output that is generated when an error occurs in the built filter criteria.
- Corrected the wrong appearance of the filters when the
- May 14th, 2005 - Some fixes:
- Corrected some bad behaviour when using
AutoAdjustGridPosition
with anchored grids, which could screw up the designer in certain situations. - Corrected a special situation where a wrong filter criteria was created.
- Corrected some bad behaviour when using
- October 3rd, 2005 - Some enhancements and fixes:
- Corrected typo of several
ScrollBar
properties. Thanks to Goyuix for pointing this out. - Changed the
EnumerationGridFilter
so that it is easier to build customized value list filters. This is based on a request made by Thomas-H. - Added the
GridFilterCreated
event to theIGridFilterFactory
class to allow easy modifications to the filter behaviour of single classes (mainly needed for the new functionality inEnumerationGridFilter
). - Created a new startup form from where one can start three demo forms showing most of the functionality of this component.
- Minor corrections of code formatting.
- Corrected typo of several
- November 19th, 2005 - Some enhancements and fixes:
- Added strict usage of [] around the column names in the filter building process. This should eliminate issues regarding extravagant column names.
- Added a
SetFilter
function toIGridFilter
which is basically the reverse version ofGetFilter
. With the help of this, I could add the functionGet
/SetFilters
to bothDataGridFilterExtender
andFilterableDataGrid
, which can save/load the whole filter configuration. I made this based on a request from Ken Dreyer. - To make the last point easier to implement, I changed the whole logic of building filters by using
string.Format
and regular expressions, which should make the whole process a bit cleaner. - Added some unit tests to automatically test the new
Get/SetFilters
functions. - Added an event
FilterChanged
so that one can get a notification whenever the filter criterion is changed (by the extender not theDataView
itself).
- December 7th, 2005 - Version 2.0:
- I've made so many changes that I think it's worth a new version. From now on I will try incrementing the version with every bug fix or enhancement.
- Changed the
CreateGridFilter
function of theIGridFilterFactory
to allow easier customization of the filter creation process (sorry for any broken custom implementations). - Refactored the
IGridFilter
implementations to make use of a commonGridFilterBase
class and also separated the GUI of theNumericGridFilter
andDateGridFilter
from the filtering logic. - Filter controls are now allowed to be placed outside of the grid and are independent of the created
IGridFilter
s. This is used by the newIGridFilterFactory
implementations (like theFullTextSearchGridFilterFactory
based on a request from Carlso) and generally gives a huge bunch of new possibilities for customization. - Some namespace refactorings.
- A new property
AutoRefresh
accessible in theGridFilterExtender
orFilterableDataGrid
. For very large tables this property can be set tofalse
after which the view will not get updated with every change to the filter controls untilRefreshFilters
is called orAutoRefresh
is set totrue
again. - Added some more support and descriptions for the designer.
- Included compiled help file in the downloads. An NDoc project file is also included containing the settings to build the help file as well as a HTML documentation (which is not included in the downloads).
- Several minor bug fixes and refactorings.
- January 14th, 2006 - Version 2.1:
- Added a new property
GridMode
which controls whether the grid is filtered or matching rows just get highlighted. This is based on a request from Muhammad Waqas Butt. - To achieve the highlighting I added the new class
DataFilter
which wraps the internalDataFilter
class contained in the .NET Framework. Because this class is internal I needed to build a wrapper which uses it with reflection mechanisms. - Corrected LeftToRight behaviour which was totally screwed up. Thanks to arashr for pointing this out.
- Added a new property
- April 22nd, 2006 - Version 2.2:
- Fixed that the
HandleEnumerationTypes
property on theDefaultGridFilterFactory
was ignored. - Fixed that the
SetFilters
andGetFilters
calls would sometimes not work onBoolGridFilter
s. - Added a new
IGridFilter
implementation with the nameNullGridFilter
and a corresponding factory namedNullGridFilterFactory
. This is based on a request from HITMUS. - Added a new
IGridFilter
implementation with the nameDistinctValuesGridFilter
and a corresponding factory namedDistinctValuesGridFilterFactory
. Also added functionality to theDefaultGridFilterFactory
to allow usage of this filter in standard situations. This is based on a request from HITMUS and anandcts. - Extended Sample1 to show the new functionalities.
- Wrote some more unit tests.
- Fixed that the
- May 7th, 2006 -
GridViewExtensions
- Version 1.0:- Added a
DataGridView
version of the component, and a new section in the article shortly describing it.
- Added a
- May 20th, 2006 - GridViewExtensions 1.1 - GridExtensions 2.2:
- Added support to more easily change the filter settings programmatically. This was already in my head several weeks, and because of a request from rEgEn2k, I finally implemented it. In detail, the changes are:
- Public properties on all
IGridFilter
implementations to get and set the current settings. - A
GetGridFilters
function which returns all the currently shownIGridFilter
s. The returned collection class has functions to filter by type and accessIGridFilter
s by several means. - Added a
TextBox
along with aButton
to Sample 1 which demonstrates how to set all the values onIGridFilter
s of a special type.
- Public properties on all
- Added support to more easily change the filter settings programmatically. This was already in my head several weeks, and because of a request from rEgEn2k, I finally implemented it. In detail, the changes are:
- June 5th, 2006 - GridViewExtensions 1.2 - GridExtensions 2.3:
- Added a new operator for date and numeric columns to filter for values in between two given values. When selected, two
DateTimePicker
s/TextBox
es will appear and wait for user input. This functionality is by default off, and can be explicitly turned on every filter separately, or by setting eitherDefaultShowDateInBetweenOperator
orDefaultShowNumericInBetweenOperator
on theDefaultGridFilterFactory
. Many thanks to Luis Carlos Gallego on this one. He not only provided the idea but also most of the code for this. - Added four properties:
BaseFilters
,BaseFilterEnabled
,BaseFilterOperator
, andCurrentTableBaseFilter
. With them, it's possible to define a filter criteria which gets concatenated with the filter criteria generated by the control before applying it as a row filter. Again thanks to Luis Carlos Gallego for providing the idea and some code. - Added several unit tests (only
DataGrid
version) for the 'in between' regular expressions, and reorganized them into several files because the old one just got too big (a total of 87 tests now integrated).
- Added a new operator for date and numeric columns to filter for values in between two given values. When selected, two
- July 2nd, 2006 - GridViewExtensions 1.3:
- Added support for using
BindingsSource
s andDataSet
s which can now be directly assigned to theDataSource
property of theDataGridView
. Furthermore, theDistinctValuesGridFilter
can now work with such data bindings. Thanks to smatusan and Aventura for pointing that out and also helping me with the implementation. - Fixed a bug in the
DateGridFilter
. Thanks to macus for reporting.
- Added support for using
- October 1st, 2006 - GridExtensions 2.4:
- Fixed bug where a manual call to
RefreshFilters
wouldn't show the desired results when there are no filters currently set. - Added redirection of events from the embedded grid of the
FilterableGrid
so that they can be used within the designer as you would normally do. I've done this forMouseDown
,MouseUp
,MouseMove
,MouseEnter
,MouseLeave
,MouseHover
,KeyUp
,KeyDown
, andKeyPress
. This is based one a request made by Marcelo Miorelli. All other events can be reached by using theEmbeddedDataGrid
property. If someone needs more than this, feel free to post requests. - Replaced the property
AutoRefresh
withAutoRefreshMode
which now allows several settings instead of only on/off. This includes refreshing the filter only when pressing Enter and/or when the focus leaves a filter control. This is based on a request made by georg_werner. Added a new combo box to the first sample to test this. - Some minor code reorganization.
- Fixed bug where a manual call to
- October 1st, 2006 - GridViewExtensions 1.4:
- Added the same enhancements as in the GridExtensions.
- Added full support for any data source implementing
IBindingListView
. The only requirement is that it must allow complex filters. - Added a
IBindingListView
implementation which works as a wrapper around any givenIList
. Thus, this component now also works with list classes. Because of this implementation, sorting on lists is now supported by the grid. Have a look at the new sample 6 to see how this works. This feature was requested by several users, so I hope you'll appreciate it. As I haven't tested this in any real world scenario, I'll need feedback if it lacks something.
- October 15th, 2006 - GridViewExtensions 1.4.1:
- Fixed a bug reported by georg_werner where an internal refresh wouldn't apply the correct filtering to the grid when all contents were deleted from the filter controls.
- November 18th, 2006 - GridViewExtensions 1.5 and GridExtensions 2.5:
- Fixed bug that the
BaseFilterEnabled
property was ignored. Thanks again to georg_werner for reporting and even fixing the bug. - The
DoubleClick
event should now function as expected in bothFiltarableGrid
andFiltarableGridView
.
- Fixed bug that the