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] }
}
}
;
}
/// Generate a predicate function to skip unauthorized SQL commands.
private static Predicate <string> RetryPreConditon(FilterSqlStatements unauthorizedSqlStatements)
{
var pattern = GetRegexPattern(unauthorizedSqlStatements);
return((commandText) => string.IsNullOrEmpty(pattern) ||
!Regex.IsMatch(commandText, pattern, RegexOptions.Compiled | RegexOptions.IgnoreCase));
}
/// Provide a regex pattern regarding to the SQL statement.
private static string GetRegexPattern(FilterSqlStatements sqlStatements)
{
if (sqlStatements == FilterSqlStatements.None)
{
return(string.Empty);
}
var pattern = new StringBuilder();
if (sqlStatements.HasFlag(FilterSqlStatements.Insert))
{
pattern.Append(@"INSERT( +INTO){0,1}|");
}
if (sqlStatements.HasFlag(FilterSqlStatements.Update))
{
pattern.Append(@"UPDATE|");
}
if (sqlStatements.HasFlag(FilterSqlStatements.Delete))
{
pattern.Append(@"DELETE|");
}
if (sqlStatements.HasFlag(FilterSqlStatements.Execute))
{
pattern.Append(@"EXEC(UTE){0,1}|");
}
if (sqlStatements.HasFlag(FilterSqlStatements.Alter))
{
pattern.Append(@"ALTER|");
}
if (sqlStatements.HasFlag(FilterSqlStatements.Create))
{
pattern.Append(@"CREATE|");
}
if (sqlStatements.HasFlag(FilterSqlStatements.Drop))
{
pattern.Append(@"DROP|");
}
if (sqlStatements.HasFlag(FilterSqlStatements.Truncate))
{
pattern.Append(@"TRUNCATE|");
}
if (sqlStatements.HasFlag(FilterSqlStatements.Select))
{
pattern.Append(@"SELECT|");
}
if (pattern.Length > 0)
{
pattern.Remove(pattern.Length - 1, 1);
}
return(string.Format(@"\b({0})\b", pattern.ToString()));
}
