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 1 and 2 and type the answer here:
  • Post
  • How do I fetch a default value from the table and assign it to the blank field of a new record for insert in UI after I click Insert New please?

    Alternatively, can I hard code a default value in the insert new row in the UI so that the user can either accept it or change it before inserting it in the database?

    Thank you, Beth.

  • Pls Beth Massi,

    Can You be more specific on how to create the BaseBusiness class in  LINQ To SQL NTier Application

  • hi, i am using this idea "Master/detail" and i am trying to cancel the adding on the child table before submitting the data to the database, when the client do this step it generates error " Cannot remove an entity that has not been attached" and when i try to attach the entity when i create the child, if the number of children is 1 it works else it generate an error " Cannot add an entity with a key that is already in use ", do you have any idea about this problem please?

  • Hi Beth, I have a question for you.. how I can connect one master table to a 2 child tables, so my id from the master table need to be link to both child tables...

    example:

    master table     child table 1      child table 2

    Id               detail_id          product_id

    name             id                 id

    etc              cost               description

  • Hi,

    great tutorial.

    It would be nice if you can show the code for adding child items to the collection (i.e. Navigation property because I'm in Entity Framework).

  • hi this is prasad

    <a href=the-thoughtspot.blogspot.com rel="dofollow">blogspot</a>

  • Does Linq to SQL support ODBC Data provider with System DSN?

  • Hi Beth,

    Does the XML changes need to be considered in VS2010?

    Peyman

Page 5 of 5 (68 items) 12345