How to reuse code that reopens connection?

Question

Our production server kills inactive connections, so our API needs to restore them when they are needed. The following code works, but it is very repetitive:

   private const int MaxRetryCount = 3;

    public static SqlDataReader RestoreConnectionAndExecuteReader(SqlCommand command)
    {
        int retryCount = 0;

        while (retryCount++ < MaxRetryCount)
        {
            try
            {
                if (command.Connection.State == ConnectionState.Closed)
                    command.Connection.Open();
                return command.ExecuteReader();
            }
            catch(Exception e)
            {
                if(!e.Message.ToLower().Contains("transport-level error has occurred"))
                {
                    throw;
                }
            }
        }
        throw new Exception("Failed to restore connection for command:"+command.CommandText);
    }

    public static void RestoreConnectionAndExecuteNonQuery(SqlCommand command)
    {
        var retryCount = 0;
        while(retryCount++ < MaxRetryCount)
        {
            try
            {
                if (command.Connection.State == ConnectionState.Closed)
                    command.Connection.Open();
                command.ExecuteNonQuery();
                return;
            }
            catch(Exception e)
            {
                if (!e.Message.ToLower().Contains("transport-level error has occurred"))
                {
                    throw;
                }
            }
        }
        throw new Exception("Failed to restore connection for command:" + command.CommandText);
    }

How could I refactor my code and eliminate duplication? I need to keep the signatures of these methods, as they are used all over the system.

Answer 1

There's a few things I'm not loving in the code sample. But, to explicitly answer your question on removing duplication - extract out the common code to a method that takes a delegate.

private TReturn RestoreConnectionAndExecute<T>(SqlCommand command, Func<SqlCommand, TReturn> execute) 
{
   int retryCount = 0;
   while (retryCount++ < MaxRetryCount)
   {
       try
       {
          if (command.Connection.State == ConnectionState.Close) 
              command.Connection.Open();
          return execute(command);
       } 
       catch(Exception e)
       {
          ...
   }
}

public SqlDataReader RestoreConnectionAndExecuteReader(SqlCommand command) 
{
   return this.RestoreConnectionAndExecute(command, c => c.ExecuteReader());
}

public void RestoreConnectionAndExecuteNonQuery(SqlCommand command)
{
   // Ignore return
   this.RestoreConnectionAndExecute(command, c => c.ExecuteNonQuery());
}

You should really rethink a few things, though. Including:

• Catching specific exceptions
• Using Exception.Number or ErrorCode instead of the message (which will change in localized versions, and possibly in updated FX versions)
• Using statements for IDisposable resources
• Throwing specific exceptions

Answer 2

private const int MaxRetryCount = 3;

        public static SqlDataReader RestoreConnectionAndExecuteReader(SqlCommand command)
        {
            return RestoreConnectionAndExecuteQueryHelper(command, true);
        }

        public static void RestoreConnectionAndExecuteNonQuery(SqlCommand command)
        {
            RestoreConnectionAndExecuteQueryHelper(command, false);
        }

        private static SqlDataReader RestoreConnectionAndExecuteQueryHelper(SqlCommand command, bool returnReader)
        {
            var retryCount = 0;
            while (retryCount++ < MaxRetryCount)
            {
                try
                {
                    if (command.Connection.State == ConnectionState.Closed)
                        command.Connection.Open();
                    if (returnReader)
                    {
                        return command.ExecuteReader();
                    }
                    else
                    {
                        command.ExecuteNonQuery();
                        return null;
                    }
                }
                catch (Exception e)
                {
                    if (!e.Message.ToLower().Contains("transport-level error has occurred"))
                    {
                        throw;
                    }
                }
            }
            throw new Exception("Failed to restore connection for command:" + command.CommandText);
        }

Answer 3

The common part for those method is the connection retrieving process. You could create a new static method that is responsible for that called for example retrieve connection that take command as argument and return it if connection is established in other cases throws an exception.

Answer 4

private const int MaxRetryCount = 3;

public static T RestoreConnectionAndExecute<T>(SqlCommand command, Func<SqlCommand, T> func)
{
    int retryCount = 0;

    while (retryCount++ < MaxRetryCount)
    {
        try
        {
            if (command.Connection.State == ConnectionState.Closed)
                command.Connection.Open();
            return func(command);
        }
        catch(Exception e)
        {
            if(!e.Message.ToLower().Contains("transport-level error has occurred"))
            {
                throw;
            }
        }
    }
    throw new Exception("Failed to restore connection for command:"+command.CommandText);

} 

public static SqlDataReader RestoreConnectionAndExecuteReader(SqlCommand command)
{
    return RestoreConnectionAndExecute(command, c => c.ExecuteReader());
}

public static int RestoreConnectionAndExecuteNonQuery(SqlCommand command)
{
    return RestoreConnectionAndExecute(command, c => c.ExecuteNonQuery());
}