Random behavior: "The connection was not closed. The connection's current state is open."

Question

This is very strange, it only happens sometimes although this piece of code is executed often.

I have this code running like every 5 seconds and it works fine, but sometimes i get the exception saved on my log.

Exception msg: The connection was not closed. The connection's current state is open.

 public class Log
{

    static SqlConnection sqlConnection{get; set;}

    public Log()
    {
        sqlConnection = new SqlConnection(CNN_STRING);
    }



    public static void Save(){
        StringBuilder sb = new StringBuilder();

        sb.Append("UPDATE Blabla SET ...");

        SqlCommand command = new SqlCommand(sb.ToString(), sqlConnection);


         try
        {
            if (sqlConnection.State == ConnectionState.Open) { sqlConnection.Close(); }
            sqlConnection.Open();
            command.ExecuteNonQuery();
            sqlConnection.Close();
        }catch(Exception e){
            Log.AddLog("Log ", 1, string.Concat("Query: ", sb.ToString(), 
                       "Exception    message: ", e.Message));
        }
        finally
        {
            if (sqlConnection.State == ConnectionState.Open) { sqlConnection.Close(); }
        }
   }

any clues?

Consider to use [`using` statement](http://msdn.microsoft.com/en-us/library/yh598w02.aspx) to dispose your `SqlConnection` and `SqlCommand`. — Soner Gönül, Mar 27 '14 at 15:11
If the connection is already open, why close it and re-open it again? It isn't the fastest and there are costs. Odds are the connection is still closing when you are trying to open the connect again. — Justin, Mar 27 '14 at 15:12
Show the declaration of `sqlConnection`, is it `static`? Then make it a local variable and you're done. — Tim Schmelter, Mar 27 '14 at 15:13
@TimSchmelter it is static, because the method is also static — dyk, Mar 27 '14 at 15:17
i mean, it is a static class, that have some static functions which use the connection — dyk, Mar 27 '14 at 15:18

score 2 · Accepted Answer · edited May 23 '17 at 11:51

2

A static connection is not a good idea with ASP.NET since every request would share this connection which will cause locks at it's best or even errors like yours.

Maybe this answer i've posted some time ago is helpful. So don't make it static and use the using-statement.

using(var sqlConnection = new SqlConnection(CNN_STRING))
using (var command = new SqlCommand(sb.ToString(), sqlConnection))
{
    sqlConnection.Open();
    command.ExecuteNonQuery();
}

You don't need to close the connection since that is done by the using-statement(dispose).

edited May 23 '17 at 11:51

Community

1
1

answered Mar 27 '14 at 15:24

Tim Schmelter

450,073
74
686
939

thanks for the link, now i have to change 600 lines of code because i encapsulate ADO.NET functionality into a DB helper Classes! – dyk Mar 27 '14 at 15:32
by the way, "using" will always close/dispose the connection right? – dyk Mar 27 '14 at 15:37

Random behavior: "The connection was not closed. The connection's current state is open."

1 Answers1