[C#] Check if SQL Connection is Open or Closed


Answers

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

Question

How do you check if it is open or closed I was using

 if (SQLOperator.SQLCONNECTION.State.Equals("Open"))

however, even the State is 'Open' it fails on this check.




I use the following manner sqlconnection.state

if(conexion.state != connectionState.open())
   conexion.open();



Check if a MySQL connection is open

ConnectionState state = connection.State;
if (state == ConnectionState.Open)
{
    return true;
}
else
{
    connection.Open();
    return true;
}



This code is a little more defensive, before opening a connection, check state. If connection state is Broken then we should try to close it. Broken means that the connection was previously opened and not functioning correctly. The second condition determines that connection state must be closed before attempting to open it again so the code can be called repeatedly.

// Defensive database opening logic.

if (_databaseConnection.State == ConnectionState.Broken) {
    _databaseConnection.Close();
}

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



Links