Last week, during the launch of the Visual Studio 2008 and all the 2008 wave, me and Andrea received the heroes book, where on page 40 you can find our photo.

It is a big joy to be on that book with others 80 teams of all over the world.

Thanks to Microsoft, Carolyne and Selcon.

Anyone who has tried to write a test had to solve the problem to write a test that hit the database to write a record in a table with a high number of foreign key. Writing these tests result in a big overhead to put the database in a consistent state before write the real test.

Which approach can we use?

I found two approaches that I like.

The first one is to have a test only known database. If you know the database you know that a particular table have the particular value with that particular ID and you can use this ID to build your record to insert. For example if you need to test the insert an Address you can use a known city to manage the foreign key:

[Test]
public void SaveAddress_CreateANewValidAddressAndSaveItOnDatabase_ShouldSucced()
{
  AddressService as = IoC.Resolve<AddressService>();
  Address a  = new Address();
  a.Street = "via Mantova 6";
  a.City = GetBrescia();

  as.Save(a);
  // ...Asserts...
}
private City GetBrescia()
{
  return new City(Id=5, Name="Brescia");
}

The hypothesis is very strong and maybe too restrictive and difficult to respect when the database is quite big and change a lot during the development. For these reasons sometimes is better to rebuild the entire database on every test session using a TSQL Script (a script that build the tables and insert the data).

A second approach is to let that every tests is responsible to prepare the database tables that need to use. In the previous example the Test should insert the city of Brescia in the Cities table getting the ID (from database) and use this ID to build the city object.

The differences with previous method is that in this one the tests are more independent from the database and every test is responsible for his own data, on the other side the second approach is more verbose because you need to write a lot of code to insert the data in the database (and remove it too!).

I usually prefer the second approach, is more maintainable and clear in big application, even if the first is better for small applications.