public void TestSqlConnectionExtensions() { using (SqlConnection connection = new SqlConnection(TestSqlSupport.SqlDatabaseConnectionString)) { using (SqlCommand command = new SqlCommand("SELECT [ProductCategoryID], [Name] FROM [SalesLT].[ProductCategory]", connection)) { connection.OpenWithRetry(); using (IDataReader reader = command.ExecuteReader()) { while (reader.Read()) { int id = reader.GetInt32(0); string name = reader.GetString(1); Trace.WriteLine(string.Format("{0}: {1}", id, name)); } reader.Close(); } connection.Close(); } } }
public List<JobQueueItem> GetAllItemsInJobQueue() { var listToReturn = new List<JobQueueItem>(); try { using (var sqlConnection = new SqlConnection(_connectionString)) { sqlConnection.OpenWithRetry(_retryPolicy); using (var sqlCommand = _createSqlCommandHelper.CreateSelectAllItemsInJobQueueCommand(sqlConnection)) { using (var sqlDataReader = sqlCommand.ExecuteReaderWithRetry(_retryPolicy)) { if (sqlDataReader.HasRows) { while (sqlDataReader.Read()) { var jobQueueItem = CreateJobQueueItemFromSqlDataReader(sqlDataReader); listToReturn.Add(jobQueueItem); } } } } } } catch (Exception exp) { this.Log().ErrorWithLineNumber(exp.Message, exp); throw; } return listToReturn; }
public void AddItemToJobQueue(JobQueueItem jobQueueItem) { try { Monitor.Enter(_lockAddItemToJobQueue); using (var sqlConnection = new SqlConnection(_connectionString)) { sqlConnection.OpenWithRetry(_retryPolicy); using (var sqlCommand = _createSqlCommandHelper.CreateInsertIntoJobQueueCommand(jobQueueItem, sqlConnection, null)) { sqlCommand.ExecuteNonQueryWithRetry(_retryPolicy); } } } catch (Exception exp) { this.Log().ErrorWithLineNumber(exp.Message, exp); throw; } finally { Monitor.Exit(_lockAddItemToJobQueue); } }
void Connect() { // Cleanup any existing connection if (null != conn) conn.Dispose(); // Create new connection var retry = new RetryPolicy<DbRetryStrategy>(3, new TimeSpan(0, 0, 1)); conn = new SqlConnection(connectionstring); conn.OpenWithRetry(retry); }
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 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 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); } } }
public List<WorkerNode> GetAllWorkerNodes() { var listToReturn = new List<WorkerNode>(); try { using (var connection = new SqlConnection(_connectionString)) { using (var command = connection.CreateCommand()) { command.CommandText = "SELECT Id, Url, Heartbeat, Alive " + "FROM [Stardust].[WorkerNode]"; connection.OpenWithRetry(_retryPolicy); using (var reader = command.ExecuteReaderWithRetry(_retryPolicy)) { if (reader.HasRows) { var ordinalPositionForIdField = reader.GetOrdinal("Id"); var ordinalPositionForUrlField = reader.GetOrdinal("Url"); var ordinalPositionForAliveField = reader.GetOrdinal("Alive"); var ordinalPositionForHeartbeatField = reader.GetOrdinal("Heartbeat"); while (reader.Read()) { var workerNode = new WorkerNode { Id = (Guid)reader.GetValue(ordinalPositionForIdField), Url = new Uri((string)reader.GetValue(ordinalPositionForUrlField)), Alive = (bool)reader.GetValue(ordinalPositionForAliveField), Heartbeat = (DateTime)reader.GetValue(ordinalPositionForHeartbeatField) }; listToReturn.Add(workerNode); } } } } } return listToReturn; } catch (Exception exp) { this.Log().ErrorWithLineNumber(exp.Message, exp); throw; } }
public HttpResponseMessage Post(SelLoginIDDupeCheckInputParams p) { // try decrypt data if (!string.IsNullOrEmpty(p.token) && globalVal.CloudBreadCryptSetting == "AES256") { try { string decrypted = Crypto.AES_decrypt(p.token, globalVal.CloudBreadCryptKey, globalVal.CloudBreadCryptIV); p = JsonConvert.DeserializeObject<SelLoginIDDupeCheckInputParams>(decrypted); } catch (Exception ex) { ex = (Exception)Activator.CreateInstance(ex.GetType(), "Decrypt Error", ex); throw ex; } } // Get the sid or memberID of the current user. string sid = CBAuth.getMemberID(p.memberID, this.User as ClaimsPrincipal); p.memberID = sid; Logging.CBLoggers logMessage = new Logging.CBLoggers(); string jsonParam = JsonConvert.SerializeObject(p); SelLoginIDDupeCheckResult result = new SelLoginIDDupeCheckResult(); HttpResponseMessage response = new HttpResponseMessage(); EncryptedData encryptedResult = new EncryptedData(); try { /// Database connection retry policy RetryPolicy retryPolicy = new RetryPolicy<SqlAzureTransientErrorDetectionStrategy>(globalVal.conRetryCount, TimeSpan.FromSeconds(globalVal.conRetryFromSeconds)); using (SqlConnection connection = new SqlConnection(globalVal.DBConnectionString)) { using(SqlCommand command = new SqlCommand("uspSelLoginIDDupeCheck", connection)) { command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("@FindID", SqlDbType.NVarChar, -1).Value = p.findID; command.Parameters.Add("@Category", SqlDbType.NVarChar, -1).Value = p.category; connection.OpenWithRetry(retryPolicy); using(SqlDataReader dreader = command.ExecuteReaderWithRetry(retryPolicy)) { while (dreader.Read()) { result.result = dreader[0].ToString(); } dreader.Close(); } connection.Close(); /// Encrypt the result response if (globalVal.CloudBreadCryptSetting == "AES256") { try { encryptedResult.token = Crypto.AES_encrypt(JsonConvert.SerializeObject(result), globalVal.CloudBreadCryptKey, globalVal.CloudBreadCryptIV); response = Request.CreateResponse(HttpStatusCode.OK, encryptedResult); return response; } catch (Exception ex) { ex = (Exception)Activator.CreateInstance(ex.GetType(), "Encrypt Error", ex); throw ex; } } response = Request.CreateResponse(HttpStatusCode.OK, result); return response; } } } catch (Exception ex) { // error log logMessage.memberID = p.memberID; logMessage.Level = "ERROR"; logMessage.Logger = "CBSelLoginIDDupeCheckController"; logMessage.Message = jsonParam; logMessage.Exception = ex.ToString(); Logging.RunLog(logMessage); throw; } }
public HttpResponseMessage Post(SelSendEmailToMemberInputParams p) { // try decrypt data if (!string.IsNullOrEmpty(p.token) && globalVal.CloudBreadCryptSetting == "AES256") { try { string decrypted = Crypto.AES_decrypt(p.token, globalVal.CloudBreadCryptKey, globalVal.CloudBreadCryptIV); p = JsonConvert.DeserializeObject<SelSendEmailToMemberInputParams>(decrypted); } catch (Exception ex) { ex = (Exception)Activator.CreateInstance(ex.GetType(), "Decrypt Error", ex); throw ex; } } // Get the sid or memberID of the current user. string sid = CBAuth.getMemberID(p.memberID, this.User as ClaimsPrincipal); p.memberID = sid; // check proper authentication of member who trigger this API (Admin or member with authorized) Logging.CBLoggers logMessage = new Logging.CBLoggers(); string jsonParam = JsonConvert.SerializeObject(p); HttpResponseMessage response = new HttpResponseMessage(); EncryptedData encryptedResult = new EncryptedData(); RowcountResult rowcountResult = new RowcountResult(); try { /// Database connection retry policy RetryPolicy retryPolicy = new RetryPolicy<SqlAzureTransientErrorDetectionStrategy>(globalVal.conRetryCount, TimeSpan.FromSeconds(globalVal.conRetryFromSeconds)); using (SqlConnection connection = new SqlConnection(globalVal.DBConnectionString)) { using (SqlCommand command = new SqlCommand("uspSelSendEmailToMember", connection)) { command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("@MemberID", SqlDbType.NVarChar, -1).Value = p.memberID; connection.OpenWithRetry(retryPolicy); using (SqlDataReader dreader = command.ExecuteReaderWithRetry(retryPolicy)) { while (dreader.Read()) { rowcountResult.result = dreader[0].ToString(); } ////////////////////////////////////////////////////////////////////////////////////// //// mail sending module - reference CloudBreadlib/BAL/SendSMTPMail //// check firewall, anti-virus and outbound traffic //// in SendEmail lib, change your mail login info //string s = SendSMTPMail.SendEmail(dreader[0].ToString(), "subject", "content"); ////////////////////////////////////////////////////////////////////////////////////// dreader.Close(); } connection.Close(); /// Encrypt the result response if (globalVal.CloudBreadCryptSetting == "AES256") { try { encryptedResult.token = Crypto.AES_encrypt(JsonConvert.SerializeObject(rowcountResult), globalVal.CloudBreadCryptKey, globalVal.CloudBreadCryptIV); response = Request.CreateResponse(HttpStatusCode.OK, encryptedResult); return response; } catch (Exception ex) { ex = (Exception)Activator.CreateInstance(ex.GetType(), "Encrypt Error", ex); throw ex; } } response = Request.CreateResponse(HttpStatusCode.OK, rowcountResult); return response; // or return mail send result string s } } } catch (Exception ex) { // error log logMessage.memberID = p.memberID; logMessage.Level = "ERROR"; logMessage.Logger = "CBSelSendEmailToMemberController"; logMessage.Message = jsonParam; logMessage.Exception = ex.ToString(); Logging.RunLog(logMessage); throw ; } }
public HttpResponseMessage Post(COMUdtMemberInputParams p) { // try decrypt data if (!string.IsNullOrEmpty(p.token) && globalVal.CloudBreadCryptSetting == "AES256") { try { string decrypted = Crypto.AES_decrypt(p.token, globalVal.CloudBreadCryptKey, globalVal.CloudBreadCryptIV); p = JsonConvert.DeserializeObject<COMUdtMemberInputParams>(decrypted); } catch (Exception ex) { ex = (Exception)Activator.CreateInstance(ex.GetType(), "Decrypt Error", ex); throw ex; } } // Get the sid or memberID of the current user. string sid = CBAuth.getMemberID(p.MemberID, this.User as ClaimsPrincipal); p.MemberID = sid; Logging.CBLoggers logMessage = new Logging.CBLoggers(); string jsonParam = JsonConvert.SerializeObject(p); RowcountResult rowcountResult = new RowcountResult(); HttpResponseMessage response = new HttpResponseMessage(); EncryptedData encryptedResult = new EncryptedData(); try { // start task log //logMessage.memberID = p.MemberID; //logMessage.Level = "INFO"; //logMessage.Logger = "CBCOMUdtMemberController"; //logMessage.Message = jsonParam; //Logging.RunLog(logMessage); /// Database connection retry policy RetryPolicy retryPolicy = new RetryPolicy<SqlAzureTransientErrorDetectionStrategy>(globalVal.conRetryCount, TimeSpan.FromSeconds(globalVal.conRetryFromSeconds)); using (SqlConnection connection = new SqlConnection(globalVal.DBConnectionString)) { using (SqlCommand command = new SqlCommand("uspComUdtMember", connection)) { command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("@MemberID", SqlDbType.NVarChar, -1).Value = p.MemberID; command.Parameters.Add("@MemberPWD", SqlDbType.NVarChar, -1).Value = p.MemberPWD; command.Parameters.Add("@EmailAddress", SqlDbType.NVarChar, -1).Value = p.EmailAddress; command.Parameters.Add("@EmailConfirmedYN", SqlDbType.NVarChar, -1).Value = p.EmailConfirmedYN; command.Parameters.Add("@PhoneNumber1", SqlDbType.NVarChar, -1).Value = p.PhoneNumber1; command.Parameters.Add("@PhoneNumber2", SqlDbType.NVarChar, -1).Value = p.PhoneNumber2; command.Parameters.Add("@PINumber", SqlDbType.NVarChar, -1).Value = p.PINumber; command.Parameters.Add("@Name1", SqlDbType.NVarChar, -1).Value = p.Name1; command.Parameters.Add("@Name2", SqlDbType.NVarChar, -1).Value = p.Name2; command.Parameters.Add("@Name3", SqlDbType.NVarChar, -1).Value = p.Name3; command.Parameters.Add("@DOB", SqlDbType.NVarChar, -1).Value = p.DOB; command.Parameters.Add("@RecommenderID", SqlDbType.NVarChar, -1).Value = p.RecommenderID; command.Parameters.Add("@MemberGroup", SqlDbType.NVarChar, -1).Value = p.MemberGroup; command.Parameters.Add("@LastDeviceID", SqlDbType.NVarChar, -1).Value = p.LastDeviceID; command.Parameters.Add("@LastIPaddress", SqlDbType.NVarChar, -1).Value = p.LastIPaddress; command.Parameters.Add("@LastLoginDT", SqlDbType.NVarChar, -1).Value = p.LastLoginDT; command.Parameters.Add("@LastLogoutDT", SqlDbType.NVarChar, -1).Value = p.LastLogoutDT; command.Parameters.Add("@LastMACAddress", SqlDbType.NVarChar, -1).Value = p.LastMACAddress; command.Parameters.Add("@AccountBlockYN", SqlDbType.NVarChar, -1).Value = p.AccountBlockYN; command.Parameters.Add("@AccountBlockEndDT", SqlDbType.NVarChar, -1).Value = p.AccountBlockEndDT; command.Parameters.Add("@AnonymousYN", SqlDbType.NVarChar, -1).Value = p.AnonymousYN; command.Parameters.Add("@3rdAuthProvider", SqlDbType.NVarChar, -1).Value = p._3rdAuthProvider; command.Parameters.Add("@3rdAuthID", SqlDbType.NVarChar, -1).Value = p._3rdAuthID; command.Parameters.Add("@3rdAuthParam", SqlDbType.NVarChar, -1).Value = p._3rdAuthParam; command.Parameters.Add("@PushNotificationID", SqlDbType.NVarChar, -1).Value = p.PushNotificationID; command.Parameters.Add("@PushNotificationProvider", SqlDbType.NVarChar, -1).Value = p.PushNotificationProvider; command.Parameters.Add("@PushNotificationGroup", SqlDbType.NVarChar, -1).Value = p.PushNotificationGroup; command.Parameters.Add("@sCol1", SqlDbType.NVarChar, -1).Value = p.sCol1; command.Parameters.Add("@sCol2", SqlDbType.NVarChar, -1).Value = p.sCol2; command.Parameters.Add("@sCol3", SqlDbType.NVarChar, -1).Value = p.sCol3; command.Parameters.Add("@sCol4", SqlDbType.NVarChar, -1).Value = p.sCol4; command.Parameters.Add("@sCol5", SqlDbType.NVarChar, -1).Value = p.sCol5; command.Parameters.Add("@sCol6", SqlDbType.NVarChar, -1).Value = p.sCol6; command.Parameters.Add("@sCol7", SqlDbType.NVarChar, -1).Value = p.sCol7; command.Parameters.Add("@sCol8", SqlDbType.NVarChar, -1).Value = p.sCol8; command.Parameters.Add("@sCol9", SqlDbType.NVarChar, -1).Value = p.sCol9; command.Parameters.Add("@sCol10 ", SqlDbType.NVarChar, -1).Value = p.sCol10; command.Parameters.Add("@TimeZoneID ", SqlDbType.NVarChar, -1).Value = p.TimeZoneID; connection.OpenWithRetry(retryPolicy); using (SqlDataReader dreader = command.ExecuteReaderWithRetry(retryPolicy)) { while (dreader.Read()) { rowcountResult.result = dreader[0].ToString(); } dreader.Close(); } connection.Close(); // end task log logMessage.memberID = p.MemberID; logMessage.Level = "INFO"; logMessage.Logger = "CBCOMUdtMemberController"; logMessage.Message = jsonParam; Logging.RunLog(logMessage); /// Encrypt the result response if (globalVal.CloudBreadCryptSetting == "AES256") { try { encryptedResult.token = Crypto.AES_encrypt(JsonConvert.SerializeObject(rowcountResult), globalVal.CloudBreadCryptKey, globalVal.CloudBreadCryptIV); response = Request.CreateResponse(HttpStatusCode.OK, encryptedResult); return response; } catch (Exception ex) { ex = (Exception)Activator.CreateInstance(ex.GetType(), "Encrypt Error", ex); throw ex; } } response = Request.CreateResponse(HttpStatusCode.OK, rowcountResult); return response; } } } catch (Exception ex) { // error log logMessage.memberID = p.MemberID; logMessage.Level = "ERROR"; logMessage.Logger = "CBCOMUdtMemberController"; logMessage.Message = jsonParam; logMessage.Exception = ex.ToString(); Logging.RunLog(logMessage); throw; } }
public void RequeueJobThatDidNotEndByWorkerNodeUri(string workerNodeUri) { try { using (var sqlConnection = new SqlConnection(_connectionString)) { sqlConnection.OpenWithRetry(_retryPolicy); using (var sqlTransaction = sqlConnection.BeginTransaction()) { Job job = null; using (var selectJobThatDidNotEndCommand = _createSqlCommandHelper.CreateSelectJobThatDidNotEndCommand(workerNodeUri, sqlConnection, sqlTransaction)) { using (var sqlDataReader = selectJobThatDidNotEndCommand.ExecuteReaderWithRetry(_retryPolicy)) { if (sqlDataReader.HasRows) { sqlDataReader.Read(); job = CreateJobFromSqlDataReader(sqlDataReader); } } } if (job != null) { var jobQueueItem = new JobQueueItem { Created = job.Created, CreatedBy = job.CreatedBy, JobId = job.JobId, Serialized = job.Serialized, Name = job.Name, Type = job.Type }; using (var insertIntojobQueueCommand = _createSqlCommandHelper.CreateInsertIntoJobQueueCommand(jobQueueItem, sqlConnection, sqlTransaction)) { insertIntojobQueueCommand.ExecuteNonQueryWithRetry(_retryPolicy); } using (var deleteJobByJobIdCommand = _createSqlCommandHelper.CreateDeleteJobByJobIdCommand(jobQueueItem.JobId, sqlConnection, sqlTransaction)) { deleteJobByJobIdCommand.ExecuteNonQueryWithRetry(_retryPolicy); } Retry(sqlTransaction.Commit); } } } } catch (Exception exp) { this.Log().ErrorWithLineNumber(exp.Message, exp); throw; } }
public HttpResponseMessage Post(AddUseMemberItemInputParams p) { // try decrypt data if (!string.IsNullOrEmpty(p.token) && globalVal.CloudBreadCryptSetting == "AES256") { try { string decrypted = Crypto.AES_decrypt(p.token, globalVal.CloudBreadCryptKey, globalVal.CloudBreadCryptIV); p = JsonConvert.DeserializeObject<AddUseMemberItemInputParams>(decrypted); } catch (Exception ex) { ex = (Exception)Activator.CreateInstance(ex.GetType(), "Decrypt Error", ex); throw ex; } } // Get the sid or memberID of the current user. string sid = CBAuth.getMemberID(p.MemberID_MemberGameInfoes, this.User as ClaimsPrincipal); p.MemberID_MemberGameInfoes = sid; p.MemberID_MemberItem = sid; Logging.CBLoggers logMessage = new Logging.CBLoggers(); string jsonParam = JsonConvert.SerializeObject(p); HttpResponseMessage response = new HttpResponseMessage(); EncryptedData encryptedResult = new EncryptedData(); RowcountResult rowcountResult = new RowcountResult(); try { // start task log //logMessage.memberID = p.MemberID_MemberItem; //logMessage.Level = "INFO"; //logMessage.Logger = "CBAddUseMemberItemController"; //logMessage.Message = jsonParam; //Logging.RunLog(logMessage); /// Database connection retry policy RetryPolicy retryPolicy = new RetryPolicy<SqlAzureTransientErrorDetectionStrategy>(globalVal.conRetryCount, TimeSpan.FromSeconds(globalVal.conRetryFromSeconds)); using (SqlConnection connection = new SqlConnection(globalVal.DBConnectionString)) { using (SqlCommand command = new SqlCommand("uspAddUseMemberItem", connection)) { command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("@InsertORUpdateORDelete", SqlDbType.NVarChar, -1).Value = p.InsertORUpdateORDelete.ToUpper(); command.Parameters.Add("@MemberItemID_MemberItem", SqlDbType.NVarChar, -1).Value = p.MemberItemID_MemberItem; command.Parameters.Add("@MemberID_MemberItem", SqlDbType.NVarChar, -1).Value = p.MemberID_MemberItem; command.Parameters.Add("@ItemListID_MemberItem", SqlDbType.NVarChar, -1).Value = p.ItemListID_MemberItem; command.Parameters.Add("@ItemCount_MemberItem", SqlDbType.NVarChar, -1).Value = p.ItemCount_MemberItem; command.Parameters.Add("@ItemStatus_MemberItem", SqlDbType.NVarChar, -1).Value = p.ItemStatus_MemberItem; command.Parameters.Add("@sCol1_MemberItem", SqlDbType.NVarChar, -1).Value = p.sCol1_MemberItem; command.Parameters.Add("@sCol2_MemberItem", SqlDbType.NVarChar, -1).Value = p.sCol2_MemberItem; command.Parameters.Add("@sCol3_MemberItem", SqlDbType.NVarChar, -1).Value = p.sCol3_MemberItem; command.Parameters.Add("@sCol4_MemberItem", SqlDbType.NVarChar, -1).Value = p.sCol4_MemberItem; command.Parameters.Add("@sCol5_MemberItem", SqlDbType.NVarChar, -1).Value = p.sCol5_MemberItem; command.Parameters.Add("@sCol6_MemberItem", SqlDbType.NVarChar, -1).Value = p.sCol6_MemberItem; command.Parameters.Add("@sCol7_MemberItem", SqlDbType.NVarChar, -1).Value = p.sCol7_MemberItem; command.Parameters.Add("@sCol8_MemberItem", SqlDbType.NVarChar, -1).Value = p.sCol8_MemberItem; command.Parameters.Add("@sCol9_MemberItem", SqlDbType.NVarChar, -1).Value = p.sCol9_MemberItem; command.Parameters.Add("@sCol10_MemberItem", SqlDbType.NVarChar, -1).Value = p.sCol10_MemberItem; command.Parameters.Add("@MemberID_MemberGameInfoes", SqlDbType.NVarChar, -1).Value = p.MemberID_MemberGameInfoes; command.Parameters.Add("@Level_MemberGameInfoes", SqlDbType.NVarChar, -1).Value = p.Level_MemberGameInfoes; command.Parameters.Add("@Exps_MemberGameInfoes", SqlDbType.NVarChar, -1).Value = p.Exps_MemberGameInfoes; command.Parameters.Add("@Points_MemberGameInfoes", SqlDbType.NVarChar, -1).Value = p.Points_MemberGameInfoes; command.Parameters.Add("@UserSTAT1_MemberGameInfoes", SqlDbType.NVarChar, -1).Value = p.UserSTAT1_MemberGameInfoes; command.Parameters.Add("@UserSTAT2_MemberGameInfoes", SqlDbType.NVarChar, -1).Value = p.UserSTAT2_MemberGameInfoes; command.Parameters.Add("@UserSTAT3_MemberGameInfoes", SqlDbType.NVarChar, -1).Value = p.UserSTAT3_MemberGameInfoes; command.Parameters.Add("@UserSTAT4_MemberGameInfoes", SqlDbType.NVarChar, -1).Value = p.UserSTAT4_MemberGameInfoes; command.Parameters.Add("@UserSTAT5_MemberGameInfoes", SqlDbType.NVarChar, -1).Value = p.UserSTAT5_MemberGameInfoes; command.Parameters.Add("@UserSTAT6_MemberGameInfoes", SqlDbType.NVarChar, -1).Value = p.UserSTAT6_MemberGameInfoes; command.Parameters.Add("@UserSTAT7_MemberGameInfoes", SqlDbType.NVarChar, -1).Value = p.UserSTAT7_MemberGameInfoes; command.Parameters.Add("@UserSTAT8_MemberGameInfoes", SqlDbType.NVarChar, -1).Value = p.UserSTAT8_MemberGameInfoes; command.Parameters.Add("@UserSTAT9_MemberGameInfoes", SqlDbType.NVarChar, -1).Value = p.UserSTAT9_MemberGameInfoes; command.Parameters.Add("@UserSTAT10_MemberGameInfoes", SqlDbType.NVarChar, -1).Value = p.UserSTAT10_MemberGameInfoes; command.Parameters.Add("@sCol1_MemberGameInfoes", SqlDbType.NVarChar, -1).Value = p.sCol1_MemberGameInfoes; command.Parameters.Add("@sCol2_MemberGameInfoes", SqlDbType.NVarChar, -1).Value = p.sCol2_MemberGameInfoes; command.Parameters.Add("@sCol3_MemberGameInfoes", SqlDbType.NVarChar, -1).Value = p.sCol3_MemberGameInfoes; command.Parameters.Add("@sCol4_MemberGameInfoes", SqlDbType.NVarChar, -1).Value = p.sCol4_MemberGameInfoes; command.Parameters.Add("@sCol5_MemberGameInfoes", SqlDbType.NVarChar, -1).Value = p.sCol5_MemberGameInfoes; command.Parameters.Add("@sCol6_MemberGameInfoes", SqlDbType.NVarChar, -1).Value = p.sCol6_MemberGameInfoes; command.Parameters.Add("@sCol7_MemberGameInfoes", SqlDbType.NVarChar, -1).Value = p.sCol7_MemberGameInfoes; command.Parameters.Add("@sCol8_MemberGameInfoes", SqlDbType.NVarChar, -1).Value = p.sCol8_MemberGameInfoes; command.Parameters.Add("@sCol9_MemberGameInfoes", SqlDbType.NVarChar, -1).Value = p.sCol9_MemberGameInfoes; command.Parameters.Add("@sCol10_MemberGameInfoes", SqlDbType.NVarChar, -1).Value = p.sCol10_MemberGameInfoes; connection.OpenWithRetry(retryPolicy); using (SqlDataReader dreader = command.ExecuteReaderWithRetry(retryPolicy)) { while (dreader.Read()) { rowcountResult.result = dreader[0].ToString(); } dreader.Close(); } connection.Close(); // end task log logMessage.memberID = p.MemberID_MemberItem; logMessage.Level = "INFO"; logMessage.Logger = "CBAddUseMemberItemController"; logMessage.Message = jsonParam; Logging.RunLog(logMessage); /// Encrypt the result response if (globalVal.CloudBreadCryptSetting == "AES256") { try { encryptedResult.token = Crypto.AES_encrypt(JsonConvert.SerializeObject(rowcountResult), globalVal.CloudBreadCryptKey, globalVal.CloudBreadCryptIV); response = Request.CreateResponse(HttpStatusCode.OK, encryptedResult); return response; } catch (Exception ex) { ex = (Exception)Activator.CreateInstance(ex.GetType(), "Encrypt Error", ex); throw ex; } } response = Request.CreateResponse(HttpStatusCode.OK, rowcountResult); return response; } } } catch (Exception ex) { // error log logMessage.memberID = p.MemberID_MemberItem; logMessage.Level = "ERROR"; logMessage.Logger = "CBAddUseMemberItemController"; logMessage.Message = jsonParam; logMessage.Exception = ex.ToString(); Logging.RunLog(logMessage); throw; } }
public HttpResponseMessage Post(UdtMoveGiftInputParams p) { // try decrypt data if (!string.IsNullOrEmpty(p.token) && globalVal.CloudBreadCryptSetting == "AES256") { try { string decrypted = Crypto.AES_decrypt(p.token, globalVal.CloudBreadCryptKey, globalVal.CloudBreadCryptIV); p = JsonConvert.DeserializeObject<UdtMoveGiftInputParams>(decrypted); } catch (Exception ex) { ex = (Exception)Activator.CreateInstance(ex.GetType(), "Decrypt Error", ex); throw ex; } } // Get the sid or memberID of the current user. string sid = CBAuth.getMemberID(p.MemberID, this.User as ClaimsPrincipal); p.MemberID = sid; Logging.CBLoggers logMessage = new Logging.CBLoggers(); string jsonParam = JsonConvert.SerializeObject(p); HttpResponseMessage response = new HttpResponseMessage(); EncryptedData encryptedResult = new EncryptedData(); RowcountResult rowcountResult = new RowcountResult(); try { // task start log //logMessage.memberID = p.MemberID; //logMessage.Level = "INFO"; //logMessage.Logger = "CBUdtMoveGiftController"; //logMessage.Message = jsonParam; //Logging.RunLog(logMessage); /// Database connection retry policy RetryPolicy retryPolicy = new RetryPolicy<SqlAzureTransientErrorDetectionStrategy>(globalVal.conRetryCount, TimeSpan.FromSeconds(globalVal.conRetryFromSeconds)); using (SqlConnection connection = new SqlConnection(globalVal.DBConnectionString)) { using (SqlCommand command = new SqlCommand("uspUdtMoveGift", connection)) { command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("@InsertORUpdate", SqlDbType.NVarChar, -1).Value = p.InsertORUpdate.ToUpper(); // INSERT UPDATE 여부 분기 command.Parameters.Add("@GiftDepositoryID", SqlDbType.NVarChar, -1).Value = p.GiftDepositoryID; command.Parameters.Add("@MemberItemID", SqlDbType.NVarChar, -1).Value = p.MemberItemID; command.Parameters.Add("@MemberID", SqlDbType.NVarChar, -1).Value = p.MemberID; command.Parameters.Add("@ItemListID", SqlDbType.NVarChar, -1).Value = p.ItemListID; command.Parameters.Add("@ItemCount", SqlDbType.NVarChar, -1).Value = p.ItemCount; command.Parameters.Add("@ItemStatus", SqlDbType.NVarChar, -1).Value = p.ItemStatus; command.Parameters.Add("@sCol1", SqlDbType.NVarChar, -1).Value = p.sCol1; command.Parameters.Add("@sCol2", SqlDbType.NVarChar, -1).Value = p.sCol2; command.Parameters.Add("@sCol3", SqlDbType.NVarChar, -1).Value = p.sCol3; command.Parameters.Add("@sCol4", SqlDbType.NVarChar, -1).Value = p.sCol4; command.Parameters.Add("@sCol5", SqlDbType.NVarChar, -1).Value = p.sCol5; command.Parameters.Add("@sCol6", SqlDbType.NVarChar, -1).Value = p.sCol6; command.Parameters.Add("@sCol7", SqlDbType.NVarChar, -1).Value = p.sCol7; command.Parameters.Add("@sCol8", SqlDbType.NVarChar, -1).Value = p.sCol8; command.Parameters.Add("@sCol9", SqlDbType.NVarChar, -1).Value = p.sCol9; command.Parameters.Add("@sCol10", SqlDbType.NVarChar, -1).Value = p.sCol10; connection.OpenWithRetry(retryPolicy); using (SqlDataReader dreader = command.ExecuteReaderWithRetry(retryPolicy)) { while (dreader.Read()) { rowcountResult.result = dreader[0].ToString(); } dreader.Close(); } connection.Close(); // task end log logMessage.memberID = p.MemberID; logMessage.Level = "INFO"; logMessage.Logger = "CBUdtMoveGiftController"; logMessage.Message = jsonParam; Logging.RunLog(logMessage); /// Encrypt the result response if (globalVal.CloudBreadCryptSetting == "AES256") { try { encryptedResult.token = Crypto.AES_encrypt(JsonConvert.SerializeObject(rowcountResult), globalVal.CloudBreadCryptKey, globalVal.CloudBreadCryptIV); response = Request.CreateResponse(HttpStatusCode.OK, encryptedResult); return response; } catch (Exception ex) { ex = (Exception)Activator.CreateInstance(ex.GetType(), "Encrypt Error", ex); throw ex; } } response = Request.CreateResponse(HttpStatusCode.OK, rowcountResult); return response; } } } catch (Exception ex) { // error log logMessage.memberID = p.MemberID; logMessage.Level = "ERROR"; logMessage.Logger = "CBUdtMoveGiftController"; logMessage.Message = jsonParam; logMessage.Exception = ex.ToString(); Logging.RunLog(logMessage); throw; } }
public void UpdateResultForJob(Guid jobId,string result, DateTime ended) { using (var sqlConnection = new SqlConnection(_connectionString)) { sqlConnection.OpenWithRetry(_retryPolicy); using (var updateResultCommand = _createSqlCommandHelper.CreateUpdateResultCommand(jobId, result, ended, sqlConnection)) { updateResultCommand.ExecuteNonQueryWithRetry(_retryPolicy); } var finishDetail = "Job finished"; if (result == "Canceled") finishDetail = "Job was canceled"; else if (result == "Fatal Node Failure" || result == "Failed") finishDetail = "Job Failed"; using (var insertJobDetailCommand = _createSqlCommandHelper.CreateInsertIntoJobDetailCommand(jobId, finishDetail, DateTime.UtcNow, sqlConnection)) { insertJobDetailCommand.ExecuteNonQueryWithRetry(_retryPolicy); } } }
public void AssignJobToWorkerNode() { try { var allAliveWorkerNodesUri = new List<Uri>(); using (var sqlConnection = new SqlConnection(_connectionString)) { sqlConnection.OpenWithRetry(_retryPolicy); using (var selectAllAliveWorkerNodesCommand = _createSqlCommandHelper.CreateSelectAllAliveWorkerNodesCommand(sqlConnection)) { using (var readerAliveWorkerNodes = selectAllAliveWorkerNodesCommand.ExecuteReaderWithRetry(_retryPolicy)) { if (readerAliveWorkerNodes.HasRows) { var ordinalPosForUrl = readerAliveWorkerNodes.GetOrdinal("Url"); while (readerAliveWorkerNodes.Read()) { allAliveWorkerNodesUri.Add(new Uri(readerAliveWorkerNodes.GetString(ordinalPosForUrl))); } } } } } if (!allAliveWorkerNodesUri.Any()) return; foreach (var uri in allAliveWorkerNodesUri) { AssignJobToWorkerNodeWorker(uri); } } catch (Exception exp) { this.Log().ErrorWithLineNumber(exp.Message, exp); } }
public IList<Job> GetAllExecutingJobs() { var jobs = new List<Job>(); try { using (var sqlConnection = new SqlConnection(_connectionString)) { sqlConnection.OpenWithRetry(_retryPolicy); using (var getAllExecutingJobsCommand = _createSqlCommandHelper.CreateGetAllExecutingJobsCommand(sqlConnection)) { using (var sqlDataReader = getAllExecutingJobsCommand.ExecuteReaderWithRetry(_retryPolicy)) { if (sqlDataReader.HasRows) { while (sqlDataReader.Read()) { var job = CreateJobFromSqlDataReader(sqlDataReader); jobs.Add(job); } } } } } } catch (Exception exp) { this.Log().ErrorWithLineNumber(exp.Message, exp); throw; } return jobs; }
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; }
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 IList<JobDetail> GetJobDetailsByJobId(Guid jobId) { try { var jobDetails = new List<JobDetail>(); using (var sqlConnection = new SqlConnection(_connectionString)) { sqlConnection.OpenWithRetry(_retryPolicy); using (var selectJobDetailByJobIdCommand = _createSqlCommandHelper.CreateSelectJobDetailByJobIdCommand(jobId, sqlConnection)) { using (var sqlDataReader = selectJobDetailByJobIdCommand.ExecuteReaderWithRetry(_retryPolicy)) { if (sqlDataReader.HasRows) { while (sqlDataReader.Read()) { var jobDetail = CreateJobDetailFromSqlDataReader(sqlDataReader); jobDetails.Add(jobDetail); } } } } } return jobDetails; } catch (Exception exp) { this.Log().ErrorWithLineNumber(exp.Message, exp); throw; } }
public bool DoesJobQueueItemExists(Guid jobId) { var count = 0; using (var sqlConnection = new SqlConnection(_connectionString)) { sqlConnection.OpenWithRetry(_retryPolicy); var command = _createSqlCommandHelper.CreateDoesJobQueueItemExistsCommand(jobId, sqlConnection); count = Convert.ToInt32(command.ExecuteScalar()); } return count == 1; }
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; } }
/** * @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; }
public Job GetJobByJobId(Guid jobId) { Job job = null; try { using (var sqlConnection = new SqlConnection(_connectionString)) { sqlConnection.OpenWithRetry(_retryPolicy); using (var selectJobByJobIdCommand = _createSqlCommandHelper.CreateSelectJobByJobIdCommand(jobId, sqlConnection)) { using (var sqlDataReader = selectJobByJobIdCommand.ExecuteReaderWithRetry(_retryPolicy)) { if (sqlDataReader.HasRows) { sqlDataReader.Read(); job = CreateJobFromSqlDataReader(sqlDataReader); } } } } } catch (Exception exp) { this.Log().ErrorWithLineNumber(exp.Message, exp); throw; } return job; }
public void CreateJobDetailByJobId(Guid jobId,string detail, DateTime created) { using (var sqlConnection = new SqlConnection(_connectionString)) { sqlConnection.OpenWithRetry(_retryPolicy); using (var insertCommand = _createSqlCommandHelper.CreateInsertIntoJobDetailCommand(jobId, detail, created, sqlConnection)) { insertCommand.ExecuteNonQueryWithRetry(_retryPolicy); } } }
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 HttpResponseMessage Post(ComSelCouponInputParams p) { // try decrypt data if (!string.IsNullOrEmpty(p.token) && globalVal.CloudBreadCryptSetting == "AES256") { try { string decrypted = Crypto.AES_decrypt(p.token, globalVal.CloudBreadCryptKey, globalVal.CloudBreadCryptIV); p = JsonConvert.DeserializeObject<ComSelCouponInputParams>(decrypted); } catch (Exception ex) { ex = (Exception)Activator.CreateInstance(ex.GetType(), "Decrypt Error", ex); throw ex; } } // Get the sid or memberID of the current user. string sid = CBAuth.getMemberID(p.MemberID, this.User as ClaimsPrincipal); p.MemberID = sid; Logging.CBLoggers logMessage = new Logging.CBLoggers(); string jsonParam = JsonConvert.SerializeObject(p); List<ComSelCouponModel> result = new List<ComSelCouponModel>(); HttpResponseMessage response = new HttpResponseMessage(); EncryptedData encryptedResult = new EncryptedData(); try { /// Database connection retry policy RetryPolicy retryPolicy = new RetryPolicy<SqlAzureTransientErrorDetectionStrategy>(globalVal.conRetryCount, TimeSpan.FromSeconds(globalVal.conRetryFromSeconds)); using (SqlConnection connection = new SqlConnection(globalVal.DBConnectionString)) { using (SqlCommand command = new SqlCommand("uspComSelCoupon", connection)) { command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("@CouponID", SqlDbType.NVarChar, -1).Value = p.CouponID; connection.OpenWithRetry(retryPolicy); using (SqlDataReader dreader = command.ExecuteReaderWithRetry(retryPolicy)) { while (dreader.Read()) { ComSelCouponModel workItem = new ComSelCouponModel() { CouponID = dreader[0].ToString(), CouponCategory1 = dreader[1].ToString(), CouponCategory2 = dreader[2].ToString(), CouponCategory3 = dreader[3].ToString(), ItemListID = dreader[4].ToString(), ItemCount = dreader[5].ToString(), ItemStatus = dreader[6].ToString(), TargetGroup = dreader[7].ToString(), TargetOS = dreader[8].ToString(), TargetDevice = dreader[9].ToString(), Title = dreader[10].ToString(), Content = dreader[11].ToString(), sCol1 = dreader[12].ToString(), sCol2 = dreader[13].ToString(), sCol3 = dreader[14].ToString(), sCol4 = dreader[15].ToString(), sCol5 = dreader[16].ToString(), sCol6 = dreader[17].ToString(), sCol7 = dreader[18].ToString(), sCol8 = dreader[19].ToString(), sCol9 = dreader[20].ToString(), sCol10 = dreader[21].ToString(), }; result.Add(workItem); } dreader.Close(); } connection.Close(); } /// Encrypt the result response if (globalVal.CloudBreadCryptSetting == "AES256") { try { encryptedResult.token = Crypto.AES_encrypt(JsonConvert.SerializeObject(result), globalVal.CloudBreadCryptKey, globalVal.CloudBreadCryptIV); response = Request.CreateResponse(HttpStatusCode.OK, encryptedResult); return response; } catch (Exception ex) { ex = (Exception)Activator.CreateInstance(ex.GetType(), "Encrypt Error", ex); throw ex; } } response = Request.CreateResponse(HttpStatusCode.OK, result); return response; } } catch (Exception ex) { // error log logMessage.memberID = p.MemberID; logMessage.Level = "ERROR"; logMessage.Logger = "CBComSelCouponController"; logMessage.Message = jsonParam; logMessage.Exception = ex.ToString(); Logging.RunLog(logMessage); throw; } }
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 HttpResponseMessage Post(SelLoginInfoInputParams p) { // try decrypt data if (!string.IsNullOrEmpty(p.token) && globalVal.CloudBreadCryptSetting == "AES256") { try { string decrypted = Crypto.AES_decrypt(p.token, globalVal.CloudBreadCryptKey, globalVal.CloudBreadCryptIV); p = JsonConvert.DeserializeObject<SelLoginInfoInputParams>(decrypted); } catch (Exception ex) { ex = (Exception)Activator.CreateInstance(ex.GetType(), "Decrypt Error", ex); throw ex; } } // Get the sid or memberID of the current user. string sid = CBAuth.getMemberID(p.memberID, this.User as ClaimsPrincipal); p.memberID = sid; Logging.CBLoggers logMessage = new Logging.CBLoggers(); string jsonParam = JsonConvert.SerializeObject(p); List<SelLoginInfoModel> result = new List<SelLoginInfoModel>(); HttpResponseMessage response = new HttpResponseMessage(); EncryptedData encryptedResult = new EncryptedData(); try { // start task log //logMessage.memberID = p.memberID; //logMessage.Level = "INFO"; //logMessage.Logger = "CBSelLoginInfoController"; //logMessage.Message = jsonParam; //Logging.RunLog(logMessage); /// Database connection retry policy RetryPolicy retryPolicy = new RetryPolicy<SqlAzureTransientErrorDetectionStrategy>(globalVal.conRetryCount, TimeSpan.FromSeconds(globalVal.conRetryFromSeconds)); using (SqlConnection connection = new SqlConnection(globalVal.DBConnectionString)) { using (SqlCommand command = new SqlCommand("uspSelLoginInfo", connection)) { command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("@MemberID", SqlDbType.NVarChar, -1).Value = p.memberID; command.Parameters.Add("@MemberPWD", SqlDbType.NVarChar, -1).Value = p.memberPWD; command.Parameters.Add("@LastDeviceID", SqlDbType.NVarChar, -1).Value = p.memberPWD; command.Parameters.Add("@LastIPaddress", SqlDbType.NVarChar, -1).Value = p.memberPWD; command.Parameters.Add("@LastMACAddress", SqlDbType.NVarChar, -1).Value = p.memberPWD; connection.OpenWithRetry(retryPolicy); using (SqlDataReader dreader = command.ExecuteReaderWithRetry(retryPolicy)) { while (dreader.Read()) { SelLoginInfoModel workItem = new SelLoginInfoModel() { MemberID = dreader[0].ToString(), MemberPWD = dreader[1].ToString(), EmailAddress = dreader[2].ToString(), EmailConfirmedYN = dreader[3].ToString(), PhoneNumber1 = dreader[4].ToString(), PhoneNumber2 = dreader[5].ToString(), PINumber = dreader[6].ToString(), Name1 = dreader[7].ToString(), Name2 = dreader[8].ToString(), Name3 = dreader[9].ToString(), DOB = dreader[10].ToString(), RecommenderID = dreader[11].ToString(), MemberGroup = dreader[12].ToString(), LastDeviceID = dreader[13].ToString(), LastIPaddress = dreader[14].ToString(), LastLoginDT = dreader[15].ToString(), LastLogoutDT = dreader[16].ToString(), LastMACAddress = dreader[17].ToString(), AccountBlockYN = dreader[18].ToString(), AccountBlockEndDT = dreader[19].ToString(), AnonymousYN = dreader[20].ToString(), _3rdAuthProvider = dreader[21].ToString(), _3rdAuthID = dreader[22].ToString(), _3rdAuthParam = dreader[23].ToString(), PushNotificationID = dreader[24].ToString(), PushNotificationProvider = dreader[25].ToString(), PushNotificationGroup = dreader[26].ToString(), sCol1 = dreader[27].ToString(), sCol2 = dreader[28].ToString(), sCol3 = dreader[29].ToString(), sCol4 = dreader[30].ToString(), sCol5 = dreader[31].ToString(), sCol6 = dreader[32].ToString(), sCol7 = dreader[33].ToString(), sCol8 = dreader[34].ToString(), sCol9 = dreader[35].ToString(), sCol10 = dreader[36].ToString() }; result.Add(workItem); } dreader.Close(); } connection.Close(); // end task log logMessage.memberID = p.memberID; logMessage.Level = "INFO"; logMessage.Logger = "CBSelLoginInfoController"; logMessage.Message = jsonParam; Logging.RunLog(logMessage); } /// Encrypt the result response if (globalVal.CloudBreadCryptSetting == "AES256") { try { encryptedResult.token = Crypto.AES_encrypt(JsonConvert.SerializeObject(result), globalVal.CloudBreadCryptKey, globalVal.CloudBreadCryptIV); response = Request.CreateResponse(HttpStatusCode.OK, encryptedResult); return response; } catch (Exception ex) { ex = (Exception)Activator.CreateInstance(ex.GetType(), "Encrypt Error", ex); throw ex; } } response = Request.CreateResponse(HttpStatusCode.OK, result); return response; } } catch (Exception ex) { // error log logMessage.memberID = p.memberID; logMessage.Level = "ERROR"; logMessage.Logger = "CBSelLoginInfoController"; logMessage.Message = jsonParam; logMessage.Exception = ex.ToString(); Logging.RunLog(logMessage); throw; } }
private void CancelJobByJobIdWorker(Guid jobId) { try { using (var sqlConnection = new SqlConnection(_connectionString)) { sqlConnection.OpenWithRetry(_retryPolicy); using (var sqlTransaction = sqlConnection.BeginTransaction()) { string sentToWorkerNodeUri = null; using (var createSelectWorkerNodeUriCommand = _createSqlCommandHelper.CreateSelectWorkerNodeCommand(jobId, sqlConnection, sqlTransaction)) { using (var selectSqlReader = createSelectWorkerNodeUriCommand.ExecuteReaderWithRetry(_retryPolicy)) { if (selectSqlReader.HasRows) { selectSqlReader.Read(); sentToWorkerNodeUri = selectSqlReader.GetString(selectSqlReader.GetOrdinal("SentToWorkerNodeUri")); } } } if (sentToWorkerNodeUri != null) { var builderHelper = new NodeUriBuilderHelper(sentToWorkerNodeUri); var uriCancel = builderHelper.GetCancelJobUri(jobId); var response = _httpSender.DeleteAsync(uriCancel).Result; if (response != null && response.IsSuccessStatusCode) { using (var createUpdateCancellingResultCommand = _createSqlCommandHelper.CreateUpdateCancellingResultCommand(jobId, sqlConnection, sqlTransaction)) { createUpdateCancellingResultCommand.ExecuteNonQueryWithRetry(_retryPolicy); } using (var deleteFromJobDefinitionsCommand = _createSqlCommandHelper.CreateDeleteFromJobQueueCommand(jobId, sqlConnection, sqlTransaction)) { deleteFromJobDefinitionsCommand.ExecuteNonQueryWithRetry(_retryPolicy); } Retry(sqlTransaction.Commit); } else { this.Log().ErrorWithLineNumber("Could not send cancel to node. JobId: " + jobId); } } } } } catch (Exception exp) { this.Log().ErrorWithLineNumber(exp.Message, exp); throw; } }