Overview

I don’t know if you have ever used the Redgate Sql Data Compare Tool. It is a great tool that allows you to compare data in tables when you have a matching key to go on. I use it quite a bit to make sure configuration data is in sync across systems. In those cases, the data in the tables should match exactly, so the tool works really well.

The Problem

I have a situation in my current position where we have an engine that produces output to a data table that affects the manufacturing floor. We have a need to do regression testing whenever a change is made to this engine. In this case we will restore a backup of the production database in test and then run the same job that was run in production and compare the results. In most cases the data should match exactly. In a few cases we expect different data because of the engine change.

So the issue is: currently this is a manual process to check the data over 50 to 100 previously run jobs to verify the engine change didn’t break or affect anything else. I wanted to speed up this regression testing process so I looked into using the Redgate SQL Data Compare API so it could do the heavy lifting for me.

The Solution

When I use Redgate SQL Data Compare API, I will set the server to be the same, the database will be the same, the table will be the same. I will change the key so it is no longer the primary key of the table to a Serial Number column that stays the same across runs of the job in the engine.

Step 1

If you don’t already own the Redgate tools, that would be step one. My company provides me with a license so I have the full suite of tools including the folder “SQL Comparison SDK 12”. Inside this folder is the .NET dlls you will need to do the API calls.

Step 2

First, create a .NET project and set a reference to these dlls:

Most likely you will find them in this folder: C:\Program Files (x86)\Red Gate\SQL Comparison SDK 12\Assemblies\SQL Data Compare

Step 3

These examples are in C#, so they would be slightly different if you are using a different .NET language. Add these using statements:

  using RedGate.SQLCompare.Engine;
  using System.Data.SqlClient;
  using RedGate.SQLDataCompare.Engine;
  using RedGate.SQLDataCompare.Engine.ResultsStore;

Step 4

Declare the database and session variables. Just like SQL Data Compare, you have to setup and register your two database connections. In this case since they point to the same server and database it is pretty straight forward.

Database OldDB = new Database();
Database newDB = new Database();
ComparisonSession session = new ComparisonSession();

Step 5

Set the database server name and database and register.

//This allows you to use an existing sql connection string an pull the server name from it
SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder(YourConnectionStringHere);
ConnectionProperties sourceConnectionProperties = new ConnectionProperties(csb.DataSource, "DatabaseName");
                 
ConnectionProperties targetConnectionProperties = new ConnectionProperties(csb.DataSource, "DatabaseName");
//This Register call takes a number of seconds and is the slowest part of the compare
OldDB.RegisterForDataCompare(sourceConnectionProperties, Options.Default);
newDB.RegisterForDataCompare(targetConnectionProperties, Options.Default);

Step 6

Setup the table mapping and set the new key to match the rows in the table.

TableMappings mappings = new TableMappings();
TableMapping tableMapping = (TableMapping)mappings.Join(OldDB.Tables["[dbo].[YourTableNameHere]"], newDB.Tables["[dbo].[YourTableNameHere]"]);
//Remove the current default key, usually primary key of the table
tableMapping.MatchingMappings.Clear();
//set the column to map the data in my case it is SerialNumber              
tableMapping.MatchingMappings.Add(tableMapping.FieldMappings["SerialNumber"]);

Step 7

Remove any columns you don’t want to have a compare done on. Like primary keys, identity columns, date columns etc.

tableMapping.FieldMappings["YourTableNameID"].Include = false;
tableMapping.FieldMappings["HeaderID"].Include = false;
tableMapping.FieldMappings["WrittenDate"].Include = false;
tableMapping.RefreshMappingStatus();

Step 8

Set the WHERE clause so we know which sets of data in the table to compare against. In my case, each job run is given a unique HeaderID so I can use that when comparing the two sets of data in the same table.

//In my example there is a unique headerid for each job run.
tableMapping.Where = new WhereClause($"HeaderID = {oldHeaderID}", $"HeaderID = {newHeaderID}");

Step 9

We now need to compare the data and display the results.

session.CompareDatabases(OldDB, newDB, mappings);
TableDifference diff = session.TableDifferences["[dbo].[YourTableNameHere]"];
//My project is a simple Windows Form with a RichText box to display the output.
int cntSame = 0;
int cntDiff = 0;
foreach (Row row in diff.ResultsStore)
{
    int i = 0;
    string tmp = string.Empty;
    foreach (FieldPair field in diff.ResultsStore.Fields)
    {
        int field1 = field.OrdinalInResults1;
        int field2 = field.OrdinalInResults2;
        if (field1 != field2)
        {
            //get the values
            if (row.Type.ToString().ToLower() != "same")
            {
                object value1 = row.Values[field1];
                object value2 = row.Values[field2];
                if (value1 == null)
                    value1 = "NULL";
                if (value2 == null)
                    value2 = "NULL";
                tmp = row.FieldDifferent(i) ? "<>" : " == ";
                if (row.FieldDifferent(i))
                {
                    DebugToTextBoxRed($"{field.Field(false).Name}\t{value1.ToString()}\t{tmp}\t{value2.ToString()}");
                }
                else
                {
                    DebugToTextBox($"{field.Field(false).Name}\t{value1.ToString()}\t{tmp}\t{value2.ToString()}");
                }
            }
        }
        else
        {
            if (row.Type.ToString().ToLower() != "same")
            {
                DebugToTextBox("======================================================");
                //this is part of the custom index we are comparing on
                object value = row.Values[field1];
                DebugToTextBox($"Row {row.Index} type {row.Type.ToString()} *{field.Field(false).Name}\t{value.ToString()}");
                cntDiff++;
            }
            else
            {
                cntSame++;
            }
        }
        i++;
    } //foreach
} //foreach
DebugToTextBox($"Matching Row Count: {cntSame}");
if (cntDiff == 0)
{
    DebugToTextBox($"Diff Row Count: {cntDiff}");
}
else
{
    DebugToTextBoxRed($"Diff Row Count: {cntDiff}");
}
//Scroll to the bottom of the richTextBox to see the final results
richTextBox1.SelectionStart = richTextBox1.Text.Length;
richTextBox1.ScrollToCaret();
private void DebugToTextBox(string msg)
{
     richTextBox1.AppendText(msg);
     richTextBox1.AppendText(System.Environment.NewLine);
}
  private void DebugToTextBoxRed(string msg) 
  {
    //Print out red text 
    richTextBox1.SelectionStart = richTextBox1.TextLength; richTextBox1.SelectionLength = 0; 
    richTextBox1.SelectionColor = Color.Red; richTextBox1.AppendText(msg); 
    richTextBox1.AppendText(System.Environment.NewLine); richTextBox1.SelectionColor = richTextBox1.ForeColor; }

Final Step

Don’t forget to dispose of the database and session objects.

session.Dispose();
OldDB.Dispose();
newDB.Dispose();

Conclusion

In my actual use, I call this compare multiple times and just pass in different HeaderIDs. The registering of the databases takes the longest amount of time, so I do that first and then hold on to those objects and call the data compare just passing the database and session objects in. For me this process decreased the time it takes to do regression testing. It also does a better job since we know the old and new data matches exactly.