One-To-Many (Master-Detail) Forms with LINQ to SQL

One-To-Many (Master-Detail) Forms with LINQ to SQL

  • Comments 68

In previous posts this month I showed how to use LINQ to SQL classes with a couple different Combobox data binding scenarios. (You can read those articles here and here.) Today I'm going to show you how to create a one-to-many data entry form (and we'll use a couple Combobox lookup lists as well). I'll show you what you need to do to enable proper insert, update and deletes of the hierarchical data. I'll also show how we can specify that these operations happen via stored procedures.

The Database Model

For this example I'll be creating my own database and not using Northwind. This is because Northwind isn't a very typical database especially when it comes to referential integrity. I want to create tables that have non-nullable foreign keys as well as use a timestamp field for concurrency checking.

So here's the database diagram of what we'll be building off of:

I've also specified Update, Insert and Delete stored procedures for each of the tables. This is so we can lock down the database security a bit and disallow UPDATE, INSERT and DELETE SQL statements from executing against it. All we need to grant is SELECT and EXECUTE permissions. This is a typical configuration for databases because it helps prevent against malicious code executing on the database by stopping changes from happening outside the stored procs.

So to get started building my form, I'll start by adding a new item to my project called "LINQ to SQL Classes" which will open the O/R designer and allow me to drag tables in my database from the Server Explorer onto the model's design surface. I'll drag all of the four tables above and since the database is called OMS I'll name the model OMS.dbml. This will create our LINQ to SQL classes and infer the associations from the database relationships. I'll also drag all the stored procs onto the Methods pane.

Next we want to associate the stored procs with the update, insert and delete behaviors for each class. Select the class then in the properties window you will see three properties; Delete, Insert and Update and they are all set to "Use Runtime". Select the properties and you then can specify the procedures in the methods pane to use for each behavior. You can also simply right-click on the class and select "Configure Behavior". On this screen you can specify the behavior for all the classes by selecting the Customize radio button and then selecting the corresponding procedure shown in the method pane.

After you got all of these set up, save the model and this will generate all the LINQ to SQL classes plus the DataContext which is used to manage the connection and communication to our database.

Data Sources and Data Binding the Form

Next we need to get these into our Data Sources window so that we can quickly get our form designed. I showed how to do this before in the previous posts against Northwind. This time I want to create a master-detail form of Orders and related OrderDetails in a grid and I want to show the Customer and Product as lookup lists. Select Add New DataSource from the Data menu and select Object as the Data Source Type. Next, select the Order class and click Finish. This will populate your Data Sources window with the Order and also its related OrderDetails because of the association. We'll also want to add Customer then Product to our Data Sources window as well because we'll need those for our lookup lists.

When you inspect the properties of the classes in the Data Sources window you will see that the associated parent object is also visible along with the child collections. For instance, if you expand Order you will see the parent Customer as well as the child OrderDetails. This indicates the associated parent Customer object for that Order object. I'm going to want to display that information as a lookup list so change the drop control for Customer to "None" and change the CustomerID to a Combobox. I also do not want to display the Modified field so also set that to "None". Then I'll set the drop control of the Order to "Details".

Drag the Order onto the form to set up the controls as well as the BindingNavigator and BindingSource for the Order. Next drag the Customer from the Data Sources Window onto the top of the CustomerID Combobox to set up the CustomerDataSource for the list of items. In the properties for the Combobox set the ValueMember = CustomerID and the DisplayMember = LastName in order to finish setting up the lookup list for Customer.

Next drag the OrderDetails listed under Order onto the form to drop down a DataGridView. You will notice that this will also pull in the parent objects, Product and Order in this case. Just edit the columns and remove those as well as the Modified field. For this example, I'll still display but set the OrderDetailID and OrderID to ReadOnly since these will be filled in automatically for us after we save the data. We'll also need to change the ProductID column type to a DataGridViewComboBoxColumn and then select the Product as the DataSource by selecting Other Data Sources --> Project Data Sources --> Product. This process will create a ProductBindingSource in the component tray.

Also we will need to specify the DisplayMember = Name and ValueMember = ProductID on the column here.

Loading the LINQ to SQL Classes with Data

