Deborah's Developer MindScape






         Tips and Techniques for Web and .NET developers.

February 27, 2010

Binding to a ComboBox using a DataTable and Linq

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:

image

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.

image

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:

image

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!

3 Comments

  1.   jayapriya — December 27, 2010 @ 2:09 am    Reply

    temporily i was Store a data in one combobox… in delete mode i want a data one by one to delete how it can be do? please help me

  2.   Jabberpunch — March 4, 2011 @ 1:40 pm    Reply

    Can you please explain why do you need to use a DataTable? I thought the big advantage of using Linq was to eliminate the need to deal with DataTables, and DataAdapters, etc. Why don’t you simply bind the controls to your Linq query directly? I’m by no means an expert, I’m just learning Linq which is why I’m asking.

  3.   DeborahK — March 5, 2011 @ 12:32 pm    Reply

    Hi Jabberpunch –

    There are many “flavors” of Linq. You are probably refering to Linq to SQL or Linq to Entities, with both provide access to data in a database without DataTables or DataSets.

    In this example, I am using Linq to DataTables which is useful if you are using ADO.NET in your application.

    There is also Linq to Objects, Linq to XML, Linq to EBay, and …

    Here is a link to the list of Linq providers:
    http://blogs.msdn.com/b/charlie/archive/2008/02/28/link-to-everything-a-list-of-linq-providers.aspx

    And here is a link to an overview of Linq:
    http://blogs.msdn.com/b/charlie/archive/2008/02/28/link-to-everything-a-list-of-linq-providers.aspx

    Hope this helps.

RSS feed for comments on this post. TrackBack URI

Leave a comment

*

© 2014 Deborah's Developer MindScape   Provided by WPMU DEV -The WordPress Experts   Hosted by Microsoft MVPs