Unit testing a Data Access Layer

Question

I've been reading up on unit testing the data access layer of a project. Most options boil down to:

• Use a dedicated test database but cleanup in the finalizing phase of all unit tests (or do it manually)
• Use the database but don't commit or simply roll back
• Mock the database

In a former project we used to use the rollback way but I like to know more about the other options and how these are best performed. If you have samples/articles/videos/... please share them.

Answer 1

You need to have 2 types of tests with a project. unit tests and integration tests

Unit tests test one aspect of your project without the dependencies on data access and presentation. For unit tests you would mock your database and user dependency injection to uncouple your code from the data provider. This leads to a better architecture and enables you to plug in a different data provider if you so wish. e.g. moving from ADO.net to nHibernate.

Integration tests are where you test your whole system and make sure your code can get the correct data from the database etc. For integration tests each developer should have a copy of the database on their workstation against which they can test. You should try to automate the create and population of your database so you can quickly and easily go back to a good copy of the database. Tools like nant and DBFit will help you to script your databases creation.

I wouldn't use a central database to test on as other developers may be testing on it at the same time and it may not be in a good state and you could get false positives and spend ages trying to debug a problem which is not a problem.

Answer 2

I prefer to use a test database instead of the do not commit idea.

My dev database has dummy records or a fully sanitized sampling of production data.

My integration testing database is a copy of the actual production database (this version is the one used to test just before I roll changes out live).