Easy LINQ To SQL Based Master-Detail Editor C#

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 detailed 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 meaningful 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 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 onto 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, 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 Explorer

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 are 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 the 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 to 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 have 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).

Implementation

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 statements at the top of the form class address what we need to run the form.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Linq;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using L2S_NorthwindBase;
namespace YourNamespace
{
    public class YourForm: Form
    {
        // Your form code goes here
        // Example method
        private void SomeMethod()
        {
            // Code for your method
        }
    }
}

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.

using System;
using System.Windows.Forms;
using L2S_NorthwindBase;
namespace LinqMasterDetail
{
    public partial class frmMasterDetail : Form
    {
        // Declare a new DataContext
        L2SNWNDDataContext dc;
        public frmMasterDetail()
        {
            InitializeComponent();
            // Initialize the DataContext
            dc = new L2SNWNDDataContext();
        }
        // Add your methods and event handlers here
        // Example method
        private void SomeMethod()
        {
            // Code for your method
        }
    }
}

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

private void Form1_Load(object sender, EventArgs e)
{
    // Create a new data context
    dc = new L2SNWNDDataContext();
    // Set the binding source data source to the full order table
    this.orderBindingSource.DataSource = dc.GetTable<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. When 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>
/// Implement save; will save any changes to the master or details
/// for the current record
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void orderBindingNavigatorSaveItem_Click(object sender, EventArgs e)
{
    try
    {
        this.Validate();
        orderBindingSource.EndEdit();
        dc.SubmitChanges();
        MessageBox.Show("Changes saved, click OK to continue", "Save");
    }
    catch (Exception ex)
    {
        MessageBox.Show("Error saving changes: " + ex.Message, "Error Saving");
    }
}

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>
/// Dispose of the form, terminate the application
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button1_Click(object sender, EventArgs e)
{
    this.Dispose();
}

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.