BindingSource Filter with Starts, contains, ends with and case sensitive options
Introduction
This code sample focuses on filtering a BindingSource component in a Windows Form project where its data source is a DataTable rather than filtering from the Filter property of a BindingSource. The reason is that many developers writing window form applications use a BindingSource in tangent with a TableAdapter or simply using a DataSet or DataTable and need case or case insensitive capabilities for filter where the BindingSource component filter does not have the ability to filter with case insensitive casing.
In this article a language extension library which has extensions for a BindingSource with a DataSource set to a DataTable will show how to make filter using LIKE conditions easy. There are methods focus on LIKE conditions for starts-with, ends-with and contains with one for a general equal, all provide an option for casing.
Anatomy
Filter type
An enum is provided to indicate which type of filter is desired. The first member is for use in a ComboBox to prompt the user to select which type of filter to apply for a filter operation. These enum members are passed to a language extension method to apply a filter on a BindingSource component with the DataSource set to a DataTable.
public enum FilterCondition
{
Select,
StartsWith,
Contains,
EndsWith
}
To populate a ComboBox with the above Enum.
ProductNameFilterComboBox.DataSource = Enum.GetValues(typeof(FilterCondition));
When read to apply a filter, assert that the first member is not select.
var filterType = (FilterCondition)ProductNameFilterComboBox.SelectedItem;
if (filterType == FilterCondition.Select) return;
Once a valid enum member has be validated a TextBox is needed to capture user text for a filter. If the TextBox Text is empty clear the filter, otherwise apply the filter.
string.IsNullOrWhiteSpace(ProductNameTextBox.Text
Helpers
If there are unescaped apostrophes in the text to filter they need to be escaped which is done using the following language extension method within filter language extensions.
public static string EscapeApostrophe(this string pSender)
{
return pSender.Replace("'", "''");
}
Within the extension methods below there are helper extension methods to the filtering extension method which means be sure to copy those to your project with the extension methods or better, copy the entire extension class to a project, trim out any extensions not indented to use.
BindingSource filter extensions
Each filter is against a DataTable which has a property CaseSensitive which indicates whether string comparisons within the table are case-sensitive. In each of the following examples the placement of the percent symbol (wildcard character) indicates the type of LIKE Transact-SQL.
Note: When you do string comparisons by using LIKE, all characters in the pattern string are significant. Significant characters include any leading or trailing spaces. If a comparison in a query is to return all rows with a string LIKE 'abc ' (abc followed by a single space), a row in which the value of that column is abc (abc without a space) isn't returned. However, trailing blanks, in the expression to which the pattern is matched, are ignored. If a comparison in a query is to return all rows with the string LIKE 'abc' (abc without a space), all rows that start with abc and have zero or more trailing blanks are returned.
Starts-with
public static void RowFilterStartsWith(
this BindingSource sender,
string field, string value,
bool caseSensitive = false)
{
sender.DataTable().CaseSensitive = caseSensitive;
sender.DataView().RowFilter = $"{field} LIKE '{value.EscapeApostrophe()}%'";
}
Usage where the second argument would typically come from text entered into a TextBox by a user. The last argument dictates case sensitivity.
bindingSource.RowFilterStartsWith("ContactName", "An", true);
Ends-with
public static void RowFilterEndsWith(
this BindingSource sender,
string field, string value,
bool caseSensitive = false)
{
sender.DataTable().CaseSensitive = caseSensitive;
sender.DataView().RowFilter = $"{field} LIKE '%{value.EscapeApostrophe()}'";
}
Contains
public static void RowFilterContains(
this BindingSource sender,
string field, string pValue,
bool caseSensitive = false)
{
sender.DataTable().CaseSensitive = caseSensitive;
sender.DataView().RowFilter = $"{field} LIKE '%{pValue.EscapeApostrophe()}%'";
}
Each of the above can be placed into a single extension method where in code its not clear which type of filter/like condition is being performed which is personal taste plus may be harder to debug with one extension method for all three types of like conditions.
To provide the option to use clearly named extensions with less clear names the following trigger is the FilterCondition using a switch statement to use the named extensions.
public static void RowFilter(
this BindingSource sender,
string field,
string value,
FilterCondition condition,
bool pCaseSensitive = false)
{
switch (condition)
{
case FilterCondition.StartsWith:
sender.RowFilterStartsWith(field, value.EscapeApostrophe(), pCaseSensitive);
break;
case FilterCondition.Contains:
sender.RowFilterContains(field, value.EscapeApostrophe(), pCaseSensitive);
break;
case FilterCondition.EndsWith:
sender.RowFilterEndsWith(field, value.EscapeApostrophe(), pCaseSensitive);
break;
}
}
Multiple field conditions
If there is a need to perform filters on multiple fields simple add a AND condition and alter the extension below which has been kept simple so it's easy to extend.
public static void RowFilterTwoConditions(
this BindingSource sender,
string field1,
string value1,
string pField2,
string value2,
bool caseSensitive = false)
{
sender.DataTable().CaseSensitive = caseSensitive;
sender.DataView().RowFilter =
$"{field1} = '{value1.EscapeApostrophe()}' AND {pField2} = '{value2.EscapeApostrophe()}'";
}
Freeform LIKE
All of the filter/LIKE conditions have been done in each of the language extensions, another option is to create the condition in the form and pass to the following extension method. This requires a bit more knowledge of creating conditions from multiple TextBoxes, CheckBox and or Radio buttons.
public static void RowFilterFreeForm(
this BindingSource sender,
string filter,
bool caseSensitive = false)
{
sender.DataTable().CaseSensitive = caseSensitive;
sender.DataView().RowFilter = filter;
}
Mock up example
bindingSource.RowFilterFreeForm("Country IN ('Argentina', 'Canada', 'UK')", true);
Clearing filters
To clear a filter, use the following extension method.
public static void RowFilterClear(this BindingSource sender)
{
sender.DataView().RowFilter = "";
}
Helper extensions to LIKE extensions
The following are used with the extensions above and can be used to access a underlying DataTable and Default view of the DataTable.
Get the DataTable for a BindingSource
public static DataTable DataTable(this BindingSource sender)
{
return (DataTable)sender.DataSource;
}
Get the DefaultView for a BindingSource.
public static DataView DataView(this BindingSource pSender)
{
return ((DataTable)pSender.DataSource).DefaultView;
}
Unit test
To ensure the extension methods work as expected, unit test are provided. Since each unit test method uses the same database table, the table is read in for each test and set to a BindingSource in TestInitialize method which runs before each test method ensuring a fresh copy of data.
There are currently 21 test, this could be 42 or more test were the additional test would test more scenarios for filtering.
Sample project
In the following project, select filter type, enter text into the TextBox and the filter is applied as text entered. The button Filter Data is to demonstrate performing a LIKE without filtering as typing.
using System;
using System.Windows.Forms;
using Operations;
using FormHelpers;
using static ExtensionLibrary.BindingSourceExtensions;
namespace SampleWinForm
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
Shown += Form1_Shown;
ProductNameTextBox.KeyDown += ProductNameTextBox_KeyDown;
ProductNameTextBox.TextChanged += ProductNameTextBox_TextChanged;
}
private readonly BindingSource _bindingSource = new BindingSource();
private void Form1_Shown(object sender, EventArgs e)
{
ProductNameFilterComboBox.DataSource = Enum.GetValues(typeof(FilterCondition));
var dataOperations = new DataOperations();
_bindingSource.DataSource = dataOperations.GetProducts();
dataGridView1.DataSource = _bindingSource;
dataGridView1.Columns[0].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
ProductNameTextBox.SetCueText("Filter or empty to clear");
ActiveControl = dataGridView1;
}
private void FilterDataButton_Click(object sender, EventArgs e)
{
FilterOperation();
}
private void ProductNameTextBox_KeyDown(object sender, KeyEventArgs e)
{
if (e.KeyCode == Keys.Enter)
{
FilterOperation();
}
}
/// <summary>
/// Search while typing
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void ProductNameTextBox_TextChanged(object sender, EventArgs e)
{
var filterType = (FilterCondition)ProductNameFilterComboBox.SelectedItem;
if (filterType == FilterCondition.Select) return;
if (ProductNameTextBox.Text.Length > 0)
{
_bindingSource.RowFilter("ProductName", ProductNameTextBox.Text, filterType);
}
else
{
_bindingSource.RowFilterClear();
}
}
private void FilterOperation()
{
var filterType = (FilterCondition)ProductNameFilterComboBox.SelectedItem;
if (filterType == FilterCondition.Select) return;
if (string.IsNullOrWhiteSpace(ProductNameTextBox.Text))
{
_bindingSource.RowFilterClear();
}
else
{
_bindingSource.RowFilter("ProductName", ProductNameTextBox.Text, filterType);
}
}
}
}
Prepare to run code samples
- From Solution Explorer, right click, select restore NuGet packages for BaseConnectionLibrary.
- Using a new query window in Visual Studio or using a new query window in SSMS execute the database script in GenerateDatabaseAndData.sql.
- In the project Operations, in DataOperations class constructor change DatabaseServer to the name of your server, otherwise an exception will be thrown.
Using the extensions in a project
- Copy ExtensionsLibrary to the same folder as your Visual Studio, add the project.
- Add a reference to the Windows Form project will will utilize the extensions.
- Add controls on a form similar to the code sample provided.
Using list
Besides using a DataTable, a list<T> is an option. For example, using the same fields as used above for a DataTable a class is used.
public class Product
{
public int Identifier { get; set; }
public string Name { get; set; }
}
To review, the following was used to populate a DataTable.
public DataTable GetProducts()
{
var dt = new DataTable();
using (var cn = new SqlConnection { ConnectionString = ConnectionString })
{
using (var cmd = new SqlCommand { Connection = cn })
{
cmd.CommandText =
"SELECT ProductID, ProductName FROM dbo.Products ORDER BY ProductName";
cn.Open();
dt.Load(cmd.ExecuteReader());
dt.Columns["ProductID"].ColumnMapping = MappingType.Hidden;
}
}
return dt;
}
With minor refactor, the following will return a list rather than a DataTable.
public List<Product> Products()
{
var productList = new List<Product>();
using (var cn = new SqlConnection { ConnectionString = ConnectionString })
{
using (var cmd = new SqlCommand { Connection = cn })
{
cmd.CommandText =
"SELECT ProductID, ProductName FROM dbo.Products ORDER BY ProductName";
cn.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
productList.Add(new Product() {Identifier = reader.GetInt32(0), Name = reader.GetString(1)});
}
}
}
return productList;
}
Since a List<T> does not support sorting unlike a DataTable which supports sorting a open source BindingListView which supports sorting will be used found here and installed using NuGet.
Setting up
First installed the custom BindingListView followed by creating a variable for the BindingListView and a BindingSource component.
BindingListView<Product> view = new BindingListView<Product>(new List<Product>());
BindingSource _bindingSource = new BindingSource();
In either form load or form shown event read data from SQL-Server into a list passing the list to the BindingListView.
var dataOperations = new DataOperations();
view = new BindingListView<Product>(dataOperations.Products());
Next add the following class with extension methods to provide similar functionality used with a BindingSource/DataTable to perform LIKE where clauses.
public static class BindingListViewExtensions
{
public static void NameStartsWith(this BindingListView<Product> sender, string fieldValue, bool caseSensitive = true)
{
if (caseSensitive)
{
sender.ApplyFilter(product => product.Name.StartsWith(fieldValue));
}
else
{
sender.ApplyFilter(product => product.Name
.StartsWith(fieldValue, StringComparison.CurrentCultureIgnoreCase));
}
}
public static void NameContains(this BindingListView<Product> sender, string fieldValue, bool caseSensitive = true)
{
if (caseSensitive)
{
sender.ApplyFilter(product => product.Name.Contains(fieldValue));
}
else
{
sender.ApplyFilter(product => product.Name
.IndexOf(fieldValue, StringComparison.OrdinalIgnoreCase) >= 0);
}
}
public static void NameEndsWith(this BindingListView<Product> sender, string fieldValue, bool caseSensitive = true)
{
if (caseSensitive)
{
sender.ApplyFilter(product => product.Name.EndsWith(fieldValue));
}
else
{
sender.ApplyFilter(product => product.Name
.EndsWith(fieldValue, StringComparison.CurrentCultureIgnoreCase));
}
}
public static List<Product> Products(this BindingListView<Product> sender)
{
return sender.Cast<Product>().ToList();
}
public static void RemoveFilter(this BindingListView<Product> sender)
{
sender.Filter = null;
}
}
Back to the form, add a DataGridView (named dataGridView1) and TextBox (named ProductNameTextBox) and a CheckBox (named CaseSensitiveCheckBox with checked = true) . Subscribe to TextChanged event for the TextBox.
private void ProductNameTextBox_TextChanged(object sender, EventArgs e)
{
view.NameContains(ProductNameTextBox.Text, CaseSensitiveCheckBox.Checked);
}
In form load or shown event.
ProductNameTextBox.TextChanged += ProductNameTextBox_TextChanged;
Typing into the TextBox an incremental filter is performed. Delete text in the TextBox to remove the filter.
BindingListView caveat
To drill down to a record the following code will do this. Although drilling down to a record is done in PositionChanged event of the BindingSource this can be done in other events e.g. a button click event.
private void _bindingSource_PositionChanged(object sender, EventArgs e)
{
if (_bindingSource.Current == null) return;
var customer = ((ObjectView<Product>)_bindingSource.Current).Object;
}
See also Important details on the following page.
Summary
In this article a solid base has been provided to perform filters using like wildcards without the mechanics revealed in form code, instead all the work is performed within language extensions methods. BindingSource components provide rich features which should be explored for working with data in Windows Forms rather than simply setting a DataTable to the DataSource of a DataGridView or databinding to controls like TextBoxes.
See also
Windows forms Master-detail view with add/edit/delete on SQL-Server with VB.NET/C#\
Windows forms Entity Framework Code first from database
Windows DataGridView with inline edit and remove buttons
.NET: DataBinding
C# Complete ComboBox
Extension methods (C#)
Source code
https://github.com/karenpayneoregon/BindingSourceFiltering_C