SQLServer UnitTesting in Visual Studio - DROP DATABASE [MyDb]-CodePudding

I've set up a Unit Test project in Visual studio for my SQL Server project.

The test itself work, and the setup includes deploying that database. My problem is that I want to have a "clean slate" test, and every time I run a test, the data accumulates.

I tried manually calling a 'DROP DATABASE' from the SqlDatabaseSetup.cs, but it seems that I don't have a DataConnection at this point.

[TestClass()]
public class SqlDatabaseSetup
{

    [AssemblyInitialize()]
    public static void InitializeAssembly(TestContext ctx)
    {
        var q = ctx.DataConnection.CreateCommand();
        q.CommandText = "DROP DATABASE MyDb;";
        q.ExecuteNonQuery();

        // Setup the test database based on setting in the
        // configuration file
        SqlDatabaseTestClass.TestService.DeployDatabaseProject();
        SqlDatabaseTestClass.TestService.GenerateData();
    }

}

Is there anyway to indicate that the DB should be flushed first (without manually calling DELETE FROM XX for every table), or is there a way I can pass through a command to do that for me?

CodePudding user response：

The solution was as follows :

    [AssemblyInitialize()]
    public static void InitializeAssembly(TestContext ctx)
    {
        // Setup the test database based on setting in the
        // configuration file
        try
        {
            var conn = SqlDatabaseTestClass.TestService.OpenExecutionContext();
            var cmd = conn.Connection.CreateCommand();
            cmd.CommandText = "use master; ALTER DATABASE [MyTestDb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;DROP DATABASE MyTestDb;";
            cmd.ExecuteNonQuery();
        }
        catch
        {

        }
        SqlDatabaseTestClass.TestService.DeployDatabaseProject();
        SqlDatabaseTestClass.TestService.GenerateData();
    }