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