How to check if a database connection is still open in Ado.Net?

Question

Is there a way to check if a database connection is open? I am trying to check the connectionState but it shows ConnectionState as Open even when database is down.

private bool IsValidConnection(IDbConnection connection)
        {
            return (connection != null && connection.State == ConnectionState.Open);
        } 

Answer 1

That looks like the correct code to check if your DB connection is still open.

I would expect to see an exception thrown on your connection.Open() when the database is down. Is the database down before you open the connection or does it go down after the open?

Answer 2

While I don't have an answer, I can tell you why stuff like this happens It basically happens with any protocol above TCP that does not send periodic heartbeats.

TCP provides no way to detect that the remote end is closed, without sending any data, and even then it can take considerable time to detect the failure of the remote end.

In an ideal world, the server would send a TCP FIN packet when it goes down, but this does not happen if someone yanks out the network cable, the server crashes hard or an inbetween NAT gateway/firewall silently drops the connection. This results in the client not knowing the server is gone, and you'll have to send it something and assume the server is gone of you don't receive a response within a reasonable time, or an error occurs (typically the first few send() call after a server is silently gone won't error out).

So, if you want to make sure a DB Conenction is ok, issue a select 1;` query. Some lower level DB APIs might have a ping()/isAlive() or similer method that could be used for the same purpose.

Answer 3

a simple way would be to code a button with query, of retrieve some data from some table of the database, if an error is returned catch it.

that would show if connection there or not.