If you happen to be using Linq2Sql and are also someone who tests, I have something for you.  You can use Linq's DataContext to create your database and then populate it with test data prior to each test run.  Note that you can do this same thing when using other OR/M tools such as NHIbernate, so this is nothing new or revolutionary.

Before I get into the 'How To' on doing this, I would like to go over the 'Why' I should do this.

When building out your test suit on any data driven application you are going to need to test data access at some point.  There are really 3 solutions to this problem.

1. Simply query for known, existing data
   Although this works, this leads to very weak and brittle tests for obvious reasons.
   
2. Create/use/delete the data for each of your tests
   This is a better choice, but leads to very heavy tests and can also lead to bad or orphaned data.  Which can be a major pain point if this is done in your development environment.
   
3. Rebuild the db and test data for each test run.
   To me, this is the best choice as you get a clean slate and any orphaned data will be destroyed after each run.  No harm, no foul

Ok, on to the How to of this post.

The first thing you need to do is setup your tests to have your test fixture have FixtureSetup/FixtureTearDown methods to perform building/destroying of the database.

private TestsInit _testsInit;

[TestFixtureSetUp]
public void FixtureSetup()
{
    _testsInit = ObjectFactory.GetInstance< TestsInit >();
    _testsInit.InitDatabase();
}

[TestFixtureTearDown]
public void FixtureTeardown()
{
    _testsInit.DestroyDatabase();
}

Once you have your Setup/TearDown implemented you need to create the logic to build the database and insert test data.

public void InitDatabase()
{
    var dbContext = new DBContextDataContext( ConfigurationReader.ConnectionString_ForTests );
 
    CreateDatabase( dbContext );
    CreateTypeData( dbContext );
    CreateTestEpisode( dbContext );
}

public void DestroyDatabase()
{
    var dbContext = new DBContextDataContext( ConfigurationReader.ConnectionString_ForTests );

    DeleteDatabase( dbContext );
}

private void CreateDatabase( DataContext dbContext )
{
    DeleteDatabase( dbContext );

    dbContext.CreateDatabase();            
}

public void CreateTypeData( DBContextDataContext dbContext )
{
    dbContext.ExecuteCommand( "INSERT INTO [LevelType]( Name ) VALUES ( 'Type 1' )" );
    .....
    dbContext.ExecuteCommand( "INSERT INTO [TagTypes]( Name ) VALUES ( 'Type 4' )" );
}

private void CreateTestEpisode( DBContextDataContext dbContext )
{
    Episode episode = new Episode {LevelTypeID = 1, Name = "Test 1", Description = "Desc 1", EpisodeNumber = 1, EpisodeDate = DateTime.Now};
    ....

    episode.EpisodeDownloadInformations = new EntitySet< EpisodeDownloadInformation >
                                              {
                                                  new EpisodeDownloadInformation{ FileName = "Foo.wmv", Size = 9.25m, Time = "10:00" }
                                              };

    dbContext.Episodes.InsertOnSubmit( episode );
    dbContext.SubmitChanges();
}


private void DeleteDatabase( DataContext dbContext )
{
    if ( dbContext.DatabaseExists() )
    {
        dbContext.DeleteDatabase();
    }
}

One thing to be careful of here is that you are using an alternate connection string, not the one for your dev/stage/production environment or you will NOT be a happy developer.  Also notice that I both using the ExecuteCmmand statement as well as the object model to insert the test data.  This is done simply to show the concept.  You could also simple script up all the inserts and put them into an external file.  The way you do it is up to you.

So there you go, you now know how you can use the DataContext to build/destroy your db structure for each test run.

Till next time,