Data Comparison Tool

In the course of my job I have to validate numerous amounts of data. We have many processes that transfer data between databases to provide flexibility for our clients. In doing so we must support MS Access,MS SQL, and Oracle. Due to the complex nature of this task and the differences in how these database engines perform - it is very difficult to ensure they are all acting the same. So I have identified the problem - and developed a custom solution for it.

The outcome of this is a custom application that uses our existing DAL to compare two databases (regardless of platform). Some of the logic from this tool was based on an existing tool we had to compare single queries.

Setup:

1 Solution, 3 projects, and NUnit.

I wrote the main class as a DLL that can be used with a form, console, or integrated into an existing application. To test this - I used NUnit. This turned out to be the best method. With the console output available in NUnit and this being primarily a testing tool - I left the other options for later and just use NUnit to run my tests. I love it when I see green!!!

How it works

First it gets a list of tables from each database and compares them
Then it compares the rows counts for each table
If that passes, then it compares the row data. This is the tricky part. Since Access, SQL, and Oracle all handle identity inserts differently, it's hard to just sort on ID. To handle this I sort and attempt a compare on each column (in a data table in memory) for each table. I then walk through each data row and compare the contents. There are some fall back checks to handle other differences such as how numbers and bits are handled between the platforms.

In the end - I can move data from one platform to another, perform the same functions in each database, and then compare the databases very quickly with high confidence.

For a database with 300 plus tables I can run a complete test in under a minute with the databases in the same office.

Time Saved:

Before App - 30 minutes per test with less than 50 percent coverage
With App - 1 to 2 minutes per test with 100 percent coverage.

Horraaayyyy for automation!!!!