connection current state is connecting error message

Question

I keep getting this error randomly:

System.Web.Services.Protocols.SoapException: System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.InvalidOperationException: The connection was not closed. The connection's current state is connecting.

The code it's complaning about is below:

        DataSet ds = new DataSet(); 
        cn = new SqlConnection(GetDBConnectionString()); 

        using (cn) 
        { 
            try 
            { 
                SqlCommand cmd = new SqlCommand("uspGetNavigationItems", cn); 
                cmd.CommandType = CommandType.StoredProcedure; 
                cn.Open(); 
                SqlDataAdapter adp = new SqlDataAdapter(cmd); 
                adp.Fill(ds, "NavItems"); 
            } 
            catch (Exception ex) 
            { 
                ds = null; 
                throw ex; 
            } 
            finally 
            { 
                if (cn.State != ConnectionState.Closed) 
                { 
                    cn.Close(); 
                } 
            } 
        } 

        if (ds.Tables.Count > 0) 
        { 
            if (ds.Tables[0].Rows.Count > 0) 
            { 
                return ds.Tables[0]; 
            } 
            else 
            { 
                return null; 
            } 
        } 
        else 
        { 
            return null; 
        }

I don't understand where the problem is, why it's saying the connection is connecting, when I have a finally to clean it up. Is it because i'm using Finally to close and the using statement, which is supposed to close it as well? Again this happens randomly not always, that's why i'm not sure what's going on.

Thank you.

score 4 · Accepted Answer · edited May 23 '17 at 10:34

You don't need to close the connection in finally if you're using the using-statement since it will close it from dispose implicitely.

Rule of thumb: use the using-statement for every class implementing IDisposable(like Connections,DataAdapter,Commands). On the other hand, a DataSet or a DataTable does not implement it and does not need to be disposed manually or via using.

But change:

cn = new SqlConnection(GetDBConnectionString()); 
using (cn) 
{
    //code
}

to:

using (var cn = new SqlConnection(GetDBConnectionString())) 
{
    //code
}

This will be translated to:

SqlConnection cn = new SqlConnection(GetDBConnectionString());
try
{
    //code
}
finally
{
    if (cn != null)
       ((IDisposable)cn).Dispose();
}

Sidenote: throw instead of throw ex would keep the stacktrace. With throw ex you're hiding the original source of the exception.

https://stackoverflow.com/a/22628/284240

Along these lines: the command object should also be in a using statement. — NotMe, Mar 28 '12 at 14:29

score 0 · Answer 2 · answered Mar 28 '12 at 14:15

0

Take away that finally block as your using statement will take care of your Connection Closing

answered Mar 28 '12 at 14:15

Shyju

214,206
104
411
497

connection current state is connecting error message

2 Answers2