How to use new database instance of each MSTest unit test run

Question

I'm using MSTest under Visual Studio 2010 to test an ASP.NET MVC 3 project. I have a SQL Express 2005 DB that I'd like it to use and I want a fresh instance of the DB every time, copied from a template I've included in the project. Pretty standard requirements, I would have though, but I can't get this to work.

I've created a .testsettings file which enables deployment and my connection string looks like this:

<add name="MyDb" connectionString="Data Source=.\SQLEXPRESS2005;Database=MyDbTest;AttachDBFilename=|DataDirectory|MyDbTest.mdf;User Instance=true;Integrated Security=SSPI;" providerName="System.Data.SqlClient" />

The test runs OK the first time, but after that it fails with errors like this:

Test method ... threw exception:  System.Data.DataException: An exception occurred while initializing the database. See the InnerException for details. ---> System.Data.EntityException: The underlying provider failed on Open.
---> System.Data.SqlClient.SqlException: Database '...\bin\Debug\MyDbTest.mdf' already exists. Choose a different database name. Cannot attach the file '...\Out\MyDbTest.mdf' as database 'MyDbTest'.

The accepted answer in this MSDN thead says to remove the "Database=" connection string parameter. However, if I do that it fails with this error:

Test method ... threw exception: 
System.InvalidOperationException: Unable to complete operation. The supplied SqlConnection does not specify an initial catalog.

How do I get this to work?

score 1 · Answer 1 · edited May 23 '17 at 10:34

So far I've come up with a hack to change the DB name at runtime, at test assembly initialization time. This requires a further hack - using Reflection to enable modifying the configuration at runtime (thanks to David Gardiner).

[TestClass]
public static class TestHelper
{
    [AssemblyInitialize]
    public static void AssemblyInit(TestContext context)
    {
        RandomizeDbName();
    }

    private static void RandomizeDbName()
    {
        // Get the DB connection string setting
        var connStringSetting = ConfigurationManager.ConnectionStrings["TheDbSetting"];

        // Hack it using Reflection to make it writeable
        var readOnlyField = typeof(ConfigurationElement).GetField("_bReadOnly",
            BindingFlags.Instance | BindingFlags.NonPublic);
        readOnlyField.SetValue(connStringSetting, false);

        // Randomize the DB name, so that SQL Express doesn't complain that it's already in use
        connStringSetting.ConnectionString = connStringSetting.ConnectionString.Replace(
            "Database=MyTestDb", "Database=MyTestDb_" + new Random().Next());
    }
}

Edit: It's actually even a bit worse than this: I'm having to call TestHelper.RandomizeDbName() at the start of every test that requires a fresh DB, otherwise it gets data left over from previous tests.

How to use new database instance of each MSTest unit test run

1 Answers1