DataGridView Filter in C#

by Igor Katenov, the lead developer at 10Tec

If the users of your business application browse tabular data in the .NET DataGridView control, sooner or later they ask you a question: how can we filter data in this grid? The standard DataGridView does not implement any built-in filtering, and you start searching for a suitable DataGridView filter solution.

This article gives you some ideas how to implement DataGridView filter in C#.

Filter DataGridView by a column

Let’s suppose you need to filter a DataGridView by a column with string values. The user can enter a substring in a text box, and the DataGridView is being filtered as the user is typing every new character:

DataGirdView filter C# sample

This functionality can be implemented very easily. In many cases DataGridView is bound to a data source like ADO.NET DataTable, and we can exploit the built-in ADO.NET filtering functionality through the DefaultView.RowFilter property of DataTable to filter DataGridView:

DataTable dtSales = new DataTable();
string filterField = "Country";

private void Form1_Load(object sender, EventArgs e)
{
   dtSales.Columns.Add("Country", typeof(string));
   dtSales.Columns.Add("Sales", typeof(int));

   dtSales.Rows.Add(new object[] { "Argentina", 2000 });
   dtSales.Rows.Add(new object[] { "Belgium", 4500 });
   ...

   dataGridView1.DataSource = dtSales;
}

private void textBox1_TextChanged(object sender, EventArgs e)
{
   dtSales.DefaultView.RowFilter = string.Format("[{0}] LIKE '%{1}%'", filterField, textBox1.Text);
}

Note that the field name in the filter expression should be wrapped with square brackets to avoid problems if the field name contains spaces.

There is one minor problem with this approach – the LIKE operator works only for string values, but not for integer or date values. You can process these situations by analyzing the type of every column and using an appropriate operator like "=", but there is another way. The RowFilter property supports the syntax of the System.Data.DataColumn.Expression property that provides us with the super-useful Convert function. We can use it to convert field values to strings without analyzing the type of column, so our RowFilter property assignment statement could look like this:

dtSales.DefaultView.RowFilter = string.Format("Convert([{0}], 'System.String') LIKE '%{1}%'", filterField, textBox1.Text);

Filter DataGridView by multiple columns

In some scenarios it may be handy to filter DataGridView by multiple columns or even by all columns using one filter box. To do that, you can build a general filter expression for multiple columns with the OR operator combining filter expressions for individual columns. An example of this DataGridView multiple columns filter in C# was published on StackOverflow:

How can i filter C# dataGridView across all field names?

The problem is that this solution can be used "as is" only with DataGridView columns containing strings because the filter expression is based on the LIKE operator applicable only to strings. You need to think yourself how to apply this idea to filter DataGridView by multiple columns containing numeric data or date values.

A more elegant solution that works with any data types is the following. Create a special extra column, populate it with the string representation of every row and filter DataGridView by values from this column. Below is a C# code snippet implementing this idea:

DataColumn dcRowString = dtSales.Columns.Add("_RowString", typeof(string));
foreach (DataRow dataRow in dtSales.Rows)
{
   StringBuilder sb = new StringBuilder();
   for (int i = 0; i < dtSales.Columns.Count-1; i++)
   {
      sb.Append(dataRow[i].ToString());
      sb.Append("\t");
   }
   dataRow[dcRowString] = sb.ToString();
}

Having such a column, we can use the following TextChanged event handler of our filter field:

private void textBox1_TextChanged(object sender, EventArgs e)
{
   dtSales.DefaultView.RowFilter = string.Format("[_RowString] LIKE '%{0}%'", textBox1.Text);
}

Needless to say that you should hide this special column in the DataGridView we filter:

dataGridView1.Columns["_RowString"].Visible = false;

The result may look like this for our C# DataGridView filter sample:

DataGirdView filtering all columns in C#

The described C# DataGridView multiple columns filter approach works like a charm, but unfortunately it cannot be used to filter unbound DataGridView. As a workaround, you can write additional code that places your unbound data into a DataTable first, then bound DataGridView to it and use the C# DataGridView filtering solution described above. Another solution to this problem that is not based on a temporary data source in memory could be enumerating all DataGridView rows and set their Visible property to True or False depending on the filter condition. But taking into account the slowness of DataGridView when working with big amounts of unbound rows, the performance can dramatically degrade as the number of rows increases.

DataGridView autofilter solutions

