Why is the SqlConnection still open after forcing .Close() and with using(...)?

Question

I'm doing some experimentation with ADO.Net and EF in order to better understand how it handles the SQL Server connections.

I found something very interesting with ADO.Net. I am creating multiple tasks that calls a simple insert SQL script with the proper wait to dispose SqlConnection and SqlCommand. Nothing special here, but when the 10k tasks finishes processing, all the SQL connections are still hanging (I confirmed by running sp_who). The only way those connections get cleared up is when the application instance is closed.

How is that even possible? I tried many things to force it to close, = null the the data access instance + forced GC but nothing...

I'm trying to make sense from this behavior but I am failing. Any clues?

static void Main(string[] args)
{
    Console.WriteLine(DateTime.Now.ToString("HH:mm:ss"));

    for (int i = 0; i < 10000; i++)
    {
        Task.Run(() =>
            {
                var dbLegacy = new DataAccessLegacy();
                dbLegacy.TableBInsert();
                dbLegacy = null;
            });
    }

    Console.ReadKey();
}

public void TableBInsert()
{
    using (SqlConnection connection = new SqlConnection(@"Password=qpqp;Persist Security Info=True;User ID=sqlUser2;Initial Catalog=DatabaseA;Data Source=VM2HOSTNAME\VM2INSTANCEA"))
    {
        using (SqlCommand command = new SqlCommand("DatabaseBInsert", connection))
        {
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add("ColAInt", SqlDbType.Int);
            command.Parameters[0].Value = (new Random()).Next(0, 5000);

            command.Parameters.Add("ColBTinyInt", SqlDbType.TinyInt);
            command.Parameters[1].Value = (new Random()).Next(1, 255);

            command.Parameters.Add("ColCVarchar", SqlDbType.VarChar);
            command.Parameters[2].Value = Convert.ToChar((new Random()).Next(1, 255)).ToString();

            command.Parameters.Add("ColDVarcharMax", SqlDbType.VarChar);
            command.Parameters[3].Value = Convert.ToChar((new Random()).Next(1, 255)).ToString();

            command.Parameters.Add("ColEDecimal", SqlDbType.Decimal);
            command.Parameters[4].Value = (new Random()).Next(0, 5000) + 0.5;

            command.Parameters.Add("ColFSmallInt", SqlDbType.SmallInt);
            command.Parameters[5].Value = (new Random()).Next(0, 5000);

            command.Parameters.Add("ColGDateTime", SqlDbType.DateTime);
            command.Parameters[6].Value = DateTime.Now;

            command.Parameters.Add("ColHChar", SqlDbType.Char);
            command.Parameters[7].Value = Convert.ToChar((new Random()).Next(1, 255)).ToString();

            command.Parameters.Add("ColINVarchar", SqlDbType.NVarChar);
            command.Parameters[8].Value = Convert.ToChar((new Random()).Next(1, 255)).ToString();

            command.Parameters.Add("ColJNChar", SqlDbType.NChar);
            command.Parameters[9].Value = Convert.ToChar((new Random()).Next(1, 255)).ToString();

            connection.Open();
            command.ExecuteScalar();
            connection.Close();

            command.Dispose();
        }

        connection.Dispose();
    }
}

Answer 1

By default, ADO.Net uses connection pooling. From the docs (emphasis mine):

Connection pooling reduces the number of times that new connections must be opened. The pooler maintains ownership of the physical connection. It manages connections by keeping alive a set of active connections for each given connection configuration. Whenever a user calls Open on a connection, the pooler looks for an available connection in the pool. If a pooled connection is available, it returns it to the caller instead of opening a new connection. When the application calls Close on the connection, the pooler returns it to the pooled set of active connections instead of closing it. Once the connection is returned to the pool, it is ready to be reused on the next Open call.