public void DropDatabaseWithActiveConnection(string cnnString, SqlRetryLogicBaseProvider provider)
{
int currentRetries = 0;
string database = DataTestUtility.GetUniqueNameForSqlServer($"RetryLogic_{provider.RetryLogic.RetryIntervalEnumerator.GetType().Name}", false);
var builder = new SqlConnectionStringBuilder(cnnString)
{
InitialCatalog = database,
ConnectTimeout = 1
};
using (var cnn3 = new SqlConnection(cnnString))
using (var cnn2 = new SqlConnection(builder.ConnectionString))
using (var cnn1 = new SqlConnection(new SqlConnectionStringBuilder(cnnString)
{
ConnectTimeout = 120
}.ConnectionString))
using (var cmd = new SqlCommand())
{
cnn1.Open();
cmd.Connection = cnn1;
// Create the database and wait until it is finalized.
cmd.CommandText = $"CREATE DATABASE [{database}]; \nWHILE(NOT EXISTS(SELECT 1 FROM sys.databases WHERE name = '{database}')) \nWAITFOR DELAY '00:00:10' ";
cmd.ExecuteNonQuery();
try
{
// open an active connection to the database to raise error 3702 if someone drops it.
cnn2.Open();
cnn3.Open();
// kill the active connection to the database after the first faliure.
provider.Retrying += (s, e) =>
{
currentRetries = e.RetryCount;
if (cnn2.State == ConnectionState.Open)
{
// in some reason closing connection doesn't eliminate the active connection to the database
using (var cmd3 = cnn3.CreateCommand())
{
cmd3.CommandText = $"KILL {cnn2.ServerProcessId}";
cmd3.ExecuteNonQueryAsync();
}
cnn2.Close();
}
};
// drop the database
cmd.RetryLogicProvider = provider;
cmd.CommandText = $"DROP DATABASE [{database}]";
cmd.ExecuteNonQuery();
Assert.True(currentRetries > 0);
}
finally
{
// additional try to drop the database if it still exists.
DataTestUtility.DropDatabase(cnn1, database);
}
}
}
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>
}
}
private void TestCommandExecute(SqlRetryLogicBaseProvider provider, RetryLogicConfigs cnfig)
{
using (SqlConnection cnn = new SqlConnection(TcpCnnString))
using (SqlCommand cmd = new SqlCommand())
{
cnn.Open();
cmd.Connection = cnn;
cmd.RetryLogicProvider = provider;
cmd.CommandText = "SELECT bad command";
var ex = Assert.Throws <AggregateException>(() => cmd.ExecuteScalar());
Assert.Equal(cnfig.NumberOfTries, ex.InnerExceptions.Count);
ex = Assert.Throws <AggregateException>(() => cmd.ExecuteReader());
Assert.Equal(cnfig.NumberOfTries, ex.InnerExceptions.Count);
ex = Assert.Throws <AggregateException>(() => cmd.ExecuteReader(CommandBehavior.Default));
Assert.Equal(cnfig.NumberOfTries, ex.InnerExceptions.Count);
ex = Assert.Throws <AggregateException>(() => cmd.ExecuteNonQuery());
Assert.Equal(cnfig.NumberOfTries, ex.InnerExceptions.Count);
if (DataTestUtility.IsNotAzureSynapse())
{
cmd.CommandText = cmd.CommandText + " FOR XML AUTO";
ex = Assert.Throws <AggregateException>(() => cmd.ExecuteXmlReader());
Assert.Equal(cnfig.NumberOfTries, ex.InnerExceptions.Count);
}
}
}
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>
}
}
// </Snippet3>
// <Snippet4>
private static void RetryCommand(SqlRetryLogicBaseProvider provider)
{
// Change this if you already have a database with the same name in your database.
string dbName = "RetryCommand_TestDatabase";
// Subscribe to the retry event and discover the active sessions in a database
EventHandler <SqlRetryingEventArgs> retryEvent = (object s, SqlRetryingEventArgs e) =>
{
// Run just at first execution
if (e.RetryCount == 1)
{
FindActiveSessions(s_generalConnection, dbName);
Console.WriteLine($"Before exceeding {provider.RetryLogic.NumberOfTries} attempts.");
}
};
provider.Retrying += retryEvent;
// Create a new database.
ExecuteCommand(s_generalConnection, string.Format(CreateDatabaseFormat, dbName));
Console.WriteLine($"The '{dbName}' database is created.");
// Open a connection to the newly created database to block it from being dropped.
using var blockingCnn = new SqlConnection(string.Format(CnnStringFormat, dbName));
blockingCnn.Open();
Console.WriteLine($"Established a connection to '{dbName}' to block it from being dropped.");
Console.WriteLine("Dropping the database...");
// Try to drop the new database.
RetryCommandBeginExecuteAsync(provider, dbName).Wait();
Console.WriteLine("Command executed successfully.");
provider.Retrying -= retryEvent;
}
private async Task TestCommandExecuteAsync(SqlRetryLogicBaseProvider provider, RetryLogicConfigs cnfig)
{
using (SqlConnection cnn = new SqlConnection(TcpCnnString))
using (SqlCommand cmd = new SqlCommand())
{
cnn.Open();
cmd.Connection = cnn;
cmd.RetryLogicProvider = provider;
cmd.CommandText = "SELECT bad command";
var ex = await Assert.ThrowsAsync <AggregateException>(() => cmd.ExecuteScalarAsync());
Assert.Equal(cnfig.NumberOfTries, ex.InnerExceptions.Count);
ex = await Assert.ThrowsAsync <AggregateException>(() => cmd.ExecuteReaderAsync());
Assert.Equal(cnfig.NumberOfTries, ex.InnerExceptions.Count);
ex = await Assert.ThrowsAsync <AggregateException>(() => cmd.ExecuteReaderAsync(CommandBehavior.Default));
Assert.Equal(cnfig.NumberOfTries, ex.InnerExceptions.Count);
ex = await Assert.ThrowsAsync <AggregateException>(() => cmd.ExecuteNonQueryAsync());
Assert.Equal(cnfig.NumberOfTries, ex.InnerExceptions.Count);
cmd.CommandText = cmd.CommandText + " FOR XML AUTO";
ex = await Assert.ThrowsAsync <AggregateException>(() => cmd.ExecuteXmlReaderAsync());
Assert.Equal(cnfig.NumberOfTries, ex.InnerExceptions.Count);
}
}
public void ConcurrentExecution(string cnnString, SqlRetryLogicBaseProvider provider)
{
int numberOfTries = provider.RetryLogic.NumberOfTries;
int cancelAfterRetries = numberOfTries + 1;
int retriesCount = 0;
int concurrentExecution = 5;
provider.Retrying += (s, e) => Interlocked.Increment(ref retriesCount);
Parallel.For(0, concurrentExecution,
i =>
{
using (var cnn = CreateConnectionWithInvalidCatalog(cnnString, provider, cancelAfterRetries))
{
Assert.Throws <AggregateException>(() => cnn.Open());
}
});
Assert.Equal(numberOfTries * concurrentExecution, retriesCount + concurrentExecution);
retriesCount = 0;
Parallel.For(0, concurrentExecution,
i =>
{
using (var cnn = CreateConnectionWithInvalidCatalog(cnnString, provider, cancelAfterRetries))
{
Assert.ThrowsAsync <AggregateException>(() => cnn.OpenAsync()).Wait();
}
});
Assert.Equal(numberOfTries * concurrentExecution, retriesCount + concurrentExecution);
}
public void RetryExecuteWithTrans(string cnnString, SqlRetryLogicBaseProvider provider)
{
int numberOfTries = provider.RetryLogic.NumberOfTries;
int cancelAfterRetries = numberOfTries + 1;
int currentRetries = 0;
provider.Retrying += (s, e) => currentRetries = e.RetryCount;
string query = "SELECT bad command";
using (SqlConnection cnn = new SqlConnection(cnnString))
using (SqlCommand cmd = CreateCommand(cnn, provider, cancelAfterRetries))
using (SqlTransaction tran = cnn.BeginTransaction())
{
cmd.CommandText = query;
cmd.Transaction = tran;
Assert.Throws <SqlException>(() => cmd.ExecuteScalar());
Assert.Equal(0, currentRetries);
Assert.Throws <SqlException>(() => cmd.ExecuteReader());
Assert.Equal(0, currentRetries);
Assert.Throws <SqlException>(() => cmd.ExecuteReader(CommandBehavior.Default));
Assert.Equal(0, currentRetries);
Assert.Throws <SqlException>(() => cmd.ExecuteNonQuery());
Assert.Equal(0, currentRetries);
cmd.CommandText = query + " FOR XML AUTO";
Assert.Throws <SqlException>(() => cmd.ExecuteXmlReader());
Assert.Equal(0, currentRetries);
tran.Commit();
}
}
public void NoneRetriableExecuteFail(string cnnString, SqlRetryLogicBaseProvider provider)
{
string query = "SELECT bad command";
using (SqlConnection cnn = new SqlConnection(cnnString))
using (SqlCommand cmd = new SqlCommand())
{
cnn.Open();
cmd.Connection = cnn;
cmd.CommandText = query;
cmd.RetryLogicProvider = provider;
Assert.Throws <SqlException>(() => cmd.ExecuteScalar());
Assert.Throws <SqlException>(() => cmd.ExecuteReader());
Assert.Throws <SqlException>(() => cmd.ExecuteReader(CommandBehavior.Default));
Assert.Throws <SqlException>(() => cmd.ExecuteNonQuery());
Assert.ThrowsAsync <SqlException>(() => cmd.ExecuteScalarAsync()).Wait();
Assert.ThrowsAsync <SqlException>(() => cmd.ExecuteReaderAsync()).Wait();
Assert.ThrowsAsync <SqlException>(() => cmd.ExecuteReaderAsync(CommandBehavior.Default)).Wait();
Assert.ThrowsAsync <SqlException>(() => cmd.ExecuteNonQueryAsync()).Wait();
cmd.CommandText = query + " FOR XML AUTO";
Assert.Throws <SqlException>(() => cmd.ExecuteXmlReader());
Assert.ThrowsAsync <SqlException>(() => cmd.ExecuteXmlReaderAsync()).Wait();
}
}
private static async Task RetryCommandBeginExecuteAsync(SqlRetryLogicBaseProvider provider, string dbName)
{
using var cmd = s_generalConnection.CreateCommand();
cmd.CommandText = string.Format(DropDatabaseFormat, dbName);
// Execute the BeginExecuteXXX and EndExecuteXXX functions by using Task.Factory.FromAsync().
// Apply the retry logic by using the ExecuteAsync function of the configurable retry logic provider.
Console.WriteLine("The first attempt, before getting into the retry logic.");
await provider.ExecuteAsync(cmd, () => Task.Factory.FromAsync(cmd.BeginExecuteNonQuery(), cmd.EndExecuteNonQuery));
}
private static async Task RetryCommandAsync(SqlRetryLogicBaseProvider provider, string dbName)
{
using var cmd = s_generalConnection.CreateCommand();
cmd.CommandText = string.Format(DropDatabaseFormat, dbName);
// 3. Assign the `provider` to the command
cmd.RetryLogicProvider = provider;
Console.WriteLine("The first attempt, before getting into the retry logic.");
await cmd.ExecuteNonQueryAsync();
}
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 void UpdateALockedTable(string cnnString, SqlRetryLogicBaseProvider provider)
{
int currentRetries = 0;
string tableName = DataTestUtility.GetUniqueNameForSqlServer("Region");
string fieldName = "RegionDescription";
using (var cnn1 = new SqlConnection(cnnString))
using (var cmd1 = new SqlCommand())
using (var cnn2 = new SqlConnection(cnnString))
using (var cmd2 = new SqlCommand())
{
cnn1.Open();
cnn2.Open();
cmd1.Connection = cnn1;
cmd2.Connection = cnn2;
// Create a separate table from Region
cmd1.CommandText = $"SELECT TOP (1) * INTO {tableName} FROM Region;";
cmd1.ExecuteNonQuery();
try
{
CancellationTokenSource tokenSource = new CancellationTokenSource();
provider.Retrying += (s, e) =>
{
currentRetries = e.RetryCount;
// cancel the blocker task
tokenSource.Cancel();
cmd1.Cancel();
cnn1.Close();
};
// Hold lock the table for 10 seconds (more that the connection timeout)
cmd1.CommandText = $"BEGIN TRAN; SELECT * FROM {tableName} WITH(TABLOCKx, HOLDLOCK); WAITFOR DELAY '00:00:10'; ROLLBACK;";
cmd1.ExecuteNonQueryAsync(tokenSource.Token);
// Be sure the table is locked.
Thread.Sleep(1000);
// Update the locked table
cmd2.RetryLogicProvider = provider;
cmd2.CommandTimeout = 1;
cmd2.CommandText = $"UPDATE {tableName} SET {fieldName} = 'updated';";
cmd2.ExecuteNonQuery();
}
finally
{
DataTestUtility.DropTable(cnn2, tableName);
}
Assert.True(currentRetries > 0);
}
}
private void TestConnection(SqlRetryLogicBaseProvider provider, RetryLogicConfigs cnfig)
{
using (SqlConnection cnn = new SqlConnection(InvalidTcpCnnString))
{
cnn.RetryLogicProvider = provider;
var ex = Assert.Throws <AggregateException>(() => cnn.Open());
Assert.Equal(cnfig.NumberOfTries, ex.InnerExceptions.Count);
var tex = Assert.ThrowsAsync <AggregateException>(() => cnn.OpenAsync());
Assert.Equal(cnfig.NumberOfTries, tex.Result.InnerExceptions.Count);
}
}
public void ConnectionCancelRetryOpenInvalidCatalog(string cnnString, SqlRetryLogicBaseProvider provider)
{
int cancelAfterRetries = provider.RetryLogic.NumberOfTries - 1;
int currentRetries = 0;
provider.Retrying += (s, e) => currentRetries = e.RetryCount;
using (var cnn = CreateConnectionWithInvalidCatalog(cnnString, provider, cancelAfterRetries))
{
var ex = Assert.Throws <AggregateException>(() => cnn.Open());
Assert.Equal(cancelAfterRetries, currentRetries);
Assert.Equal(cancelAfterRetries, ex.InnerExceptions.Count);
Assert.Contains(string.Format(_cancelErrMsgPattern, currentRetries), ex.Message);
}
}
public void RetryExecuteUnauthorizedSqlStatementDML(string cnnString, SqlRetryLogicBaseProvider provider)
{
int numberOfTries = provider.RetryLogic.NumberOfTries;
int cancelAfterRetries = numberOfTries + 1;
int currentRetries = 0;
provider.Retrying += (s, e) => currentRetries = e.RetryCount;
using (SqlConnection cnn = new SqlConnection(cnnString))
using (SqlCommand cmd = CreateCommand(cnn, provider, cancelAfterRetries))
{
#region unauthorized
cmd.CommandText = "UPDATE bad command";
Assert.Throws <SqlException>(() => cmd.ExecuteNonQuery());
Assert.Equal(0, currentRetries);
cmd.CommandText = "INSERT INTO bad command";
Assert.Throws <SqlException>(() => cmd.ExecuteNonQuery());
Assert.Equal(0, currentRetries);
cmd.CommandText = "DELETE FROM bad command";
Assert.Throws <SqlException>(() => cmd.ExecuteNonQuery());
Assert.Equal(0, currentRetries);
cmd.CommandText = "TRUNCATE TABLE bad command";
Assert.Throws <SqlException>(() => cmd.ExecuteNonQuery());
Assert.Equal(0, currentRetries);
#endregion
cmd.CommandText = "SELECT bad command";
Assert.Throws <AggregateException>(() => cmd.ExecuteNonQuery());
Assert.Equal(numberOfTries, currentRetries + 1);
cmd.CommandText = "ALTER TABLE bad command";
Assert.Throws <AggregateException>(() => cmd.ExecuteNonQuery());
Assert.Equal(numberOfTries, currentRetries + 1);
cmd.CommandText = "EXEC bad command";
Assert.Throws <AggregateException>(() => cmd.ExecuteNonQuery());
Assert.Equal(numberOfTries, currentRetries + 1);
cmd.CommandText = "CREATE TABLE bad command";
Assert.Throws <AggregateException>(() => cmd.ExecuteNonQuery());
Assert.Equal(numberOfTries, currentRetries + 1);
cmd.CommandText = "DROP TABLE bad command";
Assert.Throws <AggregateException>(() => cmd.ExecuteNonQuery());
Assert.Equal(numberOfTries, currentRetries + 1);
}
}
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 CreateDatabaseWhileTryingToConnect(string cnnString, SqlRetryLogicBaseProvider provider)
{
int currentRetries = 0;
string database = DataTestUtility.GetUniqueNameForSqlServer($"RetryLogic_{provider.RetryLogic.RetryIntervalEnumerator.GetType().Name}", false);
var builder = new SqlConnectionStringBuilder(cnnString)
{
InitialCatalog = database,
ConnectTimeout = 1
};
using (var cnn1 = new SqlConnection(new SqlConnectionStringBuilder(cnnString)
{
ConnectTimeout = 60, Pooling = false
}.ConnectionString))
{
cnn1.Open();
Task createDBTask = null;
try
{
provider.Retrying += (s, e) =>
{
currentRetries = e.RetryCount;
using (var cmd = cnn1.CreateCommand())
{
// Try to create database just after first faliure.
if (createDBTask == null || createDBTask.Status == TaskStatus.Faulted)
{
cmd.CommandText = $"IF (NOT EXISTS(SELECT 1 FROM sys.databases WHERE name = '{database}')) CREATE DATABASE [{database}];";
createDBTask = cmd.ExecuteNonQueryAsync();
}
}
};
using (var cnn2 = new SqlConnection(builder.ConnectionString))
{
cnn2.RetryLogicProvider = provider;
cnn2.Open();
}
}
finally
{
createDBTask?.Wait();
DataTestUtility.DropDatabase(cnn1, database);
}
}
Assert.True(currentRetries > 0);
}
private SqlCommand CreateCommand(SqlConnection cnn, SqlRetryLogicBaseProvider provider, int cancelAfterRetries)
{
cnn.Open();
SqlCommand cmd = cnn.CreateCommand();
cmd.RetryLogicProvider = provider;
cmd.RetryLogicProvider.Retrying += (object s, SqlRetryingEventArgs e) =>
{
Assert.Equal(e.RetryCount, e.Exceptions.Count);
Assert.NotEqual(TimeSpan.Zero, e.Delay);
if (e.RetryCount >= cancelAfterRetries)
{
e.Cancel = true;
}
};
return(cmd);
}
public void DefaultOpenWithoutRetry(string connectionString, SqlRetryLogicBaseProvider cnnProvider)
{
var cnnString = new SqlConnectionStringBuilder(connectionString)
{
InitialCatalog = InvalidInitialCatalog
}.ConnectionString;
Assert.Throws <SqlException>(() => new SqlConnection(cnnString).Open());
Assert.ThrowsAsync <SqlException>(() => new SqlConnection(cnnString).OpenAsync()).Wait();
using (var cnn = new SqlConnection(cnnString))
{
cnn.RetryLogicProvider = cnnProvider;
Assert.Throws <SqlException>(() => cnn.Open());
cnn.RetryLogicProvider = cnnProvider;
Assert.ThrowsAsync <SqlException>(() => cnn.OpenAsync()).Wait();
}
}
public void RetryExecuteCancel(string cnnString, SqlRetryLogicBaseProvider provider)
{
int numberOfTries = provider.RetryLogic.NumberOfTries;
int cancelAfterRetries = numberOfTries - 1;
int currentRetries = 0;
provider.Retrying += (s, e) => currentRetries = e.RetryCount;
string query = "SELECT bad command";
using (SqlConnection cnn = new SqlConnection(cnnString))
using (SqlCommand cmd = CreateCommand(cnn, provider, cancelAfterRetries))
{
cmd.CommandText = query;
var ex = Assert.Throws <AggregateException>(() => cmd.ExecuteScalar());
Assert.Equal(cancelAfterRetries, currentRetries);
Assert.Equal(cancelAfterRetries, ex.InnerExceptions.Count);
Assert.Contains(string.Format(_cancelErrMsgPattern, currentRetries), ex.Message);
ex = Assert.Throws <AggregateException>(() => cmd.ExecuteReader());
Assert.Equal(cancelAfterRetries, currentRetries);
Assert.Equal(cancelAfterRetries, ex.InnerExceptions.Count);
Assert.Contains(string.Format(_cancelErrMsgPattern, currentRetries), ex.Message);
ex = Assert.Throws <AggregateException>(() => cmd.ExecuteReader(CommandBehavior.Default));
Assert.Equal(cancelAfterRetries, currentRetries);
Assert.Equal(cancelAfterRetries, ex.InnerExceptions.Count);
Assert.Contains(string.Format(_cancelErrMsgPattern, currentRetries), ex.Message);
ex = Assert.Throws <AggregateException>(() => cmd.ExecuteNonQuery());
Assert.Equal(cancelAfterRetries, currentRetries);
Assert.Equal(cancelAfterRetries, ex.InnerExceptions.Count);
Assert.Contains(string.Format(_cancelErrMsgPattern, currentRetries), ex.Message);
if (DataTestUtility.IsNotAzureSynapse())
{
cmd.CommandText = query + " FOR XML AUTO";
ex = Assert.Throws <AggregateException>(() => cmd.ExecuteXmlReader());
Assert.Equal(cancelAfterRetries, currentRetries);
Assert.Equal(cancelAfterRetries, ex.InnerExceptions.Count);
Assert.Contains(string.Format(_cancelErrMsgPattern, currentRetries), ex.Message);
}
}
}
static public void ExecuteQuery(SqlConnection connection, SqlRetryLogicBaseProvider provider)
{
using (var command = new SqlCommand())
{
command.Connection = connection;
command.CommandType = CommandType.Text;
command.CommandText = @"SELECT @@VERSION";
command.CommandTimeout = 180;
command.RetryLogicProvider = provider;
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
;
}
reader.Close();
}
}
private SqlConnection CreateConnectionWithInvalidCatalog(string cnnString, SqlRetryLogicBaseProvider provider, int cancelAfterRetries)
{
var builder = new SqlConnectionStringBuilder(cnnString)
{
InitialCatalog = InvalidInitialCatalog
};
SqlConnection cnn = new SqlConnection(builder.ConnectionString);
cnn.RetryLogicProvider = provider;
cnn.RetryLogicProvider.Retrying += (s, e) =>
{
Assert.Equal(e.RetryCount, e.Exceptions.Count);
Assert.NotEqual(TimeSpan.Zero, e.Delay);
if (e.RetryCount >= cancelAfterRetries)
{
e.Cancel = true;
}
};
return(cnn);
}
// Workload routine to connect and run query
static public void RunWorkload(string connString, SqlRetryLogicBaseProvider provider)
{
// Setup a string to hold error messages
StringBuilder errorMessages = new StringBuilder();
try
{
// Build the connection
using (var connection = new SqlConnection(connString))
{
// Set the retry logic provider on the connection instance
//connection.RetryLogicProvider = provider;
// open the connection
connection.Open();
var watch = new System.Diagnostics.Stopwatch();
watch.Start();
Program.ExecuteQuery(connection, provider);
watch.Stop();
// Close the connection
connection.Close();
}
}
catch (Exception ex)
{
Console.ForegroundColor = ConsoleColor.Red;
errorMessages.Append(
"Message: " + ex.Message + "\n" +
"Source: " + ex.Source + "\n");
Console.WriteLine(errorMessages.ToString());
}
}
private static void RetryConnection(SqlRetryLogicBaseProvider provider)
{
// Change this if you already have a database with the same name in your database.
string dbName = "Invalid_DB_Open";
// Create a connection to an invalid database.
using var cnn = new SqlConnection(string.Format(CnnStringFormat, dbName));
// 3. Assign the `provider` to the connection
cnn.RetryLogicProvider = provider;
Console.WriteLine($"Connecting to the [{dbName}] ...");
// Manually execute the following command in SSMS to create the invalid database while the SqlConnection is attempting to connect to it.
// >> CREATE DATABASE Invalid_DB_Open;
Console.WriteLine($"Manually, run the 'CREATE DATABASE {dbName};' in the SQL Server before exceeding the {provider.RetryLogic.NumberOfTries} attempts.");
// the connection tries to connect to the database 5 times
Console.WriteLine("The first attempt, before getting into the retry logic.");
cnn.Open();
Console.WriteLine($"Connected to the [{dbName}] successfully.");
cnn.Close();
// Drop it after test
ExecuteCommand(s_generalConnection, string.Format(DropDatabaseFormat, dbName));
Console.WriteLine($"The [{dbName}] is removed.");
}
public ErrorInfoRetryLogicProvider(SqlRetryLogicBaseProvider innerProvider)
{
InnerProvider = innerProvider;
}
public void ConcurrentExecution(string cnnString, SqlRetryLogicBaseProvider provider)
{
int numberOfTries = provider.RetryLogic.NumberOfTries;
string query = "SELECT bad command";
int retriesCount = 0;
int concurrentExecution = 3;
provider.Retrying += (s, e) => Interlocked.Increment(ref retriesCount);
Parallel.For(0, concurrentExecution,
i =>
{
using (SqlConnection cnn = new SqlConnection(cnnString))
using (SqlCommand cmd = cnn.CreateCommand())
{
cnn.Open();
cmd.RetryLogicProvider = provider;
cmd.CommandText = query;
Assert.Throws <AggregateException>(() => cmd.ExecuteScalar());
}
});
Assert.Equal(numberOfTries * concurrentExecution, retriesCount + concurrentExecution);
retriesCount = 0;
Parallel.For(0, concurrentExecution,
i =>
{
using (SqlConnection cnn = new SqlConnection(cnnString))
using (SqlCommand cmd = cnn.CreateCommand())
{
cnn.Open();
cmd.RetryLogicProvider = provider;
cmd.CommandText = query;
Assert.Throws <AggregateException>(() => cmd.ExecuteNonQuery());
}
});
Assert.Equal(numberOfTries * concurrentExecution, retriesCount + concurrentExecution);
retriesCount = 0;
Parallel.For(0, concurrentExecution,
i =>
{
using (SqlConnection cnn = new SqlConnection(cnnString))
using (SqlCommand cmd = cnn.CreateCommand())
{
cnn.Open();
cmd.RetryLogicProvider = provider;
cmd.CommandText = query;
Assert.Throws <AggregateException>(() => cmd.ExecuteReader());
}
});
Assert.Equal(numberOfTries * concurrentExecution, retriesCount + concurrentExecution);
if (DataTestUtility.IsNotAzureSynapse())
{
retriesCount = 0;
Parallel.For(0, concurrentExecution,
i =>
{
using (SqlConnection cnn = new SqlConnection(cnnString))
using (SqlCommand cmd = cnn.CreateCommand())
{
cnn.Open();
cmd.RetryLogicProvider = provider;
cmd.CommandText = query + " FOR XML AUTO";
Assert.Throws <AggregateException>(() => cmd.ExecuteXmlReader());
}
});
Assert.Equal(numberOfTries * concurrentExecution, retriesCount + concurrentExecution);
}
retriesCount = 0;
Parallel.For(0, concurrentExecution,
i =>
{
using (SqlConnection cnn = new SqlConnection(cnnString))
using (SqlCommand cmd = cnn.CreateCommand())
{
cnn.Open();
cmd.RetryLogicProvider = provider;
cmd.CommandText = query;
Assert.ThrowsAsync <AggregateException>(() => cmd.ExecuteScalarAsync()).Wait();
}
});
Assert.Equal(numberOfTries * concurrentExecution, retriesCount + concurrentExecution);
retriesCount = 0;
Parallel.For(0, concurrentExecution,
i =>
{
using (SqlConnection cnn = new SqlConnection(cnnString))
using (SqlCommand cmd = cnn.CreateCommand())
{
cnn.Open();
cmd.RetryLogicProvider = provider;
cmd.CommandText = query;
Assert.ThrowsAsync <AggregateException>(() => cmd.ExecuteNonQueryAsync()).Wait();
}
});
Assert.Equal(numberOfTries * concurrentExecution, retriesCount + concurrentExecution);
retriesCount = 0;
Parallel.For(0, concurrentExecution,
i =>
{
using (SqlConnection cnn = new SqlConnection(cnnString))
using (SqlCommand cmd = cnn.CreateCommand())
{
cnn.Open();
cmd.RetryLogicProvider = provider;
cmd.CommandText = query;
Assert.ThrowsAsync <AggregateException>(() => cmd.ExecuteReaderAsync()).Wait();
}
});
Assert.Equal(numberOfTries * concurrentExecution, retriesCount + concurrentExecution);
// TODO: there is a known issue by ExecuteXmlReaderAsync that should be solved first- issue #44
/*
*
* if(DataTestUtility.IsNotAzureSynapse())
* {
* retriesCount = 0;
* Parallel.For(0, concurrentExecution,
* i =>
* {
* using (SqlConnection cnn = new SqlConnection(cnnString))
* using (SqlCommand cmd = cnn.CreateCommand())
* {
* cnn.Open();
* cmd.RetryLogicProvider = provider;
* cmd.CommandText = query + " FOR XML AUTO";
* Assert.ThrowsAsync<AggregateException>(() => cmd.ExecuteXmlReaderAsync()).Wait();
* }
* });
* Assert.Equal(numberOfTries * concurrentExecution, retriesCount + concurrentExecution);
* }
*/
}
public async void ConnectionRetryOpenAsyncInvalidCatalogFailed(string cnnString, SqlRetryLogicBaseProvider provider)
{
int numberOfTries = provider.RetryLogic.NumberOfTries;
int cancelAfterRetries = numberOfTries + 1;
int currentRetries = 0;
provider.Retrying += (s, e) => currentRetries = e.RetryCount;
using (var cnn = CreateConnectionWithInvalidCatalog(cnnString, provider, cancelAfterRetries))
{
var ex = await Assert.ThrowsAsync <AggregateException>(() => cnn.OpenAsync());
Assert.Equal(numberOfTries, currentRetries + 1);
Assert.Equal(numberOfTries, ex.InnerExceptions.Count);
Assert.Contains(string.Format(_exceedErrMsgPattern, numberOfTries), ex.Message);
}
}
public async void RetryExecuteAsyncCancel(string cnnString, SqlRetryLogicBaseProvider provider)
{
int numberOfTries = provider.RetryLogic.NumberOfTries;
int cancelAfterRetries = numberOfTries - 1;
int currentRetries = 0;
provider.Retrying += (s, e) => currentRetries = e.RetryCount;
string query = "SELECT bad command";
using (SqlConnection cnn = new SqlConnection(cnnString))
using (SqlCommand cmd = CreateCommand(cnn, provider, cancelAfterRetries))
{
cmd.CommandText = query;
var ex = await Assert.ThrowsAsync <AggregateException>(() => cmd.ExecuteScalarAsync());
Assert.Equal(cancelAfterRetries, currentRetries);
Assert.Equal(cancelAfterRetries, ex.InnerExceptions.Count);
Assert.Contains(string.Format(_cancelErrMsgPattern, currentRetries), ex.Message);
ex = await Assert.ThrowsAsync <AggregateException>(() => cmd.ExecuteReaderAsync());
Assert.Equal(cancelAfterRetries, currentRetries);
Assert.Equal(cancelAfterRetries, ex.InnerExceptions.Count);
Assert.Contains(string.Format(_cancelErrMsgPattern, currentRetries), ex.Message);
ex = await Assert.ThrowsAsync <AggregateException>(() => cmd.ExecuteReaderAsync(CommandBehavior.Default));
Assert.Equal(cancelAfterRetries, currentRetries);
Assert.Equal(cancelAfterRetries, ex.InnerExceptions.Count);
Assert.Contains(string.Format(_cancelErrMsgPattern, currentRetries), ex.Message);
ex = await Assert.ThrowsAsync <AggregateException>(() => cmd.ExecuteReaderAsync(CommandBehavior.Default, CancellationToken.None));
Assert.Equal(cancelAfterRetries, currentRetries);
Assert.Equal(cancelAfterRetries, ex.InnerExceptions.Count);
Assert.Contains(string.Format(_cancelErrMsgPattern, currentRetries), ex.Message);
ex = await Assert.ThrowsAsync <AggregateException>(() => provider.ExecuteAsync(cmd, () => Task.Factory.FromAsync(cmd.BeginExecuteReader(), cmd.EndExecuteReader)));
Assert.Equal(cancelAfterRetries, currentRetries);
Assert.Equal(cancelAfterRetries, ex.InnerExceptions.Count);
Assert.Contains(string.Format(_cancelErrMsgPattern, currentRetries), ex.Message);
ex = await Assert.ThrowsAsync <AggregateException>(() => cmd.ExecuteNonQueryAsync());
Assert.Equal(cancelAfterRetries, currentRetries);
Assert.Equal(cancelAfterRetries, ex.InnerExceptions.Count);
Assert.Contains(string.Format(_cancelErrMsgPattern, currentRetries), ex.Message);
ex = await Assert.ThrowsAsync <AggregateException>(() => provider.ExecuteAsync(cmd, () => Task.Factory.FromAsync(cmd.BeginExecuteNonQuery(), cmd.EndExecuteNonQuery)));
Assert.Equal(cancelAfterRetries, currentRetries);
Assert.Equal(cancelAfterRetries, ex.InnerExceptions.Count);
Assert.Contains(string.Format(_cancelErrMsgPattern, currentRetries), ex.Message);
cmd.CommandText = query + " FOR XML AUTO";
ex = await Assert.ThrowsAsync <AggregateException>(() => cmd.ExecuteXmlReaderAsync());
Assert.Equal(cancelAfterRetries, currentRetries);
Assert.Equal(cancelAfterRetries, ex.InnerExceptions.Count);
Assert.Contains(string.Format(_cancelErrMsgPattern, currentRetries), ex.Message);
ex = await Assert.ThrowsAsync <AggregateException>(() => provider.ExecuteAsync(cmd, () => Task.Factory.FromAsync(cmd.BeginExecuteXmlReader(), cmd.EndExecuteXmlReader)));
Assert.Equal(cancelAfterRetries, currentRetries);
Assert.Equal(cancelAfterRetries, ex.InnerExceptions.Count);
Assert.Contains(string.Format(_cancelErrMsgPattern, currentRetries), ex.Message);
}
}
public void ConcurrentExecution(string cnnString, SqlRetryLogicBaseProvider provider)
{
var cnnStr = new SqlConnectionStringBuilder(cnnString)
{
MultipleActiveResultSets = true, ConnectTimeout = 0
}.ConnectionString;
int numberOfTries = provider.RetryLogic.NumberOfTries;
string query = "SELECT bad command";
int retriesCount = 0;
int concurrentExecution = 5;
provider.Retrying += (s, e) => Interlocked.Increment(ref retriesCount);
using (SqlConnection cnn = new SqlConnection(cnnStr))
{
cnn.Open();
Parallel.For(0, concurrentExecution,
i =>
{
using (SqlCommand cmd = cnn.CreateCommand())
{
cmd.RetryLogicProvider = provider;
cmd.CommandText = query;
Assert.Throws <AggregateException>(() => cmd.ExecuteScalar());
}
});
Assert.Equal(numberOfTries * concurrentExecution, retriesCount + concurrentExecution);
retriesCount = 0;
Parallel.For(0, concurrentExecution,
i =>
{
using (SqlCommand cmd = cnn.CreateCommand())
{
cmd.RetryLogicProvider = provider;
cmd.CommandText = query;
Assert.Throws <AggregateException>(() => cmd.ExecuteNonQuery());
}
});
Assert.Equal(numberOfTries * concurrentExecution, retriesCount + concurrentExecution);
retriesCount = 0;
Parallel.For(0, concurrentExecution,
i =>
{
using (SqlCommand cmd = cnn.CreateCommand())
{
cmd.RetryLogicProvider = provider;
cmd.CommandText = query;
Assert.Throws <AggregateException>(() => cmd.ExecuteReader());
}
});
Assert.Equal(numberOfTries * concurrentExecution, retriesCount + concurrentExecution);
retriesCount = 0;
Parallel.For(0, concurrentExecution,
i =>
{
using (SqlCommand cmd = cnn.CreateCommand())
{
cmd.RetryLogicProvider = provider;
cmd.CommandText = query + " FOR XML AUTO";
Assert.Throws <AggregateException>(() => cmd.ExecuteXmlReader());
}
});
Assert.Equal(numberOfTries * concurrentExecution, retriesCount + concurrentExecution);
retriesCount = 0;
Parallel.For(0, concurrentExecution,
i =>
{
using (SqlCommand cmd = cnn.CreateCommand())
{
cmd.RetryLogicProvider = provider;
cmd.CommandText = query;
Assert.ThrowsAsync <AggregateException>(() => cmd.ExecuteScalarAsync()).Wait();
}
});
Assert.Equal(numberOfTries * concurrentExecution, retriesCount + concurrentExecution);
retriesCount = 0;
Parallel.For(0, concurrentExecution,
i =>
{
using (SqlCommand cmd = cnn.CreateCommand())
{
cmd.RetryLogicProvider = provider;
cmd.CommandText = query;
Assert.ThrowsAsync <AggregateException>(() => cmd.ExecuteNonQueryAsync()).Wait();
}
});
Assert.Equal(numberOfTries * concurrentExecution, retriesCount + concurrentExecution);
retriesCount = 0;
Parallel.For(0, concurrentExecution,
i =>
{
using (SqlCommand cmd = cnn.CreateCommand())
{
cmd.RetryLogicProvider = provider;
cmd.CommandText = query;
Assert.ThrowsAsync <AggregateException>(() => cmd.ExecuteReaderAsync()).Wait();
}
});
Assert.Equal(numberOfTries * concurrentExecution, retriesCount + concurrentExecution);
retriesCount = 0;
Parallel.For(0, concurrentExecution,
i =>
{
using (SqlCommand cmd = cnn.CreateCommand())
{
cmd.RetryLogicProvider = provider;
cmd.CommandText = query + " FOR XML AUTO";
Assert.ThrowsAsync <AggregateException>(() => cmd.ExecuteXmlReaderAsync()).Wait();
}
});
Assert.Equal(numberOfTries * concurrentExecution, retriesCount + concurrentExecution);
}
}
