public void TestSuccessfulTransaction()
{
connection.Open();
IDbTransaction transaction = connection.BeginTransaction();
SqlCommand command = new SqlCommand("SELECT TOP 1 [CustomerID] FROM [SalesLT].[Customer] ORDER BY [CustomerID]");
command.Transaction = (SqlTransaction)transaction;
int customerId = connection.ExecuteCommand <int>(command);
command = new SqlCommand("SELECT TOP 1 [AddressID] FROM [SalesLT].[Address] ORDER BY [AddressID]");
command.Transaction = (SqlTransaction)transaction;
int addressId = connection.ExecuteCommand <int>(command);
command = new SqlCommand("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";
connection.ExecuteCommand(command);
transaction.Commit();
Assert.IsTrue(VerifyCustomerAddress(customerId, addressId), "Insert was failed");
DeleteCustomerAddress(customerId, addressId);
connection.Close();
}
public void BeginTransaction(string connectionString, ref RetryPolicy <SqlDatabaseTransientErrorDetectionStrategy> retryPolicy)
{
Connection = new ReliableSqlConnection(connectionString, retryPolicy);
Connection.Open(retryPolicy);
Transaction = (SqlTransaction)Connection.BeginTransaction(TransactionIsolationLevel);
}
protected void LoadDatabase(RetryPolicy <ErrorDetectionStrategy> retryPolicy, DataTable data)
{
// Reset load Values
var loadStartTime = DateTime.Now;
// Build the Connection String
var database = IsLoadingPrimaryDatabase ? Model.PrimaryDatabase : Model.SecondaryDatabase;
var connectionString = DatabaseHelper.ConstructConnectionString(Model.DatabaseServer, database, Model.Username, Model.Password);
using (var sqlConnection = new ReliableSqlConnection(connectionString, retryPolicy))
{
TicketsPurchased = 0d;
sqlConnection.Open(retryPolicy);
const string query = "INSERT INTO TICKETS (CustomerId, Name, TicketLevelId, ConcertId, PurchaseDate, SeatNumber) SELECT CustomerId, Name, TicketLevelId, ConcertId, PurchaseDate, SeatNumber FROM @Data";
using (var cmd = new SqlCommand(query, sqlConnection.Current))
{
cmd.Parameters.Add(new SqlParameter()
{
ParameterName = "@Data",
SqlDbType = SqlDbType.Structured,
TypeName = "TicketType",
Value = data
});
do
{
if (Worker.CancellationPending)
{
break;
}
// Run the batch insert script
cmd.Transaction = (SqlTransaction)sqlConnection.BeginTransaction();
cmd.ExecuteNonQuery();
//cmd.ExecuteNonQueryWithRetry(retryPolicy);
cmd.Transaction.Commit();
// Update Values
TicketsPurchased += Model.BulkPurchaseQty;
UpdateTotalValues();
ReportProgress(loadStartTime, database);
// Minimize tickets added
Thread.Sleep(5);
}while (!IsLoadCompleted());
}
}
}
private void LoadDatabase(RetryPolicy <ErrorDetectionStrategy> retryPolicy, string batchQuery)
{
// Reset load Values
var ticketsPurchased = 0d;
var loadStartTime = DateTime.Now;
// Build the Connection String
var database = _loadingPrimaryDatabase ? _model.PrimaryDatabase : _model.SecondaryDatabase;
var connectionString = DatabaseHelper.ConstructConnectionString(_model.DatabaseServer, database, _model.Username, _model.Password);
using (var sqlConnection = new ReliableSqlConnection(connectionString, retryPolicy))
{
sqlConnection.Open(retryPolicy);
do
{
if (_worker.CancellationPending)
{
break;
}
var transaction = sqlConnection.BeginTransaction();
using (var cmd = new SqlCommand(batchQuery, sqlConnection.Current, (SqlTransaction)transaction))
{
// Run the batch insert script
cmd.ExecuteNonQueryWithRetry(retryPolicy);
transaction.Commit();
// Update Values
ticketsPurchased += _model.BulkPurchaseQty;
UpdateTotalValues();
ReportProgress(ticketsPurchased, loadStartTime, database);
// Minimize tickets added
Thread.Sleep(50);
}
}while (ticketsPurchased < ConfigHelper.LoadRecordLimit);
}
}
protected override DbTransaction BeginDbTransaction(System.Data.IsolationLevel isolationLevel)
{
return((DbTransaction)_connection.BeginTransaction());
}
/// <summary>
/// Starts a database transaction.
/// </summary>
/// <param name="isolationLevel">Specifies the isolation level for the transaction.</param>
/// <returns>An object representing the new transaction.</returns>
protected override DbTransaction BeginDbTransaction(IsolationLevel isolationLevel)
{
return((DbTransaction)ReliableConnection.BeginTransaction(isolationLevel));
}
void purchaseWorker_DoWork(object sender, DoWorkEventArgs e)
{
try
{
object[] args = e.Argument as object[];
if (args.Length != 8 || Convert.ToInt32(args[3]) <= 0 || Convert.ToInt32(args[4]) <= 0 || Convert.ToInt32(args[5]) <= 0 || Convert.ToInt32(args[6]) <= 0 || Convert.ToInt32(args[7]) <= 0)
{
e.Result = "Please ensure you have selected a concert, ticket level, and customer, as well as supplied the ticket count and bulk purchase quantity.";
return;
}
string conn = ConstructConn(args[0].ToString(), TenantDbName, args[1].ToString(), args[2].ToString());
string rootQuery =
string.Format("Insert Into Tickets (CustomerId, Name, TicketLevelId, ConcertId, PurchaseDate) Values ({0}, '', {1}, {2}, GETDATE())",
Convert.ToInt32(args[5]).ToString(), Convert.ToInt32(args[4]).ToString(), Convert.ToInt32(args[3]).ToString());
string runtimeQuery = string.Empty;
int bulkPurchase = Convert.ToInt32(args[7]);
for (int i = 0; i < bulkPurchase; i++)
runtimeQuery += rootQuery + "; ";
int ticketCount = Convert.ToInt32(args[6]);
int purchaseCounter = 0;
var retryPolicy = new RetryPolicy<CustomTransientErrorDetectionStrategy>(exponentialBackoffStrategy);
retryPolicy.ExecuteAction(() =>
{
using (ReliableSqlConnection reliableSqlConnection = new ReliableSqlConnection(conn, retryPolicy))
{
reliableSqlConnection.Open(retryPolicy);
IDbTransaction transaction = reliableSqlConnection.BeginTransaction();
using (var cmd = new SqlCommand(runtimeQuery, reliableSqlConnection.Current, (SqlTransaction)transaction))
{
while (purchaseCounter < ticketCount)
{
if (purchaseWorker.CancellationPending)
break;
if (ticketCount - purchaseCounter < bulkPurchase)
{
runtimeQuery = string.Empty;
bulkPurchase = ticketCount - purchaseCounter;
for (int i = 0; i < bulkPurchase; i++)
runtimeQuery += rootQuery;
cmd.CommandText = runtimeQuery;
}
cmd.ExecuteNonQueryWithRetry(retryPolicy);
purchaseWorker.ReportProgress(Convert.ToInt32(((purchaseCounter * 1.0) / ticketCount) * 100), purchaseCounter);
purchaseCounter = purchaseCounter + bulkPurchase;
}
transaction.Commit();
}
}
});
e.Result = purchaseCounter + " tickets purchased";
}
catch (Exception ex) { e.Result = ex.Message; }
}
void purchaseWorker_DoWork(object sender, DoWorkEventArgs e)
{
try
{
object[] args = e.Argument as object[];
if (args.Length != 8 || Convert.ToInt32(args[3]) <= 0 || Convert.ToInt32(args[4]) <= 0 || Convert.ToInt32(args[5]) <= 0 || Convert.ToInt32(args[6]) <= 0 || Convert.ToInt32(args[7]) <= 0)
{
e.Result = "Please ensure you have selected a concert, ticket level, and customer, as well as supplied the ticket count and bulk purchase quantity.";
return;
}
string conn = ConstructConn(args[0].ToString(), TenantDbName, args[1].ToString(), args[2].ToString());
string rootQuery =
string.Format("Insert Into Tickets (CustomerId, Name, TicketLevelId, ConcertId, PurchaseDate) Values ({0}, '', {1}, {2}, GETDATE())",
Convert.ToInt32(args[5]).ToString(), Convert.ToInt32(args[4]).ToString(), Convert.ToInt32(args[3]).ToString());
string runtimeQuery = string.Empty;
int bulkPurchase = Convert.ToInt32(args[7]);
for (int i = 0; i < bulkPurchase; i++)
{
runtimeQuery += rootQuery + "; ";
}
int ticketCount = Convert.ToInt32(args[6]);
int purchaseCounter = 0;
var retryPolicy = new RetryPolicy <CustomTransientErrorDetectionStrategy>(exponentialBackoffStrategy);
retryPolicy.ExecuteAction(() =>
{
using (ReliableSqlConnection reliableSqlConnection = new ReliableSqlConnection(conn, retryPolicy))
{
reliableSqlConnection.Open(retryPolicy);
IDbTransaction transaction = reliableSqlConnection.BeginTransaction();
using (var cmd = new SqlCommand(runtimeQuery, reliableSqlConnection.Current, (SqlTransaction)transaction))
{
while (purchaseCounter < ticketCount)
{
if (purchaseWorker.CancellationPending)
{
break;
}
if (ticketCount - purchaseCounter < bulkPurchase)
{
runtimeQuery = string.Empty;
bulkPurchase = ticketCount - purchaseCounter;
for (int i = 0; i < bulkPurchase; i++)
{
runtimeQuery += rootQuery;
}
cmd.CommandText = runtimeQuery;
}
cmd.ExecuteNonQueryWithRetry(retryPolicy);
purchaseWorker.ReportProgress(Convert.ToInt32(((purchaseCounter * 1.0) / ticketCount) * 100), purchaseCounter);
purchaseCounter = purchaseCounter + bulkPurchase;
}
transaction.Commit();
}
}
});
e.Result = purchaseCounter + " tickets purchased";
}
catch (Exception ex) { e.Result = ex.Message; }
}
private void LoadDatabase(RetryPolicy<ErrorDetectionStrategy> retryPolicy, string batchQuery)
{
// Reset load Values
var ticketsPurchased = 0d;
var loadStartTime = DateTime.Now;
// Build the Connection String
var database = _loadingPrimaryDatabase ? _model.PrimaryDatabase : _model.SecondaryDatabase;
var connectionString = DatabaseHelper.ConstructConnectionString(_model.DatabaseServer, database, _model.Username, _model.Password);
using (var sqlConnection = new ReliableSqlConnection(connectionString, retryPolicy))
{
sqlConnection.Open(retryPolicy);
do
{
if (_worker.CancellationPending)
{
break;
}
var transaction = sqlConnection.BeginTransaction();
using (var cmd = new SqlCommand(batchQuery, sqlConnection.Current, (SqlTransaction)transaction))
{
// Run the batch insert script
cmd.ExecuteNonQueryWithRetry(retryPolicy);
transaction.Commit();
// Update Values
ticketsPurchased += _model.BulkPurchaseQty;
UpdateTotalValues();
ReportProgress(ticketsPurchased, loadStartTime, database);
// Minimize tickets added
Thread.Sleep(50);
}
}
while (ticketsPurchased < ConfigHelper.LoadRecordLimit);
}
}
protected void LoadDatabase(RetryPolicy<ErrorDetectionStrategy> retryPolicy, DataTable data)
{
// Reset load Values
var loadStartTime = DateTime.Now;
// Build the Connection String
var database = IsLoadingPrimaryDatabase ? Model.PrimaryDatabase : Model.SecondaryDatabase;
var connectionString = DatabaseHelper.ConstructConnectionString(Model.DatabaseServer, database, Model.Username, Model.Password);
using (var sqlConnection = new ReliableSqlConnection(connectionString, retryPolicy))
{
TicketsPurchased = 0d;
sqlConnection.Open(retryPolicy);
const string query = "INSERT INTO TICKETS (CustomerId, Name, TicketLevelId, ConcertId, PurchaseDate, SeatNumber) SELECT CustomerId, Name, TicketLevelId, ConcertId, PurchaseDate, SeatNumber FROM @Data";
using (var cmd = new SqlCommand(query, sqlConnection.Current))
{
cmd.Parameters.Add(new SqlParameter()
{
ParameterName = "@Data",
SqlDbType = SqlDbType.Structured,
TypeName = "TicketType",
Value = data
});
do
{
if (Worker.CancellationPending)
{
break;
}
// Run the batch insert script
cmd.Transaction = (SqlTransaction)sqlConnection.BeginTransaction();
cmd.ExecuteNonQuery();
//cmd.ExecuteNonQueryWithRetry(retryPolicy);
cmd.Transaction.Commit();
// Update Values
TicketsPurchased += Model.BulkPurchaseQty;
UpdateTotalValues();
ReportProgress(loadStartTime, database);
// Minimize tickets added
Thread.Sleep(5);
}
while (!IsLoadCompleted());
}
}
}