Handling different ConnectionStates before opening SqlConnection

By : wnka
Source: Stackoverflow.com
Question!

If you need to open a SqlConnection before issuing queries, can you simply handle all non-Open ConnectionStates in the same way? For example:

    if (connection.State != ConnectionState.Open)
    {
        connection.Open();
    }

I read somewhere that for ConnectionState.Broken the connection needs to be closed before its re-opened. Does anyone have experience with this? Thanks-

By : wnka


Answers

You can handle it the same way. I was getting numerous connection state == broken while using IE9. There is something fundamentally wrong with IE9 in this regard since no other browser had this issue of broken connection states after 5 or 6 updates to the database tables. So I use object context. So basically just close it and re-open it.

I have this code before all my reads and updates in the businss logic layer:

if (context.Connection.State == System.Data.ConnectionState.Broken)
{
    context.Connection.Close();
    context.Connection.Open();
}


http://msdn.microsoft.com/en-us/library/system.data.connectionstate.aspx

Broken connection state does need to be closed and reopened before eligible for continued use.

Edit: Unfortunately closing a closed connection will balk as well. You'll need to test the ConnectionState before acting on an unknown connection. Perhaps a short switch statement could do the trick.

By : ddc0660


This isn't directly answering your question, but the best practice is to open and close a connection for every access to the database. ADO.NET connection pooling ensures that this performs well. It's particularly important to do this in server apps (e.g. ASP.NET), but I would do it even in a WinForms app that accesses the database directly.

Example:

using(SqlConnection connection = new SqlConnection(...))
{
   connection.Open();
   // ... do your stuff here

}  // Connection is disposed and closed here, even if an exception is thrown

In this way you never need to check the connection state when opening a connection.

By : Joe


This video can help you solving your question :)
By: admin