Click here to Skip to main content
15,887,421 members
Search within:


Articles / Programming Languages / C#
 
Article

Datagridview with filtering capability

Rate me:
Please Sign up or sign in to vote.
3.55/5 (21 votes)
20 Jun 2007CPOL2 min read 140.3K   11.2K   65   28
Datagridview with filtering capability

Introduction

The Microsoft Windows Forms Datagridview provided with the MS Visual Studio 2005 has got a lot new and good features which were missing in the datagrid control provided in the earlier versions of MS .Net. But still, many times the developers or I should say the functional need of the application require the filtering capabilities.

In this article, I am gonna explain the method by which we can add the filtering capabilities in the Datagridview header itself.

To just start with and summarize, it can be entirely customized through an xml file and decided to which of columns, the filtering capability is to be provided.

Unlike most of the filtering grids available, it allows you to apply filter on more than one filter at a time and in that too, you can select multiple values in a column. Thus extending the feature for searching/filtering or we should say minimizing the desired view of data to the bare minimum level. Screenshot - 1.jpg

Screenshot - 2.jpg

Using the code

FilterDataGridView is a fully customizable user control. As far as the developer's point of view is concerned. He will not have to make a single line of code change in his code and yet he can achieve the filtering capability. J

As many of the times, we don't want to give the filtering capability to all the columns that why FilterDataGridView reads the name of the columns from an xml file which have to be enabled for the filtering.

That means what all one developer has to do is to provide an xml file with the name of the columns to be filtered.

Below, I have mentioned what all steps would need to be taken if you want to replace the old Datagridview with the FilterDataGridView.

  1. Add a reference to the FilteredGridViewControl.dll in your project and also add it to your toolbar.
  2. Drag the FilteredGridView from your toolbar to the window form as you do with any other control.
  3. Add FilterGridView.xml to your application. In the compile property, set the Copy to Output folder option.

This xml should look like this: 

<?xml version="1.0" encoding="utf-8" ?> 
<ColumnsToBeFiltered> 
<ColumnName>Branch</ColumnName> 
<ColumnName>City</ColumnName> 
</ColumnsToBeFiltered>

You can specify as many columns here which are to be filtered.

And here we go, all is set. Wasn't that simple?

How it works?

With FilteredGridView, when you right click on a header of a column which is there in the list of Columns to be filtered, it will list all the distinct rows. Now you can select any of the row it your grid will automatically be filtered with that value. FilteredGridView supports applying multiple filters on a single column. That means you can apply as many filters on a column and at the same time you can narrow the data by putting filters on other columns data too.

Assumptions

This version of FilterGridView only works when bound with a Data Source.

License

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


Written By
Manas Bhardwaj
Architect
Netherlands Netherlands

Read my personal blog at www.manasbhardwaj.net.


Comments and Discussions

 
First PrevNext
Questiondownload not working Pin
Member 139440729-Aug-18 19:24
Member 139440729-Aug-18 19:24 
QuestionDropdown columns Pin
sridharsr23-Aug-14 6:25
sridharsr23-Aug-14 6:25 
Questionnot sure what I'm doing wrong Pin
esb7718-Jun-13 9:55
esb7718-Jun-13 9:55 
GeneralMy vote of 5 Pin
Digital Geek21-Nov-12 23:36
Digital Geek21-Nov-12 23:36 
GeneralRe: My vote of 5 Pin
Manas Bhardwaj21-Nov-12 23:43
professionalManas Bhardwaj21-Nov-12 23:43 
GeneralRe: My vote of 5 Pin
Digital Geek22-Nov-12 2:16
Digital Geek22-Nov-12 2:16 
QuestionA Good Solution also Pin
code-kings10-Oct-12 6:38
code-kings10-Oct-12 6:38 
Questionthere is a problem with VS 2010 Pin
esb776-Oct-12 14:20
esb776-Oct-12 14:20 
AnswerRe: there is a problem with VS 2010 Pin
esb777-Oct-12 5:59
esb777-Oct-12 5:59 
GeneralRe: there is a problem with VS 2010 Pin
Manas Bhardwaj7-Oct-12 6:42
professionalManas Bhardwaj7-Oct-12 6:42 
Questionmulti select filter Pin
arkiboys1-Feb-12 23:53
arkiboys1-Feb-12 23:53 
QuestionDatagridview filtering.dll Pin
Maazatron14-Dec-11 3:01
Maazatron14-Dec-11 3:01 
AnswerRe: Datagridview filtering.dll Pin
arkiboys1-Feb-12 23:56
arkiboys1-Feb-12 23:56 
GeneralCountering thr rows Pin
Member 299000828-Aug-10 10:18
Member 299000828-Aug-10 10:18 
GeneralDynamic DataGridView filtering in C# Pin
Ivan (Zaragoza)3-Dec-08 22:50
Ivan (Zaragoza)3-Dec-08 22:50 
GeneralThis is a project Similar to MSDN: http://msdn.microsoft.com/en-us/library/aa480727.aspx Pin
TamusRoyce13-Nov-08 10:29
TamusRoyce13-Nov-08 10:29 
GeneralI am getting an error "Unable to cast object of type 'System.Windows.Forms.BindingSource' to type 'System.Data.DataTable'." Pin
bondonian29-Sep-08 9:34
bondonian29-Sep-08 9:34 
GeneralI did some changes. post here to anyone interested Pin
zhngmm26-Aug-08 13:24
zhngmm26-Aug-08 13:24 
First of all I should thank you Manas Bhardwaj for his nice job. It saved me lots of time on the project I am working on. I did some changes to fit needs of my project. I post here for those people who are interested. //Smile | :)

