Oooh I am so looking forward to the dataset merge and datatable merge functions in Whidbey.
In the meantime, I have some little functions that I use in my .net apps to do this. This topic came up in our user group meeting last night and I mentioned my work around and many people asked me to share the code with them. It’s not a big secret – the trick is two-fold. 1) Accept the fact that a datatable that is part of a dataset is superglued, stapled, chained for life to that dataset and cannot be added to any other dataset. Period. The only way to detach it is to kill it and once it is dead it can’t be married to another dataset anyway. 2) check out importrows!
So here is my routine, and it is overloaded.
Step 1: Send in the datset that the table is going into and the dattable to the function.
Step 2: Clone the datatable, this creates a NEW table with the same structure
Step 3: transfer the name to the new datatable clone
Step 4: iterate through the rows of source table and use ImportRow to suck a copy of them into the new table
Step 5: Return the new datatable
Public
Function MoveTabletoNewDS(ByRef DestDS As DataSet, ByRef SourceTBL As DataTable)Dim newTable As DataTable = SourceTBL.Clone
newTable.TableName = SourceTBL.TableName
Dim oRow As DataRow
For Each oRow In SourceTBL.Rows
newTable.ImportRow(oRow)
Next
DestDS.Tables.Add(newTable)
End Function
This overload is so that I can also work with strongly typed datasets. I want to pull a datatable out of one dataset and put it into another strongly typed dataset.
Public Function MoveTabletoNewDS(ByRef DestDS As DataSet, ByRef SourceTBL As DataTable, ByRef NewTable As Object)‘newtable has been created from a strongly typed object
newTable.TableName = SourceTBL.TableName
Dim oRow As DataRow
For Each oRow In SourceTBL.Rows
newTable.ImportRow(oRow)
Next
DestDS.Tables.Add(newTable)
End Function
Sign up for my newsletter so you don't miss my conference & Pluralsight course announcements!