Merge Datasets

[Brian P]

My ultimate goal is to take data from two different databases, (source
and target) and merge any changes between them into the target database.

To test the theory, I'm working on a small example, but I don't
understand why unchanged rows are marked as modified.

When running the code, the results are:

--------------------------------------------------
ID: 1 Value: Red Status: Modified
ID: 2 Value: Blue Status: Modified
ID: 3 Value: Green Status: Added
--------------------------------------------------

I don't understand why Red is marked as modified, since it is identical
in both the source and target data tables.

Any ideas?
--Brian



Here is my code:

static void MergeTest()
{

DataSet dsSource = new DataSet("Source");
DataTable dtSource = new DataTable("Colors");
dtSource.Columns.Add("ColorID", Type.GetType("System.Int32"));
dtSource.Columns.Add("ColorName", Type.GetType("System.String"));
dtSource.PrimaryKey = new DataColumn[] {dtSource.Columns["ColorID"]};
dtSource.Rows.Add(new object[] {1, "Red"});
dtSource.Rows.Add(new object[] {2, "Blue"});
dtSource.Rows.Add(new object[] {3, "Green"});
dtSource.AcceptChanges();

DataSet dsTarget = new DataSet("Target");
DataTable dtTarget = new DataTable("Colors");
dsTarget.Tables.Add(dtTarget);
dtTarget.Columns.Add("ColorID", Type.GetType("System.Int32"));
dtTarget.Columns.Add("ColorName", Type.GetType("System.String"));
dtTarget.PrimaryKey = new DataColumn[] {dtTarget.Columns["ColorID"]};
dtTarget.Rows.Add(new object[] {1, "Red"});
dtTarget.Rows.Add(new object[] {2, "Black"});
dtTarget.Rows.Add(new object[] {4, "Yellow"});
dtTarget.AcceptChanges();

foreach (DataRow row in dtSource.Rows)
{
dtTarget.LoadDataRow(row.ItemArray, false);
}

Console.WriteLine(dsTarget.HasChanges());

DataTable delta;
delta = dtTarget.GetChanges();

foreach (DataRow row in delta.Rows)
{
Console.WriteLine("ID: {0} Value: {1} Status: {2}",
row["ColorID"].ToString().PadRight(4),
row["ColorName"].ToString().PadRight(10),
row.RowState);
}

Console.WriteLine();
Console.WriteLine();
Console.WriteLine("Press Enter to Continue...");
Console.ReadLine();

}

 

[Brian P]

Okay, reading a bit more into the LoadDataRow it seems to be that it only tries
to find an existing row with the same key, and if so, updates that row,
otherwise, if no existing row is found, it adds the row.

Well, I was trying to be lazy and not have to compare each individual column to
check for changes, and was hoping ado.net would do that for me =)

So, now, I'm wonder if I can use a hash code to compare the two objects or if I
really have to compare each column from the source row to the target row.


Any thoughts?
--Brian

 

[Brian P]

Well, I guess it isn't too bad to compare each column...
Though, this looks a bit cryptic. Oh well, it looks like ADO.NET 2.0 has better
merge functionality...

--Brian

//compare datarows
foreach (DataColumn col in dtSource.Columns)
{
string name = col.ColumnName;

if (!row[name].Equals(targetRow[name]))
{
dtTarget.LoadDataRow(row.ItemArray, false);
}

}

 

[Cor Ligthert [MVP]]

Brian,

I am not aware that the merge function in this in 2.0 is changed to 1.x.

http://msdn2.microsoft.com/en-us/library/2wfzxak4

However just as far as I see this.

Cor

Well, I guess it isn't too bad to compare each column...
Though, this looks a bit cryptic. Oh well, it looks like ADO.NET 2.0 has
better merge functionality...

--Brian

//compare datarows
foreach (DataColumn col in dtSource.Columns)
{
string name = col.ColumnName;

if (!row[name].Equals(targetRow[name]))
{
dtTarget.LoadDataRow(row.ItemArray, false);
}

}

Okay, reading a bit more into the LoadDataRow it seems to be that it only
tries to find an existing row with the same key, and if so, updates that
row, otherwise, if no existing row is found, it adds the row.

Well, I was trying to be lazy and not have to compare each individual
column to check for changes, and was hoping ado.net would do that for me
=)

So, now, I'm wonder if I can use a hash code to compare the two objects
or if I really have to compare each column from the source row to the
target row.


Any thoughts?
--Brian