Handling Internet Connection Hiccups and Database Connections

Question

I realize that there is no way to atomically guarantee:

if(hasInternet)
    doDatabaseCall();

However, what is the correct way of handling connection problems when dealing with DbConnection and DbCommand objects in the .NET world? I'm specifically interested in the MySqlConnection and MySqlCommand objects but assume (hope) its pattern of exceptions is the same as SQL Server's classes.

I'd assume that if the internet goes down before calling, conn.Open(), a MySqlException gets raised. I'd also assume the same happens if you call ExecuteReader or ExecuteNonQuery and the internet has gone down.

I'm not sure because the docs for the MySql objects don't say. The SqlServer objects just say that it might raise a SqlException which means:

An exception occurred while executing the command against a locked row. This exception is not generated when you are using Microsoft .NET Framework version 1.0.

That doesn't seem to cover connection issues... What I'd like to do is handle the exception, wait for some amount of time, and start over again. My application's sole purpose is to execute these database calls and its running on a dedicated system so retrying forever is really the best option I believe. That said, I would love to differentiate between connection exceptions and other kinds of database exceptions, is that possible?

I've done some testing and it appears to work as I assume but does it work in all edge cases? Such as: the command was successfully sent to the database server but the connection goes down before or while the results are being returned? If it doesn't work in all edge cases then I'm going to have to: execute a command, query for the desired state change, execute the next command, etc. It's important that each command goes through.

I am connecting to a port on localhost that is forwarded via SSH to a remote server if that makes a difference.

Answer 1

As for the SqlDataProvider:

The SqlException exception has a several properties that give you detailed information why your operation failed.

For your use case the "Class" property might be a good choice. It's a byte indicating the severity of the exception.

See: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlexception.class.aspx

If that is not specific enough, you can examine that individual errors in the Errors collection.

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlexception.errors.aspx

Based on that information you can decide whether to retry.