Friday, May 6, 2011

Filtering a DataGridView

Been reading a lot of people getting confused about how to develop a filter to a DataGridView.

The easiest way to do that is to apply the filter to a BindingSource object, which will then modify the DataGridView to display the filtered result in it.

So, here's my example form design:

The only property I've changed on the designer on the DataGridView and the BindingSource is their names.
That ComboBox will let us decide which column we want to filter, with the TextBox's value. That CheckBox will indicate whether the filter is active or not.

A very easy way to implement a Live Filter

We'll implement a live filter, meaning the data will be filtered as the user types, instantly. Now, this kind of filter is pretty good for small data, small collections. Imagine instantly filtering 30000 records...

For our live filter, we'll assign each key control's event to the same handler, this way we'll have to write less code, it'll be easier.

Our DataGridView will have two columns, so here we throw in some sample data, and configure the comboBoxColumns items using an anonymous type, all inside our form's Load event:

private void FormTestGrid_Load(object sender, EventArgs e)
    // This prepares our data source and our grid:

    DataTable dt = new DataTable();

    DataColumn colName = new DataColumn("Name");
    DataColumn colPrice = new DataColumn("Price");


    dt.Rows.Add("Bike", 150.00);
    dt.Rows.Add("Helmet", 59.99);
    dt.Rows.Add("Shoes", 90.99);
    dt.Rows.Add("Shirt", 30.00);
    dt.Rows.Add("Blouse", 75.00);
    dt.Rows.Add("Cap", 30.00);
    dt.Rows.Add("Pants", 50.00);
    dt.Rows.Add("Glasses", 15.00);

    bindingSource.DataSource = dt;

    dataGridView.DataSource = bindingSource;

    dataGridView.Columns.Add("colName", "Name");
    dataGridView.Columns.Add("colPrice", "Price");

    dataGridView.Columns["colName"].DataPropertyName = "Name";
    dataGridView.Columns["colPrice"].DataPropertyName = "Price";

    // And this prepares our ComboBox to enable us to select what column we want to filter:

    comboColumns.DataSource =
            new { Text = "Name", Value = "Name"},
            new { Text = "Price", Value = "Price"},

    comboColumns.DisplayMember = "Text";
    comboColumns.ValueMember = "Value";

The above code fills a DataTable and tells the BindingSource that it's its DataSource, adds the Columns to our Grid and binds them to the correct data source columns.
It also adds the items that represent our columns to the ComboBox.

Now we declare the method that will receive every control's key events:

private void FilterAction(object sender, EventArgs e)

We set our events to that handler, inside our constructor (Could also be inside our Load event):

public FormTestGrid()

    comboColumns.SelectedValueChanged += new EventHandler(FilterAction);
    txtFilter.TextChanged += new EventHandler(FilterAction);
    chkFilter.CheckedChanged += new EventHandler(FilterAction);

The BindingSource component has a 'Filter' property which accepts SQL-Like Syntax constraints. For more information check the MSDN documentation on it.

And now we can write the method that will actually apply the filter, FilterAction.

private void FilterAction(object sender, EventArgs e)
    if (chkFilter.Checked)
        bindingSource.Filter = string.Format("{0} like '%{1}%'", comboColumns.SelectedValue, txtFilter.Text);                
        bindingSource.Filter = string.Empty;

Notice that we use 'like' instead of '=' so that it doesn't have to be the exact same value to filter, but that's just for our example, you should decide what works better for you.

So this is it, just with those few lines of code, assigning three control's events to the same handler, we can implement our live filter to our Grid.