Tips on Related Data Binding and ComboBoxes
I've been noticing a lot of questions on the forums related to Winforms data binding and the ComboBox and I thought I'd post something up here to help people out. In fact data binding, or what we call “Windows Forms over Data” is a huge, sometimes misunderstood, topic. Because of this I’m putting together a “how-to” video series on a variety of topics in this area. Two of the videos are dedicated to data binding the ComboBox in a couple very common scenarios:
1. To display information from a lookup table and send the selected value into another table's field.
2. To display a list of parent table's records and use that as a filter to display related child records. For instance, as the user selects a record in the ComboBox, you want to display all the related child records in a grid.
The trick is setting up the data binding properly using the BindingSource. I wrote about how to set up related lists in this post in order to get this second scenario to work with BindingSources managing lists of objects. Here, I’ll put it into the context of using DataSets.
In the first case it's not necessary to set up a data relation in your DataSet between the lookup table and the table you're editing, but it doesn't hurt. In the second case it is necessary to create a relation between your parent and child tables. Let's take an example from our beloved Northwind.
First we’ll use the Data Source Configuration Wizard to create a DataSet with Regions and Territories. In VS 2005, go to the Data menu and select “Show Data Sources” then select “Add New Data Source”.
If we take a look at the DataSet through the DataSet Designer, we see that these tables are related on RegionID:
In the first scenario we want to select a Region from the ComboBox and have that value populated into the Territorries record. In this case we set up the RegionBindingSource with the following properties:
Me.RegionBindingSource.DataSource = Me.NorthwindDataSet
Me.RegionBindingSource.DataMember = "Region"
Then you use set the BindingSource as the Datasource of the ComboBox and set the display member and value member properties:
Me.ComboBox1.DataSource = Me.RegionBindingSource
Me.ComboBox1.DisplayMember = "RegionDescription"
Me.ComboBox1.ValueMember = "RegionID"
These properties control what items are displayed in the ComboBox and what value is used when the user makes a selection. Now to get that value into the Territories table, we use the TerritoriesBindingSource when we create the binding to the RegionID:
Me.TerritoriesBindingSource.DataMember = "Territories"
Me.TerritoriesBindingSource.DataSource = Me.NorthwindDataSet
Dim b As New System.Windows.Forms.Binding("SelectedValue", _
Me.TerritoriesBindingSource, "RegionID", True)
Me.ComboBox1.DataBindings.Add(b)
Okay we're all set, right? Well... almost! You'll also need to call EndEdit on the TerritoriesBindingSource at some point in order to write the value back to the DataSet. If you’re using the designers to do this then it takes care of the code for you when you click save on the BindingNavigator’s save button. So depending on the style of your form you could do this from an "Update" button (similarly you could call CancelEdit from a Cancel button).
Private Sub TerritoriesBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TerritoriesBindingNavigatorSaveItem.Click
Me.Validate()
Me.TerritoriesBindingSource.EndEdit()
Me.TerritoriesTableAdapter.Update(Me.NorthwindDataSet.Territories)
End Sub
The cool thing about EndEdit/CancelEdit on the BindingSources is that they cancel or commit only the fields in which they have bindings for, where as the DataSet rows' AcceptChanges/RejectChanges works on the whole row regardless of the data bindings.
Now let's take our second scenario where we want to use the ComboBox as a row filter. In this case we have to have a relation set up between our parent and our child; in the example this is FK_Territories_Region in our DataSet. So the BindingSources in this case need to be set up so that they are related as well. If you use the Data Sources Window, make sure you are working with the Region and the related Territories, just like if you were creating a One-to-Many form.
So the BindingSources will be set up like so:
Me.RegionBindingSource.DataSource = Me.NorthwindDataSet
Me.RegionBindingSource.DataMember = "Region"
Me.TerritoriesBindingSource.DataSource = Me.RegionBindingSource
Me.TerritoriesBindingSource.DataMember = "FK_Territories_Region"
Notice that the main difference here is that the TerritoriesBindingSource’s DataSource property is set to the parent BindingSource, RegionBindingSource and its DataMember is the relation name. This sets up automatic filtering on the TerritoriesBindingSource as the position changes on the RegionBindingSource. Also notice how the BindingSources decouple the data from the actual controls, making it very easy to switch controls or change the data sources.
Now the ComboBox properties can then be set up just like the first example:
Me.ComboBox1.DataSource = Me.RegionBindingSource
Me.ComboBox1.DisplayMember = "RegionDescription"
Me.ComboBox1.ValueMember = "RegionID"
Technically we don't need to specify the ValueMember property this time because we're not writing it anywhere, but it doesn't hurt to specify it. Next all we need to set up is the DataSource property of the DataGridView setting it to the TerritoriesBindingSource.
Me.TerritoriesDataGridView.DataSource = Me.TerritoriesBindingSource
(By the way, all the code up to this point could all be written by the designers by using drag-and-drop from the Data Sources Window. You’ll see this in the videos ;-))
Okay we're all set, right? Well... almost! Unfortunately a ComboBox won't move the BindingSource’s position for you like list controls do (Grids, ListBoxes). So the trick is to simply set the position of the RegionBindingSource by handling the ComboBox's SelectedIndexChanged event. So we need to write some code:
Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
Me.RegionBindingSource.Position = Me.ComboBox1.SelectedIndex
End Sub
Because we have related BindingSources the grid will automatically filter its rows based on the selected parent row in the ComboBox.
And notice that this code would work with other controls as well, for instance, instead of a ComboBox we could have used a ListBox or have set up two related grids.
Because we’re using DataSets as the source of our data, the BindingSources are maintaining DataViews so you can easily access the current DataRowView and cast it to the Typed DataSets’s row for typed access to the fields. For instance it’s handy to be able to get the current row when working on your form. You can do this by adding a property to your form. Just make sure to check for Nothing because there may not be a selected row:
Public ReadOnly Property CurrentRegion() As NorthwindDataSet.RegionRow
Get
If Me.RegionBindingSource.Position > -1 Then
Return CType(CType(Me.RegionBindingSource.Current, DataRowView).Row, NorthwindDataSet.RegionRow)
Else
Return Nothing
End If
End Get
End Property
We can use this property like so:
Dim region As NorthwindDataSet.RegionRow = Me.CurrentRegion
If region IsNot Nothing Then
MsgBox("The selected Region is:" & region.RegionDescription)
End If
The BindingSources are your friends. They decouple the data from the actual controls, making it very easy to switch controls or change the data sources without affecting the bindings. Use the Data Sources Window and it will save you from writing 90% of your data binding code. Have fun and keep a look out for the Windows Forms over Data video series that will be published soon!
Beth is a Program Manager on the Visual Studio Community Team at Microsoft and is responsible for producing and managing content for business application developers, driving community features and team participation onto MSDN Developer Centers (http://msdn.com), and helping make Visual Studio one of the best developer tools in the world. She also produces regular content on her blog (http://blogs.msdn.com/bethmassi), Channel 9, and a variety of other developer sites and magazines. As a community champion and a long-time member of the Microsoft developer community she also helps with the San Francisco East Bay .NET user group and is a frequent speaker at various software development events. Before Microsoft, she was a Senior Architect at a health care software product company and a Microsoft Solutions Architect MVP. Over the last decade she has worked on distributed applications and frameworks, web and Windows-based applications using Microsoft development tools in a variety of businesses. She loves teaching, hiking, mountain biking, and driving really fast.
