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!

Comments

• Anonymous
  October 16, 2008
  Hi Beth, This post right here is why I bookmarked your blog weeks ago. It's absolutely amazing how simple you make things look and how easy it is to follow what you do! I have a basic question here that (since I'm at work) I don't have the time to look for an answer to, but I hope you can answer it for me. I noticed in you VS2005 Forms over Data video series, you use varchar data types, here you use nvarchar data type. Is there a reason for this change? What's the difference between the two data types? Hope you're keeping well. Thanks for your time. -L

• Anonymous
  October 17, 2008
  Hi Bety, well, as you can see My name is Mauricio Muñoz, I was born in Colombia does a lot of a lot of time. (My English is really not English, BUT is some thing like that)(Please apoligize). Ok, I am Computer programmer sice 1983 beginning the Xt architecture. Anyway, little by little, I am trying to keepme update although my English is very limited. Some times I have specific ask but only in this place I found answers. THANKS A LOT. NOW, Can you give to me an sample to find two variables in one table? (sql) Sample : TableX fields: CodeX, DescripX, DateX and ... textbox1.text and text2.text finding for CodeX and DateX.   ?? Thanks Mauricio Muñoz evoluciondigital@yahoo.com

• Anonymous
  October 18, 2008
  It would be coll if you provide the code in C#. VB.NET is too much verbose and not at all structured. I just hate VB.NET!! Sorry, just my 2 cents...

• Anonymous
  October 18, 2008
  Joseph if you unable to understand VB you've got a serious problem...

• Anonymous
  October 21, 2008
  Hi Mauricio, The SQL syntax may vary depending on what database you are using but you probably want a statement like: SELECT CodeX, DescripX, DateX FROM TableX WHERE CodeX = @codeX, DateX = @dateX Then depending on how you are accessing your database (via TableAdapters or manually) you'll need to supply the command parameters. If you created a TableAdapter method (say FillByCodeAndDate) then you just call it and pass the textbox text to it. Here's a couple examples of creating a parameterized query: http://msdn.microsoft.com/en-us/vbasic/bb643828.aspx http://blogs.msdn.com/bethmassi/archive/2007/05/25/creating-a-parameterized-query.aspx See these tutorials for SQL server: http://www.asp.net/Learn/sql-videos/ For syntax, see SQL Server Books Online: http://msdn.microsoft.com/en-us/library/bb510741.aspx HTH, -B

• Anonymous
  November 02, 2008
  Hi Beth, I've got a question about datagrid. I'd like to refresh automatically a datagrid after a database update, made through a SQL requests. The informations on the net aren't very clear about this. How can I do this ? I hope you help me out. Thanks in advance. Cheers, Thierry

• Anonymous
  November 03, 2008
  Hi Beth, I am facing a problem.  I made a user control for numeric entry only, with decimal precision.  But when I bind this user control with table field for data entry.  It will accept alpha characters also. How can I use this numeric user control with data field. Bye Biju

• Anonymous
  November 07, 2008
  The comment has been removed

• Anonymous
  November 07, 2008
  Hi Biju, You probably need to set the formatting on the control. If it's a textbox then on the property sheet select (DataBindings) then (Advanced) to open the "Formatting and Advanced Binding" options. Then for the Format type select "Numeric". If it's a completely custom control you've build then you may want to handle the Parse and Format events on the Binding itself. HTH, -B

• Anonymous
  November 07, 2008
  Hi Logan, varchar and nvarchar are nearly identical. The difference is that nvarchar is used to store double-byte characters, which is used to store multilingual data in your database tables. So nvarchar takes up twice as much storage as varchar but allows for any language. Cheers, -B

• Anonymous
  December 01, 2008
  Hi Beth Thanks for the helpful tips. I'm still struggling to insert into related tables using LINQ to SQL. Your example shows a Customer object which includes the EmailAddress and Phone properties from the CustomerInfo table. How did you get these there? I can't seem to get the properties from the second table shown in my main object, which means I can't map them to my Insert stored procedure parameters. Any suggestions? Scott

• Anonymous
  December 02, 2008
  Hi Scott, I manually created the class and then mapped the stored procs to the corresponding behaviors. HTH, -B

• Anonymous
  December 19, 2008
  Hi Ms. Massi, I have read this article. I like and understood the Entity Splitting DataTables method, but I am having some problem with it. I keep on corrupting the database file. What I am doing wrong? Is there away to do the same thing, but using and Msaccess database file instead? If you e-mail me a reply please put in the subject database. Thank you for your time. Thank you, Gilmer (funguyjape@yahoo.com)

• Anonymous
  December 19, 2008
  Hi Gilmer, What do you mean corrupting? I'm not an expert on Access, it may require different syntax in the stored procedures. Here's a couple links to try: http://msdn.microsoft.com/en-us/library/bb149076.aspx http://www.devcity.net/Articles/18/msaccess_sp.aspx HTH, -B

• Anonymous
  February 01, 2009
  hi beth can use please get a video tutorial on binding a database to wpf

• Anonymous
  February 03, 2009
  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

• Anonymous
  February 04, 2009
  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?

• Anonymous
  February 05, 2009
  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

• Anonymous
  February 05, 2009
  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

• Anonymous
  February 05, 2009
  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

• Anonymous
  February 05, 2009
  Hi Beth, Thnx for your help. I are simply great!!!

• Anonymous
  February 19, 2009
  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

• Anonymous
  March 22, 2009
  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?

• Anonymous
  April 13, 2009
  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.

• Anonymous
  March 24, 2010
  The comment has been removed

• Anonymous
  March 16, 2011
  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?

• Anonymous
  March 16, 2011
  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

• Anonymous
  June 28, 2011
  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

• Anonymous
  September 06, 2011
  The comment has been removed

• Anonymous
  October 11, 2011
  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.

• Anonymous
  August 29, 2013
  Hello Friend , I want to bind nested Datagridview with parent and child relationship Please help me.................. Thanks!!!!

• Anonymous
  December 16, 2013
  Hie.I would like to know if it is possible to join two tables with a primary key when one of the tables have null values. So that values will be written against the ids which are already from another table

• Anonymous
  January 26, 2015
  This post right here is why I bookmarked your blog weeks ago. It's absolutely amazing how simple you make things look and how easy it is to follow what you do! I have a basic question here that (since I'm at work) I don't have the time to look for an answer to, but I hope you can answer it for me. I noticed in you VS2005 Forms over Data video series, you use varchar data types, here you use nvarchar data type. Is there a reason for this change? What's the difference between the two data types? Hope you're keeping well. Thanks for your time

• Anonymous
  April 01, 2015
  Hi Beth, I am using Entity data model for performing this task. The problem is that my tables has One-Many relationship. Perhaps because of this i am an getting error. So i would like to know that do this scenario work for One-Many relationships? Thanks in advance.