ADO.NET DataTable.Merge() method doesn't work as expected

Question

Let say Customer table has Address1 and Address2 columns. After creating typed dataset with VS 2008 wizard, I wrote the following code:

Dim t1 As MyDataSet.CustomerDataTable = New MyDataSet.CustomerDataTable
Dim r1 As MyDataSet.CustomerRow = t1.NewCustomerRow()
r1.Address1 = "a1"
t1.Rows.Add(r1)

Dim t2 As MyDataSet.CustomerDataTable = New MyDataSet.CustomerDataTable
Dim r2 As MyDataSet.CustomerRow = t2.NewCustomerRow()
r2.Address2 = "b1"
t2.Rows.Add(r2)

t1.Merge(t2, True)

Dim row As DataRow = t1.Rows(0)
Dim address2 As String = row("Address2")

I expected address2 be equal to "b1", but it actually contains DBNull. Why?

Answer 1

From the documentation on DataSet.Merge Method (DataTable):

When merging a new source DataSet into the target, any source rows with a DataRowState value of Unchanged, Modified, or Deleted are matched to target rows with the same primary key values. Source rows with a DataRowState value of Added are matched to new target rows with the same primary key values as the new source rows.

Did you establish a primary key on your DataTable? That makes a great deal of difference in how things behave.

How many rows do you have after the merge? Even if you do have a primary key, I expect that what you'll now have is 2 rows (unless you're using an identity and the 2 DataTables are on the same number), One with "a1" in Addres1 and another with "b1" in Address2. Look carefully at what it says about the behavior of added rows to understand why.