asked on

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

iboutchkine

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

YZlat

''merge two datasets using Merge method
ds1.Merge(ds2, False, MissingSchemaAction.Ignore)
''bind Datagrid to a dataset
DataGrid1.DataSource=ds1
DataGrid1.DataBind()

YZlat

http://www.dotnetbips.com/displayarticle.aspx?id=94

graye

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

fromapu

ASKER

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

graye

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

fromapu

ASKER

Hi,

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

graye

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?

fromapu

ASKER

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.

ASKER CERTIFIED SOLUTION

graye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial