• Live Help

• Components

  Component Categories

  Component Types

  • .NET Components

  Component Environments

  Component Publishers

• Applications

  Application Categories

  Application Types

  Application Publishers

• Add-ins

  Add-in Categories

  Add-in Types

  Add-in Publishers

• Cloud Services

  If you have a Cloud Service / API relevant to our global audience of Application Developers and IT Professionals, then get in touch. Every day, customers are searching our web site for software solutions to solve specific IT problems.

  Join Our Program and start working with us today!

• Brands

  Bestselling Brands

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 GateSQL Comparison SDK 12AssembliesSQL Data Compare Synthesia unlock key generator.

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:

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.

Step 5

Set the database server name and database and register. Kendrick lamar good kid maad city download.

Step 6

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

Step 7

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

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.

Step 9

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

Final Step

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

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.