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 4 and 7 and type the answer here:
  • Post
  • Hi,

    I am trying to add all the items from a table to a combobox.

    The tables I have are

    Access:

    EmployeeNR

    AccessLevel

    Password

    Departments:

    Department

    Functions:

    Function

    Description

    Department

    After I created a form with the FunctionsTableAdapter adding the fields in the form, I added the Department field from the Departments table as a combobox. I need the user to be able to add a new function and just select the department in the combobox.

    If the department does not exist they will add it on another form. I am using VB.Net in VS2008. I have tried the following code on the load procedure of the form and it does not work at all. The combobox just stays empty.

    DepartmentComboBox.DataSource = DataDataSet.Tables("Departments")

    DepartmentComboBox.DisplayMember = "Department"

    I am not using the OleDBConnection thing. I added the Data Connection throught the Data Sources. It is a Jet Database(Ms Access)

    If I must use some kind of sql querry, could someone please assist me in doing this? I have looked almost everywhere.

  • Using the example you provided, how could you:

    1.  Only show TerritoryDescriptions that started with the letter B

    and

    2.  Sort the results first by the TerritoryDescription and then by the TerritoryID (two column sorting)

    This information will be helpful for me to get past a problem in my project.

  • Hi Rob,

    In the above example you would simply provide a LINQ query and use that as the datasource. Since there is already an association between the Territory and Region in our model we can write:

    Me.RegionBindingSource.DataSource = _

    From t In db.Territories _

    Where t.TerritoryDescription Like "B*" _

    Order By t.TerritoryDescription, t.TerritoryID _

    Select t.Region Distinct

    If there was no association we could still join manually on RegionId in this case:

    Me.RegionBindingSource.DataSource = _

    From r In db.Regions _

    Join t In db.Territories On r.RegionID Equals t.RegionID _

    Where t.TerritoryDescription Like "B*" _

    Order By t.TerritoryDescription, t.TerritoryID _

    Select r Distinct

    This will select regions that have at least one territory that starts with "B".

    HTH,

    -B

  • Awesome!  Thanks for your quick response.  I'll give it a try.  :)

  • Thanks for your quick help, but maybe I didn't explain properly what I need to do as that didn't produce the expected results.

    For example, lets say you have two tables, one a parent, one a child.  You want to be able to use the binding navigator on the parent table to scroll between parent records, and at the same time, the related child table automatically displays the related child records.  This part is easy and explained in your helpful blog.

    To take it one step further, what if you wanted to show only a subset of the child table based on the parent record chosen.   For example, you want to see all related child records in which a field starts with "B*".  

    Your solution, implemented in my code affected the recordset returned by the parent table and didn't do what I needed.  Do you think this is possible with linq?  Sorry but I am used to the traditional ways of doing this and having trouble making this work with linq.  Maybe this is not possible using this technique.  Thanks in advance!

  • Hi Rob,

    On the client just handle the PositionChanged event of the Parent BindingSource and then you can filter the child collection. i.e.

       Private Sub RegionBindingSource_PositionChanged() _

           Handles RegionBindingSource.PositionChanged

           If Me.RegionBindingSource.Position > -1 Then

               Dim region = CType(Me.RegionBindingSource.Current, Region)

               Me.TerritoriesBindingSource.DataSource = From t In region.Territories _

                                                       Where t.TerritoryDescription Like "B*"

           End If

       End Sub

    HTH,

    -B

  • That did the trick!  Thanks :)

  • Lets say you want to implement a custom filter or sort order of the child table as in your answer on May 31 by adding the code to the position changed event.  This works great but seems to break the relationship between parent and child for the purpose of adding rows to the child bindingsource.

    Question,

    If adding code like "Me.TerritoriesBindingSource.DataSource = From t In region.Territories order by t.territorydescription"

    to the positionchanged event of the parent binding source for the purpose of sorting, how do you address that this breaks the relationship to the parent bindingsource when adding new records to the child binding source?  Is there another way to specify a custom sort or filter for a child bindingsource so that the foreign key in the child bindingsource is automatically set when adding a row to the child?

    I hope that question makes sense!

    Thanks!!

  • Beth, thanks for all of your insight throughout!

    I've implemented the combobox issue as described and in doing so broke an event that places a variable into a cell within the same DataGridView.

    Using the RowEnter event I have:

           Me.ActivityLogDataGridView.Rows(e.RowIndex).Cells(1).Value() = currentLog

    currentLog is set to the linking LogID value from a corresponding dgv in the same form.

    Prior to getting the combobox to work correctly, this process was working fine.  After adding the Sub New() procedure, I get a StackOverflowException pointing back to the RowEnter statement above.  Commenting out the statement clears the error and the functionality.  Commenting out the Sub New() brings back this functionality, but I loose the combobox.

    Any thoughts?

  • Beth,

    I am currently developing an application for disipline tracking software for the school district I work for. I have been using the video series a lot, and I still haven't been able to find a way to get the last feature working.

    Let me first explain real quick our disipline system. When a students record is added to the database, it adds a new record each time, the first three are Level 1 actions and conseqences, but If the student commits a fourth level 1, I need a way for the program to tell the user to change it to a level two offense. What can I use for this?

    Thanks!

  • Beth, thank you for the great information the you have been providing. It has been a huge help to me. I am running into an issue that I can't seem to get past. I have a 2 combobox's that are used to filter a datagridview. Each combobox is data bound to a different datatable that has a relation. I have been able to set a filter on the datagridview to only show rows based on selection of the 2 combobox’s.  The Issue I am running into is to get the 2nd combobox to show only distinct values. I set the bindings from the design view and I am using VS2008 Pro. Can you point me in the right direction to accomplish this? Thank you for any help you can provide.

  • Hi all,

    How can I change color of a cell in datagridview in this issue:

    - Linking BindingSource with DAtagridView

    - When I add item in BindingSource I want change color of a new row inserted in DatagridView

    Thanks in advance, kind regards

  • Hi Beth,

    I have a form that has a IGPNHDataSet with three Related tables(Registration_Table, Complaint_Table, Offender_Table)

    Relationship as follows: FK_complaint_ID, FK_offender_ID are both in the Registration_Table..

    Registration Table:

    Reg_ID as PK

    Date

    Complaint_ID

    Offender_ID

    Desc

    Nature

    Complaint Table:

    complaint_Id as PK

    lastname

    firstname

    phone

    Offender Table:

    Offender_ID

    lastname

    firstname

    phone

    etc..

    Above is my tables structure.

    On the form, I drag all the info for all three tables..

    Onload, I get all the records from the tables..

    when i click on NEW to do a new registration which will include new complainant and offender, the fields from the regitration table is cleared and i can enter new record

    but for complainant and offender, no action is taking, i cannot enter new info for them.

    Can you please guide me on what to do and need to save/update the records

    if need be, send me some guidances at wnmichaud@yahoo.com

    thanks..

  • It’s extremely common to have to hook up lookup tables on your data entry forms in order to populate

  • I brought up the Data Sources window and I added some data sources and I don't get the equivalent of RegionBindingSource (or any code for that matter).

    What am I doing wrong?

Page 2 of 3 (36 items) 123