Joseph Cooney's Weblog


Database testing in .NET

I am part-way through reading Test Driven Development in .NET by Jim Newkirk and Alexei Voronstov. Thus far I have had mixed feelings about it. The hardest parts about unit testing I have always thought are testing with a database, and testing UI, so I was particularly keen to see how they tacked the database access testing. The approach they use in the 5th chapeter is to create a single typed dataset to represent the entire database. They specify a select command and use a CommandBuilder to automatically generate commands. Updates and deletes are performed using a DataAdapeter.Update() and DataAdapter.Delete() methods. I don't really think this is a real-world database access strategy since it has a number of limitations. It is entirely possible to keep a similar approach but define your commands (either as text queries or as stored procedures). I don't think this would have not changed the testing approach (excepth perhaps writing a few more tests to test the command-building code) but the mere suggestion of testing application code and stored procedures in the same tests would probably have been too much for unit testing purists. The authors make sure they get themselves out of that little problem by refering to their tests as “programmer tests” as opposed to “unit tests“. In general I would have liked to possibly see a few different database access styles demonstrated, with testing strategies presented for each one, also possibly including the use of mock objects in database testing. I realize that this would probably have made the book considerably larger. Perhaps a seperate volume on the topic of testing database code is warranted? The review and summary by Steve is pretty on the money, so read that if you're thinking about getting this one.
Update: source code from the book

Comments (5) -

Richard Mason

Right on, Joseph. I've just started reading TDD in .NET as well. Even though I hadn't done any unit testing before, by the end of the first chapter I was keen to see how they handled DBs and UI. Unfortunately so far I haven't been convinced, but I haven't finished yet so maybe it'll all click into place by the end.

Thomas Eyde

I usually define a db API like UserDatabase, OrderDatabase and so on. Each will have methods relevant to the needed data operations, like Add(), Delete() and GetXxx(). For testing purposes I also add a Clear() method which is seldom used in production code, but helps to set up the database in a clean state.

Then I test the API (test-first of course) and code the implementation as I go and refactor when needed.

The funny thing is that I end up with a simpler database, I don't have as many explicit relations as I used to have because I wait until I really need those restrictions. It is all about doing the simplest thing. If I need to guard against duplicates, then a unique index is the thing. If it's mandatory that a thing is not added before its container thing, then a one-to-many relation is added.

This is working fine for me. I am not testing any database code directly, but if the expected behaviour is achieved, then I see the db code as correct. It's not very hard to identify the errors, after coding two or three stored procedures I usually have a small framework for command building and other plumbings, so from now on the errors happens mostly in parameter passing or in the stored procedure code.


I tend to take the opposite approach with database design. I identify what "good data" should look like and try to restrict everything else with referrential integrity, nullability of columns, unique indexes and check constraints.

I'm also writing an article on TDD of a database for MSDN.  While it does not tackle *everything* you mention above, if MS accepts my articles I'll keep on cranking them out!

I found the lack of any database-testing coverage rather annoying when I was trying to do this back when NUnit v2 first came out.

Ashish Neralwar


Comments are closed