Introduction

I was looking for an easy and flexible grid filtering mechanism to use with new and old applications. I've found no preexisting solutions that fully satisfy my needs. So, I decided to make my own filtering library. The goals I've tried to reach are:

Easy to integrate: Availability of a "few lines of code" usage to satisfy the needs of rapid integration.

User Friendly: Nice to look, easy to use.

Not code pervasive: Using a DataGridView derivation would constrain people to re-declare their instances. Moreover, this could be in conflict with existing grid derivations.

Flexible: There are never enough filters in the world!

Using the Code

This is for eager people. The promised "few lines of code" are just one. Add the DgvFilterPopup.dll to your references. Write somewhere a line like this:

DgvFilterManager filterManager = new DgvFilterManager(dataGridView1);

That's all. Your grid is now able to filter the column values. Right-click on the column headers to see a popup with different filtering features, based on the clicked column data type.

Note: Your grid must be data-bound to a DataView, DataTable, or a BindingSource resolving to one of these two.

Class Architecture

The three main classes are exposed in the following diagram:

The DgvFilterManager Class

The DgvFilterManager class doesn't provide a user interface. Its work is to coordinate the interaction between the DataGridView and the visual filtering elements. When you assign a DataGridView to a DgvFilterManager, the latter attaches some handlers to respond to right click on column headers and to perform some custom painting on the grid. When the user right clicks a column header, the DgvFilterManager shows a popup near the column. This popup is a control that serves as a host for other controls, one for each column. Only one of these child controls is visible at a time, based on the clicked column. We have one filter host control and many column filter child controls.

The filter host control must be a derivation of the DgvBaseFilterHost class, while filter controls must be derived from the DgvBaseColumnFilter class. These two classes don't provide any user interface themselves.

As a default, the DgvFilterManager uses a standard host implementation, named DgvFilterHost, and depending on each column type and data type, one of the filter standard implementations (see below): DgvTextBoxColumnFilter, DgvCheckBoxColumnFilter, DgvComboBoxColumnFilter, or DgvDateColumnFilter.

When a DataGridView is attached to the manager, the latter performs the following actions:

It creates a filter host that is an instance of the DgvFilterHost class. If you have already provided a filter host, this step is skipped.

It creates a list of DgvBaseColumnFilters, one per column, and initializes each element to a specialization of DgvBaseColumnFilter. If AutoCreateFilters is false, this step is skipped.

You can force a specific column filter type for a certain column, intervening in this process through the ColumnFilterAdding event. You can also intervene, after the auto-creation process, accessing the filter instances through one of the two indexers provided by the manager, and replacing them with user-chosen instances.

The DgvBaseFilterHost Class

The purpose of the filter host control is to show a popup near a right-clicked column and to host child column filter controls. When the popup is shown, only the column filter control related to the right-clicked column is visible. DgvBaseFilterHost is a derivation of UserControl, and provides functionalities to cooperate with DgvFilterManager.

Note: This class is intended as an abstract class. However, declaring it as abstract would generate errors within the designer when designing derived classes.

In your derivation, you have to provide a host area (such as a panel) and override the FilterClientArea to return it. Also, create visual elements for remove filter, remove all filters, apply filter, and use the DgvFilterManager methods ActivateFilter and ActivateAllFilters to make them alive.

The DgvBaseColumnFilter Class

The purpose of a column filter control is to contain visual elements, allowing the end user to construct a filter. When inheriting from it, you can work just like creating any other user control. This class is a derivation of UserControl, and provides functionalities to cooperate with DgvFilterManager.

Note: This class is intended as an abstract class. However, declaring it as abstract would generate errors within the designer when designing derived classes.

You should override OnFilterExpressionBuilding to provide a filter expression construction logic and to set the values of the FilterExpression and FilterCaption properties.

Standard Implementations

The DgvFilterHost Class

This is the standard implementation of DgvBaseFilterHost. This class does nothing special. Most of the logic is in its base class. It just contains visual elements such as buttons and graphics, and a panel acting as the client area for child column filter controls.

The DgvTextBoxColumnFilter Class

This is one of the DgvBaseColumnFilter standard implementations. It's composed of a combobox containing a list of operators and a textbox in which to type the value of the filter. This column filter is used by default with DataGridViewTextBoxColumns, except when the bound data type is DateTime. The list of available operators is different between string types and numeric types.

The DgvTextBoxColumnFilter Class

A standard implementation for the filtering of checkbox columns. The only available operators are the equal and the general null and not null operators.

The DgvDateColumnFilter Class

A standard implementation for the filtering of date columns.

The DgvComboBoxColumnFilter Class

A standard implementation for the filtering of combobox columns. By default, on textbox columns, the filter manager uses DgvTextBoxColumnFilter instances. However, you can force an instance of DgvComboBoxColumnFilter on such columns. In this case, the DgvComboBoxColumnFilter instance automatically creates a distinct list of values from the column data. You should do an explicit call to the RefreshValues() method when the underlying data changes.

Customizing

If "one line usage" is not sufficient for your needs, you can control the process of adding and showing filters in different ways.

Using a DgvComboBoxColumnFilter for Non-Combobox Columns

Use one of the manager indexers to access the filter, and assign it an instance of the DgvComboBoxColumnFilter class.

FilterExpressionBuilding

Using the DgvBaseColumnFilter event, you can customize the filter expression building process. In the following code example, we add new operators and then manage them in the event handler. The FilterExpression and FilterCaption properties will be used by the manager to build the whole filter and to set the column caption.

