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 4 and 8 and type the answer here:
  • Post
  • PingBack from http://www.travel-hilarity.com/airline_travel/?p=1295

  • Thanks for this Post!

    I've done everything as you described, but when I run my application always the first entry in the territorieslist doesn't change the Combobox to the right value. the other entries work, but first one always shows the first entry in the Regionlist instaed of the one that belobgs to this entry.

    Maybe you have an Idea.

    Thanks Steven

  • Hi Steven,

    Did you make sure you set the ValueMember of the Combobox to the RegionID?

  • Two recent LINQ databinding with ComboBoxes posts from Beth Massi of the Visual Basic team. Creating

  • With WinForm on the way out & WPF the new kid in the block, using WPF would be wonderful

  • Hi KK,

    I'll put it on the list ;-). I wanted to start with Winforms because the visual designers are much better and it's easier to understand.

    In the meantime check out www.windowsclient.net

    Cheers,

    -B

  • In my previous posts here and here I showed how to use LINQ to SQL classes with a couple different Combobox

  • In the last few posts on LINQ to SQL I've showed how to set up an object model using the O/R designer

  • In the last few posts on LINQ to SQL I've showed how to set up an object model using the O/R designer

  • hi , my name is  saleem

    can you answer my qestions please

    1- i made a simple media player to play sound and video i just want to know how can i get all file names

    i selected and pressed enter

    i tried a startupnextinstance event

    and i got all file names but this was a very slow way and doesn't work well when you have alot of files (more than 100)

    i think i should use somthing exist in windows

    i think  somthing related to user32.dll or another file

    please can you tell me what the way is

    ( i want the same way that is used in windows media player to open many files)

    2-i can drag and drop files from explorer to my play list using dragdrop event but this doesn't work when i drag files from openfiledialog to my playlist

    my listbox doesn't feel dragdrop event

    3- how can i next the song using nextmediabutton

    while my application is minimized and i am working  on another program

    4- sorry if my English is not very good

    and thank you for every thing

  • In my previous posts on LINQ to SQL I showed how to build LINQ to SQL classes and set up the data binding

  • Hi Beth

    This code doesn't work

    I Have this

    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.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList

           Me.RegionIDComboBox.FormattingEnabled = True

           Me.RegionIDComboBox.Location = New System.Drawing.Point(131, 56)

           Me.RegionIDComboBox.Name = "RegionIDComboBox"

           Me.RegionIDComboBox.Size = New System.Drawing.Size(145, 21)

           Me.RegionIDComboBox.TabIndex = 6

           Me.RegionIDComboBox.ValueMember = "RegionID"

    but when I change value in cmbBox nothing happens

  • Hi Ama,

    You need to make sure you call EndEdit() on the bindingsource before you call SubmitChanges. Take a look at the code snippet above for the save button.

    HTH,

    -B

  • Hello Beth,

    I am developing an application in Visual Basic 6 with Farpoint Spread as an Active X Control which contains my table data. I began teaching myself code middle of last year to transform an excel application into a fully executable program. I used the Vlookup funtion in Excel in many aspects and am needing to do the same in VB6 but have not been able to find anything on this. If you could spare some of your time to offer me help on this, I would greatly appreciate it.

    Sincerely,

    Shawn Guillory

    President & Founder

    Guillory's Technical Consulting

    spgtechconsulting@yahoo.com

  • Hi Beth,

    For this code works properly, we must fill the Regions before the Territories , otherwise, like Steven said, the first entry in the territorieslist will be always  1 and will be in the state of updated.

    Example:

    territoriesBindingSource.DataSource =

       from t in db.Territories

       where t.RegionID == 3

       select t;

    regionBindingSource.DataSource = db.Regions;    

Page 1 of 2 (24 items) 12