Merge two datatables within the same dataset

Hi,

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

            Try
                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"))
                ds.Tables("TitleAuthor").Constraints.Add(fkAuthor)

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

                Return ds
            Catch ex As Exception
                getError = ex.ToString()
            Finally
                daAuthor.Dispose()
                daTitleAuthor.Dispose()
                daTitle.Dispose()
            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
fromapuAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

iboutchkineCommented:
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
                    oDt2.ImportRow(oDt1.Rows(i))
              Next
               
0
YZlatCommented:
''merge two datasets using Merge method
ds1.Merge(ds2, False, MissingSchemaAction.Ignore)
''bind Datagrid to a dataset
DataGrid1.DataSource=ds1
DataGrid1.DataBind()
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

grayeCommented:
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

0
fromapuAuthor Commented:
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
0
grayeCommented:
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...
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatadatacolumnclassexpressiontopic.asp

 
0
fromapuAuthor Commented:
Hi,

But those solutions also do not let me to update the changes in both the data table at one go
0
grayeCommented:
Huh?

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?
0
fromapuAuthor Commented:
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.
0
grayeCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.