What is the best way to unit test heavily database dependant .NET middleware? E.g. a process which reads data from multiple databases, manipulates it, and then combines and writes it to other databases?
Should the databases be filled with static data which is somehow reset on each unit test run? Should the whole SQL Server access be somehow mocked? Is it not feasible to unit test such an application in the real world?
I think the best practice is really to Mock all access to database - just return some predetermined static data on call - you don't need to test how databases work - your need to test your unit behavior. Lesser your unit test interacts with outside - better it is.
With mocking your will be able to check that call to database is valid without really invoking it, so it's what you need i think.
You should try to layer your code so that you can mock the operation of your functions.
The layer that does actually need to talk to the database could use a system of setting up the data and working with it within a transaction, which you would rollback at the end of the test.
I'd recommend mocking the data access level. The benefit of using mocks in this instance include: 1) unit tests will run quicker. If they have to do the full work of connecting to the db, extracting data etc. then the cost will become expensive. Expensive tests = people stop running them/start losing faith in them! 2) You can test a vast range of scenarios without having to go through the hassle of setting up suitable test/static data that you have to ensure is always in the DB before the tests start. 3) taking away the external db system from the equation means you are testing just the .NET code you want to test. No external dependency.
You could either have a data access layer that does pure db interaction, and then mock that. Or, use mock SqlCommands etc in your .NET code.
The answer is mocking
However the way to do this I have found is as follows.
Separate the DAL into 2 layers. The bottom simply performs atomic read and writes to the databases - these objects all implement a set of interfaces IReadFromDB and IWriteToDB.
Then you can create your read and write business logic in a higher DAL level but rather than reference the objects that will read and write to the database reference the interfaces and use properties to be able to substitute functionality. I tend to include the desired functional objects in the constructors so that things work 'out of the box' so to speak.
This will make it a cinch to 'swap out' the functionality and so to unit test the business logic.
As for testing the DB read and writes ... I haven't found a way that doesn't involve work. I usually use a different connection string to a copy of the database and then write data generation and cleanup code for unit tests to leave the state of the db the same as before and after.
Yes, its time consuming ... however it doesn't risk alienating a client. It depends on your priorities.
Someone else mentioned performance testing. I would not consider this to be part of a unit test. I usually do this with a test harness in combination with debug code simply because performance of small parts is often misleading - when you move to the big picture the parts that are actually casing problems are often not the parts that localised testing would flag in my experience.
I think you should separate your concerns into different kinds of tests.
I would abstract the data access layer and then mock it. With mocking you will be able to increase the code coverage of your tests. It will also prevent the idea of 'Test Cancer' that will happen by doing expensive calls to network/filesystem/database because people will stop running them.
You should have a few tests that call to the database but they should be limited as much as possible.
Hi there.
I have two methods for unit testing code which has database dependancies:
With TypeMock, I can fake database data coming into my code, which helps me run my tests without having to worry about cleaning up the database, or restoring backups etc. I use mocking for testing code outside of the database logic, but still requires fake data from the database.
With custom SQL scripts, I can run SQL commands on the TestInitialise() sub and, when the unit test finishes, I have cleanup SQL run on the TestCleanup() sub. I use this method for testing code where the database logic is more complicated. For example, I might need to insert some rows into tables which will help with testing the code. Then on the TestCleanup() I will have my delete SQL commands which remove all the records that were inserted via the TestInitialise() sub.
Cheers. Jas.
In my work we use Nunit and Typemock for our Unit testing. The fact that we don't need to change our code for the tests is a big plus. Mocking the DAL is the way to go.