What is good C# coding style for catching SQLException and retrying

Question

I have a method that calls a SQLServer function to perform a free text search against a table. That function will occasionally on the first call result in a SQLException: "Word breaking timed out for the full-text query string". So typically I want to retry that request because it will succeed on subsequent requests. What is good style for structuring the retry logic. At the moment I have the following:

var retryCount = 0;
var results = new List<UserSummaryDto>();
using (var ctx = new UsersDataContext(ConfigurationManager.ConnectionStrings[CONNECTION_STRING_KEY].ConnectionString))
{
    for (; ; )
    {
        try
        {
            results = ctx.SearchPhoneList(value, maxRows)
                         .Select(user => user.ToDto())
                         .ToList();
            break;
        }
        catch (SqlException)
        {
            retryCount++;
            if (retryCount > MAX_RETRY) throw;
        }
    }
}

return results;

Answer 1

I'd change the exception handling to only retry on certain errors:

• 1204, 1205 deadlocks
• -2 timeout
• -1 connection broken

These are the basic "retryable" errors

catch (SqlException ex)
{
    if !(ex.Number == 1205 || ex.Number == 1204 || ... )
    {
        throw
    }
    retryCount++;
    if (retryCount > MAX_RETRY) throw;
}

Edit, I clean forgot about waits so you don't hammer the SQL box:

• Add a 500 ms wait on deadlock
• Add a 5 sec delay on timeout

Edit 2:

I'm a Developer DBA, don't do much C#. My answer was to correct exception processing for the calls...

Answer 2

Thanks for all the feedback. I'm answering this myself so I can incorporate elements from the answers given. Please let me know if I've missed something. My method becomes:

var results = new List<UserSummaryDto>();
Retry<UsersDataContext>(ctx => results = ctx.SearchPhoneList(value, maxRows)
                                            .Select(user => user.ToDto())
                                            .ToList());
return results;

And I've refactored the original method for reuse. Still lots of levels of nesting. It also relies on there being a default constructor for the data context which may be too restrictive. @Martin, I considered including your PreserveStackTrace method but in this case I don't think it really adds enough value - good to know for future reference thanks:

private const int MAX_RETRY = 2;
private const double LONG_WAIT_SECONDS = 5;
private const double SHORT_WAIT_SECONDS = 0.5;
private static readonly TimeSpan longWait = TimeSpan.FromSeconds(LONG_WAIT_SECONDS);
private static readonly TimeSpan shortWait = TimeSpan.FromSeconds(SHORT_WAIT_SECONDS);
private enum RetryableSqlErrors
{
    Timeout = -2,
    NoLock = 1204,
    Deadlock = 1205,
    WordbreakerTimeout = 30053,
}

private void Retry<T>(Action<T> retryAction) where T : DataContext, new()
{
    var retryCount = 0;
    using (var ctx = new T())
    {
        for (;;)
        {
            try
            {
                retryAction(ctx);
                break;
            }
            catch (SqlException ex)
                when (ex.Number == (int) RetryableSqlErrors.Timeout &&
                      retryCount < MAX_RETRY)
            {
                Thread.Sleep(longWait);
            }
            catch (SqlException ex)
                when (Enum.IsDefined(typeof(RetryableSqlErrors), ex.Number) &&
                      retryCount < MAX_RETRY)
            {
                Thread.Sleep(shortWait);
            }
            retryCount++;
        }
    }
}