If you have multiple threads trying to execute an SqlCommand via ExecuteNonQuery using a single SqlConnection object, you may get the following exception:
System.InvalidOperationException: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.
The following code replicates the problem:
Dim conn As New SqlConnection("Data Source=localhost;User Id=sa; Password=; Initial Catalog=test")
Sub Main()
conn.Open()
For i As Integer = 0 To 1000
Dim th As New Threading.Thread(AddressOf TestSqlThread)
th.Start(i)
NextEnd Sub
Private Sub TestSqlThread(ByVal state As Object)
Console.WriteLine(state)
Dim cmdLocal As New SqlCommand("INSERT INTO testtable(col1, col2) VALUES('H', 'Hello2')", conn)
cmdLocal.ExecuteNonQuery()
cmdLocal.Dispose()
End Sub
What the above code does is to open a single connection and create multiple threads which call ExecuteNonQuery on the connection which was created. The multiple threads do not wait for each other - they execute concurrently. The previous call to ExecuteNonQuery may not have been finished before the next one comes in. So there may be concurrent attempts to call ExecuteNonQuery to update the database. .NET only supports up to a maximum number of concurrent calls to ExecuteNonQuery on a single SqlConnection object. When the limit is reached, the exception mentioned above is thrown.
A workaround is to use a lock to make sure you call ExecuteNonQuery one after another, and not concurrently:
Dim mylock as new object
Private Sub TestSqlThread(ByVal state As Object)
Console.WriteLine(state)
Dim cmdLocal As New SqlCommand("INSERT INTO testtable(col1, col2) VALUES('H', 'Hello2')", conn)
Synclock MyLock
cmdLocal.ExecuteNonQuery()
End Synclock
cmdLocal.Dispose()
End Sub
However, this will have a performance impact as the query will be executed one by one and slow down the application. A better alternative would be to create a separate SqlConnection object for each thread that needs to call ExecuteNonQuery.
Read More »
System.InvalidOperationException: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.
The following code replicates the problem:
Dim conn As New SqlConnection("Data Source=localhost;User Id=sa; Password=; Initial Catalog=test")
Sub Main()
conn.Open()
For i As Integer = 0 To 1000
Dim th As New Threading.Thread(AddressOf TestSqlThread)
th.Start(i)
NextEnd Sub
Private Sub TestSqlThread(ByVal state As Object)
Console.WriteLine(state)
Dim cmdLocal As New SqlCommand("INSERT INTO testtable(col1, col2) VALUES('H', 'Hello2')", conn)
cmdLocal.ExecuteNonQuery()
cmdLocal.Dispose()
End Sub
What the above code does is to open a single connection and create multiple threads which call ExecuteNonQuery on the connection which was created. The multiple threads do not wait for each other - they execute concurrently. The previous call to ExecuteNonQuery may not have been finished before the next one comes in. So there may be concurrent attempts to call ExecuteNonQuery to update the database. .NET only supports up to a maximum number of concurrent calls to ExecuteNonQuery on a single SqlConnection object. When the limit is reached, the exception mentioned above is thrown.
A workaround is to use a lock to make sure you call ExecuteNonQuery one after another, and not concurrently:
Dim mylock as new object
Private Sub TestSqlThread(ByVal state As Object)
Console.WriteLine(state)
Dim cmdLocal As New SqlCommand("INSERT INTO testtable(col1, col2) VALUES('H', 'Hello2')", conn)
Synclock MyLock
cmdLocal.ExecuteNonQuery()
End Synclock
cmdLocal.Dispose()
End Sub
However, this will have a performance impact as the query will be executed one by one and slow down the application. A better alternative would be to create a separate SqlConnection object for each thread that needs to call ExecuteNonQuery.