FilterView.cs
// Codes of this class is adopted from Manas Bhardwaj's article "Datagridview with filtering capability"
// in CodeProject website (http://www.codeproject.com/KB/miscctrl/Datagridview_Fiter.aspx)
// Modified: 
//    - Removed xml part and allow all columns to be filtered
//    - Corrected filter string to allow space in table column name
//    - improve popup menu popup loction, especially while gridview data is big and column scoll
//    - Corrected popup menu item display when item string contain '&' character
//    - Change all static member as internal member instead, this correct filter contents when
//      DataSource is changed from outside gridview
//    - Add Customized filter for string type data column
//    - Add Customized filter for datetime type data column
//    - Change DateTime column only filter on date instead of (date + time)
// Note:
//    - this class only applys on DataTable type of datasouce
// 
using System;
using System.Collections;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Text;
using System.Windows.Forms;

namespace FilteredGridViewControl
{
    public partial class FilterDataGridView : DataGridView
    {
        #region Member Variables
        private string _columnName = string.Empty;
        private Hashtable _checkedFilterHashTable = null;
        private Hashtable _customFilterHashTable = null;
        private DataTable _dataSource = null;
        private bool _isInternalDataSourceChange = false;

        #endregion
        public FilterDataGridView()
        {
            //FilterColumns();
            InitializeComponent();
        }      

