Merge two datatables within the same dataset

Posted on 2005-05-11
Last Modified: 2008-03-17

I have created a procedure that returns a dataset containing three datatables. The procedure is as follows:

Public Function getAuthorTitleDataset() As DataSet
            Dim ds As New DataSet("AuthorWithTitles")
            Dim daAuthor As SqlDataAdapter
            Dim daTitleAuthor As SqlDataAdapter
            Dim daTitle As SqlDataAdapter
            Dim fkAuthor As ForeignKeyConstraint
            Dim fkTitle As ForeignKeyConstraint

                cnn = New SqlConnection(connString)

                daAuthor = New SqlDataAdapter("Select au_id, au_lname, au_fname from authors", cnn)
                daAuthor.Fill(ds, "Author")

                daTitleAuthor = New SqlDataAdapter("Select au_id, title_id from titleauthor", cnn)
                daTitleAuthor.Fill(ds, "TitleAuthor")

                daTitle = New SqlDataAdapter("Select title_id, title from titles", cnn)
                daTitle.Fill(ds, "Titles")

                ds.Tables("Author").Columns("au_id").Unique = True
                ds.Tables("Author").Columns("au_id").AllowDBNull = False
                ds.Tables("Author").PrimaryKey = New DataColumn() {ds.Tables("Author").Columns("au_id")}

                ds.Tables("Titles").Columns("title_id").Unique = True
                ds.Tables("Titles").Columns("title_id").AllowDBNull = False
                ds.Tables("Titles").PrimaryKey = New DataColumn() {ds.Tables("Titles").Columns("title_id")}

                fkAuthor = New ForeignKeyConstraint("authorTitleAuthor", ds.Tables("Author").Columns("au_id"), ds.Tables("TitleAuthor").Columns("au_id"))

                fkTitle = New ForeignKeyConstraint("titleTitleAuthor", ds.Tables("Titles").Columns("title_id"), ds.Tables("TitleAuthor").Columns("title_id"))

                Return ds
            Catch ex As Exception
                getError = ex.ToString()
            End Try
        End Function
Now I have two data grids showing data from the two tables. The procedure is as follows.
Dim ds As New DataSet
        ds = objDataManager.getAuthorTitleDataset()
        With Me
            .dgAuthor.DataSource = ds.Tables("Author").DefaultView
            .dgtitle.DataSource = ds.Tables("Titles").DefaultView
        End With

I want to have a third data grid that shows the data after merging the two datatables.
Also when I update any column and click on a button named "cmdUpdate" the change should be reflected in the database as well as the respective datagrid.
Please help
Question by:fromapu
    LVL 28

    Expert Comment

    merge is using the ImportRow method of a Datatable obj this way:

    Dim oDt1 as DataTable = oDataSet.Tables("DT1")
    Dim oDt2 as DataTable = oDataSet.Tables("DT2")
    Dim i as integer

                  For i = 0 To oDt1.Rows.Count-1
    LVL 35

    Expert Comment

    ''merge two datasets using Merge method
    ds1.Merge(ds2, False, MissingSchemaAction.Ignore)
    ''bind Datagrid to a dataset
    LVL 35

    Expert Comment

    LVL 41

    Expert Comment

    Hang on a second....  what do you mean my "merge"?

    I see from your example that you created a set of foreign key constraints... does that mean that you're trying to do an SQL-style JOIN operations between the two tables?   (Produce a 3rd table that is the result of a join between the two existing tables?)

    If so, then the best way to do this would be to perform the join at the database level (not in your code).  That means creating a command object with the appropriate SQL JOIN syntax and then using that to populate a 3rd table.

    Another way to do with (but it's a bit overboard) is to perform the JOIN operation in your code.   I've got a tutorial and some sample code on how to do just that at


    Author Comment

    Hi graye

    The solution given by you is an ideal one. But the thing is suppose I have a datagrid that shows the author first  name, last name , titles. I update the last name in one row, the titles in another. Then I want to update the details to the database. How do I do it
    LVL 41

    Expert Comment

    OK... I think I see what you're after

    You need a 3rd DataGrid that is dynamically linked to the other 2 DataGrids (you said that in your original post, but I guess I didn't get it).  You want to be able to perform an edit on either one of the "source" datagrids and have those changes shown in the merged (3rd) datagrid.

    If that's what you're trying to do... then the solution provided by iboutchkine and YZlat would probably work... to create a merged table (perhaps a 3rd table... to keep the original tables "clean").  Edits to the 2 source datagrids would be applied back to the source DataTables using the normal DataAdapter Update method (one update per source table).

    Another techinque to accomplish the same thing would be to "tack on" the related columns using DataColumn expressions...


    Author Comment


    But those solutions also do not let me to update the changes in both the data table at one go
    LVL 41

    Expert Comment


    I thought you wanted to be able to edit the 2 "source" datagrids and have the results appear in the 3rd datagrid.   Are you saying that you want to edit the 3rd datagrid (the merged one) and have those changes appear in the 2 source datagrids?

    Author Comment

    Hi graye,

    Now you got me. This is exactly what I expect... Actually this is a dummy example for the application we are about to build. It may contain many records. A user would see record after joining many tables.... He edits the data and the data is updated in the respective tables.
    Any solution would be of great help.
    LVL 41

    Accepted Solution

    That's acutally kinda difficult to do.... it's not a programming issue... it's a database issue.

    Let's forget about the programming aspect for a second.... pretend you've got a database where you create a view that displays the results of a series of joins on a set of base tables.   What you've got is a read-only "result set"... a display of values that came from many different sources.  If you make changes to this results set, you'd have to "walk the changes" back to the base tables by hand.

    There are a few situations where it can be automated...  If you've got only Inner Joins and both keys used in the join are also primary keys in there respective base tables.  If that's the case, then "walking the changes" wouldn't be too difficult... and can be easily automated.  In fact you could use the DataColumn Expressions concept I suggested and get the results you'd expect with little or no programming.

    If on the other hand, the join is not a 1:1 and the keys are not the primary keys, then there's not really much a solution other than to construct a pair of custom  SQL statement to process against the two base tables.   It's not too difficult actually, just a bit clumbsy.

    Let me know if the Expression concept is suitable for your situation

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    Article by: jpaulino
    XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String ( Literal, only instead of starting and ending with w…
    Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
    This video discusses moving either the default database or any database to a new volume.
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now