DataGridView – how to show a subset in a DropDownColumn
The standard approach is to add event handlers for CellBeginEdit and CellEndEdit. In CellBeginEdit a new datasources is bound to the dropdown and in CellEndEdit the binding is reversed. Here is a sample from the DataGridVew FAQ.
private
void dataGridView1_CellBeginEdit(object sender,
DataGridViewCellCancelEventArgs e)
{
if (e.ColumnIndex == territoryComboBoxColumn.Index)
{
// Set the combobox cell datasource to the filtered BindingSource
DataGridViewComboBoxCell dgcb = (DataGridViewComboBoxCell)dataGridView1
[e.ColumnIndex, e.RowIndex];
dgcb.DataSource = filteredTerritoriesBS;
// Filter the BindingSource based upon the region selected
this.filteredTerritoriesBS.Filter = "RegionID = " +
this.dataGridView1[e.ColumnIndex - 1, e.RowIndex].Value.ToString();
}
}
private
void dataGridView1_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
if (e.ColumnIndex == this.territoryComboBoxColumn.Index)
{
// Reset combobox cell to the unfiltered BindingSource
DataGridViewComboBoxCell dgcb = (DataGridViewComboBoxCell)dataGridView1
[e.ColumnIndex, e.RowIndex];
dgcb.DataSource = territoriesBindingSource; //unfiltered
this.filteredTerritoriesBS.RemoveFilter();
}
}
In general this approach leads to some code repetition and adds to the general clutter of a Windows Form that uses a DataGridView. It gets EMORMOUSLY more cluttered if there are multiple columns with DropDown's involved.
This helper class presents a more general approach I used in a recent project. The project specified many dropdowns which should only present the user with 'Active' options. The relevant database lookup tables have an IsActive boolean column to distinguish active and inactive rows. The prime data tables do have a mixture of active and inactive values.
The first problem is that the DataGridView requires that a DropDown contain the value held by its prime row. So it is not safe to populate the Dropdown with only active values.
The basic strategy (see the comments in the code) is:
- Find the cell being edited
- Save the Value and FormattedValue
- Find the original DataSource bound to the dropdown (this source must include both active and inactive entries) the code assumes this source is a DataTable
- Create a new DataTable with only the pruned list of entries in it
- If needed add the Value and FormattedValue to the DataTable because the DataGridView will be upset if it's not there. Annotate the FormattedValue to discourage the user from choosing it.
Here is the code.
public
enum
ActiveOnlyDropDownHelperNullOptions { ForbidNull, AllowNull }
public
class
ActiveOnlyDropDownHelper
{
ActiveOnlyDropDownHelperNullOptions _nullOptions;
BindingSource _originalBindingSource;
DataGridViewComboBoxColumn _column;
DataGridView _dgv;
string _exclusionFilter;
public ActiveOnlyDropDownHelper(DataGridView dgv,
DataGridViewComboBoxColumn column,
ActiveOnlyDropDownHelperNullOptions nullOptions)
: this(dgv, column, nullOptions, "IsActive <> true")
{
}
public ActiveOnlyDropDownHelper(DataGridView dgv,
DataGridViewComboBoxColumn column,
ActiveOnlyDropDownHelperNullOptions nullOptions,
string exclusionFilter)
{
dgv.CellBeginEdit += dgv_CellBeginEdit;
dgv.CellEndEdit += dgv_CellEndEdit;
_nullOptions = nullOptions;
_column = column;
_dgv = dgv;
_exclusionFilter = exclusionFilter;
}
private
void dgv_CellBeginEdit(object sender,
DataGridViewCellCancelEventArgs e)
{
if (e.ColumnIndex == _column.Index)
{
// Grab the cell we are working in
DataGridViewComboBoxCell dgcb = (DataGridViewComboBoxCell)
_dgv[e.ColumnIndex, e.RowIndex];
// Grab the current value and its displayedvalue
object currentValue = dgcb.Value;
object currentFormattedValue = dgcb.FormattedValue;
// Get the original data bound to the lookup combo
// - assumes it was bound to a DataTable
// via a BindingSource
_originalBindingSource = (BindingSource)dgcb.DataSource;
DataTable dt = (DataTable)_originalBindingSource.DataSource;
// Copy that DataTable so we can change it
dt = dt.Copy();
if (_nullOptions ==
ActiveOnlyDropDownHelperNullOptions.AllowNull)
{
var row = dt.NewRow();
row[dgcb.ValueMember] = DBNull.Value;
row[dgcb.DisplayMember] = "Null";
dt.Rows.Add(row);
}
// Remove the rows that are unwanted
DataRow[] deadRows = dt.Select(_exclusionFilter);
foreach(var deadRow in deadRows)
dt.Rows.Remove(deadRow);
// The DataGridView will throw an exception if the
// prime data has a look up value that's not in the
// dropdown's list.
// If the current value is missing from the drop down then
// add it - with an anotation
if (dt.Select(dgcb.ValueMember + " = " + currentValue).Length
== 0)
{
var row = dt.NewRow();
row[dgcb.ValueMember] = currentValue;
row[dgcb.DisplayMember] = currentFormattedValue +
" (Inactive)";
dt.Rows.Add(row);
}
// Bind the dropdown to the pruned list.
dgcb.DataSource = dt;
}
}
private
void dgv_CellEndEdit(object sender,
DataGridViewCellEventArgs e)
{
if (e.ColumnIndex == _column.Index)
{
// Return the original BindingSource
DataGridViewComboBoxCell dgcb = (DataGridViewComboBoxCell)
_dgv[e.ColumnIndex, e.RowIndex];
dgcb.DataSource = _originalBindingSource;
}
}
}
And here is an example of how to use it. Note you don't even need to save the object reference. When the event handlers are attached to the DataGridView that will keep the object alive. If you have multiple dropdown columns in the grid you can keep piling these on.
new
ActiveOnlyDropDownHelper(partDataGridView,
dataGridViewDropDownColumn3,
ActiveOnlyDropDownHelperNullOptions.AllowNull,
"ID = 30");
Enjoy!