C# Database Integration Test Setup (NUnit)

readme.md

C# Database Integration Test Setup (NUnit)

Details borrowed from Pluralsight

Details

• Database is initialized once during every run on a test suite.
• The build server will have LocalDB installed.
• Integration tests are run
• Database is removed and deleted
• Used the following packages to parse files with GO statements
  • Microsoft.SqlServer.Smo.dll
  • Microsoft.SqlServer.SqlEnum.dll
  • Microsoft.Web.Infrastructure

DatabaseInit.cs

[SetUpFixture]
public class DatabaseInit
{
    private static string _runningPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);

    [OneTimeSetUp]
    public void SetUpDatabase()
    {
        Debug.WriteLine("SetUpDatabase");
        DestroyDatabase();
        CreateDatabase();
    }

    [OneTimeTearDown]
    public void TearDownDatabase()
    {
        Debug.WriteLine("TearDownDatabase");
        DestroyDatabase();
    }

    private static void CreateDatabase()
    {
        ExecuteSqlCommand(Master, $@"
            CREATE DATABASE [MY_DATABASE_NAME]
            ON (NAME = 'MY_DATABASE_NAME',
            FILENAME = '{Filename}')");

        // seed from file (possibly from migration)
        var sqlFile = Path.Combine(_runningPath, "Seed.sql");
        var sql = File.ReadAllText(sqlFile);
        ExecuteSqlCommand(GetSqlConnectionStringBuilder("MY_DATABASE_NAME"), sql);
    }

    private static void DestroyDatabase()
    {
        var fileNames = ExecuteSqlQuery(Master, @"
            SELECT [physical_name] FROM [sys].[master_files]
            WHERE [database_id] = DB_ID('MY_DATABASE_NAME')",
            row => (string)row["physical_name"]);

        if (fileNames.Any())
        {
            ExecuteSqlCommand(Master, @"
                ALTER DATABASE [MY_DATABASE_NAME] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
                EXEC sp_detach_db 'MY_DATABASE_NAME'");

            fileNames.ForEach(File.Delete);
        }
    }

    private static void ExecuteSqlCommand(
        SqlConnectionStringBuilder connectionStringBuilder,
        string commandText)
    {
        using (var connection = new SqlConnection(connectionStringBuilder.ConnectionString))
        {
            //connection.Open();
            //using (var command = connection.CreateCommand())
            //{
            //    command.CommandText = commandText;
            //    command.ExecuteNonQuery();
            //}

            // using SMO Dlls to parse files with "GO"
            Server server = new Server(new ServerConnection(connection));
            server.ConnectionContext.ExecuteNonQuery(commandText);
        }
    }

    private static List<T> ExecuteSqlQuery<T>(
        SqlConnectionStringBuilder connectionStringBuilder,
        string queryText,
        Func<SqlDataReader, T> read)
    {
        var result = new List<T>();
        using (var connection = new SqlConnection(connectionStringBuilder.ConnectionString))
        {
            connection.Open();
            using (var command = connection.CreateCommand())
            {
                command.CommandText = queryText;
                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        result.Add(read(reader));
                    }
                }
            }
        }
        return result;
    }

    private static SqlConnectionStringBuilder Master => GetSqlConnectionStringBuilder("master");

    private static SqlConnectionStringBuilder GetSqlConnectionStringBuilder(string database)
    {
        return new SqlConnectionStringBuilder
        {
            DataSource = @"(LocalDB)\MSSQLLocalDB",
            InitialCatalog = database,
            IntegratedSecurity = true
        };
    }

    private static string Filename => Path.Combine(_runningPath,"MY_DATABASE_NAME.mdf");
}