Editing Data from Two Tables in a Single DataGridView

Editing Data from Two Tables in a Single DataGridView

  • Comments 32

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:

entity1

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:

entity2

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.

entity3

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.

entity4

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.

entity5

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

entity6

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). entity7Then 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.)

entity8

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.

entity9

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!

Leave a Comment
  • Please add 8 and 2 and type the answer here:
  • Post
  • Hi Harry,

    Those are already available here http://msdn.microsoft.com/en-us/vbasic/bb466226.aspx#wpfdata

    You can also check www.windowsclient.net.

    HTH,

    -B

  • I believe this is exactly what I need to do.  I don't understand where to put the create procedure code shown at the top.  

    Also, is there anyway to do this with wizards?  I created a query on a data table but it wouldn't create update commands because I used join.  Is this procedure how to get around that?

  • Hi Beth

    I am a newbie and watching your amazing videos. I down load and install VB 2008 and SQL Server 2008 Express Edition with SP1 form Microsoft site. But I am Facing a problem i.e. when i save data, msgbox appears as "Saved" but it is not showing in database(mdf). I download your program WPFDataEntry_VB and run it but the same prob is there. Where am I wrong? Pls reply

  • Hi Brian,

    You need to put the stored procedure in your SQL database. You can just add a new data connection to it via the Server Explorer, expand the database node and then right click on the Stored Procedures and select "Add new Stored Procedure". Pase the code above into the editor window and then click Save.

    HTH,

    -B

  • Hi Rajesh,

    You're probably running into this issue: http://blogs.msdn.com/bethmassi/archive/2007/05/29/working-with-a-local-data-file-in-vs.aspx

    HTH,

    -B

  • Hi Beth,

    Thnx for your help. I are simply great!!!

  • Hi Beth:

     I am enjoying your How To Video Series here and it's just amazing how easy you make it appear. I do have a question for you though - I was watching your video on how to set up and use N-Tier in Visual Studio 2008.  My Question is this, will this work for the Express Editions as well?  Currently I am locked into Visual Studio 2003 until such time as my employer upgrades to 2008 which will be sometime soon.  So in the mean time I'm trying to get a heads up at home using the express edition but so far, it looks like I would need Visual Studio 2008 before I can create an N-Tier application using the methods you have described in your videos.  If this is the case, then I assume that I will need to hand code that process into my own classes.  Are my assumptions correct or have I missed something using the Express editions?    Love the videos though!

    Thanks

  • Hi Beth,

    First thanks for your excellent articles!

    First time I've commented but I've received lots of help from you without you knowing :-)

    This is a very common scenario, having the need to combine data from different database tables in a single DataGridView (particularly if you design your database properly)

    I have looked through books an online and found scant on it for .NET

    Of course Object Relation Mapping (ORM) is supposed to solve this problem and there are a few of those for different platforms.

    Questions:

    Would you describe the Entity Framework as ORM?

    Also in your insert query you did this

    -- Get back the customer ID

       SELECT @customerID = CustomerID

       FROM [dbo].[Customer]

       WHERE @@ROWCOUNT > 0 AND [CustomerID] = scope_identity()

    Is there any reason you didn't just insert the

    scope_identity()

    straight into the CustomerContactInfo table?

  • Hi Beth,

    I'm new in Linq and Entity Model.

    This article is very easy to learn and helpfull to me.

    Thank you !

    My best regards!

    Cgiovani.

  • This is great, thanks a lot!

    Btw. I got an error just below comment line:

    -- Get back the customer ID

    Solution is to add: (customerID, EMailAddress,Phone) just after INSERT INTO [dbo].[CustomerContactInfo]

  • I have a windows form with 3 tables on a grid :-

    Orders, OrderDetails, Products

    These are all from Northwind database using sql sever

    I can drill down from the orders table and it highlights the relevant order detail record as i select an order from the order grid

    However, for the life of me I cannot get the products grid to show the relevant product details against the orderdetails grid.

    If I select an order -> the relevant orderdetails are shown in the orderdetails grid

    however i want to see in the grid products the relevant product names

    Can anyone help?

  • I have a windows form with 3 tables on a grid :-

    Orders, OrderDetails, Products

    These are all from Northwind database using sql sever

    I can drill down from the orders table and it highlights the relevant order detail record as i select an order from the order grid

    However, for the life of me I cannot get the products grid to show the relevant product details against the orderdetails grid.

    If I select an order -> the relevant orderdetails are shown in the orderdetails grid

    however i want to see in the grid products the relevant product names

    Can anyone help?

    Using the Binding source as the products table and the foreign key from the orderdetails table FK_Order_Details_Order just messes up my grid with labels from the orderdetails grid and not the products grid

  • Hi beth thanks for all the support but also i would like to ask for ur assistance

    on how do i retrieve an image from a database that i have saved in sql serever 2008

    these are my codes

    Dim da As New SqlClient.SqlDataAdapter("SELECT * FROM outlet_user WHERE sysustatus='Active'", myconnection)

           dt.Clear()

           da.Fill(dt)

           da.Dispose()

           Dim i As Integer

           For i = 0 To dt.Rows.Count - 1

               If txtusername.Text.ToLower = dt.Rows(i).Item("sysuname").ToString.ToLower And txtpasswd.Text.ToLower = dt.Rows(i).Item("syspwd") And dt.Rows(i).Item("systype") = 1 Then

                   frmsending.pctstaff.Image = (dt.Rows(i).Item("uimage"))

                   frmsending.sysuid = (dt.Rows(i).Item("sysuid"))

                   frmsending.txtoid.Text = (dt.Rows(i).Item("oid"))

                   MessageBox.Show("You Logged In As Staff :" & dt.Rows(i).Item("ufname") & " " & dt.Rows(i).Item("ulname"), "Login Message")

                   frmsending.txtstaffidname.Text = dt.Rows(i).Item("sysuid").ToString.ToUpper & ":" & dt.Rows(i).Item("ufname").ToString.ToUpper & " " & dt.Rows(i).Item("ulname").ToString.ToUpper & ""

                   Me.Hide()

                   End If

  • mcupryk@shaw.ca

    I have two tables. tbl_profiles and tbl_photos.

    Now tbl_profiles has a pk that is in tbl_photos.

    this is the ProfileID of int type.

    tbl_profiles        tbl_photos

    ProfileID(PK)      PhotoID   (PK)

    .........               ProfileID (FK)

    -----------------------------------------------

    I want to create a entity data model with the two tables merged into one.

    With reference to the following article blogs.msdn.com/.../editing-data-from-two-tables-in-a-single-datagridview.aspx

    I am try to merge the table into one. When I compile. I get the following:

    Error 6 Error 3024: Problem in mapping fragments starting at line 234: Must specify mapping for all key properties (tbl_Profile.ProfileID, tbl_Profile.PhotoID) of the EntitySet tbl_Profile.

     C:\owner\wwwroot\OmegaLove\ProfileGenerator\ProfilesPhotos.edmx 235 105 ProfileGenerator

    When copy the fields from tbl_Photos should I copy ProfileID (FK) ?

    Any help would be appreciated.

  • Hi macupryk,

    You can achieve the same thing which you want without exactly following Beth's approach.

    This is the way to do it:

    Just create the entity data model of the two tables as usual. Then create a DTO Classes which basically consists of all of two table fields. Then create a collection object of this DTO class and populate it after fetching data from the two tables individually. Set this DTO collection object as the datasource for your DataGridView.

    In the similar way you can implement the edit, update and delete operations on this DataGridView.

Page 2 of 3 (32 items) 123