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

Question

I am experimenting with ADO.Net and EF to better understand how it handles SQL Server connections.

I found something very interesting with ADO.Net. I am creating several tasks that trigger a simple SQL script insert with proper expectation for posting SqlConnectionand SqlCommand. Nothing special here, but when 10k tasks end, all SQL joins are still hanging (I confirmed by running sp_who). The only way to clear these connections is to close the application instance.

How is this possible? I tried many things to make it close: = null data access instance + forced GC, but nothing ...

I am trying to understand 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

ADO.Net . ( ):

. . , . , Open , . , . Close , . , , .