        protected override void OnColumnHeaderMouseClick(DataGridViewCellMouseEventArgs e)
        {
            if (e.Button == MouseButtons.Left)
            {
                base.OnColumnHeaderMouseClick(e);
            }
            else if (e.Button == MouseButtons.Right)
            {
                if (this._dataSource == null)
                    this._dataSource = (DataTable)this.DataSource;

                if (this._checkedFilterHashTable == null)
                    this._checkedFilterHashTable = new Hashtable();
                if (this._customFilterHashTable == null)
                    this._customFilterHashTable = new Hashtable();

                // get menu initial point
                int positionX = e.X;
                int positionY = e.Y;

                for (int index = this.FirstDisplayedScrollingColumnIndex; index < this._dataSource.Columns.Count; index++)
                {
                    if (this.Columns[index].Displayed)
                    {
                        if (index == e.ColumnIndex)
                            break;
                        positionX = positionX + this.Columns[index].Width;
                    }
                }
                positionX = positionX - this.FirstDisplayedScrollingColumnHiddenWidth + this.RowHeadersWidth;

                ContextMenu filterContextMenu = new ContextMenu();
                ArrayList columnFilterList = new ArrayList();
                for (int count = 0; count < this._dataSource.Rows.Count; count++)
                {
                    try
                    {
                        // replace "&" as "&&" to preserver display of '&' character in the menutrip
                        string filterItem = this._dataSource.Rows[count][e.ColumnIndex].ToString();
                        if (this._dataSource.Columns[e.ColumnIndex].DataType == typeof(string))
                            filterItem = filterItem.Replace("&", "&&");
                        else if (this._dataSource.Columns[e.ColumnIndex].DataType == typeof(DateTime))
                            filterItem = ((DateTime)this._dataSource.Rows[count][e.ColumnIndex]).ToString("M/d/yyyy");

                        if (!columnFilterList.Contains(filterItem))
                            columnFilterList.Add(filterItem);
                    }
                    catch
                    {
                    }
                }
                columnFilterList.Sort();

                this._columnName = this.Columns[e.ColumnIndex].DataPropertyName;

                filterContextMenu.MenuItems.Add(new MenuItem("Clear All Filters", menuItem_Click));
                filterContextMenu.MenuItems.Add("-");
                filterContextMenu.MenuItems.Add(new MenuItem("Clear This Filter", menuItem_Click));

                // add customized filter 
                if(this._dataSource.Columns[this._columnName].DataType == typeof(string)
                    || this._dataSource.Columns[this._columnName].DataType == typeof(DateTime))
                {
                    filterContextMenu.MenuItems.Add("-");
                    filterContextMenu.MenuItems.Add(new MenuItem("Add Customized Filter", menuItem_Click));
                    filterContextMenu.MenuItems.Add(new MenuItem("Clear Customized Filter", menuItem_Click));
                    if (this._customFilterHashTable.Contains(this._columnName))
                    {
                        filterContextMenu.MenuItems.Add("-");
                        ArrayList customFilter = (ArrayList)this._customFilterHashTable[this._columnName];
                        for (int i = 0; i < customFilter.Count; i++)
                        {
                            MenuItem uniqueColumnsMenuItem = new MenuItem(customFilter[i].ToString(), menuItem_Click);
                            if (this._checkedFilterHashTable.Contains(this._columnName))
                            {
                                if (((ArrayList)_checkedFilterHashTable[this._columnName]).Contains(uniqueColumnsMenuItem.Text))
                                    uniqueColumnsMenuItem.Checked = true;
                                else
                                    uniqueColumnsMenuItem.Checked = false;
                            }
                            filterContextMenu.MenuItems.Add(uniqueColumnsMenuItem);
                        }
                    }
                }

                // add column content filters
                filterContextMenu.MenuItems.Add("-");
                for (int count = 0; count < columnFilterList.Count; count++)
                {
                    MenuItem uniqueColumnsMenuItem = new MenuItem(columnFilterList[count].ToString(), menuItem_Click);
                    if (this._checkedFilterHashTable.Contains(this._columnName))
                    {
                        if (((ArrayList)_checkedFilterHashTable[this._columnName]).Contains(uniqueColumnsMenuItem.Text))
                            uniqueColumnsMenuItem.Checked = true;
                        else
                            uniqueColumnsMenuItem.Checked = false;
                    }
                    filterContextMenu.MenuItems.Add(uniqueColumnsMenuItem);
                }

                filterContextMenu.Show(this, new Point(positionX, e.Y));
            }
        }

        protected override void OnDataSourceChanged(EventArgs e)
        {
            if (!this._isInternalDataSourceChange)
            {
                // datasource is change from outside
                this._columnName = string.Empty;
                this._checkedFilterHashTable = null;
                this._dataSource = null;
            }
            base.OnDataSourceChanged(e);
        }

        void menuItem_Click(object sender, EventArgs e)
        {
            // set switch
            this._isInternalDataSourceChange = true;
            
            // check actions
            string clickedItemString = ((MenuItem)sender).Text;
            if (clickedItemString == "Clear This Filter")
                this._checkedFilterHashTable.Remove(_columnName);
            else if (clickedItemString == "Clear All Filters")
                this._checkedFilterHashTable.Clear();
            else if (clickedItemString == "Add Customized Filter")
            {
                if (!AddCustomizedFilter())
                    return;
            }
            else if (clickedItemString == "Clear Customized Filter")
                RemoveCustomizedFilter();
            else
                UpdateFilterStatus(clickedItemString);

            // refresh display
            RefreshDataDisplay();

            // reset switch
            this._isInternalDataSourceChange = false;
        }

        bool AddCustomizedFilter()
        {
            Type colType = this._dataSource.Columns[this._columnName].DataType;
            Form fm = null;

            if (colType == typeof(string))
                fm = new frmGridViewStringFilter();
            else if (colType == typeof(DateTime))
                fm = new frmGridViewDateTimeFilter();

            fm.StartPosition = FormStartPosition.CenterParent;
            if (fm.ShowDialog() == DialogResult.OK && fm.Tag != null)
            {
                string filterStr = fm.Tag.ToString();
                bool isNew = false;

                // add new filter to list
                if (this._customFilterHashTable.Contains(this._columnName))
                {
                    ArrayList customFilter = (ArrayList)this._customFilterHashTable[this._columnName];
                    if (!customFilter.Contains(filterStr))
                    {
                        customFilter.Add(filterStr);
                        isNew = true;
                    }
                }
                else
                {
                    ArrayList customFilter = new ArrayList();
                    customFilter.Add(filterStr);
                    isNew = true;

                    this._customFilterHashTable.Add(this._columnName, customFilter);
                }

                // add filter to check list
                if (_checkedFilterHashTable.Contains(this._columnName))
                {
                    if (isNew)
                    {
                        if (((ArrayList)this._checkedFilterHashTable[_columnName]).Contains(filterStr))
                            ((ArrayList)this._checkedFilterHashTable[_columnName]).Remove(filterStr);
                        else
                            ((ArrayList)this._checkedFilterHashTable[_columnName]).Add(filterStr);
                    }
                }
                else
                {
                    ArrayList addFilterArrayList = new ArrayList();
                    addFilterArrayList.Add(filterStr);
                    this._checkedFilterHashTable.Add(this._columnName, addFilterArrayList);
                }
                return true;
            }
            return false;
        }
        void RemoveCustomizedFilter()
        {
            // remove customized filter from check list
            if (this._checkedFilterHashTable.Contains(this._columnName)
                && this._customFilterHashTable.Contains(this._columnName))
            {
                ArrayList filterList = (ArrayList)this._checkedFilterHashTable[this._columnName];
                ArrayList removeList = (ArrayList)this._customFilterHashTable[this._columnName];
                foreach (string f in removeList)
                {
                    if (filterList.Contains(f))
                        filterList.Remove(f);
                }
            }

            //  remove customized filter from customize filter table
            this._customFilterHashTable.Remove(this._columnName);
        }
        void UpdateFilterStatus(string clickedItemString)
        {
            // set filter check status
            if (_checkedFilterHashTable.Contains(this._columnName))
            {
                if (((ArrayList)this._checkedFilterHashTable[_columnName]).Contains(clickedItemString))
                    ((ArrayList)this._checkedFilterHashTable[_columnName]).Remove(clickedItemString);
                else
                    ((ArrayList)this._checkedFilterHashTable[_columnName]).Add(clickedItemString);
            }
            else
            {
                ArrayList addFilterArrayList = new ArrayList();
                addFilterArrayList.Add(clickedItemString);
                this._checkedFilterHashTable.Add(this._columnName, addFilterArrayList);
            }
        }
        void RefreshDataDisplay()
        {
            string filter = string.Empty;
            IDictionaryEnumerator enumerator = this._checkedFilterHashTable.GetEnumerator();
            ArrayList columnsArrayList = new ArrayList();
            int hashCount = 0;
            while (enumerator.MoveNext())
            {
                string colname = "[" + enumerator.Key.ToString() + "]";
                columnsArrayList = (ArrayList)enumerator.Value;

                if (columnsArrayList.Count > 0)
                {
                    for (int count = 0; count < columnsArrayList.Count; count++)
                    {
                        // replace "&&" as "&" to recover '&' character in the menutrip
                        string filterValue = columnsArrayList[count].ToString().Replace("&&", "&");
                        string condition = colname + " = '" + filterValue + "'";
                        if (this._dataSource.Columns[enumerator.Key.ToString()].DataType == typeof(string))
                        {
                            // data type: string
                            condition = colname + " LIKE '" + filterValue + "'";
                        }
                        else if (this._dataSource.Columns[enumerator.Key.ToString()].DataType == typeof(DateTime))
                        {
                            // data type: datetime
                            if (filterValue.Contains("<"))
                            {
                                // customized filter
                                int index = filterValue.IndexOf("<");
                                condition = "(" + colname + " > '" + filterValue.Substring(0, index).Trim()
                                    + "' AND " + colname + " < '" + filterValue.Substring(index + 1).Trim() + "')";
                            }
                            else
                            {
                                condition = "(" + colname + " > '" + filterValue + "' AND " 
                                    + colname + " < '" + Convert.ToDateTime(filterValue).AddDays(1).ToString("M/d/yyyy") + "')";

                            }
                        }

                        if (hashCount == 0 && count == 0)  // beginning of all conditions
                            filter = "(" + condition;
                        else if (hashCount != 0 && count == 0) // beginning of current column filter
                            filter = filter + " and (" + condition;
                        else
                            filter = filter + " or " + condition; // in the middle
                    }

                    // enclose current column's conditions
                    filter = filter + ")";
                    hashCount = 1;
                }
            }

            // filter and reset data source
            DataView view = new DataView(this._dataSource);
            view.RowFilter = filter;
            this.DataSource = view;
        }
    }
}


frmGridViewDateTimeFilter.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace FilteredGridViewControl
{
    public partial class frmGridViewDateTimeFilter : Form
    {
        public frmGridViewDateTimeFilter()
        {
            InitializeComponent();
        }

        private void btnOk_Click(object sender, EventArgs e)
        {
            this.DialogResult = DialogResult.OK;
            this.Tag = this.dtpFrom.Value.ToString("M/d/yyyy") + " < "
                + this.dtpTo.Value.AddDays(1).ToString("M/d/yyyy");

            this.Close();
        }

        private void btnCancel_Click(object sender, EventArgs e)
        {
            this.DialogResult = DialogResult.Cancel;
            this.Close();
        }
    }
}


frmGridViewStringFilter.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace FilteredGridViewControl
{
    public partial class frmGridViewStringFilter : Form
    {
        public frmGridViewStringFilter()
        {
            InitializeComponent();
        }

        private void rbStartwith_CheckedChanged(object sender, EventArgs e)
        {
            this.txtStartWith.Enabled = this.rbStartwith.Checked;
        }

        private void rbContain_CheckedChanged(object sender, EventArgs e)
        {
            this.txtContain.Enabled = this.rbContain.Checked;
        }

        private void btnCancel_Click(object sender, EventArgs e)
        {
            this.DialogResult = DialogResult.Cancel;
            this.Close();
        }

        private void btnOk_Click(object sender, EventArgs e)
        {
            this.DialogResult = DialogResult.OK;
            if (this.rbStartwith.Checked)
            {
                if(this.txtStartWith.Text.Trim() != "")
                    this.Tag = this.txtStartWith.Text.Trim() + "%";
            }
            else if (this.rbContain.Checked)
            {
                if (this.txtContain.Text.Trim() != "")
                    this.Tag = "%" + this.txtContain.Text.Trim() + "%";
            }
            this.Close();
        }
    }
}
GeneralRe: I did some changes. post here to anyone interested Pin
bondonian29-Sep-08 11:15
bondonian29-Sep-08 11:15 
GeneralRe: I did some changes. post here to anyone interested Pin
zhngmm29-Sep-08 17:23
zhngmm29-Sep-08 17:23 
GeneralNice work Pin
Mohammad Dayyan16-Aug-08 3:56
Mohammad Dayyan16-Aug-08 3:56 
GeneralUse it in more than one datagridview Pin
davomoreda6-May-08 0:08
davomoreda6-May-08 0:08 
GeneralLooks Good but... Pin
Pat Tormey3-Jul-07 1:07
Pat Tormey3-Jul-07 1:07 
GeneralDoe not work when using without data source. Pin
Joe Sonderegger15-Jun-07 1:39
Joe Sonderegger15-Jun-07 1:39 
GeneralCant add it to my page Pin
DumtySoft14-Jun-07 8:36
DumtySoft14-Jun-07 8:36 
Last Visit: 31-Dec-99 18:00     Last Update: 27-Apr-24 22:36Refresh12 Next ᐅ

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

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

Permalink
Advertise
Privacy
Cookies
Terms of Use
Layout: fixed | fluid

Posted 14 Jun 2007

Article Copyright 2007 by Manas Bhardwaj
Everything else Copyright © CodeProject, 1999-2024

Web03 2.8:2024-04-02:1