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

Mr.Brownstone · Accepted Answer · 2020-12-20 20:13:13Z

6

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

edited Dec 20, 2020 at 20:13

answered Dec 20, 2020 at 19:58

Mr.Brownstone

7246 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Mr.Brownstone Over a year ago

The pool is implemented in the SqlClient library. You get one pool per unique connection string and is not related to the developers use of the 'using' statement.

coolblue2000 Over a year ago

Yep, you are correct. I realised just after I typed the comment and so deleted it...

Richard Over a year ago

ALTER DATABASE [test] SET SINGLE_USER WITH ROLLBACK AFTER 5 will force all other connections off the db after 5s (eg. other connections in the pool).

VeeTheSecond Over a year ago

I found out you can also add Pooling=false to your connection string, which ends up also clearing the connection pool (see stackoverflow.com/questions/7004701/…). Since Integration Testing is supposed to be a smaller subset of all your tests, this would have been a viable alternative for me as well.

coolblue2000 · Accepted Answer · 2020-12-20 19:53:10Z

1

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.

answered Dec 20, 2020 at 19:53

coolblue2000

5,11614 gold badges46 silver badges77 bronze badges

2 Comments

VeeTheSecond Over a year ago

Adding a 120 second delay between connections 2 and 3 (System.Threading.Thread.Sleep(2 * 60 * 1000);) does not seem to help. I like your other suggestions, and will implement them once I get a baseline working.

coolblue2000 Over a year ago

try calling sqlConnection2.ClearAllPools() before dropping the db

Collectives™ on Stack Overflow

SqlConnection/SqlCommand keeps database in use after Close and Dispose

2 Answers 2

4 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related