DataGrid with built-in filter functionality

• 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

• Contents
• Introduction
• Background
• Using the code
• Samples
• Architecture
  • Overview
  • ExtendedDataGrid
  • IGridFilter
  • IGridFilterFactory
  • GridFiltersControl
  • DataGridFilterExtender
  • FilterableDataGrid
• DataGridView
• ToDo's
• History

Introduction

With the DataGrid, Microsoft has provided a very mighty grid control which has some excellent features. With the possibility to easily add DataViews 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 DataViews, RowFilter and DataGridTableStyles.

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 the DataSource property. Note that this property (unlike the one in the original DataGrid) takes only a DataView. As a second step a DataGridTableStyle must be added to the DataGrid which controls the visualization of data. Normally, the DataGrid provides default visualization, but in this case a real table style is needed. To make the use of this control easier, I have provided the DataGrid 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 the DataGridFilterExtender component to the form and set its DataGrid property to your instance of DataGrid. 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 an ExtendedDataGrid. As this class derives from DataGrid 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 a like criterion to filter rows beginning with a specified text. The filter is set with a simple TextBox control.

• BoolGridFilter

  BoolGridFilter is an implementation for boolean columns. The filter is set with a CheckBox 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 a CheckBox control with three states. The intermediate state means no filter is applied. The other states will filter out the rows which contain / not contain DbNull.

  Implementing this one was a bit tricky as there is no direct filter expression to filter out rows with DbNulls. 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 a DbNull value).

• NumericGridFilter

  The NumericGridFilter is an implementation for numeric columns. It uses a NumericGridFilterControl which consists of a ComboBox containing * and several comparison operators (>, =, ...) and a TextBox. When the ComboBox is set to *, the filter behaves like a TextGridFilter. Otherwise the text in the TextBox 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 for DateTime columns and has a DateGridFilterControl consisting of a DateTimePicker instead of a normal TextBox control.

• EnumerationGridFilter

  EnumerationGridFilter is an implementation for columns containing a list of distinct values. The filter is set with a ComboBox 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 customizable IEnumerationSource 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, the DataColumn 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 a ComboBox with these along with a (*) and a (null) (only if the column contains DbNull) entry. The user can then select from any of those distinct values to filter accordingly. Using this type of IGridFilter with large data sources might lead to a performance loss at startup. Look into the description of the DefaultGridFilterFactory 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 IGridFilters 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 the FilterableDataGrid. It automatically provides valid implementations for the various data types which can be contained in a DataTable.

  The creation process consists of these steps:

  • If the column data type is an enumeration and HandleEnumerationTypes is set to true, then an EnumerationGridFilter is created.
  • If CreateDistinctGridFilters is set to true, then it is analyzed if the column contains less or equal distinct values than specified by MaximumDistinctValues. If yes, then a DistinctValuesGridFilter is created. The MaximumDistinctValues property is not only important to reduce the maximum number of entries the ComboBox 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 and RemoveGridFilter. Note that, only grid filter types which implement IGridFilter and which have an empty public 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 the TextGridFilter. Note that, again only grid filter types which implement IGridFilter and which have an empty public constructor are allowed.
• NullGridFilterFactory

  This implementation of IGridFilterFactory always generates NullGridFilters no matter what column was specified.

• DistinctValuesGridFilterFactory

  This implementation of IGridFilterFactory always generates DistinctValuesGridFilters no matter what column was specified.

• FullTextSearchGridFilterFactoryTextBox

  This implementation derives from TextBox and builds a TextGridFilter for each column supplying itself as the TextBox to fetch the criterion from. The effect is that all the columns are filtered by the contents of just one single TextBox. This is even possible in several grids simultaneously. To use it just drag it onto the form with the FilterableDataGrid or GridFilterExtender and set the FilterFactory property via designer to this class.

  

• LayoutedFridFilterFactoryControl

  This implementation is build upon the DefaultGridFilterFactory or any other IGridFilterFactory implementation. What kind of IGridFilters 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 the FilterableDataGrid or GridFilterExtender and set the FilterFactory 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 IGridFilters 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 the DataView is, for example, part of a complex DataSet, it is possible (like in the normal DataGrid) to navigate through its relations. The only limitation (as mentioned above) is that an appropriate DataGridTableStyle 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 property AutoCreateTableStyles 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 the FilterPosition 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 the Form/Control the DataGridView is contained in. A simple workaround is to set the DataSource in the overridden OnLoad 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 26^th, 2005
  • Initial release.
