LocalDB connections visible in SQL management studio

Question

In my unit tests, I'm using a SQL Server LocalDB database. You could be nit picky and say that because of that fact it's not unit tests but integration tests and you would be right, but the point is that I am using the MSTest Framework to run those tests. Every test is copying an existing database and running their one test on this database.

private NAMETestSystem([CallerMemberName] string testCase = null)
{
    this.destinationDirectory = Path.Combine(Directory.GetCurrentDirectory(), testCase ?? "Undefined_" + Guid.NewGuid().ToString("N"));

    var connectionString = $"Data Source=(LocalDB)\\MSSQLLocalDB; Integrated Security = True; AttachDbFilename ={Path.Combine(this.destinationDirectory, "NAMEIntegrationTest.mdf")}";

    var entityFrameworkData = $"metadata=res://*/NAME.csdl|res://*/NAME.ssdl|res://*/NAME.msl;provider=System.Data.SqlClient;provider connection string=\"{connectionString}\"";

    // [...]

    Copy(SourceDirectory, this.destinationDirectory);

My "problem" is that each of those copies pops up in my SQL Server management studio. All 100+ or them. I don't need them there. They don't exist anymore. And to make things worse, you cannot batch-detach... I have to press Del+Enter about 150 times just to clear that window up.

Is there a way to not have my temporary local db instances appear in my SQL server management studio?

Maybe special way to close or dispose, something in the connection string I can set? Or maybe a way to detach all of them at the same time in management studio?

Can't you drop these dbs in `[TestCleanup]`? Done that in a previous role. — zaitsman, Feb 06 '18 at 12:58
@zaitsman I already `.Dispose()` the connections at the end of my test by disposing the `IServiceProvider` that is creating them... can I do something differently in `[TestCleanUp]`? How would I "drop" those? — nvoigt, Feb 06 '18 at 13:21
You would connect to localdb engine and execute a `DROP DATABASE` statement — zaitsman, Feb 06 '18 at 13:30

score 0 · Accepted Answer · answered Jun 07 '18 at 14:16

So in the end, what I did and what is working fine for now is this:

    public void Dispose()
    {
        // disposing other stuff 

        // sql db
        if (Directory.Exists(this.destinationDirectory))
        {
            DetachDatabase(this.connectionString);
            Directory.Delete(this.destinationDirectory, true);
        }
    }

    public static void DetachDatabase(string connectionString)
    {
        using (var connection = new SqlConnection(connectionString))
        {
            connection.Open();

            using (var command = connection.CreateCommand())
            {
                var sql = "DECLARE @dbName NVARCHAR(260) = QUOTENAME(DB_NAME());\n" +
                          "EXEC('ALTER DATABASE ' + @dbName + ' SET OFFLINE WITH ROLLBACK IMMEDIATE;');\n" + 
                          "EXEC('exec sp_detach_db ' + @dbName + ';');";

                command.CommandText = sql;
                command.ExecuteNonQuery();
            }
        }
    }

Might not be the prettiest solution, but at least it keeps my sanity intact while the number of tests (and number of databases) rises.

LocalDB connections visible in SQL management studio

1 Answers1