/// <summary>
/// Initializes a new instance of the <see cref="SqlRetryDatabaseInvoker"/> class.
/// </summary>
/// <param name="maxRetries">The maximum number of retries (defaults to 4).</param>
/// <param name="transientErrorNumbers">The list of of known <see cref="SqlException.Number"/> values that are considered transient (defaults to <see cref="DatabaseBase.SqlTransientErrorNumbers"/> where not specified).</param>
public SqlRetryDatabaseInvoker(int maxRetries = 4, IEnumerable <int> transientErrorNumbers = null)
{
if (maxRetries < 0)
{
throw new ArgumentException("Maximum retries can not be negative.", nameof(maxRetries));
}
MaxRetries = maxRetries;
TransientErrorNumbers.AddRange(transientErrorNumbers ?? DatabaseBase.SqlTransientErrorNumbers);
}
public static async Task <SqlDataReader> ResilientQuery(this SqlCommand command)
{
var succeeded = false;
var retries = 4;
var retryInterval = 2;
SqlDataReader reader = null;
for (var tries = 1; tries <= retries; tries++)
{
try
{
if (tries > 1)
{
Console.WriteLine($"Transient error encountered. Will begin attempt number {tries} of {retries}.");
await Task.Delay(1000 *retryInterval);
retryInterval = Convert.ToInt32(retryInterval * 1.5);
}
reader = await command.ExecuteReaderAsync();
succeeded = true;
break;
}
catch (SqlException sqlEx)
{
if (TransientErrorNumbers.Contains(sqlEx.Number) == true)
{
Console.WriteLine($"{sqlEx.Number}: transient occurred.");
continue;
}
else
{
Console.WriteLine(sqlEx);
succeeded = false;
break;
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
succeeded = false;
break;
}
}
if (succeeded)
{
return(reader);
}
else
{
throw new Exception("An error occurred connecting to the database");
}
}
// Logic to execure RESILIENT database query
static public int InsertResilient(string SQLtext, string InstanceConnectionString, bool ReadBack)
{
bool succeeded = false;
int totalNumberOfTimesToTry = 4; // how many times to retry
int retryIntervalSeconds = 10; // after how many seconds
for (int tries = 1;
tries <= totalNumberOfTimesToTry;
tries++)
{
try
{
if (tries > 1)
{
Console.WriteLine
("Transient error encountered. Will begin attempt number {0} of {1} max...",
tries, totalNumberOfTimesToTry
);
TD.Thread.Sleep(1000 * retryIntervalSeconds);
retryIntervalSeconds = Convert.ToInt32
(retryIntervalSeconds * 1.5);
}
AccessDatabase(SQLtext, InstanceConnectionString, ReadBack);
succeeded = true;
break;
}
catch (QC.SqlException sqlExc)
{
if (TransientErrorNumbers.Contains
(sqlExc.Number) == true)
{
Console.WriteLine("{0}: transient error.", sqlExc.Number);
continue;
}
else
{
Console.WriteLine(sqlExc);
succeeded = false;
break;
}
}
catch (Exception Exc)
{
Console.WriteLine(Exc);
succeeded = false;
break;
}
}
if (succeeded == true)
{
Console.WriteLine("SUCCESS.\n");
return(0);
}
else
{
Console.WriteLine("ERROR: Unable to access the database.\n");
return(1);
}
}
