Publish a Database Project as part of TestInitialize

Question

Is there a way to publish a database project (within the same solution) as part of a TestInitialize method when running unit (or integration in this case) tests in Visual Studio?

The idea is that each integration test which actually hits a database should be hitting a known good and clean database, and the best way to do that is to completely refresh the database with each test. I've been looking around online and finding a lot of related information about databases and testing, but nothing about refreshing the database when running automated tests. Nor am I finding anything in the Visual Studio interface, or at least I'm missing it.

I'm using Visual Studio Ultimate 2012 RC.

Answer 1

Following a helpful link from a colleague, I ended up using Process.Start to run sqlpackage.exe for the deployments. It's not as clean from a Visual Studio perspective as I'd hoped, but it gets the job done. My bootstrapping now looks like this:

private const string SQL_PUBLISH_COMMAND = @"C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe";
private const string SQL_PUBLISH_COMMAND_ARGUMENTS = @"/Action:Publish /SourceFile:C:\Data\Code\NewEdCo\Domain\NewEdCo.Database.SQLExpress\bin\Debug\NewEdCo.Database.SQLExpress.dacpac /Properties:CreateNewDatabase=True /TargetServerName:hactar\sqlexpress2012 /TargetDatabaseName:NewEdCoTest";


private void DeployTestDatabase()
{
    var procInfo = new ProcessStartInfo
    {
        Arguments = SQL_PUBLISH_COMMAND_ARGUMENTS,
        CreateNoWindow = true,
        ErrorDialog = false,
        FileName = SQL_PUBLISH_COMMAND,
        RedirectStandardOutput = true,
        UseShellExecute = false
    };
    var proc = new Process
    {
        StartInfo = procInfo
    };

    proc.Start();
    var result = proc.StandardOutput.ReadToEnd();
}

[TestInitialize]
public void BootstrapTests()
{
    DeployTestDatabase();
}

[TestCleanup]
public void TearDownTests()
{
    DeployTestDatabase();
}

The options used for the ProcessStartInfo and the reading of the standard output (even though I don't do anything with it yet) are there to keep the task synchronous. That way the tests aren't stepping on each other. This increases execution time for integration tests, but that's fine for my needs.

I realize I'm doing a complete refresh both before and after. If that turns out to take far too long as my number of tests increases, I'll re-think it. But for now I like to make sure it's refreshed before the test as well as leaving no trace after the test.

MSDN has an extensive list of options for sqlpackage.exe.