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 2 and 7 and type the answer here:
  • Post
  • I have the exact same problem that Fatih has.

    Any ideas how to solve it?

    I spend a few hours but found no way to overcome this one!

    I quote Fatih:

    ": 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."

  • I spent most of my day working on this issue - finding this article was a life saver.  Thank you for your expertise.  

    So do you forsee Microsoft adding the ability to specify the ondelete behavior through the user interface in the future?

  • To : pgraves or people who have the same problem

    I got this same problem for a similar project.. the way i resolve it.. is, i dispose the Datacontext object and recreate an instance of it..

    To : Beth

    Very good work...! i like every screencast you made!

    Thanks!

  • Hi Beth,

    I have a problem with de "Delete" of OrderDetails in the DeleteItem_Click. In my C# code (sorry, i'm not realy into VB.NET), when I click the "Delete" button in the navig. bar, I get the previous order when i check on the currentitem so :

    Order order = (Order)this.orderBindingSource.Current;

    doesn't give me the "deleted" order but the previous one. Then i checked you're VB.NET example and here i got the right (deleted one).

    I hade to change my C# Code as next to make it work:

    private void bindingNavigatorDeleteItem_Click(object sender, EventArgs e)

           {

               //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:

               try

               {

                   this.Cursor = Cursors.WaitCursor;

                   // Get the changed Objects

                   ChangeSet changeSet = _db.GetChangeSet();

                   if (changeSet.Deletes.Count > 0)

                   {

                       // Check the deleted ones

                       foreach (var deletedOrder in changeSet.Deletes)

                       {

                           // Check first if deleted object is of "Order" type

                           if (deletedOrder.GetType().Name == "Order")

                           {

                               Order currentOrder = (deletedOrder as Order);

                               if (currentOrder != null)

                               {

                                   // Mark related OrderDetail records for deletion.

                                   foreach (OrderDetail detail in currentOrder.OrderDetails)

                                   {

                                       _db.OrderDetails.DeleteOnSubmit(detail);

                                   }

                               }

                           }

                       }

                   }

               }

               catch (Exception ex)

               {

                   MessageBox.Show(ex.Message);

               }

               finally

               {

                   this.Cursor = Cursors.Default;

               }

           }

       }

    Any clue ?

    greetz,

    Emmanuel Nuyttens.

  • Hi Emmanuel ,

    In your situation you may need full control over the order things are deleted. You can solve this very easily. In the form designer select the OrderBindingNavigator and for the DeleteItem property set that to "(none)". Now we can write the delete manually on the Order and the OrderDetails in the Click event handler. It's the same code as before, but at the end we need to delete the parent order:

    Private Sub BindingNavigatorDeleteItem_Click() _

          Handles BindingNavigatorDeleteItem.Click

           If Me.OrderBindingSource.Position > -1 Then

               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

               '******

               Me.OrderBindingSource.Remove(order)

               '******

           End If

       End Sub

    HTH,

    -B

  • Hi Beth,

    Allright, this works smoothly, thx for the quick reply !

    Emmanuel.

  • Hi Beth,

    Thanks for such great post, it's really helped us to resolve relationship problems we're having in O/R designer.

    Still, we're stuck at the following problem:

    Suppose we have a two level dataGrid (we're using UltraGrid in our project), if while saving a newly added row at the second (child) level, an error occurs from the database (unique key constraint, for example,) and we delete the newly added row (that caused the trouble) and submit the changes again, it throws an error "Unabled to remove a relationship ..."

    However, we can reinitialize the database to get rid off with the error but doing so will detached the Grid from datacontext and any other changes will also be lost.

    It appears to be a bug in Change Tracker not been resolved in SP1 as well since, if we add and immediately delete a new row without calling submit changes in between, everything works fine.

    Thanks

  • Hi Peter,

    It sounds like the relationship isn't being picked up between parent/child on the client side. Can you make sure your bindingsources are configured properly? Does it work if you use two DataGridViews with bindingsources as I'm doing in this example?

    You may also want to ask your question to the LINQ to SQL forums: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=2035&SiteID=1

    HTH,

    -B

  • Hi Beth,

    Thanks for the reply. The problem has somewhat been caught.

    Actually, we're using parent.child.add(NewRow) procedure since using InsertOnSubmit makes UltraGrid lost one of many newly added rows while it still exists in the data context causing database to fire any  validation error infinitely. It seems the Add method creates a child entity only and does not attach it explicitly with data context till submit changes is called.

    Similarly, we're using parent.child.remove(SelectedRow) (since DeleteOnSubmit works only with attached records), which works fine, but if some error occurs while submitting a new record and we remove  that row and call submit changes, relationship error occured.

    Now the solution: I've added extra InsertOnSubmit statement (that would attach added row with data context) along with parent.child.add and have replaced parent.child.remove with DeleteOnSubmit and it's been working fine.

    Thanks for that link, it would definitely be helpful.

  • Hello Beth and thank you for your videos.

    Unfortunately, being visually impaired, they are

    a bit difficult to see.

    I am interested in creating a database application.

    My question to you is, do you know of any text that

    gives step by step instructions on how to use this new

    LINQ to add, edit, update, delete and search?

    I'm interested in the add, edit, update and delete

    being on the form with the search button being able

    to open another form with a grid. There, the user

    can select the record they want and have the first

    form populated with the information for editing.

    Thank you very very very very very much in advance.

    --George

  • Hi George,

    There are a lot of good articles on this blog as well as ThinqLinq.com. I'd also read through the walkthroughs in the MSDN Documentation: http://msdn.microsoft.com/en-us/library/bb399349.aspx

    HTH,

    -B

  • I have a form containing two DataGridView Controls.  DataGridView1 & DataGridView2.

    Northwind Database used.

    //Code

    BindingSource OrdBindingSource = new BindingSource(NorthwindDataSet, "Orders");

    //"OrderToOrderDetailsRelation" is Parent Child Relationship between Orders & OrderDetails Table filled in the Northwind DataSet.

    BindingSource OrdDetailsBindingSource = new BindingSource(OrdBindingSource, "OrderToOrderDetailsRelation");

    DataGridView1.DataSource = OrdBindingSource;

    DataGridView2.DataSource=OrdDetailsBindingSource;

    how do i sum Column Values of Expression Column (Total Value) of DataGridView2.

    I want to reflect the sum into a textbox whenever I select different rown in DataGridView1.

  • in addition I am using Untyped DataSet.

    I have learned from your VB.NET video on How Do I: Video Series.

    the Code Provided with the video is:

    Private Sub CategoriesBindingSource_CurrentChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles CategoriesBindingSource.CurrentChanged

           Dim row As CategoryProductDataSet.CategoriesRow

           row = CType(CType(Me.CategoriesBindingSource.Current, DataRowView).Row, CategoryProductDataSet.CategoriesRow)

           Dim total = Aggregate Products In Me.CategoryProductDataSet.Products _

                       Where Products.CategoryID = row.CategoryID AndAlso _

                       Products.Discontinued = False _

                       Into Sum(Products.UnitPrice * Products.UnitsInStock)

           Me.TextBox1.Text = Format(total, "c")

       End Sub

    I just Want to know, how do i implement this in my C# Code.

  • Hi Sunil,

    The Aggregate keyword is VB-specific and isn't supported in C#. Instead you have to call the .Sum extension method and pass it a lambda expression. This should help you get started:

    http://msdn.microsoft.com/en-us/vcsharp/aa336747.aspx

    HTH,

    -B

  • Hi Beth, I have a very tricky question for you.  I have a master-detail datagridview and I also want to have some text box related to master dataset and detail dataset to show current value in the datagridview.  However, when I run the program (VB.NET), it pops up a running error of "the column doesn't belong to the detail table."  If I just keep the common fields textbox for both dataset, it's ok.  Anything outside of the common fields but only with detail dataset, I will see the running error.  I wonder if theoretically I just can't bind the textbox to the detail dataset.  Thanks.  By the way, if possible, this is my email: ben.gu@tpwd.state.tx.us.

Page 4 of 5 (68 items) 12345