CODEWITHSUNDEEP
c#ado.netdatatable
Paritosh
Paritosh

Reputation: 4503

connection current state is connecting error message

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.

Upvotes: 1

Views: 6044

Answers (2)

Tim Schmelter
Tim Schmelter

Reputation: 460238

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

Upvotes: 4

Shyju
Shyju

Reputation: 218852

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

Upvotes: 0

Related Questions