How can I filter a DataGridView?
I have created a simple DataGridView through the toolbox and have selected data through the wizard (no code in .cs file) from a database. It is working flawlessly as you can see in the picture below:
Now I want to filter the entries in it by contact person name. I have a textbox and search button so when the user enters a "contact person name" such as "Altaf" and then clicks on search, the GridView should refresh and only entries with ticketid=4 should appear.
The only code in the .cs file (which is auto-generated) is:
I tried this in a ButtonClick event as suggested by someone but it generates the error: "Cannot interpret token ‘<' at position 27"
I have no experience in DataGridViews or WinForms coding, so please explain in detail.
DataGridView Filter in C#
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:
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:
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:
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:
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:
Having such a column, we can use the following TextChanged event handler of our filter field:
Needless to say that you should hide this special column in the DataGridView we filter:
The result may look like this for our C# DataGridView filter sample:
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:
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:
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:
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.
C# DataGridView Sorting and Filtering
The DataGridView control is highly configurable and extensible, and it provides many properties, methods, and events to customize its appearance and behavior. A DataView provides a means to filter and sort data within a DataTable. The following C# program shows how to filter and sort a DataGridView by using a DataView.
How to sort Datagridview
The DataGridView control in C# provides automatic sorting, so that you can manually sort any column in the datagridview control. You can sort the data in ascending or descending order based on the contents of the specified column. Also you can see the DataGridView sorting when user clicks on the column header.
In the above code , datagridview sort the title column thats 1st column.
How to filter Datagridview
You can filter datagridview column in many ways. You can sort data while retrieving it from database using order by clause or you can use the following method.
In the above code, datagridview is filter the column Type and value is Business.
Фильтрация DataGridView без изменения источника данных
Я разрабатываю пользовательский элемент управления в С# Visual Studio 2010 — своего рода текстовое поле быстрого поиска для фильтрации datagridview. Он должен работать для трех типов источников данных datagridview: DataTable, DataBinding и DataSet. Моя проблема заключается в фильтрации DataTable из объекта DataSet, который отображается в DataGridView.
Могут быть 3 случая (примеры для стандартного приложения WinForm с DataGridView и TextBox на нем) — первые 2 работают нормально, у меня проблема с третьим:
1. datagridview.DataSource = dataTable: он работает
поэтому я могу фильтровать, установив: dataTable.DefaultView.RowFilter = «country LIKE ‘% s%'»;
Я разработал общий оператор для применения фильтра:
Квадратные скобки допускают пробелы в имени столбца.
Кроме того, если вы хотите включить несколько значений в свой фильтр, вы можете добавить следующую строку для каждого дополнительного значения: