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.
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
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.
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.
DataTable oTable = oData.GetData();
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
{
Color cLoaded = Color.Bisque;
if (IsLoaded) { cLoaded = Color.Tomato; }
this.pbButton.BackColor = cLoaded;
if (this.Size != szSmall)
{
pbButton.Size = szSmall;
this.Size = szSmall;
return;
}
ParseDataSource();
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.
private bool ParseDataSource()
{
if (oDGV == null)
{ return false; }
if (oDGV.DataSource == null)
{ return false; }
if (this.pnlData.Controls.Count > 0)
{ return true; }
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
private void DoBS(BindingSource oBS)
{
FilterDef oFD;
DataTable oTable = null;
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;
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
private Panel AddSet(FilterDef oDef)
{
Panel oPanel = new Panel();
ComboBox oCBO = new ComboBox();
Label oLabel = new Label();
Control ctrlFilter;
oPanel.Height = 21;
oPanel.Tag = oDef;
oPanel.Dock = DockStyle.Top;
oLabel.Width = 120;
oLabel.Text = oDef.Caption;
oLabel.TextAlign = ContentAlignment.MiddleRight;
oLabel.Dock = DockStyle.Left;
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;
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:
TextBox oTB = new TextBox();
oTB.Width = 80;
oTB.Dock = DockStyle.Left;
oTB.Name = string.Format("ctrlF{0}", oDef.Ordinal);
ctrlFilter = oTB;
break;
}
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;
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.
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.
