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