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); } }