Subclassing

A more powerful way to customize your filters is through subclassing. You should think of the proposed standard implementations of DgvBaseFilterHost and DgvBaseColumnFilter as just some possible implementations.

Creating Your Own Host

As said above, derive from DgvBaseFilterHost and provide some visual elements. Add a container within your control to host the child filter controls, and return it by an override of the FilterClientArea property. The base class provides the necessary logic to cooperate with the manager, and provides some facilities helping to position the child filter controls and to adjust the host size. Another facility simplifies the creation of transparent skinned hosts, thanks to the method BitmapToRegion I've found in a very nice article by John O'Byrne.

Note: A skinned host must be constrained to a fixed size. Be sure to inhibit the resize logic by overriding the DoAutoFit method. Also, keep in mind this limitation when designing your own host and your filters.

Creating Your Own Column Filters

Creating new column filters is simple. Derive from DgvBaseColumnFilter and add your visual elements. Override OnFilterExpressionBuilding to provide filter building logic and, using DataView.RowFilter rules, assign a value to the FilterExpression property and a title to the FilterCaption property.

Remember that the filter is applied when the user clicks on the OK button of the host. However, you can obtain an immediate filter application doing a call to the RebuildFilter method of the filter manager.

New Filters

To satisfy some requests, in the 1.1.0.0 update, I've introduced three new filter implementations:

The DgvMonthYearColumnFilter Class

This filter allows the user to select a month and a year. By setting the YearMin and YearMax properties, you can control the shown years range. Month names default to English, but you may provide culture-specific names by once setting the value of the static property MonthCsvList with a comma separated list of month names.

The DgvNumRangeColumnFilter Class

Use this filter to allow the user to specify a range filter on numeric columns.

The DgvDateRangeColumnFilter Class

Use this filter to allow the user to specify a range filter on date columns.

Conclusions

In this article, I've exposed the class architecture and common usage scenarios. This conceptual overview, I hope, will help you understand how it works. For detailed explanations and references, you can see the attached documentation.

Note: To those interested in documenting their works, I've used these materials:

Comments and Discussions

Before filtering I color code certain cells based on some validation logic. After I apply a filter through the filter popup the color-coding is lost. That I can live with, but I'm at a loss as to know which event can indicate this change (either on the DGV or the FilterManager, which is the only class of your library I have used). If there is no simple way then can you explain exactly what is occurring when the filter is "applied"?

Hi Stark.
Your request about caption customizazion of operators is very rightful.
Unfortunately I still haven't introduced this feature.
You should modify the source code and rebuild the library.
For the text operators ("...xxx","xxx...","..xx..") you may simply perform a global search and replace.
The other operators (<>,=,<,>) are used directly in filter expression construction so you have to replace
the operator strings with your own captions and than manage the various cases.
For example, if you change "<>" in "different", than you have to map the the "different" caption with the "<>" when constructing
the filter.
I hope this may help
Vin

I want to create filters programmatically. For dgvTextColumnFilter it works fine. But for date column filter, the DateTimePickerValue.Text is always blank. Thus the filter condition does not get displayed in the caption properly. This is the code snippet i am using. can u help me where i am going wrong?

Hi Viji,
your code is right. The problem is due to a DateTimePicker bug (Microsoft says a "design choice") as you can read here.
An assignment to the text property is ignored if the control is not visible.
The only thing we can do is to modify the library:

Hi Tom,
you're near to the solution.
The FilterExpressionBuild task is:
1) Contruct the FilterExpression starting from user choices in the user interface
2) Call the filter manager RebuildFilter() method to referesh the whole filter.

So the last line in your code overwrites your programmatic settings.

Replace your last line:fm.Item("Status").FilterExpressionBuild()
with fm.RebuildFilter()

However this approach doesn't "store" the filter in the user interface.
I suggest to set the UI elements instead, as you can read in my replay to this post: [Filter Column programmatically^]
It's more boring but more consistent.
Regards.
Vin

I have been snooping around and i landed on this. i thought it was a saviour but alas!
I add data to my datagridview directly from an Excel worksheet. But i can't get this filter to work.
Please someone help me on this.Thx

Hi doldots,
I'm sorry you wasted your time trying to use the filter with collections.
In my defense I point out that there is a warning about this in the very beginning of the article:

"Note: Your grid must be data-bound to a DataView, DataTable, or a BindingSource resolving to one of these two."

The reason of this restriction is technical. The filter library uses the filtering capabilities of dataviews. Collections of objects dont't offer such capability in .net 2.0 (in 3.5 this could be done with linq).

As a workaround I can suggest this:
If the objects in your collection are only data containers you created just to host data from the sheet, than consider to read the data and put it in a manually created DataTable.
Also, you could take advantage of ado, connecting directly to the excel with an OleDbConnection and leaving ado to do the work of creating automatically the DataTable schema and filling it in the right way.

Thanks so much for this wonderful control. It certainly made my life easier!

What I REALLY love is that this is not limited to the standard WinForms DataGridView! I am currently using it with the DotNetBar DataGridViewX (better styling than the standard winforms one).
I guess any control somehow derived from DataGridView can be used with this amazing filter.

I'have a stored procedure in sql2005 that return a datatable with several column, with decimal col I've no problem to filter but with datetime col nothing happen.
Your job seems to be awesome, or I'm loosing something.
Regards
Andrea