Database unit testing scenarios

I am looking for ideas on how people approach testing with databases. I am moving toward a release of SnapDAL and besides it being a complete rewrite in c#, it is
built from the ground up to support writing of tests. The implementation is based on the .net Mock Objects project which essentially has mocked all of the framework classes. Coincidentially, SnapDAL is based on the same classes. OK, so that’s all well and good.

Mock object techniques are not new to you special people who read my blog 😉 Hopefully it’s enough to say one of the testing scenarios SnapDAL will support is to setup an IDbCommand object with it’s connections and parameters, set expectations, run the test and verify that the code called the objects correctly. This type of testing is especially useful for me for blocks of code that have a really heavyweight database component to them. For example, we send financial transactions to the markets on a set schedule and when we do, we work with large amounts of data. It is not practical to bring all that data into the clr and manipulate it for both memory and bandwidth reasons. So, the code assembles various criteria and calls a long running query. Well, to unit test the code, the long running query is irrelavent. So, to mock that call, and just verify that that the code passed the parameters correctly is perfect.

Now, the call does return records. If you mock the call, you still have to return what the caller expected to see returned.
The mock framework includes a MockDataReader which you set as an expectation of the MockCommand. The normal way of using this class is to pass the record meta information, column names, types etc., in as a dataset. The row data is passed in as a 2 dimensional array. Now that works just fine until you have to return a lot of data. Then it starts turning into more code than the code under test really fast. So SnapDAL adds a couple of other techniques for building the MockDataReader. The options are to run a separate query to build a a real reader which is turned into the mock reader and returned, or to build a reader on previously saved off data in xml files. These files can be built for you automatically by SnapDAL by recording real calls.

So at this point I have supported testing database code in a few different ways. These are:

  • standard mock techniques where test data is entered in code in the test
  • mock techniques that allow to retrieve test data from xml files, as well as to help build the test files
  • mock techniques that allow you to substitute a different query and provider (such as an OleDB query to Access)
  • a non mock technique that allows you to substitute a reader from a normal call by interception with a decorator
  • support the ability to group the setup of these xml files and alternate queries into named tests so that the same query can be mocked in different ways for different tests
  • test databases are supported in the normal way by switching connection strings
  • I’ve started building code that restores database tables using bulk copy which is much faster than doing a db restore, but this code may never leave my office as making it general could be too hard

So what other techniques have people used for testing your database calls?


2 responses to “Database unit testing scenarios

  1. Thanks Roy. This post has been usurped by a more permanent article where I do mention your tool. The url is