Why is SQL Connection closing unexpectedly

Question

In the following code, why is my connection closing on me? The code at the bottom works, so my application is functioning. I'm really just curious why the code at the top does not work.

using (var dbConn = new SqlConnection(_connectionStrings.SqlServer))
{
    var mySqlText = @"
                       ....  some SQL code here
                     ";

    widgets.ForEach(async widget =>
            {
                await dbConn.ExecuteAsync(mySqlText, widget);   <=== Connection is closed here 
            });
}

By contrast, this does work as expected.

var mySqlText = @"
                    ....  some SQL code here
                 ";
widgets.ForEach(async widget =>
        {
            using (var dbConn = new SqlConnection(_connectionStrings.SqlServer))
            {
                await dbConn.ExecuteAsync(mySqlText, widget); 
            }
        });

score 3 · Accepted Answer · edited Dec 29 '19 at 21:55

The problem is that you are executing in parallel with the same connection:

using (var dbConn = new SqlConnection(_connectionStrings.SqlServer))
{
    var mySqlText = @"
                       ....  some SQL code here
                     ";

    widgets.ForEach(async widget =>
            {
                await dbConn.ExecuteAsync(mySqlText, widget);   <=== Connection is closed here 
            });
}

Let me explain with the example:

Thread one, starts querying the database. It gets the connection from the connection pool and now waits
Thread two now wants to use the same database. It starts the query with the same connection.
The connection is now in use, so it looks like closed to the first one.

In your second solution, the .net framework gets an available connection from the connection pool. It might be the first one if it is freed already.

Check for best practices here. The second method is considered the best practice

Yes, `SqlConnection` is not *thread safe*; each thread should have *its own* connection +1 — Dmitry Bychenko, Dec 25 '19 at 10:20

Ivan Martinyuk · Answer 2 · 2019-12-25T09:05:05.560

It should works

using (var dbConn = new SqlConnection(_connectionStrings.SqlServer))
{
    var mySqlText = @"
                       ....  some SQL code here
                     ";

    foreach(var widget in widgets)
    {
        await dbConn.ExecuteAsync(mySqlText, widget); 
    }
}

Or

 using (var dbConn = new SqlConnection(_connectionStrings.SqlServer))
{
    var mySqlText = @"
                       ....  some SQL code here
                     ";
    var allTasks = widgets.Select(widget => dbConn.ExecuteAsync(mySqlText, widget));

    await Task.WhenAll(allTasks);
}

Full explanation How are async lambdas in the ForEach()-method handled?

Why is SQL Connection closing unexpectedly

2 Answers2