Related Data Binding and ComboBoxes with LINQ to SQL

Related Data Binding and ComboBoxes with LINQ to SQL

  • Comments 36

In a previous post I showed how to set up related data binding using ComboBoxes against DataSets and a loyal reader asked how this would be done using LINQ and Visual Studio 2008. I assume he meant LINQ to SQL in this case, because remember LINQ can be used over DataSets too -- you just have to call the TableAdapter fill methods like normal and then you can write queries over the in-memory DataSets like I show in this video.

With LINQ to SQL this can be easier to do and more efficient depending on how you set it up. This is because LINQ to SQL has the ability to fetch data from SQL Server only as you need it automatically for you. With the DataSet this is a more manual process of calling the TableAdapter's Fill method.

In this first example I'm going to set up a ComboBox that is used as a filter to select records to display in a grid. So we want 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, we want to display all the related child records in a grid below.

The first thing is to create a new Windows Forms application and I'll call it LINQComboBinding. Next, we need to add a LINQ to SQL data model that maps 1:1 to our database tables. This is a new item in Visual Studio 2008 that automatically generates classes for our tables which allows us to perform LINQ queries in our code and have them automatically translated and sent as T-SQL statements to SQL-Server. The advantage to using LINQ to SQL classes is that now you can have compile-time checking on your queries and access the results as strongly-typed objects. To add LINQ to SQL classes, right-click on the Project and select "Add New Item" and then choose the LINQ to SQL classes template.

I'll be using the Northwind sample database in this example so I'll name the model Northwind.dbml. Click "Add" and this will open the O/R designer. Just like the DataSet designer, you can drag tables from your Server Explorer (or Database Explorer if using VS2008 Express edition) onto the surface of the designer in order to automatically generate code that defines your LINQ to SQL classes. I'll select Region and Territories for this example.

Notice that the O\R designer infers the associations of the classes it creates by reading the database relationships. In the above diagram, the Region class will contain a collection of Territory classes called Territories. You can take a look at the code it generates by showing all files on the project and then opening the Northwind.designer.vb file.

Okay now we're ready to start designing our form. When we work with data binding on Windows forms we use the Data Sources Window to drag items onto the form designer to create and bind our controls. Under the covers all the controls are bound to a BindingSource that manages the data binding between the control and the DataSource. I've covered this all before with respect to using DataSets as the DataSource of the BindingSource here in this video as well as the previous article on this topic.

What's great about the BindingSource is that it can manage data binding between the control and any type of DataSource, including collections of objects, not just DataSets. And we can use the same familiar drag-drop from the Data Sources Window even with LINQ to SQL classes, there's just a couple of manual steps.

When we open the Data Sources Window (from the menu select Data --> Show Data Sources) you'll see that our LINQ to SQL classes do not show up automatically like our DataSets do. But all we need to do is "Add New DataSource" and then select Object as the Data Source Type in the Data Source Configuration Wizard:

Next thing we do is select the object we want to bind to. In this case, all we need to select is the Region class because this class already contains the Territories collection which we will use on our one-to-many form.

Click Next then Finish and this will populate your Data Sources window with Region and you will see the Territories collection under that. For this example we're going to want a Combobox that is used as a filter for a grid that is displaying the related Territories. In the Data Sources window with the Form Designer open, set the Region to drop a Combobox control.

If you only see DataGridView and Details listed then select "Customize..." and check the Combobox as an available control for the List type. Next drag the Region Combobox onto the form. This will automatically create the RegionBindingSource and a RegionBindingNavigator. I'm going to delete the BindingNavigator because we're going to use the Combobox as a navigator. Next drag the related Terrirories DataGridView onto the form to automatically set up the related TerritoryBindingSource. The DataGridView will also pick up the Region property on the Territory class which is a reference back to the parent Region object, just edit the columns on the DataGridView through its smart tag and remove it.

When we drag the Region Combobox onto our form the designer generates the proper code to set up the ValueMember and DisplayMember properties on the Combobox. ValueMember doesn't actually matter in this case. It also sets up the related TerriroryBindingSource. The key to setting up related BindingSources (even when using DataSets) is that the child's BindingSource's DataSource property must be set to the parent BindingSource and the child's DataMember property is set to the name of the "relation", in the case of a DataSet this would be the DataRelation, in this case however, it's the name of the child collection "Territories". If we were to set this up in code ourselves it would look like this:

Me.RegionComboBox.DataSource = Me.RegionBindingSource
Me.RegionComboBox.DisplayMember = "RegionDescription"

