Easy LINQ to SQL Based Master-Detail Editor in Visual Basic
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.
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
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 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
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.
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).
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:
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
''' On form load,
instance the data context
''' and set the
binding source data source
''' property to the
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
= dc.GetTable(Of Order)()
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.
''' Save any current
changes to the database
Private Sub OrderBindingNavigatorSaveItem_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs)
saved, click OK to continue", "Save")
Catch ex As Exception
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
''' Terminate the
Private Sub Button1_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles Button1.Click
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.
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.