Merging datasets with different schema using ASP.NET

The code for merging two dataset is as follows, it uses the merge method on the dataset (this can also be applied to datatable).

Sub BindGrid()
     Dim myConnection as New SqlConnection (strConn)

     Dim DS1 As DataSet
     Dim DS2 As DataSet
     Dim MyCommand As SqlDataAdapter

     MyCommand = new SqlDataAdapter("exec s_get_table1", MyConnection)
     DS1 = new DataSet()
     MyCommand.Fill(DS1, "MyTable")

     MyCommand = new SqlDataAdapter("exec s_get_table2", MyConnection)
     DS2 = new DataSet()
     MyCommand.Fill(DS2, "MyTable")

'Now this code works because the table name for both datasets are the same.
'Also the data type and column name for both tables are the same.

     DS1.merge(DS2 )

     MyDataGrid.DataSource=DS1
     MyDataGrid.DataBind()
End Sub
What if the schema of two tables are not the same?

You get an error, which is : <target>.destination and <source>.destination have conflicting properties: DataType property mismatch.

What is the workaround for this?

You need to temporarily remove the constraints from the dataset and then merge using an overloaded version of the dataset.merge method. Below is the code:

        'we can merge the mismatched columns
        DS2 .EnforceConstraints = False
        DS2.Merge(DS1 , True, MissingSchemaAction.Ignore)
        DS2 .EnforceConstraints = True

Here is an explaination of the overloaded merge method. We chose to ignore the missing schema action so the datatypes of the souce and destination column can be slightly different.