public virtual void Purge()
{
using (var connection = new SqlConnection(_connectionString))
{
string deleteCommandText = "DELETE FROM [Stardust].[WorkerNode] WHERE Alive = 0";
connection.OpenWithRetry(_retryPolicy);
using (var deleteCommand = new SqlCommand(deleteCommandText, connection))
{
deleteCommand.ExecuteNonQueryWithRetry(_retryPolicy);
}
}
}
public virtual void Purge()
{
using (var connection = new SqlConnection(_connectionString))
{
string deleteCommandText = "DELETE TOP(@batchsize) FROM [Stardust].[Job] WHERE Created < DATEADD(HOUR, -@hours, GETDATE())";
connection.OpenWithRetry(_retryPolicy);
using (var deleteCommand = new SqlCommand(deleteCommandText, connection))
{
deleteCommand.Parameters.AddWithValue("@hours", _managerConfiguration.PurgeJobsOlderThanHours);
deleteCommand.Parameters.AddWithValue("@batchsize", _managerConfiguration.PurgeJobsBatchSize);
deleteCommand.ExecuteNonQueryWithRetry(_retryPolicy);
}
}
}
/**
* @brief Save log task processor. \n
*/
public static bool RunLog(CBLoggers message)
{
if (globalVal.CloudBreadLoggerSetting != "")
{
if (string.IsNullOrEmpty(message.memberID))
{
message.memberID = ""; /// in case of non-member triggered job
}
/// critical error case, save in ATS CloudBreadErrorLog
if (message.Level.ToUpper() == "ERROR")
{
try
{
/// Save error log on Azure Table Storage
{
/// Azure Table Storage connection retry policy
var tableStorageRetryPolicy = new ExponentialRetry(TimeSpan.FromSeconds(2), 10);
CloudStorageAccount storageAccountT = CloudStorageAccount.Parse(globalVal.StorageConnectionString);
CloudTableClient tableClient = storageAccountT.CreateCloudTableClient();
tableClient.DefaultRequestOptions.RetryPolicy = tableStorageRetryPolicy;
CloudTable table = tableClient.GetTableReference("CloudBreadErrorLog");
CBATSMessageEntity Message = new CBATSMessageEntity(message.memberID, Guid.NewGuid().ToString());
Message.jobID = message.jobID;
Message.Date = DateTimeOffset.UtcNow.ToString("yyyy-MM-ddTHH:mm:ss.fffffffZ");
Message.Thread = message.Thread;
Message.Level = message.Level;
Message.Logger = message.Logger;
Message.Message = message.Message;
Message.Exception = message.Exception;
TableOperation insertOperation = TableOperation.Insert(Message);
table.Execute(insertOperation);
}
}
catch (Exception)
{
/// Catch fail to log on database. Most case database connection or login fail issue.
throw;
}
}
else
{
/// Regarding to web.config logger settting, save logs on specific storage
try
{
switch (globalVal.CloudBreadLoggerSetting)
{
case "SQL":
/// Save log on SQL
string strQuery = string.Format("insert into dbo.CloudBreadLog(memberid, jobID, [Thread], [Level], [Logger], [Message], [Exception]) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}')",
message.memberID,
message.jobID,
message.Thread,
message.Level,
message.Logger,
message.Message,
message.Exception
);
/// Database connection retry policy
RetryPolicy retryPolicy = new RetryPolicy<SqlAzureTransientErrorDetectionStrategy>(globalVal.conRetryCount, TimeSpan.FromSeconds(globalVal.conRetryFromSeconds));
SqlConnection connection = new SqlConnection(globalVal.DBConnectionString);
{
connection.OpenWithRetry(retryPolicy);
SqlCommand command = new SqlCommand(strQuery, connection);
int rowcount = command.ExecuteNonQueryWithRetry(retryPolicy);
connection.Close();
break;
}
case "ATS":
/// Save log on Azure Table Storage
{
/// Azure Table Storage connection retry policy
var tableStorageRetryPolicy = new ExponentialRetry(TimeSpan.FromSeconds(2), 10);
CloudStorageAccount storageAccountT = CloudStorageAccount.Parse(globalVal.StorageConnectionString);
CloudTableClient tableClient = storageAccountT.CreateCloudTableClient();
tableClient.DefaultRequestOptions.RetryPolicy = tableStorageRetryPolicy;
CloudTable table = tableClient.GetTableReference("CloudBreadLog");
CBATSMessageEntity Message = new CBATSMessageEntity(message.memberID, Guid.NewGuid().ToString());
Message.jobID = message.jobID;
Message.Date = DateTimeOffset.UtcNow.ToString("yyyy-MM-ddTHH:mm:ss.fffffffZ");
//Message.Date = DateTimeOffset.UtcNow.ToString("yyyy-MM-ddTHH:mm:ssZ");
Message.Thread = message.Thread;
Message.Level = message.Level;
Message.Logger = message.Logger;
Message.Message = message.Message;
Message.Exception = message.Exception;
TableOperation insertOperation = TableOperation.Insert(Message);
table.Execute(insertOperation);
break;
}
case "AQS":
/// Save log on Azure Queue Storage
{
/// Azure Queue Storage connection retry policy
var queueStorageRetryPolicy = new ExponentialRetry(TimeSpan.FromSeconds(2), 10);
CloudStorageAccount storageAccount = CloudStorageAccount.Parse(globalVal.StorageConnectionString);
CloudQueueClient queueClient = storageAccount.CreateCloudQueueClient();
queueClient.DefaultRequestOptions.RetryPolicy = queueStorageRetryPolicy;
CloudQueue queue = queueClient.GetQueueReference("messagestolog"); /// must be lower case
CBATSMessageEntity Message = new CBATSMessageEntity(message.memberID, Guid.NewGuid().ToString());
Message.jobID = message.jobID;
Message.Date = DateTimeOffset.UtcNow.ToString("yyyy-MM-ddTHH:mm:ss.fffffffZ");
Message.Thread = message.Thread;
Message.Level = message.Level;
Message.Logger = message.Logger;
Message.Message = message.Message;
Message.Exception = message.Exception;
CloudQueueMessage Qmessage = new CloudQueueMessage(JsonConvert.SerializeObject(Message));
queue.AddMessage(Qmessage);
break;
}
case "redis":
/// todolist - save log on Azure Redis Cache
/// yyyymmdd:memberid:Controller:GUID
{
string redisKey = "";
string redisVal = "";
message.Date = DateTimeOffset.UtcNow.ToString("yyyy-MM-ddTHH:mm:ss.fffffffZ"); ;
redisKey = DateTime.Now.ToUniversalTime().ToString("yyyyMMddHHmm") + ":" + message.memberID + ":" + message.Logger + ":" + Guid.NewGuid().ToString(); // guid - too long key size
redisVal = JsonConvert.SerializeObject(message);
CBRedis.saveRedisLog(redisKey, redisVal, globalVal.CloudBreadGameLogExpTimeDays);
}
break;
//case "DocDB":
// /// @todo save log data on Azure DocumentDB
// break;
default:
/// case do nothing
break;
}
}
catch (Exception)
{
/// catch save log error here.
throw;
}
}
}
return true;
}
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; }
}
public Int32 UpdateRow()
{
//uses default exponential RetyPolicy
RetryPolicy exponentialRetryPolicy = RetryPolicy.DefaultExponential;
String commandText =
@"UPDATE Writer
SET Name=@Name
WHERE Id=3";
Int32 rowsAffected;
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.OpenWithRetry(exponentialRetryPolicy);
using (SqlCommand sqlCommand = new SqlCommand(commandText, connection))
{
SqlParameter sqlParameter = new SqlParameter()
{
ParameterName = "@Name",
Value = "Stendhal",
SqlDbType = SqlDbType.NVarChar,
Size = 20
};
sqlCommand.Parameters.Add(sqlParameter);
rowsAffected = sqlCommand.ExecuteNonQueryWithRetry(exponentialRetryPolicy);
}
}
return rowsAffected;
}
public Int32 InsertRows()
{
// uses default retry policies
String commandText =
@"INSERT INTO Writer
(Id, Name, CountBooks)
VALUES
(1, N'Cervantes', 2),
(2, N'Smollett', null),
(3, 'Beyle', 4)";
Int32 rowsAffected;
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.OpenWithRetry();
using (SqlCommand sqlCommand = new SqlCommand(commandText, connection))
{
rowsAffected = sqlCommand.ExecuteNonQueryWithRetry();
}
}
return rowsAffected;
}
public void AddWorkerNode(WorkerNode workerNode)
{
try
{
using (var connection = new SqlConnection(_connectionString))
{
connection.OpenWithRetry(_retryPolicy);
using (var workerNodeCommand = connection.CreateCommand())
{
workerNodeCommand.CommandText = "INSERT INTO [Stardust].[WorkerNode] " +
"(Id, Url, Heartbeat, Alive) " +
"VALUES(@Id, @Url, @Heartbeat, @Alive)";
workerNodeCommand.Parameters.AddWithValue("@Id", workerNode.Id);
workerNodeCommand.Parameters.AddWithValue("@Url", workerNode.Url.ToString());
workerNodeCommand.Parameters.AddWithValue("@Heartbeat", workerNode.Heartbeat);
workerNodeCommand.Parameters.AddWithValue("@Alive", workerNode.Alive);
workerNodeCommand.ExecuteNonQueryWithRetry(_retryPolicy);
}
}
}
catch (Exception exp)
{
if (exp.Message.Contains("UQ_WorkerNodes_Url"))
{
using (var connection = new SqlConnection(_connectionString))
{
connection.OpenWithRetry(_retryPolicy);
var updateCommandText = @"UPDATE [Stardust].[WorkerNode] SET Heartbeat = @Heartbeat,
Alive = @Alive
WHERE Url = @Url";
using (var command = new SqlCommand(updateCommandText, connection))
{
command.Parameters.Add("@Heartbeat", SqlDbType.DateTime).Value = DateTime.UtcNow;
command.Parameters.Add("@Alive", SqlDbType.Bit).Value = true;
command.Parameters.Add("@Url", SqlDbType.NVarChar).Value = workerNode.Url.ToString();
command.ExecuteNonQueryWithRetry(_retryPolicy);
}
}
return;
}
this.Log().ErrorWithLineNumber(exp.Message, exp);
throw;
}
}
public void RegisterHeartbeat(string nodeUri, bool updateStatus)
{
try
{
using (var connection = new SqlConnection(_connectionString))
{
connection.OpenWithRetry(_retryPolicy);
var updateCommandText = @"UPDATE [Stardust].[WorkerNode] SET Heartbeat = GETUTCDATE(),
Alive = 1
WHERE Url = @Url
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO [Stardust].[WorkerNode] (Id, Url, Heartbeat, Alive)
VALUES(NEWID(), @Url, GETUTCDATE(), 1)
END";
if (!updateStatus)
{
updateCommandText = @"UPDATE [Stardust].[WorkerNode] SET Heartbeat = GETUTCDATE()
WHERE Url = @Url";
}
using (var command = new SqlCommand(updateCommandText, connection))
{
command.Parameters.Add("@Url", SqlDbType.NVarChar).Value = nodeUri;
command.ExecuteNonQueryWithRetry(_retryPolicy);
}
}
}
catch (Exception exp)
{
this.Log().ErrorWithLineNumber(exp.Message, exp);
throw;
}
}
public List<string> CheckNodesAreAlive(TimeSpan timeSpan)
{
var selectCommand = @"SELECT Id,
Url,
Heartbeat,
Alive
FROM [Stardust].[WorkerNode]";
var updateCommandText = @"UPDATE [Stardust].[WorkerNode]
SET Alive = @Alive
WHERE Url = @Url";
var deadNodes = new List<string>();
try
{
using (var connection = new SqlConnection(_connectionString))
{
connection.OpenWithRetry(_retryPolicy);
var ordinalPosForHeartBeat = 0;
var ordinalPosForUrl = 0;
var allNodes = new List<object[]>();
using (var commandSelectAll = new SqlCommand(selectCommand, connection))
{
using (var readAllWorkerNodes = commandSelectAll.ExecuteReaderWithRetry(_retryPolicy))
{
if (readAllWorkerNodes.HasRows)
{
ordinalPosForHeartBeat = readAllWorkerNodes.GetOrdinal("Heartbeat");
ordinalPosForUrl = readAllWorkerNodes.GetOrdinal("Url");
while (readAllWorkerNodes.Read())
{
var temp = new object[readAllWorkerNodes.FieldCount];
readAllWorkerNodes.GetValues(temp);
allNodes.Add(temp);
}
}
}
}
if (allNodes.Any())
{
using (var trans = connection.BeginTransaction())
{
using (var commandUpdate = new SqlCommand(updateCommandText, connection, trans))
{
commandUpdate.Parameters.Add("@Alive", SqlDbType.Bit);
commandUpdate.Parameters.Add("@Url", SqlDbType.NVarChar);
foreach (var node in allNodes)
{
var heartBeatDateTime = (DateTime)node[ordinalPosForHeartBeat];
var url = node[ordinalPosForUrl];
var currentDateTime = DateTime.UtcNow;
var dateDiff = (currentDateTime - heartBeatDateTime).TotalSeconds;
if (dateDiff > timeSpan.TotalSeconds)
{
commandUpdate.Parameters["@Alive"].Value = false;
commandUpdate.Parameters["@Url"].Value = url;
commandUpdate.ExecuteNonQueryWithRetry(_retryPolicy);
deadNodes.Add(url.ToString());
}
}
}
trans.Commit();
}
}
}
}
catch (Exception exp)
{
this.Log().ErrorWithLineNumber(exp.Message, exp);
throw;
}
return deadNodes;
}
private void AssignJobToWorkerNodeWorker(Uri availableNode)
{
try
{
using (var sqlConnection = new SqlConnection(_connectionString))
{
sqlConnection.OpenWithRetry(_retryPolicy);
JobQueueItem jobQueueItem = null;
using (var selectJobQueueItemCommand = new SqlCommand("[Stardust].[AcquireQueuedJob]", sqlConnection))
{
selectJobQueueItemCommand.CommandType = CommandType.StoredProcedure;
SqlParameter retVal = new SqlParameter("@idd", SqlDbType.UniqueIdentifier);
retVal.Direction = ParameterDirection.ReturnValue;
selectJobQueueItemCommand.Parameters.Add(retVal);
using (var reader = selectJobQueueItemCommand.ExecuteReaderWithRetry(_retryPolicy))
{
if (reader.HasRows)
{
reader.Read();
jobQueueItem = CreateJobQueueItemFromSqlDataReader(reader);
}
}
}
if (jobQueueItem == null)
{
sqlConnection.Close();
return;
}
var builderHelper = new NodeUriBuilderHelper(availableNode);
var urijob = builderHelper.GetJobTemplateUri();
var response = _httpSender.PostAsync(urijob, jobQueueItem).Result;
if (response != null && (response.IsSuccessStatusCode || response.StatusCode.Equals(HttpStatusCode.BadRequest)))
{
string sentToWorkerNodeUri = availableNode.ToString();
using (var sqlTransaction = sqlConnection.BeginTransaction())
{
using (var insertIntoJobCommand = _createSqlCommandHelper.CreateInsertIntoJobCommand(jobQueueItem, sentToWorkerNodeUri, sqlConnection, sqlTransaction))
{
if (response.IsSuccessStatusCode)
{
insertIntoJobCommand.Parameters.AddWithValue("@Result", DBNull.Value);
}
else
{
insertIntoJobCommand.Parameters.AddWithValue("@Result", response.ReasonPhrase);
}
insertIntoJobCommand.ExecuteNonQueryWithRetry(_retryPolicy);
}
using (var deleteJobQueueItemCommand = _createSqlCommandHelper.CreateDeleteFromJobQueueCommand(jobQueueItem.JobId, sqlConnection, sqlTransaction))
{
deleteJobQueueItemCommand.ExecuteNonQueryWithRetry(_retryPolicy);
}
Retry(sqlTransaction.Commit);
using (var insertIntoJobDetailsCommand = _createSqlCommandHelper.CreateInsertIntoJobDetailCommand(jobQueueItem.JobId, "Job Started", DateTime.UtcNow, sqlConnection))
{
insertIntoJobDetailsCommand.ExecuteNonQueryWithRetry(_retryPolicy);
}
}
urijob = builderHelper.GetUpdateJobUri(jobQueueItem.JobId);
//what should happen if this response is not 200?
var resp = _httpSender.PutAsync(urijob, null);
}
else
{
using (var sqlTransaction = sqlConnection.BeginTransaction())
{
if (response == null)
{
var updateCommandText = @"UPDATE [Stardust].[WorkerNode]
SET Alive = @Alive
WHERE Url = @Url";
using (var command = new SqlCommand(updateCommandText, sqlConnection, sqlTransaction))
{
command.Parameters.Add("@Alive", SqlDbType.Bit).Value = false;
command.Parameters.Add("@Url", SqlDbType.NVarChar).Value = availableNode.ToString();
command.ExecuteNonQueryWithRetry(_retryPolicy);
}
}
var commandText = "update [Stardust].[JobQueue] set Tagged = NULL where JobId = @Id";
using (var cmd = new SqlCommand(commandText, sqlConnection, sqlTransaction))
{
cmd.Parameters.AddWithValue("@Id", jobQueueItem.JobId);
cmd.ExecuteNonQuery();
}
sqlTransaction.Commit();
}
}
}
}
catch (Exception exp)
{
this.Log().ErrorWithLineNumber(exp.Message, exp);
throw;
}
}
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);
}
}