Why isn't ODBCConnection.State reliable?

Question

Consider a simple setup:

// _conn is the OdbcConnection with a MySQL-Server (MySQL-Connector 3.51)
// _cmd is a created OdbcCommand

// Constructor has created the objects successfully

public void DoSomething() {
    if(_conn.State == ConnectionState.Open)
        _cmd.ExecuteNonQuery();
}

Now my problem is, that OdbcConnection.State isn't reliable. The thing is that after some time the connection gets lost, but the State-Property doesn't know anything about it and keeps telling me that the connection is open at least till the point were I try to execute a command (which fails gracefully). I even had the situation were the State-Property would never refresh and kept telling me that the connection was still there (but commands failed).

Of course I could add Try {...} Catch {...} blocks to my code, but I tried to avoid them because extending a two-line function with at least four lines of error handling is a little bit...heavy.

So my question is: Why isn't OdbcConnection.State reliable and can I fix it?

Answer 1

The nature of networking is that you often don't know there's a problem until you actually try to send data. If you simply pull the plug out of the back of the computer (or you pull the plug out of a switch somewhere in between your client and the server) then there's no way for the operating system to know until it actually tries to send data that there's even a problem.

The reason, then, that State is not reliable is because it's impossible to make it completely reliable without actually actually trying to send data to the server. Since that's far too much work for a simple property to do (a whole network roundtrip) it doesn't do that, and just does the simplest thing possible.

Also, there's the fact that there's a race condition between when you call State == Open and actually executing the command: you might call State == Open, and then someone pulls the plug before you execute the command.

So at the end of the day, you're going to have to have that exception handler anyway. I would also suggest that you don't put the exception around every single call to the database. If you're doing a website, then have a page-level handler and leave it at that. There's really no point trying "handle" a database going down in any other way than by displaying an error message to the user anyway...