Unit Testing Database Driven .NET Applications

Question

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?

Answer 1

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.