CODEWITHSUNDEEP
c#.netsql-serversqlconnectionsqlcommand
VeeTheSecond
VeeTheSecond

Reputation: 3186

SqlConnection/SqlCommand keeps database in use after Close and Dispose

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();
        }
    }
}

Upvotes: 3

Views: 1091

Answers (2)

Mr.Brownstone
Mr.Brownstone

Reputation: 724

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();
        }
    }
}

Upvotes: 6

coolblue2000
coolblue2000

Reputation: 4948

Have you tried waiting for a short time before dropping the db? It could well be that the connection has not managed to close in time.

Also you do not need to close the connection, calling dispose will do it all for you anyway. Plus it is best to do this in a using statement

using(var sqlConnection1 = new SqlConnection(string.Format(connectionStringTemplate, "master")))
{
//do your stuff here
}

This way you do not have to worry about closing anything as it will automatically do so at the end of the using block.

I also would not recommend opening loads of connections. One connection will suffice as opening connections is expensive. So create one (or two in your case) and then reuse it for each command.

Upvotes: 1

Related Questions