Merge two datatables within the same dataset

Posted on 2005-05-11
Medium Priority
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
  • 4
  • 3
  • 2
  • +1
LVL 28

Expert Comment

ID: 13976853
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

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

Expert Comment

ID: 13977680
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 41

Expert Comment

ID: 13979703
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 http://home.hot.rr.com/graye/Articles/DataTableJoins.htm


Author Comment

ID: 13985053
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

ID: 13987246
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

ID: 13995045

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

Expert Comment

ID: 13996123

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

ID: 14001463
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

graye earned 1000 total points
ID: 14002846
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses
Course of the Month14 days, 19 hours left to enroll

840 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