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>
}
}
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] }
}
;
}
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);
}
}
public async void ValidateRetryCount_SqlCommand_Async(string methodName, int numOfTries)
{
ErrorInfoRetryLogicProvider _errorInfoRetryProvider = new(
SqlConfigurableRetryFactory.CreateFixedRetryProvider(new SqlRetryLogicOption()
{
NumberOfTries = numOfTries, TransientErrors = new[] { 50000 }
}));
try
{
using var connection = new SqlConnection(DataTestUtility.TCPConnectionString);
connection.Open();
using SqlCommand cmd = connection.CreateCommand();
cmd.RetryLogicProvider = _errorInfoRetryProvider;
cmd.CommandText = "THROW 50000,'Error',0";
_errorInfoRetryProvider.CallCounter = 0;
switch (methodName)
{
case "ExecuteScalarAsync":
await cmd.ExecuteScalarAsync();
break;
case "ExecuteReaderAsync":
{
using SqlDataReader _ = await cmd.ExecuteReaderAsync();
break;
}
case "ExecuteXmlReaderAsync":
{
using System.Xml.XmlReader _ = await cmd.ExecuteXmlReaderAsync();
break;
}
case "ExecuteNonQueryAsync":
await cmd.ExecuteNonQueryAsync();
break;
default:
break;
}
Assert.False(true, "Exception did not occur.");
}
catch
{
Assert.Equal(numOfTries, _errorInfoRetryProvider.CallCounter);
}
}
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));
}
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 static IEnumerable <object[]> GetNoneRetriableCondition()
{
yield return(new object[] { DataTestUtility.TCPConnectionString, null });
yield return(new object[] { DataTestUtility.TCPConnectionString, SqlConfigurableRetryFactory.CreateNoneRetryProvider() });
}
public void InvalidCRLFactoryCreation()
{
Assert.Throws <ArgumentNullException>(() => SqlConfigurableRetryFactory.CreateFixedRetryProvider(null));
Assert.Throws <ArgumentNullException>(() => SqlConfigurableRetryFactory.CreateIncrementalRetryProvider(null));
Assert.Throws <ArgumentNullException>(() => SqlConfigurableRetryFactory.CreateExponentialRetryProvider(null));
}
/// <summary>
/// The default non retry provider will apply if a parameter passes by null.
/// </summary>
private void AssignProviders(SqlRetryLogicBaseProvider cnnProvider = null, SqlRetryLogicBaseProvider cmdProvider = null)
{
ConnectionProvider = cnnProvider ?? SqlConfigurableRetryFactory.CreateNoneRetryProvider();
CommandProvider = cmdProvider ?? SqlConfigurableRetryFactory.CreateNoneRetryProvider();
}
