Creating units test for SQL Code

If you want to create unit tests that act against a database a good alternative is to use an SQL Server Local DB.

However sadly, the MSTest no longer supports TestDeployment on .NET Core, but I developed a couple of helpers.

First a I created a template DB and set the copy always action.

Solution with a database template with a Copy to Always action

Next. I created a couple of helpers. To copy the template, to erase files and detach the db it if it is still attached from a previous run:

public string CreateDatabase(string dbName)
{
        var templateBasePath = Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), "BaseDatabase.mdf");
        var templateBasePath_Log = Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), "BaseDatabase_log.ldf");
        var dbPath = Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), dbName +".mdf");
        var dbPath_Log = Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), dbName + "_log.ldf");
        var connectionstringTemplate = @"Data Source = (LocalDB)\MSSQLLocalDB; AttachDbFilename = {0}; Integrated Security = True";
        var connString = string.Format(connectionstringTemplate, dbPath);
        if (File.Exists(dbPath))
        {
            DetachDatabase(dbPath);

            EraseDbFiles(dbPath, dbPath_Log);
        }
        File.Copy(templateBasePath, dbPath);
        File.Copy(templateBasePath_Log, dbPath_Log);
        return connString;
}

private static void DetachDatabase(string dbPath)
{
        try
        {
            var serverConnString = @"Server=(localdb)\mssqllocaldb;Initial Catalog=master;MultipleActiveResultSets=False;Integrated Security=True";
            using (var connection = new SqlConnection(serverConnString))
            {
                connection.Open();
                var cmd = connection.CreateCommand();
                cmd.CommandText = string.Format("ALTER DATABASE[{0}] SET OFFLINE WITH ROLLBACK IMMEDIATE", dbPath);
                cmd.ExecuteNonQuery();
                cmd = connection.CreateCommand();
                cmd.CommandText = String.Format("exec sp_detach_db '{0}'", dbPath);
                cmd.ExecuteNonQuery();
            }
        }
        catch
        {
            Console.WriteLine("Could not detach db {0}", dbPath);
        }
}

private void EraseDbFiles(string dbPath, string dbPathLog)
{
        try
        {
            if (File.Exists(dbPath)) File.Delete(dbPath);
            if (File.Exists(dbPathLog)) File.Delete(dbPathLog);
        }
        catch
        {
            Console.WriteLine("Could not delete the files (open in Visual Studio?)");
        }
}

Now on your tests you just do something like:

[TestMethod]
public void TestMethod1()
{
var connstr = CreateDatabase("test1");
using (var sqlConnection = new SqlConnection(connstr))
{
sqlConnection.Open();
var cmd = sqlConnection.CreateCommand();
cmd.CommandText = "select * from customer";
cmd.ExecuteNonQuery();
}
}