public class DatabaseFixture
{
    private readonly string DataDirectory = AppDomain.CurrentDomain.BaseDirectory;
    private const string DatabaseName = "TestDb";
    private const string DbFilename = DatabaseName + ".mdf";
    private const string DbLogFilename = DatabaseName + "_log.ldf";
    private const string TestDbConnectionStringName = "TestDb";
    private const string MasterDbConnectionStringName = "MasterDb";
    private string DbPath => Path.Combine(DataDirectory, DbFilename);
    private string DbLogPath => Path.Combine(DataDirectory, DbLogFilename);

    public DatabaseFixture()
    {
        // Initialize test database for all Database Tests
        AppDomain.CurrentDomain.SetData("DataDirectory", DataDirectory);

        DeleteDatabase();
        CreateDatabase();
        SeedDatabase();
    }

    private void DeleteDatabase()
    {
        var existingDbName = GetExistingDatabaseName();

        if (!String.IsNullOrEmpty(existingDbName))
        {
            try
            {
                using (var conn = GetMasterDbConnection())
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = $@"DROP DATABASE [{existingDbName}]";
                    cmd.ExecuteNonQuery();
                }
            }
            catch (DbException ex) when (ex.Message.Contains("Unable to open the physical file"))
            {
                /* 
                We can safely swallow this exception. 
                LocalDB exhibits a strange behavior where it throws this exception when it can't find the mdf/ldf files (e.g. they got deleted by the build)
                But it still goes on to remove the related entry from sys.databases, which is the outcome we were hoping for in the first place.
                */
            }
        }
        
        if (File.Exists(DbPath)) { File.Delete(DbPath); }
        if (File.Exists(DbLogPath)) { File.Delete(DbLogPath); }
    }

    private string GetExistingDatabaseName()
    {
        using (var conn = GetMasterDbConnection())
        using (var cmd = conn.CreateCommand())
        {
            cmd.CommandText = GetExistingDatabaseNameCommandText;

            return (string) cmd.ExecuteScalar();
        }
    }

    private void CreateDatabase()
    {
        using (var conn = GetMasterDbConnection())
        using (var cmd = conn.CreateCommand())
        {
            cmd.CommandText = CreateDatabaseCommandText;
            cmd.ExecuteNonQuery();
        }
    }
    private void SeedDatabase()
    {
        using (var conn = GetTestDbConnection())
        using (var cmd = conn.CreateCommand())
        {
            cmd.CommandText = SeedDatabaseCommandText;
            cmd.ExecuteNonQuery();
        }
    }
    
    private static OdbcConnection GetTestDbConnection()
    {
        var conn = new OdbcConnection(ConfigurationManager.ConnectionStrings[TestDbConnectionStringName].ConnectionString);
        conn.Open();
        return conn;
    }

    private static OdbcConnection GetMasterDbConnection()
    {
        var conn = new OdbcConnection(ConfigurationManager.ConnectionStrings[MasterDbConnectionStringName].ConnectionString);
        conn.Open();
        return conn;
    }

    private string GetExistingDatabaseNameCommandText => $@"
SELECT [d].[name]
FROM sys.databases AS [d] 
INNER JOIN sys.master_files AS [f] 
ON [f].[database_id] = [d].[database_id] 
WHERE [f].[physical_name]=N'{DbPath}'";

    private string CreateDatabaseCommandText => $@"
CREATE DATABASE [{DatabaseName + Guid.NewGuid()}] ON PRIMARY 
    (NAME = N'{DbFilename}', 
     FILENAME = N'{DbPath}', 
     SIZE = 25MB, 
     MAXSIZE = 50MB, 
     FILEGROWTH = 5MB)
LOG ON
    (Name = N'{DbLogFilename}',
     FILENAME = N'{DbLogPath}')";

        private static string SeedDatabaseCommandText => @"
CREATE TABLE [dbo].[Analog](
    [Name] [varchar](100) NOT NULL,
    [Description] [varchar](100) NOT NULL,
    [Group] [varchar](10) NOT NULL
)
CREATE TABLE [dbo].[Rate](
    [Name] [varchar](100) NOT NULL,
    [Description] [varchar](100) NOT NULL,
    [Group] [varchar](10) NOT NULL
)
CREATE TABLE [dbo].[Tank](
    [Name] [varchar](100) NOT NULL,
    [Description] [varchar](100) NOT NULL,
    [Group] [varchar](10) NOT NULL
)";
    }