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

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,
Duy.

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.

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.

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.

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?

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.