C# detect offline mode (SQL Server connection)

Question

I am developing a C# application which connects to SQL Server. If the network connection breaks, the application should be able to go into a "read-only mode" (offline mode) and only read data from a local database. Right now, I am trying to figure out how to detect the disconnect:

public int executeNonQuery(string query, List<SqlParameter> parameters)
{
        int result;

        using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))
        {
            tryOpenSqlConnection(sqlConnection);

            using (SqlCommand cmd = new SqlCommand(query, sqlConnection))
            {
                if (parameters != null)
                {
                    cmd.Parameters.AddRange(parameters.ToArray());
                }
                result = cmd.ExecuteNonQuery();
            }

            sqlConnection.Close(); 
        }

        return result;
}

private void tryOpenSqlConnection(SqlConnection sqlConnection)
{
        try
        {
            sqlConnection.Open();
        }
        catch (SqlException se)
        {
            if (se.Number == 26)
            {
                catchOfflineMode(se);
            }

            throw se;
        }
    }

//...

private void catchOfflineMode(SqlException se)
{
    Console.WriteLine("SqlException: " + se.Message);
    Console.WriteLine("Setting offline mode...");

    //...
}

I thought about using the SQL error codes to detect the loss of connection. But the problem is that sometimes I get exceptions only after the SqlConnection already established, e.g. during execution of the command. The last exception I got was

Error Code 121 - The semaphore timeout period has expired

So, I would have to check every single error code that could have to do with losing network connection.

EDIT: I also thought about catching every SqlException and then checking the ethernet connection (e.g. pinging the server) to check whether the exception comes from a lost connection or not.

Are there better ways to do it?

Answer 1

I came up with my own solution by creating a simple helper class called

ExternalServiceHandler.cs

which is used as a proxy for external service calls to detect the online and offline status of the application after an operation failed.

using System;
using System.Threading;
using System.Threading.Tasks;
using Application.Utilities;

namespace Application.ExternalServices
{
    class ExternalServiceHandler: IExternalServiceHandler
    {
        public event EventHandler OnlineModeDetected;
        public event EventHandler OfflineModeDetected;

        private static readonly int RUN_ONLINE_DETECTION_SEC = 10;
        private static ExternalServiceHandler instance;
        private Task checkOnlineStatusTask;
        private CancellationTokenSource cancelSource;
        private Exception errorNoConnection;

        public static ExternalServiceHandler Instance
        {
            get
            {
                if (instance == null)
                {
                    instance = new ExternalServiceHandler();
                }
                return instance;
            }
        }

        private ExternalServiceHandler()
        {
            errorNoConnection = new Exception("Could not connect to the server.");
        }

        public virtual void Execute(Action func)
        {
            if (func == null) throw new ArgumentNullException("func");

            try
            {
                func();
            }
            catch
            {
                if(offlineModeDetected())
                {
                    throw errorNoConnection;
                }
                else
                {
                    throw;
                } 
            }
        }

        public virtual T Execute<T>(Func<T> func)
        {
            if (func == null) throw new ArgumentNullException("func");

            try
            {
                return func();
            }
            catch
            {
                if (offlineModeDetected())
                {
                    throw errorNoConnection;
                }
                else
                {
                    throw;
                }
            }
        }

        public virtual async Task ExecuteAsync(Func<Task> func)
        {
            if (func == null) throw new ArgumentNullException("func");

            try
            {
                await func();
            }
            catch
            {
                if (offlineModeDetected())
                {
                    throw errorNoConnection;
                }
                else
                {
                    throw;
                }
            }
        }

        public virtual async Task<T> ExecuteAsync<T>(Func<Task<T>> func)
        {
            if (func == null) throw new ArgumentNullException("func");

            try
            {
                return await func();
            }
            catch
            {
                if (offlineModeDetected())
                {
                    throw errorNoConnection;
                }
                else
                {
                    throw;
                }
            }
        }

        private bool offlineModeDetected()
        {
            bool isOffline = false;
            if (!LocalMachine.isOnline())
            {
                isOffline = true;
                Console.WriteLine("-- Offline mode detected (readonly). --");

                // notify all modues that we're in offline mode
                OnOfflineModeDetected(new EventArgs());

                // start online detection task
                cancelSource = new CancellationTokenSource();
                checkOnlineStatusTask = Run(detectOnlineMode, 
                    new TimeSpan(0,0, RUN_ONLINE_DETECTION_SEC), 
                    cancelSource.Token);
            }
            return isOffline;
        }

        private void detectOnlineMode()
        { 
            if(LocalMachine.isOnline())
            {
                Console.WriteLine("-- Online mode detected (read and write). --");

                // notify all modules that we're online
                OnOnlineModeDetected(new EventArgs());

                // stop online detection task
                cancelSource.Cancel();
            }
        }

        public static async Task Run(Action action, TimeSpan period, CancellationToken cancellationToken)
        {
            while (!cancellationToken.IsCancellationRequested)
            {
                await Task.Delay(period, cancellationToken);

                if (!cancellationToken.IsCancellationRequested)
                {
                    action();
                }    
            }
        }

        protected virtual void OnOfflineModeDetected(EventArgs e)
        {
            OfflineModeDetected?.Invoke(this, e);
        }
        protected virtual void OnOnlineModeDetected(EventArgs e)
        {
            OnlineModeDetected?.Invoke(this, e);
        }
    }
}

The LocalMachine.isOnline() method looks like this:

namespace Application.Utilities
{
    public class LocalMachine
    {
        // ... //

        public static bool isOnline()
        {
            try
            {
                using (var client = new WebClient())
                {
                    string serveraddress = AppSettings.GetServerHttpAddress();
                    using (var stream = client.OpenRead(serveraddress))
                    {
                        return true;
                    }
                }
            }
            catch
            {
                return false;
            }
        }

        // ... //
}

The helper class can be used every time an external service call is made. In the following example, a SQL non query is executed by the ExternalServiceHandler:

public async Task<int> executeNonQueryAsync(string query)
{
    return await ExternalServiceHandler.Instance.ExecuteAsync(async () =>
    {
        return await DBManager.executeNonQueryAsync(query);
    });
}

The solution works fine for me. If you have any better ideas, please let me know.