Me.TerritoriesBindingSource.DataMember = "Territories"
Me.TerritoriesBindingSource.DataSource = Me.RegionBindingSource

When we use the Data Sources window this is all set up automatically in the designer-generated code. The only thing the designer doesn't do for us is fill the data like it does when using DataSets. It's very straightforward. In the code-behind for the form add the following code:

Public Class Form1

    Dim db As New NorthwindDataContext

    Sub New()

        ' This call is required by the Windows Form Designer.
        InitializeComponent()

        ' Add any initialization after the InitializeComponent() call.
        Me.RegionBindingSource.DataSource = db.Regions
    End Sub

End Class

The NorthwindDataContext is a class that was generated when we created our model Northwind.dbml. This manages the connection between our LINQ to SQL classes and the database. It also keeps track of changes we make to the Region and Territory objects. All we need to do is set the RegionBindingSource.DataSource property equal to the Regions property on the DataContext. This will initiate the load of all the regions from the database and will create a collection of Region objects. Notice that we aren't specifically loading any territories. By default, LINQ to SQL classes will lazy load related collections so only the related Territory objects will load from the database when the selected Region object's Territories collection is accessed. This happens when we move the position of the RegionBindingSource and the TerritoryBindingSource filters its list. You can see what I mean by adding this code before you set the RegionBindingSource.DataSource property in order to log the T-SQL statements to Debug Output window:

' Add any initialization after the InitializeComponent() call.
db.Log = Console.Out
Me.RegionBindingSource.DataSource = db.Regions

Run the form. When it opens you will see two statements, one to retrieve all the Regions and one to just load the Territories related to the selected Region. As you select a Region in the Combobox, just the related Territories are selected and displayed.

Voila! Notice that in VS 2008 the Combobox will automatically move the BindingSource's position similar to other list controls like the ListBox and the DataGridView eliminating the need to handle the SelectedIndexChanged event to set the BindingSource.Position manually.

In the next post I'll show how we can use the Region Combobox as a lookup list when editing Territories and how we can submit changes to the database.

UPDATE: I placed the code for all the articles on this topic into a Code Gallery project for you to play with.

Enjoy!

Leave a Comment
  • Please add 3 and 8 and type the answer here:
  • Post
  • I have learned a lot from your blogs and videos.  Sorry to dig up an old post, but just getting into LINQ to SL on Winforms.  Been using it on webforms, mostly MVC.

    The question I have is this, how can I sort the data prior to binding it to the combobox?  I want to sort on customername (text) and customerid will be the value.  

  • Hi Kevin,

    When you drag-drop the tables from the data source window onto the Winform it will create the BindingSources for you on the form in the component tray. Take a look at these videos:

    http://msdn.microsoft.com/en-us/vbasic/bb725824.aspx

    http://msdn.microsoft.com/en-us/vbasic/cc138241.aspx

    HTH,

    -B

  • Hi Jerry,

    You can just write a LINQ query to order the collection you want to bind to. For instance in the above example if I wanted to sort the Regions on RegionDescription field then I could do this:

    Me.RegionComboBox.DataSource = Nothing

    Dim sortedRegionList = (From r In db.Regions _

                         Order By r.RegionDescription).ToList()

    Me.RegionBindingSource.DataSource = sortedRegionList

    Me.RegionComboBox.DataSource = Me.RegionBindingSource

    HTH,

    -B

  • Sorry for the delayed response, I'm in a different time zone!  I saw you post re using Live Writer to paste VS code which includes formatting (see below).

    I now wonder how you also paste dialog boxes as well.

    tia

    jjw

  • Hi Jack,

    I use live writer for everything. I just paste screen shots into the posts and it handles uploading them as pictures.

    Cheers,

    -B

  • Hi Beth,

    I have a dataset containing 6 tables (Country, Region, Province, City, Suburb, Street) which have a relationship established on unique key down to the street level so the locality is related. When i use this dataset in a form (to store users location details in a separate dataset) and apply 6 combo boxes in a form to allow the user to select location, the details display ok but show all records from each datatable. In the combobox selected value change event for each combobox i have retrieved the selected keyid and then applied this in a fill query to obtain correct subset child data for the parent id selected. when i move to another record in the main binding source, the lookups in the 6 comboboxes do not display the data user has selected. I have been searching for an answer in particular through your video series but have been unable to find a solution to this problem. Hope you may be able to enlighten me

    regards,

    Mick C

Page 3 of 3 (36 items) 123