Now that we have our form designed and the data binding all set up we're ready to create our objects and fill them with data. Unlike when we are using DataSets on our forms, the Form Designer will not generate any loading or saving code for us when using LINQ to SQL classes. But the code we need to write is very straightforward and we can write LINQ queries to limit our result sets. For this simple example I will select all Orders and also all of the Customers and Products into our lookup lists but keep in mind this may be a bad design if there are hundreds of rows in your database. In that situation it's better to write a search form.

So in the Form's Load event handler we need to set up the BindingSource's DataSources with data returned from our tables. First we'll fill the form with all the orders from the Orders table.

Public Class Form1

    Dim db As New OMSDataContext

    Private Sub Form1_Load() Handles MyBase.Load

        Me.OrderBindingSource.DataSource = db.Orders

Next we want to populate the Customers list. We can get cute and can specify a LINQ query here in order to select the customer names in "LastName, FirstName" format.

        Me.CustomerBindingSource.DataSource = From c In db.Customers _
                                              Let LastName = c.LastName & ", " & c.FirstName _
                                              Select LastName, c.CustomerID _
                                              Order By LastName

Finally we want to select our list of Products. Here's a trick that will place an "empty" product at the top of the list so that it can indicate to the user to select a value. We can add validation later to check that the selection has been made by checking that the ProductID > 0.

        Dim emptyProduct As Product() = _
                {New Product With {.Name = "<Select a product>", .ProductID = 0}}

        Me.ProductBindingSource.DataSource = (From Empty In emptyProduct).Union( _
                                              From Product In db.Products _
                                              Order By Product.Name)

    End Sub

You might be wondering why we're not explicitly setting the OrderDetailBindingSource's DataSource. This is because the OrderDetails are loaded from the database automatically only when we access the OrderDetails collection on the Order object. This happens when the OrderBindingSource moves position and the OrderDetailsBindingSource needs to display the OrderDetail objects for the Order. If you want to see the T-SQL statements being run just put a call to db.Log = Console.Out in the Load to display the statements in the Debug Output window. Just make sure to remove it before building your release.

Saving Hierarchical Data

Next we need to add the save code by enabling the save button and handling the click event. In my previous post when we built a single-table entry form with a lookup list I showed how to do this:

    Private Sub OrderBindingNavigatorSaveItem_Click() _
        Handles OrderBindingNavigatorSaveItem.Click

        Me.Validate()
        Me.OrderBindingSource.EndEdit()
        Me.OrderDetailsBindingSource.EndEdit()

        Try
            db.SubmitChanges()

            MsgBox("Your data was saved.")

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

    End Sub

Okay so let's give this form a try. Run the form and make a change, and/or insert a new Order, click save, and you should see that everything worked out smoothly. The keys are properly populated on insert and the relationship works correctly. However if we try to delete an OrderDetail (child) row from our grid we get the following error:

System.InvalidOperationException: An attempt was made to remove a relationship between a Order and a OrderDetail. However, one of the relationship's foreign keys (OrderDetail.OrderID) cannot be set to null.

To fix this we need to indicate to the model that we want to delete the OrderDetail when it's OrderID is set to null. Unfortunately this cannot be done in the O/R designer so you have to open the model in an XML editor. Fortunately, once you change it the designer won't mess with it again unless you remove the class completely. Open the dbml file with the XML Editor (just right-click on it an select "Open with...") and locate the XML that describes the OrderDetail class. Notice the association under the OrderDetail table:

<Table Name="dbo.OrderDetail" Member="OrderDetails">
  <Type Name="OrderDetail">
    <Column Name="OrderDetailID" Type="System.Int32" DbType="Int NOT NULL IDENTITY" 
IsPrimaryKey="true" IsDbGenerated="true" CanBeNull="false" /> <Column Name="OrderID" Type="System.Int32" DbType="Int NOT NULL" CanBeNull="false" /> <Column Name="ProductID" Type="System.Int32" DbType="Int NOT NULL" CanBeNull="false" /> <Column Name="Quantity" Type="System.Int32" DbType="Int NOT NULL" CanBeNull="false" /> <Column Name="Price" Type="System.Decimal" DbType="Money" CanBeNull="true" /> <Column Name="Modified" Type="System.Data.Linq.Binary" DbType="rowversion NOT NULL"
CanBeNull="false" IsVersion="true" /> <Association Name="Order_OrderDetail" Member="Order" ThisKey="OrderID"
Type="Order" IsForeignKey="true"/>
<Association Name="Product_OrderDetail" Member="Product" ThisKey="ProductID"
Type="Product" IsForeignKey="true" /> </Type>

