Creating Lookup Lists with LINQ to SQL

Creating Lookup Lists with LINQ to SQL

  • Comments 24

In yesterday's post I showed you how to bind LINQ to SQL classes to a Combobox in order to filter records on a one-to-many form. Today I want to show you how how you can use a Combobox as a lookup list in order to edit values on a record. We'll be building on yesterday's example. So to recap, we have added a LINQ to SQL object model called Northwind.dbml to our Windows Forms application that contains Regions and Territory classes and then added the Region object to the Datasources Window by selecting "Add New DataSource" and selecting the Object data source type.

Let's create a simple form that we can use to edit Territories. From the Data Sources window, expand the Region and its related Territories collection then drag the TerritoryID and TerritoryDescription onto the form as Textboxes. This will automatically create the TerriroriesBindingSource and BindingNavigator. Next, select the Combobox control for the RegionID and then drag that onto the form.

 

This sets up the binding to the Territory so that when a value is selected in the Combobox, the Territory.RegionID property is set to that value. Next we need to bind the list of Regions to display in the Combobox, so just drag the Region object (root table displayed in the Data Sources Window) onto the top of (directly over) the Combobox you just dropped on the form. This is a handy trick that will set up the RegionBindingSource in the component tray and set the Combobox's DataSource property to the RegionBindingSource. Next set the ValueMember of the Combobox to "RegionID" in the properties sheet. You may also want to set the Combobox DropDownStyle to "DropDownList".

If we were to look at the generated code, this is how it sets up our data binding in this scenario:

Me.RegionIDComboBox.DataBindings.Add(New System.Windows.Forms.Binding("SelectedValue", _
                                     Me.TerritoriesBindingSource, "RegionID", True))
Me.RegionIDComboBox.DataSource = Me.RegionBindingSource
Me.RegionIDComboBox.DisplayMember = "RegionDescription"
Me.RegionIDComboBox.ValueMember = "RegionID"

What this is doing is first setting up the data binding between the selected value of the Combobox and the TerritoriesBindingSource on the RegionID property. Then we set the DataSource property of the Combobox to the RegionBindingSource which fills the list with Regions to choose from.

Now the rest is up to us. Unlike when using DataSets, the designer doesn't generate code to fill or save our data, but fortunately the code we have to write is minimal. First let's fill our data. It's important in this scenario to fill the Territories before the Regions so that the data binding will work properly.

Public Class Form2

    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.TerritoriesBindingSource.DataSource = db.Territories
        Me.RegionBindingSource.DataSource = db.Regions
    End Sub

End Class

Next we need to write our save code. It's really easy to do this when working with client-server forms like this one because the DataContext automatically tracks changes to our objects for us. We just call SubmitChanges on the DataContext when we want to send all the changes to the database. Enable the save button on the BindingNavigator and then write some simple Save code in the click event handler:

Private Sub TerritoriesBindingNavigatorSaveItem_Click() _
    Handles TerritoriesBindingNavigatorSaveItem.Click

    Me.TerritoriesBindingSource.EndEdit()

    Try
        db.SubmitChanges()

        MsgBox("Saved.")
    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try

End Sub

Let's give it a try! Run the form and edit, add and delete the Territories then click Save and you should see your changes next time you run the form. If you want to see the T-SQL statements being run just put a call to db.Log = Console.Out in order to display the statements in the Debug Output window. Just make sure to remove it before building your release.

Hopefully this helps get you started designing forms using LINQ to SQL classes. It's interesting to note that I actually didn't write one LINQ query in this example. However it's really easy to write queries against your object model now and have the LINQ to SQL provider worry about the SQL statements. For instance, if I wanted to only return the Territories in the Region that started with "East" then we could set the TerritoriesBindingSource.DataSource property like so:

Me.TerritoriesBindingSource.DataSource = _
    From Territory In db.Territories _
    Where Territory.Region.RegionDescription Like "East*"

Me.RegionBindingSource.DataSource = db.Regions

This would result in only a subset of data returned to our form which is always a good idea especially in multi-user database systems! Here's the SQL statements that are automatically sent to the database for us:

SELECT [t0].[TerritoryID], [t0].[TerritoryDescription], [t0].[RegionID]
FROM [dbo].[Territories] AS [t0]
INNER JOIN [dbo].[Region] AS [t1] ON [t1].[RegionID] = [t0].[RegionID]
WHERE [t1].[RegionDescription] LIKE @p0
-- @p0: Input NVarChar (Size = 5; Prec = 0; Scale = 0) [East%]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

SELECT [t0].[RegionID], [t0].[RegionDescription]
FROM [dbo].[Region] AS [t0]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

Looks like reasonable SQL code to me! ;-)

Next time we'll create a one-to-many data entry form and talk about a couple tricks you'll need to know in order to update data properly. (Psssst... here's a hint)

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

    First of all, many thanx for providing us this information in a fluent & easy to understand way. Fortunatelly, some people tend to explain those stuff in a to complicated way.

    I have the same problem concerning the filtering of territories to the selected region, and all prop's are properly binded !

    So my solution was to handle the "SelectedIndexChanged" of the region combo as next:

    public FormTerritory()

           {

               InitializeComponent();

               territoriesBindingSource.DataSource = _db.Territories;

               regionBindingSource.DataSource = _db.Regions;

               // Get the Current RegionId && Filter the Territories

               if (regionIDComboBox.Items.Count > 0)

               {

                   FilterTerritories(Int32.Parse(regionIDComboBox.SelectedValue.ToString()));

               }

           }

    private void regionIDComboBox_SelectedIndexChanged(object sender, EventArgs e)

           {

               // Get the Current RegionId && Filter the Territories

               if (regionIDComboBox.Items.Count > 0)

               {

                   FilterTerritories(Int32.Parse(regionIDComboBox.SelectedValue.ToString()));

               }

           }

           private void FilterTerritories(int p_regionId)

           {

               territoriesBindingSource.DataSource =

                   from territory in _db.Territories

                   where territory.RegionID == p_regionId

                   select territory;

           }

    Greetz & keep going on posting those nice articles !

    Emmanuel Nuyttens

    .NET Architect Alsico NV.

    BELGIUM (Ronse)

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

  • This is a very surpprising style it is the best new techniqe in software developement

  • How would I do this if I had 2 look up columns in a single table that were tied to two seperate tables?

  • I need to do this in C#.  Any tips?

  • Hi jhalsey,

    You would repeat the same process for two lookup tables. And there's nothing special you have to do to support C# here.

    Have fun,

    -B

  • When I try to follow the code here, I get an exception:  

    foreignkeyreferencealreadyhasvalue.  If I add the item to the list associated with the foreignkey element, then all goes well.  however, when I try to have the combo box set the foreign key directly, i get an exception.  Am I looking at this incorreclty?

    thanks,

    John

  • After very carefully following the tutorial, I was able to correct my problem.  I created several combo boxes on the same page, each based off of a different parent table, using the same binding source for the child table (territoriesBindingSource).  All worked as advertised.  Thank you for a wonderful tutorial!!!

  • PingBack from http://alriyada-recovery.com/

Page 2 of 2 (24 items) 12