.NET TDD with a Database and ADO.NET Entity Framework - Integration Tests

I'm using ADO.NET entity framework, and am using an AdventureWorks database attached to my local database server. For unit testing, w开发者_开发百科hat approaches have people taken to work with a database?

Obviously, the database has to be in a pre-defined state of change so that the tests can have some isolation from each other... so I need to be able to run through the inserts and updates, then rollback either between tests or after the batch of tests are done.

Any advice?

Thanks.

I have experienced a similar kind of situation.

Storing data to DB and retaining them to previous state are not ideally unit tests. They are Integration test cases.

In order to perform the integration tests (insert / update operations ) without affecting the actual data ,I would suggest the following

1. Use different database(change the name of the actual DB) , and let the schema remain the same as the actual DB.

2. Point the unit tests to the newly created DB.

3. Now how do we create a new DB , for running the tests alone in an automated way ?

Ans : Have the SQL scripts listed in a batch file , such as this.(here i presume you are using SQL SERVER)

E.g., sqlcmd -i c:\data\runscripts\InventoryMonthEnd.sql

Specify the batch file , which lodges this sql statement(s) to be executed in MSBuild process.

Sample MSBuild Task .

<ItemGroup>
<StoredProcScripts Include="$(RelativeSPDir)/*.sql" />
</ItemGroup>

<Target>
<Exec Command="isql -E -n -b -d DBName -i %(StoredProcScripts.Identity)" Condition="'%(StoredProcScripts.Identity)' != ''"/>
</Target>

Personally I have each test create it's own data and delete it when the test completes. This way, the data a test requires and the test itself are kept closely together and can be maintained as one unit. For data shared by a set of related tests (in the same *Test.cs file), I use [TestInitialize] Start() and [TestCleanup] Finish().

I also have a utility program that re-creates an empty test database, as the unit test's cleanup code will not run if I'm debugging a test and terminate it in the debugger. To re-create the database, I use EF 4's ObjectContext.CreateDatabase() method.

You can use MbUnit which has a rollback attribute. This will make sure that all the changes made in the Test will be rolled back.

http://weblogs.asp.net/astopford/archive/2006/07/25/MbUnit-database-testing_2C00_-and-thanks-to-Roy.aspx

This is how we solved it:

1. Use a Sandbox database in first place for development/testing
2. Create all necessary data in the tests
3. Delete all of the test data created by the tests after they pass/fail. Something like a try...finally

A really effective way to manage data in your tests is to use a transaction. A colleague introduced this to me and I later saw it used in a number of places (GOOS)

In C# if you use a TransactionScope and dont comit the transaction you have a really effective way of keeping things clean.