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 7 and 8 and type the answer here:
  • Post
  • 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

  • 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

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

  • Joseph if you unable to understand VB you've got a serious problem...

  • 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

  • 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

  • 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

  • Hi Thierry,

    You just need to re-execute the Fill on the TableAdapter (same as in the Form's Load), but be careful how much data you are pulling down, this can affect scalability.

    HTH,

    -B

  • 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

  • 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

  • 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

  • Hi Scott,

    I manually created the class and then mapped the stored procs to the corresponding behaviors.

    HTH,

    -B

  • 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)

  • 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

  • hi beth

    can use please get a video tutorial on binding a database to wpf

Page 1 of 3 (32 items) 123