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

Posted on 2016-09-05
Last Modified: 2016-09-12
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.

Question by:purplesoup
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
LVL 10

Expert Comment

by:Duy Pham
ID: 41785115
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,
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 41785569
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.

Author Comment

ID: 41785834
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 Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

LVL 75

Expert Comment

by:käµfm³d 👽
ID: 41786043
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 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.

Author Comment

ID: 41786055
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?
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 41786190
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.
LVL 10

Accepted Solution

Duy Pham earned 500 total points
ID: 41786354
@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.

Featured Post

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question