Simple database table comparison using DataTable.Merge and DataTable.GetChanges

Question

We are converting our application from using Sql Server Express (let's call this version 3) to Sql Server Compact Edition (let's call this version 4). I am trying to make sure that upgrades from previous versions (versions 1 and 2) are the same in the new product (version 4) as the old (version 3). The upgrade code had to be rewritten for various reasons.

I'm creating a quick test application that will compare a version 3 Sql Server Express database and a version 4 Sql CE database. If the conversion logic is good, the databases should be exactly equivalent--same tables, same columns, same row values.

It would seem to me that there should be an easy way to do this using DataTable.Merge and DataTable.GetChanges, but I'm not having success. If rows exist in the version 3 database and not in the version 4 database, the GetChanges still returns null. If there are changes to the same rows (based on the primary key, which is a Guid), GetChanges still returns null.

string selectSql = String.Format("SELECT {0} FROM {1}", String.Join(", ", columns.ToArray()), tableName);

var expressAdapter = new SqlDataAdapter(selectSql, expressConnection);
DataTable expressTable = new DataTable();
expressAdapter.Fill(expressTable);
expressTable.PrimaryKey = new DataColumn[] { expressTable.Columns[tableName + "ID"] };

SqlCeConnection ceConnection = new SqlCeConnection(this.SqlServerCeConnectionString);
var ceAdapter = new SqlCeDataAdapter(selectSql, ceConnection);
DataTable ceTable = expressTable.Clone();
ceAdapter.Fill(ceTable);

expressTable.Merge(ceTable);
return expressTable.GetChanges(); // this is null even when the expressTable has rows that do not exist in ceTable.

Am I using Merge and GetChanges in a way too far removed from their intended use? If so, other options would be welcome.

Answer 1

I'm pretty sure Merge doesn't do what you want it to.

If you check the expressTable.Rows.Count before and after the merge, you'll see what I mean.

Of course there are a lot of obvious ways to do this and they require a lot of work on your part. If you can you might want to consider a product like Red Gate Data compare

http://www.red-gate.com/products/SQL_Data_Compare/index.htm

Answer 2

I tried to do something similar sometime ago, but eventually it didn't work, because AcceptChanges was called on rows added by Merge, so they were all in unmodified state, so GetChanges returned null...

Answer 3

I don't think you're using this staff in an intended way.

In case you need to compare you'd better think in terms of having two data sources and keeping them in 'sync'. In case you want/need to do this automatically (via some code) you might want to take a look at Microsoft Sync Framework which was specially developed for finding/resolving differences between data sources. In you case 'resolution' could be trivial (i.e. 'no action, report only'). In case this is a 'once-in-a-while' activity and you're OK with doing it manually, you can use some tool (RedGate Data Compare which was mentioned is quite OK for that, but I'm not 100% sure if it supports SQL Server CE).

Answer 4

Hi there,

You might want to give a try to Volpet's Table Diff:

http://www.volpet.com/

You can try a fully-functional copy for 30 days.

Please let me know for anything.