SqlConnection/SqlCommand keeps database in use after Close and Dispose

Question

I am trying to create a temporary database for the purpose of integration testing in Xunit, but when I try to drop the temporary database, I get an error:

Cannot drop database "TempDatabase_[numbers]" because it is currently in use.

Simply closing and disposing the command and connection does not seem to cut it.

This is a trimmed-down version of my test that is failing:

using System;
using System.Data.SqlClient;
using Xunit;

namespace Test
{
    public class Test_Raw_Spec
    {
        [Fact]
        public void PerformWorkInTemporaryDatabase()
        {
            string connectionStringTemplate = "Data Source=SQLEXPRESS;Initial Catalog={0};Integrated Security=SSPI;Connection Timeout=10";
            int dbNum = (new Random()).Next() % 1000000;
            int tblNum = (new Random()).Next() % 1000000;

            string nameTempDb = $"TempDatabase_{dbNum}";
            string nameTempTable = $"TempTable_{tblNum}";

            var sqlConnection1 = new SqlConnection(string.Format(connectionStringTemplate, "master"));
            var sqlCommand1 = new SqlCommand($"CREATE DATABASE {nameTempDb}", sqlConnection1);
            sqlConnection1.Open();
            sqlCommand1.ExecuteNonQuery();
            sqlCommand1.Dispose();
            sqlConnection1.Close();
            sqlConnection1.Dispose();

            var sqlConnection2 = new SqlConnection(string.Format(connectionStringTemplate, nameTempDb));
            var sqlCommand2 = new SqlCommand($"CREATE TABLE {nameTempTable}(id int)", sqlConnection2);
            sqlConnection2.Open();
            sqlCommand2.ExecuteNonQuery();
            sqlCommand2.Dispose();
            sqlConnection2.Close();
            sqlConnection2.Dispose();

            var sqlConnection3 = new SqlConnection(string.Format(connectionStringTemplate, "master"));
            var sqlCommand3 = new SqlCommand($"DROP DATABASE {nameTempDb}", sqlConnection3);
            sqlConnection3.Open();
            sqlCommand3.ExecuteNonQuery();
            sqlCommand3.Dispose();
            sqlConnection3.Close();
            sqlConnection3.Dispose();
        }
    }
}

Answer 1

This is because of Connection Pooling. When you close and dispose of the connection it is released back to the pool ready to be used again instead of being destroyed. Creating and destroying connections over and over again is a very expensive process and so connection pools are used in an attempt to improve the overall performance of your application. Connections are destroyed when they are finalised (when the pool is recycled or restarted such as when your application starts up or shuts down.)

Additionally, you could be more efficient in your usage of command and connections. You can change the text of a command if it is done executing. If you don't want to do this you could at least reuse the connection:

private void Execute()
{
    using (var connection = new SqlConnection("."))
    {
        connection.Open();

        using (var command = connection.CreateCommand())
        {
            command.CommandText = "CREATE DATABASE [test]";
            command.ExecuteNonQuery();

            connection.ChangeDatabase("test");
            command.CommandText = "CREATE TABLE [dbo].[MyTable] (id int)";
            command.ExecuteNonQuery();
            
            // you must change your db context to drop the database
            connection.ChangeDatabase("master");
            command.CommandText = "DROP DATABASE [test]";
            command.ExecuteNonQuery();
        }
    }
}