sql - 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...