Another type of filtering available for DataGridView is so called autofilter, which is widely used in Microsoft Office applications like MS Excel and MS Access. This approach implies that every column header in DataGridView includes a small combo button one can use to open a drop-down box to define sophisticated filter criteria for the column.

An example of that can be easily found in the MSDN – this article is titled "Building a Drop-Down Filter List for a DataGridView Column Header Cell". The article contains a link to the C# DataGridView column filtering sample, which looks like the following picture:

DataGirdView column filtering C# sample from MSDN

It is obvious that this is not a full-featured autofilter solution. As you can see from the picture above, you can select only one value to filter by for a column – filtering by multiple values isn’t supported. The article also lists other non-implemented features you may expect to see in an autofilter solution, such as custom filter expressions. And perhaps, the most important restriction of this solution is that it does not work for DataGridView in unbound mode or when the underlying data source does not provide filtering.

Finding a good free DataGridView autofilter solution is not an easy task, but we have managed to find one on CodeProject. This is Advanced DataGridView with Excel-like auto filter, which is also distributed as the ADGV NuGet package:

Advanced DataGridView with Excel-like auto filter

This C# library provides you with two controls, ADGV.DataGridView and ADGV.SearchToolBar, intended to be used together. The first control inherits the standard DataGridView control to provide you with some specific members related to sorting and filtering. You should use it instead of the standard DataGridView if you need the autofilter functionality in your DataGridViews.

The ADGV search toolbar, which you can see above the DataGridView on the screenshot, is used to implement text search by one or all grid columns. Note that this is search but not filter like we implemented in our C# DataGridView filter solution in the very beginning.

The first noticeable feature of this DataGridView filter tool is the ability to select multiple values for filtering. Among other useful features are custom column filter (‘begins with’, ‘contains’, and the like) and the ability to clear the column filter from the interface. The library also provides you with some methods for saving and restoring the current filter and sort criteria in memory.

Unfortunately, the author stopped the development of this wonderful library and it is provided "as is" with full C# source code. You can face some minor problems while using it, and you can try to fix them by yourself if you like to examine and debug someone else’s code. However, you cannot fix the following two big drawbacks of this DataGridView filtering approach.

First, this tool is based on the same ADO.NET filtering engine used in the solutions above and thus it cannot be used with unbound DataGridViews. If you look into the sample C# source code supplied with the ADGV library, you will see that the filtering and sorting are done in code like this:

private void dataGridView_SortStringChanged(...)
{
   this.bindingSource.Sort = this.dataGridView.SortString;
}
private void dataGridView_FilterStringChanged(...)
{
   this.bindingSource.Filter = this.dataGridView.FilterString;
}

Note: you will also need to copy these two event handlers for every DataGridView in your solution to make filtering/sorting work.

The second big drawback of this solution is that it is based on the same DataGridView control that starts to work slowly as the number of rows increases. By the way, you can also notice that the filter box that displays all unique column values to filter may work with noticeable delays when the number of rows in DataGridView exceeds 10'000.

10Tec WinForms grid with filtering

Our iGrid.NET control is positioned as a DataGridView replacement. If you need to implement DataGridView filter in C#/VB.NET applications, iGrid.NET can be considered as an excellent alternative. It provides you with search and filter features which are free from the problems described above:

Better DataGridView filter solution for C#/VB.NET

The main advantages of iGrid.NET used as an equivalent DataGridView filter solution are the followings:

  • It works very fast for big amounts of rows. Even if your grid has 100'000 rows, the filtering is performed enough fast and the filter box is opened almost instantly.
  • Filtering works in unbound mode as well, which is the main mode of iGrid.NET. If you need to filter an ADO.NET data source, you can simply upload it to iGrid with one method call.
  • You can build an unlimited number of custom filter expressions for every column.
  • The column filter button displays a tooltip with the filter expression currently set for the column.
  • The searching/filtering by columns is integrated into the grid as incremental search and does not require using additional controls like ADGV.SearchToolbar or implementing special tricks in code.

The iGrid column filtering is implemented in the grid add-on called AutoFilterManager. It provides you with a wide variety of features related to grid filtering. You can see AutoFilterManager screenshots and find out more about its features in the corresponding section on this website. Taking into account all its capabilities, we can recommend iGrid.NET as a much better professional alternative if you need to implement DataGridView filtering in C# or VB.NET applications.

Find out more about AutoFilterManager for iGrid.NET »