Click here to Skip to main content
Click here to Skip to main content

DatGridView Filtering User Control

, 10 Jul 2009
Rate this:
Please Sign up or sign in to vote.
A user control that can be attached to any DataGridView that uses a DataTable as the DataSource.

DGVPaste

Introduction

I have an ongoing requirement for filtering and exporting DataGridViews. Not just sometimes, almost every application requires this type of functionality. I have had a hunt around for a filtering option and came across the MSDN Auto Filter sample. This is fine (except it broke when put in a production environment), but I need the partial filter where the user can type some characters and get a result based on the partial match.

Then, you have the requirement where the user wants to apply operators: greater/less than on numerics, and like on strings (they also want between on dates, but I have not done that yet).

Now, 90% of my grids are bound to a data table/view; I rarely use lists, so this iteration only supports these data sources. I may extend this to support lists as this is fairly trivial (that's gonna hurt when it comes back to bite me!).

Background

I dislike using someone else's controls as I am then using a "black box" type environment. I know this is irrational, but that's the way it goes. I also like to make the simplest solution to meet my requirements. A great example of this is the Infragistics controls. Don't get me wrong; they are great controls, but have you tried chasing through the property trees on them? I find the time required to set up a simple solution to be too long.

So, I start out with these minimum requirements:

  • Able to filter any column in the underlying data table.
  • Able to export the data to a CSV file.

This was rapidly expanded to:

  • Able to apply operators on the filter.
  • Able to filter hidden columns on the table.

I have my own standards that require the solution be unobtrusive, taking up minimum real estate, and it has to be simple to implement and reasonably elegant. I decided not to extend the DataGridView as that solution was neither simple nor easy to implement. The next choice was a user control and this is the result.

User_Control_filter_active.jpg

I do like unobtrusive, and the users have no trouble locating it once they are told.

After a minimum of research, I decided I would insist on the DGV using a BindingSource; this made the filtering a no brainer and I do like simple. The control assumes it gets a DataGridView with a BindingSource based on a data table or data view.

I then looked at using the binding navigator to host the management of the filtering, but I had no use for the actual navigation part and was unwilling to sacrifice the real estate, so that version got chucked.

I then decided to use a panel as the container (I started with a FlowPanel, but it would not behave) and to use docking to manage the positioning of the controls. Each set would be made up of a label for the column name, a data control for the filter, and a combobox for the operator to be applied; these would be placed in another panel to make up a set. The data control started out as a textbox, and when the control was behaving as expected, I changed to a date time picker and a check box.

I now needed to get the information about each column in the table. To hold this, I created a local class with the properties for each of the relevant information I would need. Hence was born the FilterDef class. This also has the data table with the list of data type primitives that will drive the operator combobox content.

#region FilterDef - Helper class to manage the filter definition controls
/// <summary>
/// Helper class to manage the filter definition controls
/// </summary>
private class FilterDef
{
    public FilterDef()
    {
        dtOp = new DataTable();
        dtOp.Columns.Add("Operator", typeof(System.String));
        dtOp.Columns.Add("DataType", typeof(System.String));

        dtOp.Rows.Add("like", "String");

        dtOp.Rows.Add("=", "String");
        dtOp.Rows.Add("=", "Date");
        dtOp.Rows.Add("=", "Boolean");
        dtOp.Rows.Add("=", "Numeric");

        dtOp.Rows.Add("<", "Numeric");
        dtOp.Rows.Add("<", "Date");

        dtOp.Rows.Add(">", "Numeric");
        dtOp.Rows.Add(">", "Date");
    }
    public enum enDataType { String, Numeric, Date, Boolean }

    public string ColumnName { get; set; }
    public string Caption { get; set; }
    public enDataType DataType { get; set; }
    public string FilterValue { get; set; }
    public bool IsVisible { get; set; }
    public string Operator { get; set; }
    public int Ordinal { get; set; }

    public DataTable dtOp;
}
#endregion 

Using the code

I used the same project as the copy and paste article, and that code is still in the download.

Drop the user control on to the DataGridView, this is important; the control orients on the DataGridView and uses top and left to position itself in the top left corner of the DataGridView. You will find you cannot drop it directly onto the DataGridView as it is not a container control, so put it on the form and drag it onto the DataGridView after it is created.

User_Control_in_IDE.jpg

In the form's initialisation or load area, pass the user control the DataGridView that is the host.

private void Form1_Load(object sender, EventArgs e)
{
    this.ucDGVHelper1.oDGV = dgData;
}

In the method that assigns the DataBinding, you must set the current DataSource to null; this fires the BindingSourceDataSourceChanged and clears out any existing values for the filter. This is required to eliminate a strange error if there is an existing filter applied to the BindingSource when you change the DataSource.

//get the data
DataTable oTable = oData.GetData();

//bind the data to the BindingSource
oBS.DataSource = oTable.DefaultView;
dgData.DataSource = oBS;

That's it, I can go home now, job's done.

What it does

The first thing it does is position itself relative to the host DataGridView. This is done in the property setter.

private DataGridView mvDGV;
public DataGridView oDGV 
{ 
    private get
    {
        return mvDGV;
    }        
    set
    {
        mvDGV = value;
        if (value != null)
        {
            this.Top = value.Top;
            this.Left = value.Left;
            this.Size = szSmall;
        }
    }
}

When the user clicks the image, the filter panel is populated based on the underlying data table, and the control is expanded to display the filter. If the DataGridView has not been loaded, then just the toolbar is displayed.

Populating the Filter Panel

ShowFilter

private void ShowFilter()
{
    try
    {
        //set the color of the button based on if there is a filter set
        Color cLoaded = Color.Bisque;
        if (IsLoaded) { cLoaded = Color.Tomato; }
        this.pbButton.BackColor = cLoaded;

        //use the size to determine if the filter is currently displayed
        if (this.Size != szSmall)
        {
            //hide the filter 
            pbButton.Size = szSmall;
            this.Size = szSmall;                    
            return;
        }
        //check if the data source has benn processed before
        ParseDataSource();

        //set the panel size
        this.Width = 240;
        SetPanelHeight();
        pbButton.Size = szLrg;
    }
    catch (Exception)
    { throw; }
}

This is the managing method and is called by a number of controls. It manages the color of the PictureBox and the size of the FilterPanel and calls ParseDataSource.

This method simply tests for the existence and type of a BindingSource It then makes sure the BindingSourceDataSourceChanged event is assigned to the BindingSource.

/// <summary>
/// check the existence and that the type of datasource
/// is valid (BindingSource or later DAtatable/view)
/// </summary>
/// <returns></returns>
private bool ParseDataSource()
{
    if (oDGV == null)
    { return false; }
    if (oDGV.DataSource == null)
    { return false; }

    if (this.pnlData.Controls.Count > 0)
    { return true; }

    //get the bindingsource from the DGV
    if (oDGV.DataSource.GetType().Equals(
        typeof(System.Windows.Forms.BindingSource)))
    {
        AssignEvent();
        DoBS((BindingSource)oDGV.DataSource); 
    }
    return true;
}

This is where the decision will be made when I implement the filtering for a List<> and is the reason it is a separate method. DoBS is the method that extracts the information from the BindingSource object.

BindingSourceDataSourceChanged

This event clears out all the settings in the panel and the FilterDef for the current data source.

private void BindingSourceDataSourceChanged(object sender, EventArgs e)
{
    try
    {
        (sender as BindingSource).Filter = string.Empty;
        pnlData.Controls.Clear();
        ButtonClicked = false;
        pnlData.Controls.Clear();
        lFilterDefs.Clear();
        pbButton.Size = szSmall;
        this.Size = szSmall;        
    }
    catch (Exception)
    {
        throw; 
    }
}

DoBS

/// <summary>
/// Extract the filter information from the bindingsource object
/// </summary>
/// <param name="oBS"></param>
private void DoBS(BindingSource oBS)
{
    FilterDef oFD;
    DataTable oTable = null;
    //test for a dataview
    if (oBS.DataSource.GetType().Equals(typeof(System.Data.DataView)))
    {
        oTable = ((DataView)oBS.DataSource).Table;
    }
    if (oBS.DataSource.GetType().Equals(typeof(System.Data.DataTable)))
    {
        oTable = (DataTable)oBS.DataSource;
    }
    if (oTable != null)
    {
        lFilterDefs.Clear();
        lPanels.Clear();
        foreach (DataColumn oCol in oTable.Columns)
        {
            oFD = new FilterDef();
            oFD.ColumnName = oCol.ColumnName;
            oFD.Caption = oCol.Caption;
            oFD.Ordinal = oCol.Ordinal;

            //check there is a column in the DGV and if is is visible
            foreach (DataGridViewColumn C in oDGV.Columns)
            {
                if (C.Name == oCol.ColumnName)
                {
                    oFD.IsVisible = C.Visible;
                    break;
                }
            }

            switch (oCol.DataType.Name.ToString().ToLower())
            {
                case "boolean":
                    oFD.DataType = FilterDef.enDataType.Boolean;
                    break;
                case "int":
                case "int32":
                case "int64":
                case "numeric":
                case "decimal":
                    oFD.DataType = FilterDef.enDataType.Numeric;
                    break;
                case "datetime":
                case "date":
                    oFD.DataType = FilterDef.enDataType.Date;
                    break;
                default:
                    oFD.DataType = FilterDef.enDataType.String;
                    break;
            }
            lFilterDefs.Add(oFD);
            Panel oPanel = null;
            oPanel = AddSet(oFD);
            if (oPanel != null)
            {
                lPanels.Add(oPanel);
            }
        }

        for (int i = lPanels.Count; i > 0; i--)
        {
            oFD = (FilterDef)lPanels[i - 1].Tag;
            if (oFD.IsVisible)
            { this.pnlData.Controls.Add(lPanels[i - 1]); }
        }
    }
}

This method extracts all the information from the BindingSource needed for the filter construction. A FilterDef object is created for each column in the underlying DataTable. Each column's Visible property is checked in the DataGridView.

A switch is used to break down some of the common data types into a set of four primitives, and the method AddSet is called for each FilterDef created. The resulting panel returned from the Set is then added to the pnlData panel on the user control.

AddSet

/// <summary>
/// Add a set of controls to the panel for the filter definition
/// </summary>
/// <param name="oDef">filter definition class</param>
/// <returns>a populated panel</returns>
private Panel AddSet(FilterDef oDef)
{
    Panel oPanel = new Panel();
    ComboBox oCBO = new ComboBox();
    Label oLabel = new Label();
    Control ctrlFilter;
    //set the panel details
    oPanel.Height = 21;
    oPanel.Tag = oDef;
    oPanel.Dock = DockStyle.Top;

    //setup the label 
    oLabel.Width = 120;
    oLabel.Text = oDef.Caption;
    oLabel.TextAlign = ContentAlignment.MiddleRight;
    oLabel.Dock = DockStyle.Left;
    //filter the datatype table for display in the combo
    string sFilter = string.Format("DataType = '{0}'", 
                     oDef.DataType.ToString());
    DataView oDV = new DataView(oDef.dtOp.Copy(), sFilter, 
                   "", DataViewRowState.CurrentRows);
    oCBO.Width = 20;
    oCBO.Dock = DockStyle.Fill;
    oCBO.DropDownStyle = ComboBoxStyle.DropDownList;
    oCBO.DisplayMember = "Operator";
    oCBO.ValueMember = "Operator";
    oCBO.DataSource = oDV;
    oCBO.Name = string.Format("cbo{0}", oDef.Ordinal);
    
    switch (oDef.DataType)
    {
        case FilterDef.enDataType.Boolean:
            CheckBox oChk = new CheckBox();
            oChk.Name = string.Format("ctrlF{0}", oDef.Ordinal);
            oChk.Width = 80;
            oChk.Dock = DockStyle.Left;
            oChk.ThreeState = true;                    
            ctrlFilter = oChk;

            //combo is bot required
            oCBO.Visible = false;
            break;
        case FilterDef.enDataType.Date:
            DateTimePicker oDte = new DateTimePicker();
            oDte.Name = string.Format("ctrlF{0}", oDef.Ordinal);
            oDte.Format = DateTimePickerFormat.Short;
            oDte.Width = 80;
            oDte.Dock = DockStyle.Left;
            oDte.Value = Convert.ToDateTime(MinDate);
            ctrlFilter = oDte;
            break;
        default:
            //set the textbox
            TextBox oTB = new TextBox();
            oTB.Width = 80;
            oTB.Dock = DockStyle.Left;
            oTB.Name = string.Format("ctrlF{0}", oDef.Ordinal);
            ctrlFilter = oTB;
            break;
    }

    //add the controls to the panel
    oPanel.Controls.Add(oCBO);
    oPanel.Controls.Add(ctrlFilter);
    oPanel.Controls.Add(oLabel);
    return oPanel;
}

This method creates a panel for each FilterDef and populates the controls based on the data type primitive. The controls are named with the column ordinal for easy identification when applying the filter. ShowFilter sets the size of the user control and pnlData has the docking set to fill. This simplifies the layout of the control.

FilterGetSet

private string FilterGetSet(Control ctrlFilter, bool bReset)
{
    string sValue = string.Empty;

    //reset the filter control
    if (ctrlFilter.GetType().Equals(typeof(System.Windows.Forms.TextBox)))
    {
        if (!bReset)
        {
            sValue = (ctrlFilter as TextBox).Text;
        }
        else
            (ctrlFilter as TextBox).Text = string.Empty;
    }
    if (ctrlFilter.GetType().Equals(typeof(System.Windows.Forms.DateTimePicker)))
    {
        if (!bReset)
        {
            if ((ctrlFilter as DateTimePicker).Value == Convert.ToDateTime(MinDate))
            {
                sValue = string.Empty;
            }
            else
                sValue = (ctrlFilter as DateTimePicker).Value.ToString("dd/MMM/yyyy");
        }
        else
            (ctrlFilter as DateTimePicker).Value = Convert.ToDateTime(MinDate);
    }
    if (ctrlFilter.GetType().Equals(typeof(System.Windows.Forms.CheckBox)))
    {
        if (!bReset)
        {
            sValue = (ctrlFilter as CheckBox).Checked.ToString();
        }
        else
            (ctrlFilter as CheckBox).CheckState = CheckState.Indeterminate;
    }
    return sValue;
}

The control type is checked and the data property is reset to the default.

Note: The stupid DateTimePicker will not accept DateTime.MinValue, so I have created a constant in the control set to 1/1/1900. Management of the DateTime control is finicky as it always has a value.

Also, the CheckBox's ThreeState value is set and the filter constructor checks for Indeterminate, the users may need education in this area.

The toolstrip does the obvious:

  • Apply - the data controls are checked to build the filter string which is applied against the BindingSource Filter property.
  • Reset - clears the current filter using FilterGetSet.
  • Show/Hide - this toggles the hidden columns in the DataGridView.
  • Export - I use the FileHandler class (used in the CSV to Table article) to export the underlying table.

Clicking the PictureBox returns the UserControl to its minimised state.

User_Control_with_data.jpg

Points of interest

Did I learn anything? Yes, the DateTimePicker control is one of the worst designed I have ever seen. I actually use the Infragistics one which behaves better. I have used the standard one in the article for compatibility.

Creating the event for the change of DataSource on the BindingSource was enlightening.

History

  • Initial version.
  • To Do:

    I need to work out how to get at the item in the BindingSource regardless of the underlying data structure. This will allow me to filter the DataGridView when using a List<>.

    Change the export option to allow the user to give it a file name and allow the export to be based on the grid instead of the underlying data table.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Mycroft Holmes
Software Developer (Senior) Contractor
Singapore Singapore
Started my programming life writing Excel 1.0 macros, God what a long time ago.
 
Now I'm a dotnet developer, I get to influence direction, play with new toys, build stuff, life is wonderful.
 
Greatest buzz you can get, walk past a row of desks and see your application running on all of them (and getting paid).
 
Greatest irritant, pouring 12 months of knowledge and experience into an empty head only to have it leave.

Comments and Discussions

 
QuestionNice post PinmemberTridip Bhattacharjee12-Dec-13 21:49 
GeneralMy vote of 5 Pinmembermanoj kumar choubey27-Mar-12 21:42 
GeneralMy Vote of 2 Pinmembertonyt6-Feb-10 17:27 
GeneralRe: My Vote of 2 PinmvpMycroft Holmes6-Feb-10 17:50 
GeneralYet another solution... PinmemberRobert Rohde11-Jul-09 10:09 
GeneralRe: Yet another solution... PinmemberMycroft Holmes11-Jul-09 14:37 
GeneralNice PinmemberMd. Marufuzzaman11-Jul-09 0:58 
GeneralRe: Nice PinmemberMycroft Holmes11-Jul-09 14:38 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140709.1 | Last Updated 10 Jul 2009
Article Copyright 2009 by Mycroft Holmes
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid