Editing Data from Two Tables in a Single DataGridView

I've had a lot of questions lately on how to display data from two separate tables in the database into a single DataGridView for editing. It sure would be nice if all our data was in a single table, but in reality most of the time it's not. Basically the problem is that we want one single table (entity) representation on the client even though we have two physical tables in the database holding the information... thus we need to "split" the data in our entity on the client side into two or more physical tables on the server. There are many ways you can do this depending on the relations in the database and also depending on what your client-side data source happens to be. I'll present a simple, common database table scenario and then attempt to explain how we can work with it using DataTables, LINQ to SQL classes, and then an Entity Data Model --- three different approaches to working with data in Visual Studio.

So let's take a very simple example. In my database I have two tables with a one-to-one relationship, Customer and CustomerContactInfo, one storing basic information about a customer and another that stores contact information:

Entity Splitting DataTables

If we're using DataSets in our application already we probably want to represent this as a single Customer client-side DataTable. To do this, we need to specify some stored procedures in our database for Select, Insert, Update, and Delete so that when ADO.NET retrieves our data or sends back the updated, inserted and deleted rows to the database, it calls our stored procedures that do the work of splitting the data into the proper physical tables. You can easily map DataTables in the DataSet designer to stored procedures. In our example these stored procedures are going to be very simple:

CREATE PROCEDURE [dbo].[GetCustomers] AS
SELECT  cust.CustomerID, 
        cust.Title, 
        cust.FirstName, 
        cust.MiddleName, 
        cust.LastName, 
        cust.Suffix, 
        cust.CompanyName, 
        cust.SalesPerson, 
        contact.EmailAddress, 
        contact.Phone
FROM    [dbo].[Customer] AS cust
JOIN    [dbo].[CustomerContactInfo] 
        AS contact ON cust.CustomerID = contact.CustomerID

CREATE PROCEDURE [dbo].[DeleteCustomer](
    @CustomerID [int]
) AS
BEGIN

    DELETE   [dbo].[CustomerContactInfo]
    WHERE    [CustomerID] = @CustomerID
    
    DELETE   [dbo].[Customer]
    WHERE    [CustomerID] = @CustomerID
    
END

CREATE PROCEDURE [dbo].[UpdateCustomer](
    @CustomerID [int],
    @Title [nvarchar](8),
    @FirstName [nvarchar](50),
    @MiddleName [nvarchar](50),
    @LastName [nvarchar](50),
    @Suffix [nvarchar](10),
    @CompanyName [nvarchar](128),
    @SalesPerson [nvarchar](256),
    @EmailAddress [nvarchar](50),
    @Phone [nvarchar](25)
) AS
BEGIN

    UPDATE  [dbo].[Customer]
    SET     [Title] = @Title,
            [FirstName] = @FirstName,
            [MiddleName] = @MiddleName,
            [LastName] = @LastName,
            [Suffix] = @Suffix,
            [CompanyName] = @CompanyName,
            [SalesPerson] = @SalesPerson
    WHERE   [CustomerID] = @CustomerID

    UPDATE  [dbo].[CustomerContactInfo]
    SET     [EmailAddress] = @EmailAddress,
            [Phone] = @Phone
    WHERE   [CustomerID] = @CustomerID
    
END

CREATE PROCEDURE [dbo].[InsertCustomer](
    @Title [nvarchar](8),
    @FirstName [nvarchar](50),
    @MiddleName [nvarchar](50),
    @LastName [nvarchar](50),
    @Suffix [nvarchar](10),
    @CompanyName [nvarchar](128),
    @SalesPerson [nvarchar](256),
    @EmailAddress [nvarchar](50),
    @Phone [nvarchar](25),
    @CustomerID int OUTPUT
) AS
BEGIN

    INSERT INTO [dbo].[Customer]
    VALUES (
        @Title,
        @FirstName,
        @MiddleName,
        @LastName,
        @Suffix,
        @CompanyName,
        @SalesPerson )
    
    -- Get back the customer ID 
    SELECT @customerID = CustomerID
    FROM [dbo].[Customer]
    WHERE @@ROWCOUNT > 0 AND [CustomerID] = scope_identity()

    INSERT INTO [dbo].[CustomerContactInfo]
    VALUES (
        @customerID,
        @EmailAddress,
        @Phone)
    
END

Now that we have that set up, open up the Data Source Window in Visual Studio and add a new data source, select the Database and then select just the GetCustomers stored procedure:

Click Finish and then open up the DataSet designer by double-clicking on the CustomerDataset.xsd in the Solution Explorer. You'll notice that the name of the DataTable is GetCustomers so change that to just "Customer". Next we need to configure the DataTable so that it will use our stored procedures so right click on the DataTable and select "Configure...". The TableAdapter Configuration Wizard should open and this will allow you to map your stored procedures to the Update, Insert and Delete commands. By default the fields will match up by name so you shouldn't have to do anything but drop down the combo boxes and select the right procedures for each action.

To test this out you can drag the Customer table from the Data Sources window on to a Windows Form as a DataGrid and immediately run it.

You will see the data from both tables displayed in the grid and they will be editable. As you update, insert and delete rows here, the DataSet will keep track of these changes for you. So when it is time to save the data via a call to the TableAdapterManager.UpdateAll method, the proper stored procedure will be called for each row that was modified.

Entity Splitting LINQ to SQL Classes

So with a Dataset, the DataTables do not have to map one-to-one with your database tables, but you need to specify how the data should be saved back to the database via stored procedures. This is also true if using LINQ to SQL classes. In that case though, you drag methods onto the method pane first and then map them to the class in the designer by right-clicking on the class and selecting "Configure Behavior...".

However with LINQ to SQL classes you can't map the Select behavior unfortunately. What you do instead is map the result of the GetCustomers method to a result type of Customer (the class we created on the design surface). Then when accessing the customers data you need to remember to call the GetCustomers method and not access the Customers directly in the DataContext, otherwise you'll get a SQLException that it cant find the additional columns when retrieving the data (in our case EmailAddress and Phone).

Private db As New MyDataContext

Private SubForm1_Load() Handles MyBase.Load     
    'This will properly populate a collection of our     
    ' Customer entities.    
    Me.CustomerBindingSource.DataSource = db.GetCustomers
End Sub

To save the data in a connected state like this we can simply call SubmitChanges on the DataContext. The LINQ to SQL DataContext tracks the state of each modification, deletion or insertion into the collection of Customers and will call the corresponding stored procedures that we configured.

Private Sub CustomerBindingNavigatorSaveItem_Click() _
   Handles CustomerBindingNavigatorSaveItem.Click
   Try
       db.SubmitChanges()

       MsgBox("saved")
   Catch ex As Exception
       MsgBox(ex.ToString)
   End Try
End Sub

(Note: In order to get get drag-drop data binding in Windows Forms to work with LINQ to SQL objects you need to select Data --> "Add New Data Source" on the main menu, then select Object (not database). Then select the Customer object and Finish. This will populate your Data Sources window so you can drag the grid onto a Windows form.)

Entity Splitting Using the Entity Data Model

This scenario becomes pretty straight-forward and automatic using the Entity Framework (EF) and you're not required to write any stored procedures to get it to work. This is because EF provides more complex mappings out of the box than LINQ to SQL or DataSets. And EF separates your database schema from your object model by providing a mapping layer. If you have Visual Studio 2008 Service Pack 1 you can create what's called an Entity Data Model which provides an ObjectContext, similar in theory to the LINQ to SQL DataContext above, but it provides many more mapping features.

When you add a new Entity Data Model to your project you can choose to generate it from the database or you can create an empty model. For this example I'll choose to generate it from the database and I'll select just the Customer and CustomerContactInfo tables and no stored procedures this time. (Note that I can still map stored procs to the update, insert and delete behaviors if I need to though.)

This looks just like our database model except you can see the navigation properties displayed that let us navigate from one entity to related ones. Notice the association is also shown as one-to-(none or)one. To set up the mapping so that it will automatically split the entity for us first we need to adjust the Customer by adding the EmailAddress and Phone. You can select the EmailAddress and Phone properties of the CustomerContactInfo and cut then paste them into the Customer. Then you can delete the CustomerContactInfo class from the design surface.

Now select the Customer and look at the Mapping Details window. Below the column mappings you will see <Add a Table or View>. Drop that down and select CustomerContactInfo and it will automatically map the columns in that table to the properties that we added.

Save the model and rebuild the project. Now you can add Customer as a data source for drag-drop Winforms data binding the same way you do for LINQ to SQL classes or your own objects. And the code for loading and saving of the Customers is similar to the LINQ to SQL code above except loading the Customers is much more intuitive. And the ObjectContext tracks changes for you and generates the proper insert, update, and delete statements automatically without you having to define stored procedures to do the splitting.

Private db As New MyDatabaseEntities

Private Sub Form1_Load() Handles MyBase.Load

    Me.CustomerBindingSource.DataSource = db.Customer
End Sub

Private Sub CustomerBindingNavigatorSaveItem_Click() _
    Handles CustomerBindingNavigatorSaveItem.Click

    Try
        db.SaveChanges()

        MsgBox("saved")
    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try
End Sub

This is just the tip of the iceberg using the Entity Data Model. I'd recommend reading the documentation in the MSDN library, visiting the forums and the FAQ, as well as visiting Julie Lerman's site (she's been living EF since the early Betas). I'm just learning EF myself but as you can see it allows you to model more complex data scenarios.

Enjoy!