We need to add an attribute here called DeleteOnNull and set it to true in order to be able to delete a child row independently in the database when calling SubmitChanges(). Once we make this change we can now delete just a single OrderDetail from the grid and save normally:

<Association Name="Order_OrderDetail" Member="Order" ThisKey="OrderID" Type="Order" 
IsForeignKey="true" DeleteOnNull="true"/>

The other option to fix this issue is to modify the Delete Rule to "Cascade" on the relationship in the database. In that case the designer correctly infers this attribute on the association.

Okay let's run the form again and now when you try to delete an OrderDetail child from the grid and click save, it saves without error. But if you try to delete an entire order by clicking the delete button on the ToolStrip and then save we now get a database error:

System.Data.SqlClient.SqlException: The DELETE statement conflicted with the REFERENCE constraint "FK_OrderDetail_Orders". The conflict occurred in database "OMS", table "dbo.OrderDetail", column 'OrderID'.
The statement has been terminated.

This is because unlike DataSets, you can't specify in the model that when you delete a parent, it should cascade to the children automatically. So we need to write some code to do this. There's a variety of ways you can do this and one way I already showed in a previous post by adding code to the DataContext that works nicely if we are not using stored procs. The basic idea is that we need to tell the DataContext to delete the child objects anytime an Order is deleted. In this example, I chose to do this by calling DeleteOnSubmit before we call SubmitChanges. I added this code into the Click event handler for the Delete button on the ToolStrip of the form:

    Private Sub BindingNavigatorDeleteItem_Click() _
        Handles BindingNavigatorDeleteItem.Click

        If Me.OrderBindingSource.Position > -1 Then
'Grab a reference to the currently selected order
Dim order As Order = CType(Me.OrderBindingSource.Current, Order)
'Ensure that children are deleted when the parent is deleted For Each detail In order.OrderDetails db.OrderDetails.DeleteOnSubmit(detail) Next End If End Sub

Now run the form and try a variety of Update, Insert and Delete operations on the data and you will have a smooth ride. If you enable logging on the DataContext or run SQL profiler on the database you will see our stored procedures being called in the proper order.

Next time I'll show you how we can add simple validation to our LINQ to SQL classes by creating a base business class to inherit from and using the IDataErrorInfo interface along with the ErrorProvider.

UPDATE: I placed the code for this article (including the previous article code on this topic) into a Code Gallery project for you to play with.

Enjoy!

