jvandertil / blog Goto Github PK

    public class SqlServerFixture : IAsyncLifetime
    {
        private readonly string _dbName;
        private readonly string _connectionString;

        private readonly Checkpoint _emptyDatabaseCheckpoint;

        public SqlConnection Connection { get; private set; }

        public SqlServerFixture()
        {
            _dbName = "db-" + Guid.NewGuid().ToString();
            _connectionString = "Data Source=(localdb)\\MSSQLLocalDB; Integrated Security=True;";

            _emptyDatabaseCheckpoint = new Checkpoint
            {
                // Reseed identity columns
                WithReseed = true,
                TablesToIgnore = new[]
                {
                    // DbUp journal does not need cleaning
                    "SchemaVersions"
                },
            };
        }

        public async Task InitializeAsync()
        {
            Connection = new SqlConnection(_connectionString);
            await Connection.OpenAsync();

            await ExecuteDbCommandAsync($"CREATE DATABASE [{_dbName}]");
            await ExecuteDbCommandAsync($"USE [{_dbName}]");

            var builder = new SqlConnectionStringBuilder(_connectionString)
            {
                InitialCatalog = _dbName
            };

            var connectionString = builder.ToString();

            var runner = new DbUp.SqlServerMigrationRunner(connectionString);
            var result = runner.PerformUpgrade();

            if (!result.Successful)
            {
                throw result.Error;
            }

            await _emptyDatabaseCheckpoint.Reset(Connection);
        }

        public async Task DisposeAsync()
        {
            await ExecuteDbCommandAsync($"EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'{_dbName}'");
            await ExecuteDbCommandAsync("USE [master]");
            await ExecuteDbCommandAsync($"ALTER DATABASE [{_dbName}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE");
            await ExecuteDbCommandAsync("USE [master]");
            await ExecuteDbCommandAsync($"DROP DATABASE [{_dbName}]");

            await Connection.DisposeAsync();
        }

        public Task ResetDatabaseAsync()
        {
            return _emptyDatabaseCheckpoint.Reset(Connection);
        }

        private async Task ExecuteDbCommandAsync(string commandText)
        {
            using var cmd = Connection.CreateCommand();
            cmd.CommandText = commandText;
            await cmd.ExecuteNonQueryAsync();
        }
    }