Testing with LocalDb

Dynamically creating a LocalDb database in tests

You can create a new SqlServer LocalDb database for each test run. The connection string can be reset globally (the same trick as changing AppSettings dynamically in unit tests)

MSTest with EF Code First

Using EF Code First migrations to initialize the database. Note that the Migrations.Configuration class is normally internal, not public, so either change it, use InternalsVisibleTo or create a suitable public method that runs the migration. The connection string name must match what the dbContext uses (usually the same name e.g. "ApplicationContext"). The localDb file is created in the integration test bin folder and is cleared out each run.

NB: AssemblyInitialize must be in a class marked [TestClass]

[AssemblyInitialize]
public static void AssemblyInit(TestContext context)
{
    //use this database file name (in current bin folder)
    var testDatabase = new TestDatabase("TestDatabase");
    testDatabase.CreateDatabase();
    //globally inject a connection string with this name
    testDatabase.InitConnectionString("ApplicationContext");
    //if we're using Entity Framework Code First, run all the migrations.
    var migrate =
        new System.Data.Entity.MigrateDatabaseToLatestVersion<ApplicationContext,
            Application.Data.Migrations.Configuration>();
    var dbContext = new ApplicationContext();
    migrate.InitializeDatabase(dbContext);
}

TestDatabase

CleanupDatabase ensures the database is emptied each run (if it can detach- don't leave a connection open in Visual Studio).

using System;
using System.Configuration;
using System.Data.SqlClient;
using System.IO;
using System.Reflection;

namespace IntegrationTests
{
    class TestDatabase
    {
        private const string LocalDbMaster =
            @"Data Source=(LocalDB)\v11.0;Initial Catalog=master;Integrated Security=True";
        private const string TestConnectionString =
            @"Data Source=(LocalDB)\v11.0;Initial Catalog={0};Integrated Security=True;
            MultipleActiveResultSets=True;AttachDBFilename={1}.mdf";

        private readonly string _databaseName;

        public TestDatabase(string databaseName)
        {
            _databaseName = databaseName;
        }

        public void CreateDatabase()
        {
            var isDetached = DetachDatabase();
            if (!isDetached) return; //reuse database
            var fileName = CleanupDatabase();

            using (var connection = new SqlConnection(LocalDbMaster))
            {
                connection.Open();
                var cmd = connection.CreateCommand();
                cmd.CommandText = string.Format("CREATE DATABASE {0} ON (NAME = N'{0}', FILENAME = '{1}.mdf')",
                    _databaseName,
                    fileName);
                cmd.ExecuteNonQuery();
            }
        }

        public void InitConnectionString(string connectionStringName)
        {
            var connectionString = string.Format(TestConnectionString, _databaseName, DatabaseFilePath());
            var config = ConfigurationManager.OpenExeConfiguration(Assembly.GetCallingAssembly().Location);
            var settings = config.ConnectionStrings.ConnectionStrings[connectionStringName];
            if (settings == null)
            {
                settings = new ConnectionStringSettings(connectionStringName, connectionString, "System.Data.SqlClient");
                config.ConnectionStrings.ConnectionStrings.Add(settings);
            }
            settings.ConnectionString = connectionString;
            config.Save();
            ConfigurationManager.RefreshSection("connectionStrings");
        }

        private string CleanupDatabase()
        {
            var fileName = DatabaseFilePath();
            try
            {
                if (File.Exists(fileName + ".mdf")) File.Delete(fileName + ".mdf");
                if (File.Exists(fileName + "_log.ldf")) File.Delete(fileName + "_log.ldf");
            }
            catch
            {
                Console.WriteLine("Could not delete the files (open in Visual Studio?)");
            }
            return fileName;
        }
        private bool DetachDatabase()
        {

            using (var connection = new SqlConnection(LocalDbMaster))
            {
                connection.Open();
                var cmd = connection.CreateCommand();
                cmd.CommandText = String.Format("exec sp_detach_db '{0}'", _databaseName);
                try
                {
                    cmd.ExecuteNonQuery();
                    return true;
                }
                catch
                {
                    Console.WriteLine("Could not detach");
                    return false;
                }
            }
        }
        private string DatabaseFilePath()
        {
            return Path.Combine(
                Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location),
                _databaseName);
        }
    }
}