About 6 weeks ago I was tasked with implementing the most requested feature in one of our products at PragmaticWorks named BI Documenter. BI Documenter will take a snapshot of selected databases, ssis packages, analysis services databases and reporting services databases and allow you to create CHM (help docs) that show everything in your selected objects. We are trying to make BI Documenter a more everyday use tool by adding features like impact analysis, lineage and the most requested feature, snapshot compare.
When BI Documenter takes a snapshot it stores the snapshot data in database tables. It's stored in a very granular fashion. Some of the table names are:
Database
Tables
Views
Procs
Packages
DataFlowTasks
ConnectionManagers
We basically document almost everything about the db, package, etc that is selected. When the help document is created that data is retrieved from the tables via hibernator objects.
After thinking about how I would implement the compare I really had two choices; Run a bunch of sql statements with inner and outer joins or compare the objects. I decided to compare the objects. The code is already there to retrieve the database and hydrate the objects and I could take advantage of LINQ so it was a logical choice for a developer.
After deciding on this strategy I found that it would be a little tougher than I anticipated. Not because it was difficult to compare the objects but rather it was going to be time consuming to write compare code for each object (database, table, view, stored proc, etc). So I decided to use reflection to help my cause. The following code will look at two objects properties and compare the data and return a list of the properties that are not equal and their values.
private List<PropertyComparison> ComparePropertiesAndFindNonMatches<T>(T object1, T object1)
{
PropertyInfo[] properties = object1.GetType().GetProperties();
List<PropertyComparison> nonMatchedProperties = new List<PropertyComparison>();
foreach (PropertyInfo info in properties)
{
if (info.PropertyType.BaseType.Name == "Object" && info.PropertyType.Name != "String")
continue;
object[] atts = info.GetCustomAttributes(true);
bool hasExcludeAttribute = false;
foreach (object att in atts)
{
ExcludeFromCompare excludeAtt = att as ExcludeFromCompare;
if (excludeAtt != null)
{
hasExcludeAttribute = true;
break;
}
}
if (hasExcludeAttribute) continue;
object value1 = info.GetValue(object1, null);
object value2 = info.GetValue(object2, null);
if (value1 == null && value2 == null)
{
continue;
}
if (value1 == null || value2 == null)
{
nonMatchedProperties.Add(new PropertyComparison() { PropertyName = info.Name, Value1 = (value1 == null ? string.Empty : value1), Value2 = (value2 == null ? string.Empty : value2) });
continue;
}
if (!value1.Equals(value2))
{
nonMatchedProperties.Add(new PropertyComparison() { PropertyName = info.Name, Value1 = value1, Value1 = value2 });
}
}
return nonMatchedProperties;
}
I use a generic (<T>) method to make sure that I don't make the mistake of trying to compare two different types of objects. For instance if I tried use the following code, it wouldn't work because I'm trying to compare a database object to a table object.
SsDatabase db1 = null;
SsTable table1 = null;
List<PropertyComparison> nonMatchedProperties = ComparePropertiesAndFindNonMatches<SsDatabase>(db1, table1);
The line of code below was used to not compare object types unless it was a string. I supposed I could have just checked for Name == "Value" but I digress.
if (info.PropertyType.BaseType.Name == "Object" && info.PropertyType.Name != "String")
The PropertyComparison class is a really simple class to store the values of the property being compared.
public class PropertyComparison
{
public string PropertyName
{
get;
set;
}
public object Value1
{
get;
set;
}
public object Value2
{
get;
set;
}
}
The ExcludeFromCompare attribute was used to decorate a property if I didn't want the property compared. This is useful for properties that will change with each snapshot like the property DatabaseId which stores an identity column when hydrated.
[AttributeUsage(AttributeTargets.Property)]
public class ExcludeFromCompare : Attribute
{
}
And that's that. A pretty easy to use method to compare to objects properties of the same type.