Clean database before executing a test in .NET

Question

Scenario

We're working on a set of integration tests that make use of the application database. For this purpose we already switched the database to a separate test database that's only used during integration tests.

The test database is reset after each test to it's original state, by restoring a SQL Server database snapshot. This works okay, but it's given us quit a headache to set up.

Question

Are there any tools that make cleaning up a database before or after an automated test easier?

We are using MSTest as our test framework, but I'm open to any suggestions that require a different test framework for the database cleaning tool to work.

Answer 1

Respawn can also be used.

The description from the README.md file in the GitHub repo. (https://github.com/jbogard/Respawn)

"Respawn is a small utility to help in resetting test databases to a clean state. Instead of deleting data at the end of a test or rolling back a transaction, Respawn resets the database back to a clean checkpoint by intelligently deleting data from tables."

Example with xUnit, SQL Server and Dapper.

using System.Data.Common;
using System.Threading.Tasks;
using Dapper;
using Microsoft.Data.SqlClient;
using Respawn;
using Xunit;

namespace TestRespawn
{
    public class ResetSqlServerFixture : IAsyncLifetime
    {
        private readonly Checkpoint _checkpoint;
        private readonly string _connectionString;

        public ResetSqlServerFixture(string connectionString)
        {
            _connectionString = connectionString;

            Connection = new SqlConnection(connectionString);

            _checkpoint = new Checkpoint();
        }

        public DbConnection Connection { get; }

        public virtual Task DisposeAsync() => _checkpoint.Reset(_connectionString);

        public virtual Task InitializeAsync() => Task.CompletedTask;
    }

    // Usage
    public class ExampleTests : ResetSqlServerFixture
    {
        public ExampleTests()
            : base(@"Server=(localdb)\MSSQLLocalDB;Integrated Security=true;")
        {
        }

        [Fact]
        public async Task GetVersion()
        {
            // Arrange
            var sqlQuery = "SELECT @@Version";

            // Action
            var version = await this.Connection.ExecuteScalarAsync(sqlQuery) as string;

            // Assert
            Assert.Contains("Microsoft", version);
        }
    }
}