Easy LINQ to SQL Based Master-Detail Editor in Visual Basic

Introduction

This article describes a simple approach to creating a LINQ to SQL based master-detail view with the ability to save changes made to the data. With practically no coding involved, the application below will allow the user to edit the master record as well as the detail records.



Figure 1: Test Application in Use

Setup a LINQ to SQL Project

If you have a project that connects to a database that will be used in several other projects, rather than defining a LINQ to SQL interface for each of the projects, create a single LINQ to SQL data access project and then add that project to each of the projects that will interface with the same database using LINQ to SQL.

In order to create a separate LINQ to SQL project for use in multiple projects, create a new class library project:



Figure 2: Starting a Class Library Project

Provide the project with a meaning name and click the OK button. With the project up, delete the default class (class1.cs). Next thing up, select Project->Add New Item from the menu, when the dialog opens, select the data category and then select LINQ to SQL Classes from the template list; provide a suitable name and then click OK to open the DBML designer.



Figure 3: Adding LINQ to SQL Classes

With the DBML designer open, open the Server Explorer and then open or create an open a connection to the database. Open the connection and drag all or part of the tables onto the object relational designer surface (you need only add those that you are going to use in your application but if you are going to reuse this LINQ to SQL project in other projects, you might want to just grab everything.



Figure 4: Dragging tables to the object relational designer surface from the server explorer.

After dragging the tables onto the designer, you can repeat the process by opening up the stored procedures in the server explorer and dragging them into the right hand side of the server explorer.

There are a couple of things I do at this point that might be useful to you as well; these are not required but may save you some trouble downstream. The first thing that I do is to set each table's field update property to 'Never'; assuming that you know when and what you want to save, you can remove an impediment to your progress by setting this property to 'Never'. 

This can help out with saving data particularly if you are working with disconnected data and is useful if you don't want to use time stamps and the like to effect and update. The property is used to determine the frequency used in checking optimistic concurrency; if your application needs are different you may opt to set this property to 'Always' or 'When Changed'; the other two options.



Figure 5: Setting Update Check to Never

The second thing that I do is to cycle throw every field in every table to make sure that where the server data type is set to CHAR, that the member property type is set to string. If the type is set to CHAR, you will encounter problems when attempting to save the data. This generally occurs when the database contains a field with a VARCHAR (1) or CHAR (1).



Figure 6: Setting a Member Property Type to String

That is it; build it and save it and then you can reference it into any project hitting that database to reuse the LINQ to SQL classes without going through that bother again.

The Solution

The solution contains two projects, the one that we just created along with a second Windows forms project. The LINQ to SQL classes are contained in the first project cited as L2S_NorthwindBase. The Windows Forms application is called LinqMasterDetail. The first project contains the classes used to connect to, read and write to the Northwind database. The second project contains a single form displaying a master detail relationship that encompasses the Northwind Orders table along with the Order Details and Employees table. The orders table displays the bulk of the Order information while the order details and the selling employee's information is displayed in the lower left and right areas of the form respectively. 



Figure 7: The Solution Explorer

Setting up the Form

In order to build the master detail form, begin by adding the LINQ to SQL project to this solution (L2S_NorthwindBase in my example). Right click on the solution title in the solution explorer, select Add Existing, navigate to the project and add it. It should then appear in the solution explorer as it does in the preceding figure.

The next thing to take care of is to add a reference to the System.Data.Linq dll. Right click references in the Forms project and select Add Reference from the list. When the dialog opens, use the .NET tab to locate and add the required DLL.

After that, we need to add a project data source. To do that, from the menu, select Data->Add New Data Source. When the dialog opens, pick the Object option, then select the Add Reference button, pick the LINQ to SQL project and then select the table of interest. In my example, I picked the Orders table.

Now, with the Data Sources tab made visible, you can drag the Orders table from the Data Source onto the surface of the form. When the table is dropped onto the form, the IDE will also add a binding navigator the form and it will add a good bit of functionality to the application from the outset. It will not automatically implement the save button which will initially be disabled but we will address that in a moment.

As for the details, you can add them from under the Orders table in the Data Source. The icon for these details is different and you can easily identify those as they are the only ones that may be opened to examine their internals (they will have a 'plus' icon adjacent to their labels). You can drag these onto the surface of the form as well; if there is a one-to-many relationship, the IDE will drop a grid onto the form, else it will a single set of the appropriate controls (text boxes, date time pickers, etc.).

Once those items are dropped onto the form, you can open the properties for the grid and set the column headers to provide for a better UI and you can edit the labels and resize the controls to further clean up the UI.

At this point we are nearly ready to run the application. If you are running the demo code available with this download, you will need to update the connection property in the LINQ to SQL project to point to your local instance of the Northwind database; you can accomplish that by opening up the LINQ to SQL project property settings and configuring a new connection string. If you don't have the Northwind database you can still find it in the Microsoft downloads. 

http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en 

I have run this old database in SQL Server 2000, 2005, and 2008. It is still a pretty good example of a database and it is worth keeping around for testing purposes. Of course the same is true of the Pubs database and the newer Adventure Works database (which has a number of different versions available).

The Code

There is not a lot of code to contend with as the IDE is doing the bulk of the work for us. The first thing we will want to do is make sure that our using statements at the top of the form class address what we need to run the form:

Imports L2S_NorthwindBaseVB
Imports System.Data.Linq

Aside from the defaults, all that was added was the System.Data.Linq and L2S_NorthwindBase DLLs. Next up, we need to declare a public data context:

Public Class Form1

    ' Declare a data context 
    Dim dc As L2SNorthwindDbDataContext

With the data context declared, we can instance it in the form load event handler:

    ''' <summary>
    ''' On form load, instance the data context
    ''' and set the binding source data source
    ''' property to the orders table
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub Form1_Load(ByVal sender As Object, _
                           ByVal e As System.EventArgs) Handles Me.Load

        ' create new instance of the data context
        dc = New L2SNorthwindDbDataContext

        ' set the binding source's data source to the orders table
        Me.OrderBindingSource.DataSource = dc.GetTable(Of Order)()

    End Sub

Here we are also going to set the order binding source to the orders table. The code required to handle that is provided above. Next up, we are going to implement the save button on the binding navigator. By default it is disabled to select the binding navigator control's save button and set its enabled property to True. That done, double click the button to create an event handler and add the following code to it. The important parts are contained in the try; first validate the form, close the binding source edits, and then submit the changes through our data context instance. That is all there is to that.

     ''' <summary>
    ''' Save any current changes to the database
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub OrderBindingNavigatorSaveItem_Click(ByVal sender As 
    System.Object, ByVal e As System.EventArgs) 
    Handles OrderBindingNavigatorSaveItem.Click

        Try
            Me.ValidateChildren()
            OrderBindingSource.EndEdit()
            dc.SubmitChanges()
            MessageBox.Show("Changes saved, click OK to continue", "Save")
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error Saving")
        End Try

    End Sub

The last thing to do in the example is to implement that exit button. Not much there, since this is a single form application you can terminate it by either closing the form or calling Application.Exit():

     ''' <summary>
    ''' Terminate the application
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub Button1_Click(ByVal sender As System.Object, _
          ByVal e As System.EventArgs) Handles Button1.Click

        Application.Exit()

    End Sub

That is it; you can now run the application and edit fields in the master table as well as in the details. Clicking the save button will persist any changes made to the underlying database tables. 

Summary

This project demonstrates configuring LINQ to SQL classes, working with a data context, and building a master-detail view within the context of a Windows forms application. Further, the example demonstrates an approach to implementing the binding navigator's save button. Naturally there are other ways to do these things but this is likely the easiest way and it requires practically no coding to deliver a simple master-detail form with edit capabilities.

This is not a bulletproof approach to building a master detail form; it is just an easy one. One problem that you may encounter is the fact that the binding navigator's code does not handle an exception on the delete button that occurs if you are on an empty row when it is clicked. The solution to those problems would entail writing your own navigation code and dropping the binding navigator altogether, or you can create and implement your own event handlers for each of the binding navigator's controls.