public void TestSuccessfulTransactionWithRetryableError()
{
int customerId = 0;
int addressId = 0;
int executeCount = 0;
Action action = new Action(() =>
{
using (ReliableSqlConnection connection = InitializeConnection())
{
using (SqlCommand command = (SqlCommand)(SqlCommand)connection.CreateCommand())
{
command.CommandText = "SELECT TOP 1 [CustomerID] FROM [SalesLT].[Customer] ORDER BY [CustomerID]";
customerId = (int)command.ExecuteScalarWithRetry();
}
if (executeCount == 0)
{
executeCount++;
throw new TimeoutException();
}
using (SqlCommand command = (SqlCommand)(SqlCommand)connection.CreateCommand())
{
command.CommandText = "SELECT TOP 1 [AddressID] FROM [SalesLT].[Address] ORDER BY [AddressID]";
addressId = (int)command.ExecuteScalarWithRetry();
}
using (SqlCommand command = (SqlCommand)(SqlCommand)connection.CreateCommand())
{
command.CommandText = "INSERT INTO [SalesLT].[CustomerAddress] ([CustomerID], [AddressID], [AddressType]) VALUES (@CustomerID, @AddressID, @AddressType)";
command.Parameters.Add("@CustomerID", SqlDbType.Int).Value = customerId;
command.Parameters.Add("@AddressID", SqlDbType.Int).Value = addressId;
command.Parameters.Add("@AddressType", SqlDbType.NVarChar, 100).Value = "Custom Address";
command.ExecuteNonQueryWithRetry();
}
}
});
RetryPolicy retryPolicy = null;// RetryPolicyFactory.GetRetryPolicy<SqlDatabaseTransientErrorDetectionStrategy>("FixedIntervalDefault");
using (TransactionRetryScope scope = new TransactionRetryScope(retryPolicy, action))
{
try
{
scope.InvokeUnitOfWork();
scope.Complete();
}
catch (Exception ex)
{
Trace.WriteLine(ex.Message);
}
}
Assert.IsTrue(VerifyCustomerAddress(customerId, addressId));
DeleteCustomerAddress(customerId, addressId);
}
public void TestFailedTransaction()
{
int expectedCount = RetrieveCountofTable();
int customerId = 0;
int addressId = 0;
Action action = new Action(() =>
{
using (ReliableSqlConnection connection = InitializeConnection())
{
using (SqlCommand command = (SqlCommand)connection.CreateCommand())
{
command.CommandText = "SELECT TOP 1 [CustomerID] FROM [SalesLT].[Customer] ORDER BY [CustomerID]";
customerId = (int)command.ExecuteScalarWithRetry();
}
using (SqlCommand command = (SqlCommand)connection.CreateCommand())
{
command.CommandText = "SELECT TOP 1 [AddressID] FROM [SalesLT].[Address] ORDER BY [AddressID]";
addressId = (int)command.ExecuteScalarWithRetry();
}
using (SqlCommand command = (SqlCommand)connection.CreateCommand())
{
command.CommandText = "INSERT INTO [SalesLT].[CustomerAddress] ([CustomerID], [AddressID], [AddressType]) VALUES (@CustomerID, @AddressID, @AddressType)";
command.Parameters.Add("@CustomerID", SqlDbType.Int).Value = customerId;
command.Parameters.Add("@AddressID", SqlDbType.Int).Value = addressId;
command.Parameters.Add("@AddressType", SqlDbType.NVarChar, 100).Value = "Custom Address";
command.ExecuteNonQueryWithRetry();
}
using (SqlCommand command = (SqlCommand)connection.CreateCommand())
{
command.CommandText = "RAISEERROR('ERROR', 16, 1)";
command.ExecuteNonQueryWithRetry();
}
}
});
using (TransactionRetryScope scope = new TransactionRetryScope(action))
{
try
{
scope.InvokeUnitOfWork();
scope.Complete();
}
catch (Exception ex)
{
Trace.WriteLine(ex.Message);
}
}
int actualCount = RetrieveCountofTable();
Assert.AreEqual(expectedCount, actualCount, "Rollback failed");
}
public void TestSuccessfulTransaction()
{
int customerId = 0;
int addressId = 0;
Action action = new Action(() =>
{
using (ReliableSqlConnection connection = InitializeConnection())
{
using (SqlCommand command = (SqlCommand)connection.CreateCommand())
{
command.CommandText = "SELECT TOP 1 [CustomerID] FROM [SalesLT].[Customer] ORDER BY [CustomerID]";
customerId = (int)command.ExecuteScalarWithRetry();
}
using (SqlCommand command = (SqlCommand)connection.CreateCommand())
{
command.CommandText = "SELECT TOP 1 [AddressID] FROM [SalesLT].[Address] ORDER BY [AddressID]";
addressId = (int)command.ExecuteScalarWithRetry();
}
using (SqlCommand command = (SqlCommand)connection.CreateCommand())
{
command.CommandText = "INSERT INTO [SalesLT].[CustomerAddress] ([CustomerID], [AddressID], [AddressType]) VALUES (@CustomerID, @AddressID, @AddressType)";
command.Parameters.Add("@CustomerID", SqlDbType.Int).Value = customerId;
command.Parameters.Add("@AddressID", SqlDbType.Int).Value = addressId;
command.Parameters.Add("@AddressType", SqlDbType.NVarChar, 100).Value = "Custom Address";
command.ExecuteNonQueryWithRetry();
}
}
});
using (TransactionRetryScope scope = new TransactionRetryScope(RetryPolicy.NoRetry, action))
{
try
{
scope.InvokeUnitOfWork();
scope.Complete();
}
catch (Exception ex)
{
Trace.WriteLine(ex.Message);
}
}
Assert.IsTrue(VerifyCustomerAddress(customerId, addressId));
DeleteCustomerAddress(customerId, addressId);
}
public void TestSuccessfulTransaction()
{
int customerId = 0;
int addressId = 0;
Action action = new Action(() =>
{
using (ReliableSqlConnection connection = InitializeConnection())
{
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT TOP 1 [CustomerID] FROM [SalesLT].[Customer] ORDER BY [CustomerID]";
customerId = (int)command.ExecuteScalarWithRetry();
}
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT TOP 1 [AddressID] FROM [SalesLT].[Address] ORDER BY [AddressID]";
addressId = (int)command.ExecuteScalarWithRetry();
}
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = "INSERT INTO [SalesLT].[CustomerAddress] ([CustomerID], [AddressID], [AddressType]) VALUES (@CustomerID, @AddressID, @AddressType)";
command.Parameters.Add("@CustomerID", SqlDbType.Int).Value = customerId;
command.Parameters.Add("@AddressID", SqlDbType.Int).Value = addressId;
command.Parameters.Add("@AddressType", SqlDbType.NVarChar, 100).Value = "Custom Address";
command.ExecuteNonQueryWithRetry();
}
}
});
using (TransactionRetryScope scope = new TransactionRetryScope(RetryPolicy.NoRetry, action))
{
try
{
scope.InvokeUnitOfWork();
scope.Complete();
}
catch (Exception ex)
{
Trace.WriteLine(ex.Message);
}
}
Assert.IsTrue(VerifyCustomerAddress(customerId, addressId));
DeleteCustomerAddress(customerId, addressId);
}
public void TestFailedTransaction()
{
int expectedCount = RetrieveCountofTable();
int customerId = 0;
int addressId = 0;
Action action = new Action(() =>
{
using (ReliableSqlConnection connection = InitializeConnection())
{
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT TOP 1 [CustomerID] FROM [SalesLT].[Customer] ORDER BY [CustomerID]";
customerId = (int)command.ExecuteScalarWithRetry();
}
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT TOP 1 [AddressID] FROM [SalesLT].[Address] ORDER BY [AddressID]";
addressId = (int)command.ExecuteScalarWithRetry();
}
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = "INSERT INTO [SalesLT].[CustomerAddress] ([CustomerID], [AddressID], [AddressType]) VALUES (@CustomerID, @AddressID, @AddressType)";
command.Parameters.Add("@CustomerID", SqlDbType.Int).Value = customerId;
command.Parameters.Add("@AddressID", SqlDbType.Int).Value = addressId;
command.Parameters.Add("@AddressType", SqlDbType.NVarChar, 100).Value = "Custom Address";
command.ExecuteNonQueryWithRetry();
}
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = "RAISEERROR('ERROR', 16, 1)";
command.ExecuteNonQueryWithRetry();
}
}
});
using (TransactionRetryScope scope = new TransactionRetryScope(action))
{
try
{
scope.InvokeUnitOfWork();
scope.Complete();
}
catch (Exception ex)
{
Trace.WriteLine(ex.Message);
}
}
int actualCount = RetrieveCountofTable();
Assert.AreEqual(expectedCount, actualCount, "Rollback failed");
}
public void TestSuccessfulTransactionWithRetryableError()
{
int customerId = 0;
int addressId = 0;
int executeCount = 0;
Action action = new Action(() =>
{
using (ReliableSqlConnection connection = InitializeConnection())
{
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT TOP 1 [CustomerID] FROM [SalesLT].[Customer] ORDER BY [CustomerID]";
customerId = (int)command.ExecuteScalarWithRetry();
}
if (executeCount == 0)
{
executeCount++;
throw new TimeoutException();
}
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT TOP 1 [AddressID] FROM [SalesLT].[Address] ORDER BY [AddressID]";
addressId = (int)command.ExecuteScalarWithRetry();
}
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = "INSERT INTO [SalesLT].[CustomerAddress] ([CustomerID], [AddressID], [AddressType]) VALUES (@CustomerID, @AddressID, @AddressType)";
command.Parameters.Add("@CustomerID", SqlDbType.Int).Value = customerId;
command.Parameters.Add("@AddressID", SqlDbType.Int).Value = addressId;
command.Parameters.Add("@AddressType", SqlDbType.NVarChar, 100).Value = "Custom Address";
command.ExecuteNonQueryWithRetry();
}
}
});
RetryPolicy retryPolicy = RetryPolicyFactory.GetRetryPolicy<SqlDatabaseTransientErrorDetectionStrategy>("FixedIntervalDefault");
using (TransactionRetryScope scope = new TransactionRetryScope(retryPolicy, action))
{
try
{
scope.InvokeUnitOfWork();
scope.Complete();
}
catch (Exception ex)
{
Trace.WriteLine(ex.Message);
}
}
Assert.IsTrue(VerifyCustomerAddress(customerId, addressId));
DeleteCustomerAddress(customerId, addressId);
}
public void TransactionIsCommittedWhenSomeRetriesFailAndThenSucceeds()
{
this.DeleteAllOnTranscationScopeTestTable();
int retryTransactionCount = 0;
var policyForTransaction = this.retryManager.GetRetryPolicy <FakeSqlAzureTransientErrorDetectionStrategy>("Retry 5 times");
policyForTransaction.Retrying += (s, args) =>
{
retryTransactionCount++;
};
int retrySqlCommandCount = 0;
var policyForSqlCommand = this.retryManager.GetRetryPolicy <FakeSqlAzureTransientErrorDetectionStrategy>("Retry 2 times, first retry is fast");
policyForSqlCommand.Retrying += (s, args) =>
{
retrySqlCommandCount++;
};
int transactionActionExecutedCount = 0;
Action action = new Action(() =>
{
transactionActionExecutedCount++;
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = "Insert Into TranscationScopeTestTable (rowId) Values (@rowId);";
command.Connection = connection;
command.Parameters.Add(new SqlParameter("rowId", SqlDbType.UniqueIdentifier)
{
Value = Guid.NewGuid()
});
command.ExecuteNonQueryWithRetry(policyForSqlCommand);
}
if (retryTransactionCount < 4)
{
using (SqlCommand command = connection.CreateCommand())
{
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "ErrorRaisingForExecuteNonQuery";
command.Connection = connection;
command.Parameters.Add(new SqlParameter("rowId", SqlDbType.UniqueIdentifier)
{
Value = Guid.NewGuid()
});
command.Parameters.Add(new SqlParameter("maxCountToRaiseErrors", SqlDbType.Int)
{
Value = 10
});
command.Parameters.Add(new SqlParameter("error", SqlDbType.Int)
{
Value = 60000
});
command.ExecuteNonQueryWithRetry(policyForSqlCommand);
}
}
}
});
using (TransactionRetryScope scope = new TransactionRetryScope(policyForTransaction, action))
{
try
{
scope.InvokeUnitOfWork();
scope.Complete();
}
catch (Exception)
{
Assert.Fail("Should not throw");
}
}
Assert.AreEqual(1, this.GetCountOnTranscationScopeTestTable());
Assert.AreEqual(5, transactionActionExecutedCount);
Assert.AreEqual(4, retryTransactionCount);
Assert.AreEqual(8, retrySqlCommandCount);
}
