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

    Does the sample provided with this post work for you? Check the update statements and make sure you are calling EndEdit on the binding sources before attempting to save.

    -B

  • Hi Beth,

    Thanks in advance! 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.

    Apologies for insisting on this question.

    Warm regards,

    G

  • Hi Gonzalo,

    If you want the defaults to appear on the objects when you add them to the collections you can do this in a variety of ways. You can take a look at the OnCreated partial method to set values when the object is created. But since LINQ to SQL classes are just plain CLR classes you could also just create a non-default constructor and pass it any values you want:

    Sub New(ByVal orderDate As Date)

       Me.New()

       'Set default values

       Me._OrderDate = orderDate

       Me._CustomerID = 1

    End Sub

    Then on your form you could handle the BindingSource.AddingNew event:

    Private Sub OrderBindingSource_AddingNew(ByVal sender As Object, ByVal e As System.ComponentModel.AddingNewEventArgs) Handles OrderBindingSource.AddingNew

       Dim o As New Order(Date.Today)

       e.NewObject = o

    End Sub

    HTH,

    -B

  • Thanks a lot Beth! You are a VB angel.

    TH,

    G

  • 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'l

  • 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'l

  • Thanks Beth, Being quite new to Linq I have been grateful for your videos and other help in getting my master details forms working properly.  However there is one scenario below in which I am still struggling to get my data to update efficiently.

    I have a form in which the controls on the top half are bound to fields in a table (Vehicles) using LINQ, and the bottom half shows a datagrid with rows from a related table (Vehicle Status).

    A few of the controls in the top half display data from a view which is bound to the Vehicles, and which only shows the most recent records from Vehicle Status - giving information about the current (most recent) status.

    When the vehicle status changes I successfully insert a new row in the vehicle status table, using the sequence: bindingsource.endedit, BindingSource.Add, datacontext.submitchanges, and the new record is saved as the most recent item in the status table.  The datagridview refreshes and shows the new data row at the top as expected.

    However the controls in the upper portion of the form which are bound to the 'latest status' view do not refresh to show the new latest status data.

    I am now calling BindingSource.ResetBindings(False) on the vehicles table and the status table as well as calling datacontext.Refresh(Data.Linq.RefreshMode.OverwriteCurrentValues, VehicleBindingSource), all to no avail.  Even requerying the data from the database does not refresh the controls on the form.  The only way I can refresh this data is by closing and re-loading the form - not the most elegant solution.

    This is now a large application so posting code will be a major operation, however any ideas would be very much appreciated.

    Regards,

    Pete

  • Hi Beth

    Your articles are Great !!!

    Quick Question

    How do delete an individual row from the orderdetails datagridview ?

    Also how can I prevent the user from entering alpha data in a numeric field in the datagridview ?

  • Hi Kim,

    You call the OrderDetailBindingSource.Remove method to delete the current row. The DataGridView will also delete a row if you select the row header on the left and then press the delete key.

    Check out this topic (and related resources at the bottom) for info on formatting data in the DataGridView: http://msdn.microsoft.com/en-us/library/hezscd0d.aspx

    HTH,

    -B

  • Beth, thanks for all your efforts. I'm a big fan.

    I've hesitated bothering you with this recurring problem but it's vexed me no matter how many times I've followed this tutorial either here or in the HDI vid version.

    Most of the time, though not all, the following IndexOutOfRangeException is rasied when I close the form:

    "Index 0 does not have a value at System.Windows.Forms.CurrencyManager.get_Item(Int32 index) at

    System.Windows.Forms.DataGridView.DataGridViewDataConnection.GetError(Int32rowIndex)."

    I'm using VB 2008 Professional on a 64-bit unit, with a target platform of x86. Googling around has failed to help. Perhaps you can point me in the right direction? Please.

    Best wishes,

    Jim

  • Followup: I've managed to stop the error by  adding this to the FromClosing event:

    Me.OrderDetailsDataGridView.Refresh()

    I still have no clue as what initially caused the aforementioned error, however. Oh, well. It's working now, anyway.

    Take care,

    Jim

  • Okay, now I'm being a nudge, but ...

    Can someone please clear this up for me?: I was given to understand that the LinqToSql class dbml is for one to one relationships only. If one wants to work with one to many relationships,  SqlMetal generated class files is the way to go, so the advice goes.

    What am I not understanding properly? Orders to Order Details, as we have in this tutorial, is a one to many relationship, is it not?

  • Hi JMSteele,

    You can definately represent one-to-many relationships with LINQ to SQL. LINQ to SQL classes are representations of the tables in your database and so the MAPPING is one class = one table. These classes use the database relations to create the one-to-many associations.

    However it is very common in non-trivial applications for the need to map your calsses to multiple underlying tables in the database and that is something that Entity Framework does (which will be released as part of Visual Studio SP1 this summer).

    HTH,

    -Beth

  • Much thanks for the clear response, Beth. Now it makes sense.

  • Hi

    First sry for my bad English. You'r tutorials are very good and i learn so much so tnx a lot. I have just one question. How is possible "price" to be in "product" table and when you choose a product you get price to for this product . Then you just put a Quantity and you get total automatically :).

    Tnx for any help and ty for all your tutorials they are best.

    Bye

Page 3 of 5 (68 items) 12345