Connection does not close like it should

Question

I am encountering the following error in my ASP project:

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

While calling the .open() function on a SqlConnection Object.

I have tried this :

    if (Conn.State != ConnectionState.Closed)
    {
        Log.Message(xxx);
        try
        {
            Conn.Close();
        }
        catch (Exception ex)
        {
            Log.Error(xxxx);
        }
    }
    Conn.Open();

But this still raises the error. The Conn object is declared as:

private static readonly SqlConnection Conn = new SqlConnection(xxxx);

Any idea where I should look for a solution

Answer 1

Here's the pattern.

using(var conn = new SqlConnection(connectionString))
using(var cmd = new SqlCommand(someSql, conn)
{
    conn.Open();
    cmd.ExecuteNonQueryOrWhatevs();
}

1. Create your connection
2. Open your connection
3. Dispose of your connection

Don't try to reuse it. Just get it, use it, and dispose of it as fast as possible.

Also, none of this is thread safe, so don't be touching any of the above instances from different threads. One thread to use the connection only, please. Feel free to use multiple threads to process the results.