Check if SQL Connection is Open or Closed

Solution 1:

You should be using SqlConnection.State

e.g,

using System.Data;

if (myConnection != null && myConnection.State == ConnectionState.Closed)
{
   // do something
   // ...
}

Solution 2:

Here is what I'm using:

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

The reason I'm not simply using:

if (mySQLConnection.State == ConnectionState.Closed)
{
    mySQLConnection.Open();
}

Is because the ConnectionState can also be:

Broken, Connnecting, Executing, Fetching

In addition to

Open, Closed

Additionally Microsoft states that Closing, and then Re-opening the connection "will refresh the value of State." See here http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.state(v=vs.110).aspx

Solution 3:

The .NET documentation says: State Property: A bitwise combination of the ConnectionState values

So I think you should check

!myConnection.State.HasFlag(ConnectionState.Open)

instead of

myConnection.State != ConnectionState.Open

because State can have multiple flags.