Deborah's Developer MindScape

If you retrieve data into a DataTable, it is easy to bind it to a ComboBox. And before Linq, you would filter the DataTable using a DataView. But with Linq, you have some easy to use features for filtering the contents of your ComboBox.

First, here is the basic code for binding a ComboBox to a DataTable.

In C#:

private DataTable dt = Customers.Retrieve();

ComboBox1.DataSource = dt;
ComboBox1.DisplayMember = "FullName";
ComboBox1.ValueMember = "CustomerId";

In VB:

Private dt As DataTable = Customers.Retrieve

ComboBox1.DataSource = dt
ComboBox1.DisplayMember = "FullName"
ComboBox1.ValueMember = "CustomerId"

The Retrieve method on the Customers class retrieves a DataTable of customers. If you want to try this code, you can build a DataTable in code following the techniques covered here.

You can then set the DataSource to the DataTable, set the DisplayMember to the name of the field to display in the ComboBox, and set the ValueMember to the name of the field to use as the field value. This is most often the unique key.

The result looks like this:

With Linq you can add filtering criteria. So let’s add a second ComboBox that lists only the customers with a last name that starts with "B".

NOTE: Be sure to set a reference to System.Data.DataSetExtensions.

In C#:

var query = dt.AsEnumerable().Where(c=>
        c.Field<String>("LastName").StartsWith("B"));

ComboBox2.DataSource = query.AsDataView();
ComboBox2.DisplayMember = "FullName";
ComboBox2.ValueMember = "CustomerId";

In VB:

Dim query = dt.AsEnumerable.Where(Function(c) _
       c.Field(Of String)("LastName").StartsWith("B"))

ComboBox2.DataSource = query.AsDataView
ComboBox2.DisplayMember = "FullName"
ComboBox2.ValueMember = "CustomerId"

This code uses a Lambda expression to filter the DataTable to only those rows where the LastName starts with "B".

The AsEnumerable extension method is necessary to allow Linq/Lambda expressions to work with a DataTable. Any field in the DataTable is accessed using c.Field<T> Or c.Field(Of T) where T is the type of the field. In this example, the field is a string.

The second ComboBox is then bound to the query using the AsDataView extension method. This allows the binding to bind the result as a DataView.

The result looks like this.

But wait, there is more. Because of the way that binding works. adding rows to the DataTable will add rows to the first ComboBox that is bound to the DataTable. AND if the new row starts with the letter "B", it will add it to the second ComboBox as well.

In this example, code in the Add button does this:

In C#:

private void Button1_Click(object sender, EventArgs e)
{
    dt.Rows.Add(5, "Bond", "James", "Bond, James", DBNull.Value, DBNull.Value, DBNull.Value, DBNull.Value, DBNull.Value, DBNull.Value, 3);
}

In VB:

Private Sub Button1_Click2(ByVal sender As System.Object, _
            ByVal e As System.EventArgs) Handles Button1.Click
    dt.Rows.Add(5, "Bond", "James", "Bond, James", DBNull.Value, DBNull.Value, DBNull.Value, DBNull.Value, DBNull.Value, DBNull.Value, 3)
End Sub

Click the button and the result is as follows:

No refreshing or re-executing binding code required. It just works!!

Use this technique any time you want to bind a ComboBox to a filtered set of data from a DataTable.

Enjoy!