Leave a Comment
  • Please add 6 and 7 and type the answer here:
  • Post
  • Hi Chris,

    I don't mind at all. You may also want to check out Code Gallery for LINQ to SQL samples: http://code.msdn.microsoft.com/Project/ProjectDirectory.aspx?TagName=LINQ%20to%20SQL

    HTH,

    -B

  • Beth,

    Thank you for that link!

    Chris

  • Hi Beth

    I saw your sub

    Private Sub BindingNavigatorDeleteItem_Click() _

    But if I want to display a error message to the user and retrieve the info, how I can do that?

    Thanks you for your help

    James

  • Hi Beth,

    I'm not sure this is the right place to ask this, but since I've searched high and low, I'll give it go anyway.

    I'm trying to update a row in a datagrid, much like you describe above, which contains several dropdownlists. One of them has a foreignkey to my table 'tblCapacity' whit null values allowed. When I try to edit a row where a null value is currently in the capacityID attribute I get the message:''CapacityDropDownList' has a SelectedValue which is invalid because it does not exist in the list of items.

    Parameter name: value'

    I've tried (a mix from your code above and Scott Guthrie's in LINQ to SQL Part 9) to change the selectstatement of the linqdatasource I use for the dropdownlist like so:

       Protected Sub CapacityLinqDataSource_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.LinqDataSourceSelectEventArgs) Handles CapacityLinqDataSource.Selecting

           Dim campaignXL As New CampaignXLDataClassesDataContext

           Dim iNullable As Nullable(Of Integer)

           Dim emptyCapacity As tblCapacity() = _

                   {New tblCapacity With {.capacityCode = "<Select a capacity>", .capacityID = iNullable}}

           Dim capacity = (From Empty In emptyCapacity).Union( _

                                                 From tblCapacity In campaignXL.tblCapacities _

                                                 Order By tblCapacity.capacityCode)

           e.Result = capacity

       End Sub

    This doesn't work (when I edit the row it gives an error saying iNullable should have a value); when I assign '0' to iNullable it gives me the earlier error message (for a good reason because the null value is still not present in the linqdataset for the dropdownlist.

    It seems to work if I make a dynamicdata project out of my datamodel, so somehow there is a solutions for this problem. Hope you can help my, thanks in advance,

    Rikki

  • Great text! Very helpfull.

    Now I see DataSet is dead - the end of database concept. Welcome to business objects concept.

    Thats a pitty that O/R designer cannot set the association properties and one has to do it in xml. I think maybe service pack 1 for VS 2008...

  • Hi, been learning from you for a few weeks now, much respect.

    I get an error with your program when i do something i will guide below, after which everything fails (need to restart program).

    1: on a new orderdetail fill in only quantity and price, leave combobox as is.

    2: Try to save. You'll get a warning.

    3: Now highlight this orderdetail we worked on and delete..

    4: Now save again and you get an error about relation violation. After this you cant work with the database anymore.

    Can you Beth or anyone else tell how to fix this? I think this will happen with everyone.

    Thanks great tutes xx

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

  • In my last post we built the service and data access layer for our LINQ to SQL N-Tier application. In

  • Hi Beth. Great articles!!!

    I have a question, I read your articles about smart client with Linq. I have to implement a connected client with Linq. My problem is he following:

    Suppose I mapped with Linq tables Orders, OrderDetails and OrderDetailLines. An order has many OrderDetails and each OrderDetail has many detailLines. I created a formOrder where I Have a datacontext and I retrieve info about an order and I show a grid with orderDetails. All is databound. If I doubleclick on a detail row I show another form: OrderDetailForm (I pass to the form the rowDataboundItem) where I show OrderDetail and a grid with OrderDetailLines. I want to implement an UNDO feature, so if I modify an OrderDetailLine and then I press "Cancel Button" the orderdetailLine object is not modified. The same is for the orderdetail. I tried to implement IEditableObject but I have problems with relations properties (they return empty). I can't get "GetOriginalEntityState" methods because I must have the ability of edit a child object, then press ok to update it, the re-edit and press "Cancel" (and I want last updated values, not db values).

    I'ts a very frustrating problem from me...It's five days I try every kind of solutions...!

    The problem is that with databinding values are automatically updated. I could clone objects but I have a large number of business objects and implement a clone method would mean too many days.

    Any idea??

    Thank's in advance and sorry for the english...!

  • Please can you put all this new programming in to new video, as an add on the ‘Use DataSets in an N-Tier Application’ as I find it hard to follow.

  • Hi Beth,

    Awesome topics. Thanks for being such a soothing breeze in the "Sea of Linq". Would you suggest a way to handle Default Values With LINQ to SQL. I would like to be able to use my default values previously set on my SQL database with Linq when I do an insert to a table for data entry.

    Best regards,

    Gonzalo

  • The OMS_log.ldf file seems to be missing on the download.  I can't seem to attach OMS without it.

    Thanks

    Your work is great.

  • Okay, I built the OMS from scratch in SQLExpress. I just installed SQL 2008 Feb CTP, moved it, and upgraded to VS2008 SP1 (beta) and Framework 3.5 SP1  I'm not getting the OrderDetails collection represented within the "Order" Class in Data Sources window after I drag the Order table onto it. However I am getting "Orders" (not "Order") under Customer.

    BTW the table is "Orders" in the database and magically becomes "Order" in the OMS.dbml.  Why, and could this be an issue. I then changed the name to "Orders", rebuilding and still getting no representation of the OrderDetails collection.

  • Hi Beth,

    I am building a small application with Master Detail forms. iave the DB design with necessary Primary Keys and FK's. I have combo boxes on some columns in the Detail forms. I am unablke to update or save new data from the DEtail forms.

    Can you Please advise.

  • Hi Burton,

    When you attempt to attach the mdf file just remove the LDF reference and click OK. SQL-Server will rebuild it. You can also run the sample as it is packaged as a user instance.

    I'm not sure of the SP1 beta behavior but in the released version as long as you have referential integrity set up on your tables and you drag both of them onto the LINQ to SQL O/R designer, it will infer the relationships. The designer will rename your classes to singular but the child collections on the class are plural properties. This is just the default, you can change it.

    HTH,

    -B

Page 2 of 5 (68 items) 12345