public static IEnumerable <object[]> GetConnectionAndRetryStrategy(int numberOfRetries, TimeSpan maxInterval, FilterSqlStatements unauthorizedStatemets, IEnumerable <int> transientErrors, int deltaTimeMillisecond = 10, bool custom = true) { var option = new SqlRetryLogicOption() { NumberOfTries = numberOfRetries, DeltaTime = TimeSpan.FromMilliseconds(deltaTimeMillisecond), MaxTimeInterval = maxInterval, TransientErrors = transientErrors ?? (custom ? s_defaultTransientErrors : null), AuthorizedSqlCondition = custom ? RetryPreConditon(unauthorizedStatemets) : null }; foreach (var item in GetRetryStrategies(option)) { foreach (var cnn in GetConnectionStrings()) { yield return new object[] { cnn[0], item[0] } } } ; }
public static IEnumerable <object[]> GetNoneRetriableCondition() { RetryLogicTestHelper.SetRetrySwitch(true); yield return(new object[] { DataTestUtility.TCPConnectionString, null }); yield return(new object[] { DataTestUtility.TCPConnectionString, SqlConfigurableRetryFactory.CreateNoneRetryProvider() }); RetryLogicTestHelper.SetRetrySwitch(false); yield return(new object[] { DataTestUtility.TCPConnectionString, null }); yield return(new object[] { DataTestUtility.TCPConnectionString, SqlConfigurableRetryFactory.CreateNoneRetryProvider() }); var option = new SqlRetryLogicOption() { NumberOfTries = 2, DeltaTime = TimeSpan.FromMilliseconds(10), MaxTimeInterval = TimeSpan.FromSeconds(2) }; foreach (var provider in GetRetryStrategies(option)) { yield return new object[] { DataTestUtility.TCPConnectionString, provider[0] } } ; }
private static void RetryConnection(string connectionString) { // <Snippet1> // Define the retry logic parameters var options = new SqlRetryLogicOption() { // Tries 5 times before throwing an exception NumberOfTries = 5, // Preferred gap time to delay before retry DeltaTime = TimeSpan.FromSeconds(1), // Maximum gap time for each delay time before retry MaxTimeInterval = TimeSpan.FromSeconds(20) }; // </Snippet1> // <Snippet2> // Create a retry logic provider SqlRetryLogicBaseProvider provider = SqlConfigurableRetryFactory.CreateExponentialRetryProvider(options); // </Snippet2> using (SqlConnection connection = new SqlConnection(connectionString)) { // <Snippet3> // Assumes that connection is a valid SqlConnection object // Set the retry logic provider on the connection instance connection.RetryLogicProvider = provider; // Establishing the connection will retry if a transient failure occurs. connection.Open(); // </Snippet3> } }
private static void RetryConnection(string connectionString) { // <Snippet1> // Define the retry logic parameters var options = new SqlRetryLogicOption() { // Tries 5 times before throwing an exception NumberOfTries = 5, // Preferred gap time to delay before retry DeltaTime = TimeSpan.FromSeconds(1), // Maximum gap time for each delay time before retry MaxTimeInterval = TimeSpan.FromSeconds(20), // SqlException retriable error numbers TransientErrors = new int[] { 4060, 1024, 1025 } }; // </Snippet1> // <Snippet2> // Create a custom retry logic provider SqlRetryLogicBaseProvider provider = CustomRetry.CreateCustomProvider(options); // </Snippet2> using (SqlConnection connection = new SqlConnection(connectionString)) { // <Snippet3> // Assumes that connection is a valid SqlConnection object // Set the retry logic provider on the connection instance connection.RetryLogicProvider = provider; // Establishing the connection will trigger retry if one of the given transient failure occurs. connection.Open(); // </Snippet3> } }
public SqlRetryLogicBaseProvider GetDefaultRetry(SqlRetryLogicOption option = null) { SqlRetryLogicBaseProvider provider = new SqlCommand().RetryLogicProvider; Console.WriteLine(provider.RetryLogic.NumberOfTries); return(new CustomRetryLogicProvider(option?.NumberOfTries ?? 1)); }
public SqlRetryLogicBaseProvider GetDefaultRetry(SqlRetryLogicOption option = null) { // Trying to get access to a provider inside a custom implementation. SqlRetryLogicBaseProvider provider = new SqlCommand().RetryLogicProvider; return(new CustomRetryLogicProvider(option?.NumberOfTries ?? 1)); }
static void Main(string[] args) { // 1. Define the retry logic parameters var options = new SqlRetryLogicOption() { NumberOfTries = 5, MaxTimeInterval = TimeSpan.FromSeconds(20), DeltaTime = TimeSpan.FromSeconds(1), AuthorizedSqlCondition = null, // error number 3702 : Cannot drop database "xxx" because it is currently in use. TransientErrors = new int[] { 3702 } }; // 2. Create a retry provider var provider = SqlConfigurableRetryFactory.CreateExponentialRetryProvider(options); // define the retrying event to report execution attempts provider.Retrying += (object s, SqlRetryingEventArgs e) => { int attempts = e.RetryCount + 1; Console.ForegroundColor = ConsoleColor.Yellow; Console.WriteLine($"attempt {attempts} - current delay time:{e.Delay} \n"); Console.ForegroundColor = ConsoleColor.DarkGray; if (e.Exceptions[e.Exceptions.Count - 1] is SqlException ex) { Console.WriteLine($"{ex.Number}-{ex.Message}\n"); } else { Console.WriteLine($"{e.Exceptions[e.Exceptions.Count - 1].Message}\n"); } // It is not good practice to do time-consuming tasks inside the retrying event which blocks the running task. // Use parallel programming patterns to mitigate it. if (e.RetryCount == provider.RetryLogic.NumberOfTries - 1) { Console.WriteLine("This is the last chance to execute the command before throwing the exception."); Console.WriteLine("Press Enter when you're ready:"); Console.ReadLine(); Console.WriteLine("continue ..."); } }; // Open a general connection. s_generalConnection.Open(); try { // Assume the database is creating and other services are going to connect to it. RetryCommand(provider); } catch { s_generalConnection.Close(); // exception is thrown if connecting to the database isn't successful. throw; } s_generalConnection.Close(); }
private static IEnumerable <object[]> GetRetryStrategies(SqlRetryLogicOption retryLogicOption) { yield return(new object[] { SqlConfigurableRetryFactory.CreateExponentialRetryProvider(retryLogicOption) }); yield return(new object[] { SqlConfigurableRetryFactory.CreateIncrementalRetryProvider(retryLogicOption) }); yield return(new object[] { SqlConfigurableRetryFactory.CreateFixedRetryProvider(retryLogicOption) }); }
static public void Main(string[] args) { // Read the connection string from the app.config file string connString; ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings["ConsoleAppConnectiongp1"]; connString = settings.ConnectionString; // Setup retry logic // Define the retry logic parameters var options = new SqlRetryLogicOption() { // Tries 5 times before throwing an exception NumberOfTries = 5, // Preferred gap time to delay before retry DeltaTime = TimeSpan.FromSeconds(5), // Maximum gap time for each delay time before retry MaxTimeInterval = TimeSpan.FromSeconds(60), // Let's add a few errors to the default TransientErrors = new int[] { 0, 64, 40615, 40914, 40613 } }; // Create a retry logic provider SqlRetryLogicBaseProvider provider = SqlConfigurableRetryFactory.CreateExponentialRetryProvider(options); // define the retrying event to report the execution attempts provider.Retrying += (s, e) => { int attempts = e.RetryCount; Console.ForegroundColor = ConsoleColor.Yellow; Console.WriteLine($"attempt {attempts} - current delay time:{e.Delay} \n"); Console.ForegroundColor = ConsoleColor.DarkGray; if (e.Exceptions[e.Exceptions.Count - 1] is SqlException ex) { Console.WriteLine($"{ex.Number}-{ex.Message}\n"); } else { Console.WriteLine($"{e.Exceptions[e.Exceptions.Count - 1].Message}\n"); } }; for (int i = 0; i < 10000000; i++) { RunWorkload(connString, provider); //if ((i % 100) == 0) { Console.ForegroundColor = ConsoleColor.White; Console.WriteLine("Workload executed... {0}", i); } // Delay a bit to make this a typical app instead of just making DB calls as fast as I can System.Threading.Thread.Sleep(100); } }
// <Snippet4> public static SqlRetryLogicBaseProvider CreateCustomProvider(SqlRetryLogicOption options) { // 1. create an enumerator instance CustomEnumerator customEnumerator = new CustomEnumerator(options.DeltaTime, options.MaxTimeInterval, options.MinTimeInterval); // 2. Use the enumerator object to create a new RetryLogic instance CustomRetryLogic customRetryLogic = new CustomRetryLogic(5, customEnumerator, (e) => TransientErrorsCondition(e, options.TransientErrors)); // 3. Create a provider using the RetryLogic object CustomProvider customProvider = new CustomProvider(customRetryLogic); return(customProvider); }
public void InvalidExecute() { SqlRetryLogicOption option = new SqlRetryLogicOption() { NumberOfTries = 5, DeltaTime = TimeSpan.FromSeconds(10), MinTimeInterval = TimeSpan.Zero, MaxTimeInterval = TimeSpan.FromSeconds(120) }; SqlRetryLogicBaseProvider retryLogicProvider = SqlConfigurableRetryFactory.CreateFixedRetryProvider(option); Assert.Throws <ArgumentNullException>(() => retryLogicProvider.Execute <int>(null, null)); Assert.ThrowsAsync <ArgumentNullException>(() => retryLogicProvider.ExecuteAsync(null, null)); Assert.ThrowsAsync <ArgumentNullException>(() => retryLogicProvider.ExecuteAsync <int>(null, null)); }
static void Main(string[] args) { // <Snippet1> var RetryLogicOption = new SqlRetryLogicOption() { NumberOfTries = 5, // Declare the error number 102 as a transient error to apply the retry logic when it occurs. TransientErrors = new int[] { 102 }, // When a SqlCommand executes out of a transaction, // the retry logic will apply if it contains a 'select' keyword. AuthorizedSqlCondition = x => string.IsNullOrEmpty(x) || Regex.IsMatch(x, @"\b(SELECT)\b", RegexOptions.IgnoreCase), DeltaTime = TimeSpan.FromSeconds(1), MaxTimeInterval = TimeSpan.FromSeconds(60), MinTimeInterval = TimeSpan.FromSeconds(3) }; // </Snippet1> }
public void ValidateRetryParameters() { var option = new SqlRetryLogicOption() { NumberOfTries = 10, // 1-60 MinTimeInterval = TimeSpan.FromMinutes(0), // 0-120 MaxTimeInterval = TimeSpan.FromSeconds(120), // 0-120 DeltaTime = TimeSpan.FromSeconds(1) // 0-120 }; option.NumberOfTries = 0; Assert.Throws <ArgumentOutOfRangeException>(() => SqlConfigurableRetryFactory.CreateFixedRetryProvider(option)); option.NumberOfTries = 61; Assert.Throws <ArgumentOutOfRangeException>(() => SqlConfigurableRetryFactory.CreateFixedRetryProvider(option)); option.NumberOfTries = 10; option.DeltaTime = TimeSpan.FromSeconds(-1); Assert.Throws <ArgumentOutOfRangeException>(() => SqlConfigurableRetryFactory.CreateFixedRetryProvider(option)); option.DeltaTime = TimeSpan.FromSeconds(121); Assert.Throws <ArgumentOutOfRangeException>(() => SqlConfigurableRetryFactory.CreateFixedRetryProvider(option)); option.DeltaTime = TimeSpan.FromSeconds(1); option.MinTimeInterval = TimeSpan.FromSeconds(-1); Assert.Throws <ArgumentOutOfRangeException>(() => SqlConfigurableRetryFactory.CreateIncrementalRetryProvider(option)); option.MinTimeInterval = TimeSpan.FromSeconds(121); Assert.Throws <ArgumentOutOfRangeException>(() => SqlConfigurableRetryFactory.CreateIncrementalRetryProvider(option)); option.MinTimeInterval = TimeSpan.FromSeconds(0); option.MaxTimeInterval = TimeSpan.FromSeconds(-1); Assert.Throws <ArgumentOutOfRangeException>(() => SqlConfigurableRetryFactory.CreateIncrementalRetryProvider(option)); option.MaxTimeInterval = TimeSpan.FromSeconds(121); Assert.Throws <ArgumentOutOfRangeException>(() => SqlConfigurableRetryFactory.CreateIncrementalRetryProvider(option)); option.MinTimeInterval = TimeSpan.FromSeconds(50); option.MaxTimeInterval = TimeSpan.FromSeconds(40); Assert.Throws <ArgumentOutOfRangeException>(() => SqlConfigurableRetryFactory.CreateIncrementalRetryProvider(option)); option.MinTimeInterval = TimeSpan.FromSeconds(0); option.MaxTimeInterval = TimeSpan.FromSeconds(120); option.AuthorizedSqlCondition = null; SqlConfigurableRetryFactory.CreateIncrementalRetryProvider(option); }
public SqlRetryLogicBaseProvider GetDefaultRetry(SqlRetryLogicOption option = null) { return(new CustomRetryLogicProvider(option?.NumberOfTries ?? 1)); }