Comparing data where primary key values do not match

EvilCube

Hi,

I have a requirement to compare two sets of data that share different primary key values. The data itself can either be located within the same database or another one that might be hosted by a different SQL Server instance. So far I have been able to specify a particular filter to use by using the Where property of the TableMapping class. The problem is that this applies the same SQL where clause to both ranges of data to compare, which is not the result I would like.

I'm not sure if I am missing an option or some other method/property on the API or whether this is just a limitation of the API at present. If someone could let me know if what I am trying is possible or not then that would be a great help.

Thanks,

--Chris

Brian Donahue

Hi Chris,

I hope that you can get this data comparison to work given the way that Data Compare Engine works by matching rows of data according to the primary key value. If you have a workaround, say, using other columns as keys besides the primary key, then the second part shouldn't present a challenge -- it is possible to specify two different where clauses for each database's table.

The code would look something like this:

WhereClause w=new WhereClause();
w.Clause1="fooID < 10";
w.Clause2="fooID > 10"
tableMapping.Where=w;

alexstyler

I'm not sure if this is what you need, but I was in a situation where my data may not have primary keys. So, I was matching it on either primary keys manually, or "all columns possible". In the case of all columns possible, you will get In1 and In2 results mainly instead of Different, as it can't detect that two rows are the same with different data if that data is used as a matching key. Some of the code in here is from my own classes (_databaseAdapter).

Note that this is foreach tablemapping in tablemapping, pretty basic stuff.

List<string> Keys = _databaseAdapter.GetPrimaryKeys(schemaName, tableName);

if (Keys.Count > 0)
   foreach (string key in Keys)
     tableMapping.MatchingMappings.Add(tableMapping.FieldMappings[key]);
else
   foreach (FieldMapping mapping in tableMapping.FieldMappings)
      if (mapping.ValidForMatching)
       tableMapping.MatchingMappings.Add(mapping);