.NET - Unit Testing Your Code and State in Your Database

Question

Assumptions: We use .NET coding against SQL Server 2005

I was just wondering how most people incorporate state into the Unit Tests that affect their database? I know when and where to use mocking, but when you want to move past that and actually do some database tests... what strategies do you use to setup and teardown your database? Do you do this per test? or setup a certain scenario in the database and run several tests against that "state of the world". Any advice would help. Thanks.

Answer 1

Here's what I do in NUnit...Start a database Transaction in a method marked with the [Setup] Attribute. Setup the database state as you would like. NUnit then runs the test against that state. Roll back the database transaction in a method marked with the [TearDown] attribute. You never change the state of the database that way.

Answer 2

I run most of the testcases against an empty database, meaning that only the database schema exists, but no data. The actual data is created and deleted by the test cases or the setup and teardown as needed. This takes longer than having an a prepared database, but is more resistent against database schema changes.

Answer 3

First of all, all my Database DDL 'scripts' are written in C# classes. I use Migrator.NET to do this: I have several classes in where every class contains some logic to upgrade or downgrade my DB.

I have a database that is called 'projectname_test' that I use to run my unittests against that need to have DB access. This DB is being upgraded by my Migrator.NET classes. This DB is upgraded by a CI process (CC.NET).

The unittests that access that DB will remove everything that is in that DB after they ran. And, when I want to play it drastically, I can simply drop my test DB; it will be rebuilt by the CC.NET process. :)

Answer 4

Take a look at this question and answers http://stackoverflow.com/questions/321180/how-do-i-test-database-related-code-with-nunit/367294#367294

Answer 5

If you use NHibernate as ORM it is easy to swap the real SQL Server database (a few lines in app.config) with an in-memory SQLite database that is easily recreated on every test. This way your integration/build server which will execute the unit tests no longer needs to access an SQL Server.