• March 26^th, 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.
• March 31^st, 2005 - Several enhancements and fixes were made:
  • The component can now work with normal DataGrids. Thus the use of the ExtendedDataGrid is no longer necessary. The only requirement is that an appropriate DataGridTableStyle must be created and added to the grid. For this, the new helper class DataGridStyleCreator 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 assigned Form.
  • 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 the DataGrid. 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.
• April 9^th, 2005 - Some enhancements and fixes:
  • Added the DateGridFilterControl to allow better filtering of DateTime columns.
  • Corrected some minor mistakes in my comments.
  • Added some attributes for (a bit) clearer design time support.
• April 11^th, 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 added EnumerationGridFilter.
• April 15^th, 2005 - Some enhancements:
  • Added a property Operator to allow definition if the criteria is combined with a logical AND or a logical OR.
  • Added a method ClearFilters to clear all the set filters to their initial state.
• April 16^th, 2005 - Some enhancements and fixes:
  • Corrected the wrong appearance of the filters when the RowHeadersVisible property of the DataGrid was set to false. Thanks to Dean_DWD for pointing out this.
  • Added two properties MessageErrorMode and ConsoleErrorMode that allow configuring the kind of output that is generated when an error occurs in the built filter criteria.
• May 14^th, 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.
• October 3^rd, 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 the IGridFilterFactory class to allow easy modifications to the filter behaviour of single classes (mainly needed for the new functionality in EnumerationGridFilter).
  • 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.
• November 19^th, 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 to IGridFilter which is basically the reverse version of GetFilter. With the help of this, I could add the function Get/SetFilters to both DataGridFilterExtender and FilterableDataGrid, 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 the DataView itself).
• December 7^th, 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 the IGridFilterFactory to allow easier customization of the filter creation process (sorry for any broken custom implementations).
  • Refactored the IGridFilter implementations to make use of a common GridFilterBase class and also separated the GUI of the NumericGridFilter and DateGridFilter from the filtering logic.
  • Filter controls are now allowed to be placed outside of the grid and are independent of the created IGridFilters. This is used by the new IGridFilterFactory implementations (like the FullTextSearchGridFilterFactory 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 the GridFilterExtender or FilterableDataGrid. For very large tables this property can be set to false after which the view will not get updated with every change to the filter controls until RefreshFilters is called or AutoRefresh is set to true 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 14^th, 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 internal DataFilter 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.
• April 22^nd, 2006 - Version 2.2:
  • Fixed that the HandleEnumerationTypes property on the DefaultGridFilterFactory was ignored.
  • Fixed that the SetFilters and GetFilters calls would sometimes not work on BoolGridFilters.
  • Added a new IGridFilter implementation with the name NullGridFilter and a corresponding factory named NullGridFilterFactory. This is based on a request from HITMUS.
  • Added a new IGridFilter implementation with the name DistinctValuesGridFilter and a corresponding factory named DistinctValuesGridFilterFactory. Also added functionality to the DefaultGridFilterFactory 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.
• May 7^th, 2006 - GridViewExtensions - Version 1.0:
  • Added a DataGridView version of the component, and a new section in the article shortly describing it.
• May 20^th, 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 shown IGridFilters. The returned collection class has functions to filter by type and access IGridFilters by several means.
    • Added a TextBox along with a Button to Sample 1 which demonstrates how to set all the values on IGridFilters of a special type.
• June 5^th, 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 DateTimePickers/TextBoxes 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 either DefaultShowDateInBetweenOperator or DefaultShowNumericInBetweenOperator on the DefaultGridFilterFactory. 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, and CurrentTableBaseFilter. 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).
• July 2^nd, 2006 - GridViewExtensions 1.3:
  • Added support for using BindingsSources and DataSets which can now be directly assigned to the DataSource property of the DataGridView. Furthermore, the DistinctValuesGridFilter 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.
• October 1^st, 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 for MouseDown, MouseUp, MouseMove, MouseEnter, MouseLeave, MouseHover, KeyUp, KeyDown, and KeyPress. This is based one a request made by Marcelo Miorelli. All other events can be reached by using the EmbeddedDataGrid property. If someone needs more than this, feel free to post requests.
  • Replaced the property AutoRefresh with AutoRefreshMode 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.
• October 1^st, 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 given IList. 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 15^th, 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 18^th, 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 both FiltarableGrid and FiltarableGridView.