How to run Integration Testing on DB through repositories with LINQ2SQL?

Question

How do you go about integration testing your database through your domain layer/model (repositories) that uses LINQ 2 SQL in the implementation and leave the DB as you found it? In other words, the ideal world of unit testing the DB, the integration test would leave the DB as it found it.

Are there tools out there that will handle this automagically? What are the best practices for performing integration tests on a DB through repositories?

Answer 1

The Spring Framework provides support for integration testing when using NUnit. The NUnit classes are located in the assembly Spring.Testing.NUnit.dll. In there are some classes that perform transaction management. These classes create and roll back a database transaction for each test. You simply write code that can assume the existence of a transaction.

Whether or not this will actually work with Linq to SQL is another matter. Spring says this works with ORMs. SQL Server 2008 allows you to nest transactions, so in theory you could start a transaction, perform your test through the Linq to SQL classes, and then roll your transaction back. But I haven't
tried it.

Ryan Garaguay has an interesting article about this which uses TransactionScope and NUnit to roll back the database changes (although he is using SQLCommand and SQLConnection objects in his test code, rather than Linq)