How to refresh the state of a SqlConnection?

I have this code:

if (  con.SqlConnection.State == System.Data.ConnectionState.Broken ||
      con.SqlConnection.State == System.Data.ConnectionState.Closed      
){
    con.SqlConnection.Open();
}

I have lost connection to开发者_开发技巧 the network. When i come to my if, then my SqlConnection.State still says open.

How do i refresh my state of my SqlConnection

This is one of the few cases where you should rely on exceptions.

Microsoft actually recommends that you react to an exception versus check that state variable. Think of the case where you check the variable, and then a nanosecond later the connection goes down.

I believe the two that would get thrown are InvalidOperationException or SqlException.

On TCP connections, there is only one guaranteed way to understand if the connection is still alive or not, it is sending a new packet to the open socket.

So as long as we assume you're connected to Sql Server through a TCP connection, this rule applies to it also. So you need to do send data and wait if an exception is raised.

    public bool IsConnected()
    {
        if (db == null) //db here is an Entity
            return false;

        //in my case Connection is an sqlconnection object so you can 
        //apply the same to your connection
        if (db.Database.Connection.State == ConnectionState.Closed)
            return false;

        try
        {
            var cmd = db.Database.Connection.CreateCommand();
            cmd.CommandText = "SELECT 0";
            cmd.ExecuteNonQuery();
        }
        catch
        {
            return false;
        }

        return true;
    }

You can always monitor the state of your SqlConnection by using the SqlConnection.StateChange Event

The lazy way: "SELECT 0". If the command fails, the connection state is updated to "Closed"

if (DBConn.State == ConnectionState.Open)
{
   try
   {
      using(SqlCommand cmd = new SqlCommand(DBConn))
      {
         cmd.CommandText = "SELECT 0";
         cmd.ExecuteNonQuery();
      }
   }
   Catch
   {
      DBConn.Close(); \\Declare the connection dead.
   }
}