What options are there for writing Unit Tests on code that performs mass database updates

I have code in a very old desktop application that I am converting to C#. It runs a billing cycle and I have a test database that performs the update on around 5000 rows of data.

I can take a copy of the database, run it with the desktop application and run it with my code and perform a compare with SQL EXCEPT to make sure my new code is producing exactly the same results as the the desktop code.

This is fine for running manually, but it would be nice to somehow automate this, and indeed to add it to our development environment so it can be run regularly as part of our suite of unit tests, together with various tests using different settings.

I'm not familiar with this sort of testing - I usually just run with very limited amounts of data where the unit test creates the data, runs the test, then deletes the data again, or just uses NSubstitute to simulate data persistence.

We use a Microsoft SQL Server database 2008 and later.

So any thoughts, suggestions, articles, ideas would be welcome.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Duy PhamFreelance IT ConsultantCommented:
To be honest, I don't see much difference when preparing unit test data for mass data updates and single row updates. It depends much more on how your update functions are implemented, and whether your old desktop-based application can be automated to be able to run as Unit Tests.

I think there are few main points that you might be interested in:

How to construct data for Unit Tests

With 5000 rows (either in one or a few tables), creating data from scratch doesn't seem to be a good option. But since you are reinventing/refactoring your already-working functions, you might want to consider using a copy of the database --> clone data (and tables) before running Unit Tests --> run Unit Tests --> cleanup cloned data or rollback test database to original state.
  • If you don't need to validate DB Gateway, then test data can be put into a single MS Access Database file it can easily be cloned and doesn't require extra configuration for test database server. Otherwise, using a configuration test database server isn't a bad choice.
  • Clone the test data twice for running the old and new code

Note that how your old desktop-based application connects to database could limit the options.

How to automate your old desktop-based application?

If your old desktop-based application can be separated from its business logic layers (let's call it BizLayer), and the UI layer does nothing but simply call function from BizLayer with user-specified parameters, then the easiest option is wrap your BizLayer into a class library to be used in Unit Tests (or just reference old desktop-based application if your BizLayer doesn't do any nasty thing with interactive desktop).

Otherwise you could consider using an Windows Automation tool like AutoIt.

How to compare the output?

With large amount of data, you could combine cloning tables when preparing test data, and then easily use SQL EXCEPT for comparing output from old desktop-based application and your new code.

You can also export output test data into Excel files and them compare them your self-created comparison utility or use a 3rd tool like WinMerge.

Hope that my 2 cents could give you some initials.
Best Regards,
kaufmed   ( ⚆ _ ⚆ )Commented:
Generally speaking, you don't write unit tests to update a database. You only unit test the business logic of your application. You mock out the database part of your test. You don't update the database because the database is a volatile part of your system. Database down? Can't run tests. Junior developer hosed 3 of your stored procs? Can't run tests. Network connection to database lost? Can't run tests. These things are out of your control (again, generally speaking). You don't want them to affect your ability to run tests.

As I mentioned, you should be mocking out that volatile part of your system. You would do the same for other volatile parts of your system, such as web service calls and disk I/O operations. Your unit tests should be able to be run using just your code and the testing framework, and they should not have any external dependencies, especially ones that you cannot wholly control.
purplesoupProgrammerAuthor Commented:
käµfm³d - I do get what you are saying but it just seems incrdibly naive - the amount of time it is going to take to test every code path using mocked database calls will be enormous, whereas using the "correct" data from the desktop application we have the opportunity to perform millions of data point comparisons very easily through comparing data in the database.

Duy Pham - it isn't going to be possible to automate the desktop application but it will be possible to create "correct" copies of the data generated from the desktop application to compare with the new code. Do you have any more details on how automated tests can access data from a local database to perform comparisons with the main database?

I'll keep this question open for a bit longer to see if anyone else has something to add on the topic.
Get Blueprints for Increased Customer Retention

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

kaufmed   ( ⚆ _ ⚆ )Commented:
the amount of time it is going to take to test every code path using mocked database calls will be enormous
Which is why you typically write them either at the time of development or as a part of a "test-driven development" process.

Now that I've had some sleep, what I think you're describing is really more of a functional and/or integration test, not a unit test. In these tests you would connect to the database. The database not functioning means your test doesn't function and it also means your app doesn't function. This means your test fails...as it should.

Unit tests get run during development. They get run repeatedly to ensure that logic (i.e. units) continues to work correctly as development continues. In this way, they almost become regression tests. Functional tests get run (typically) as you deploy (even to a staging/UAT environment). This is to ensure that the system is functioning as designed prior to releasing it to your testers and/or end users.

That said, can you use the VS unit testing project to create functional tests? Certainly. Just be sure to name your project accordingly so that you clearly define for other developers what the tests encompass. You'll get the benefit of the automated testing framework, and your fellow developers will have a thorough understanding of what the tests should accomplish.
purplesoupProgrammerAuthor Commented:
käµfm³d 👽

I'm not against unit tests and use NSubstitute a lot, it is because I have never tested using this amount of data in the database - mass updates - that I am asking this question in the first place.

My point is simply that with the table I'm writing to having around 50+ columns and 5,000 rows, I can in a matter of minutes verify 250,000 data points simply by running my C# code and comparing it with the same process run by the desktop application.

I am doing this regularly while writing the code to ensure I have written the code correctly.

No doubt it will also be useful to have some unit tests in addition to this. But realistically how long do you think it would take for me to cover this amount of testing with unit tests alone?

Given therefore that testing through comparing data written to the database is at least valid as part of the solution, I'm asking how can I perform these sort of data comparisons as part of regular automated testing?
kaufmed   ( ⚆ _ ⚆ )Commented:
As I mentioned above, you can use the unit testing project for this. But IMO you should clearly name that project to indicate that it is actually a suite of functional tests not unit tests. And don't include any actual unit tests in that project.
Duy PhamFreelance IT ConsultantCommented:
@purplesoup:  Since I don't have any information how your databases (local .vs. main) are organized as well as how your new code is developed, I can only imagine about few options:

  • Export "correct" copies of data created by your desktop application to Excel or simply csv files. And within automated tests, after executing your new codes, export data created by new code to the same structure to Excel or csv files, and then compares two results.

  • If it is possible, putting "correct" copies of data created by your desktop application into local database server used for running tests (either into test database with different table names or a separate database). Then in your automated tests, you can do comparison locally (could use cross-databases queries if needed) on test database server rather than having to connect to main database.

  • If due to privacy, copies of data created by your desktop application are not allowed to download locally or put into local test database server. Then with affordable and acceptable effort, create a comparison web service or utility on the server where main database is located. And after tests execute your new code, they should upload test results and ask your comparison web service to compare with main database. But this is the last option you should think about when no other option is applicable. I don't think this takes as much time as mocking up bunch of database operations, but it still gives a considerable overhead and complication which could distract your from focusing on comparing the new code with desktop application.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.