Solved

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

Posted on 2016-09-05
7
46 Views
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.

Thanks.
0
Comment
Question by:purplesoup
  • 3
  • 2
  • 2
7 Comments
 
LVL 10

Expert Comment

by:Duy Pham
Comment Utility
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.
0
 
LVL 74

Expert Comment

by:käµfm³d 👽
Comment Utility
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.
0
 

Author Comment

by:purplesoup
Comment Utility
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.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 74

Expert Comment

by:käµfm³d 👽
Comment Utility
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.
0
 

Author Comment

by:purplesoup
Comment Utility
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?
0
 
LVL 74

Expert Comment

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

Accepted Solution

by:
Duy Pham earned 500 total points
Comment Utility
@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.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now