I was real excited to see an article up on MSDN that discusses Test Driven development (via Ashutosh Nilkanth). Not only that, it discusses the issue of testing database related code inside your unit tests - a messy subject at best, a horrid nightmare at worst. I was really looking forward into some real insights into this task, but the article came up short IMHO.

The author deals with doing database tests in a very simplistic way - simply write code that does things with the database and make sure that is works. Great! But this violates one of the most important principals in TDD- unit tests should not alter the data! . They should be completely transparent. In the article, the author has a “real world” problem - since the unit tests always insert and delete code from the database, the IDENTITY columns in the database need to be reset after the tests have finished. Not only that, some mock data needs to be put in there, as well as some mock roles, and then when the tests are over you need to truncate the tables yadda yadda yadda...

The overall solution to testing such a thing is inherently flawed. We should not test against a live database. Not just because of our “pure” TDD principals, but simply because it really sucks in many other ways - You have many many variables you need to account for for the tests to be accomplished (Don't tell me this is going to run against a production DB??) All the setup and tear down tasks are simply too much work. All the extra configuration, separate settings for test code and production code and all that stuff - it simply isn't worth doing when you have a much more elegant solution - Mock Objects.

“A mock object is a "double agent" used to test the behaviour of other objects. First, a mock object acts as a faux implementation of an interface or class that mimics the external behaviour of a true implementation. Second, a mock object observes how other objects interact with its methods and compares actual behaviour with preset expectations. When a discrepancy occurs, a mock object can interrupt the test and report the anomaly. If the discrepancy cannot be noted during the test, a verification method called by the tester ensures that all expectations have been met or failures reported...“

“...Mock objects can also simulate states that may be difficult or time-consuming to realise in a runtime environment. A mock object can throw any exception or produce any error condition on demand. For example, using a real database, to see how an object will react to an offline database you would have to actually down the database. Using a mock JDBC connection, you can simulate a dead database in one test and a live database in the next, all in a fraction of a second.

As products mature, and test suites grow larger, tracing the cause of a failed test can become difficult. A mock implementation can test assertions each time it interacts with production (or "domain") code. The test is then more likely to fail at the right time and generate a useful message.

We can also build base assertions about how domain objects are suppose to work into our mock objects. These assertions can be applied automatically whenever the mock object is used. This ensures that our base assertions remain true as the application matures.

We use mock objects because they allow us to quickly create inexpensive, effective tests that are easy to maintain. “

[via MockObjects.Com FAQ]

I couldn't have said it better myself. We can either write Mock Objects ourselves into out unit tests, or we can use some of the libraries out there, that ,like NUnit, provide us with a framework that makes building our tests easier. These frameworks make building Mock objects easier. There are several frameworks for .Net mock objects. See the list here.

BTW, personally, I always used to test a database using mostly the techniques listed in that MSDN article, but lately I've discovered Mock objects and am really looking into changing my ways on this problem. I find it difficult to find real world articles dealing with this issue in .Net (there are some for Java though), but this article, by Peter Provost seems to be a great start.