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();
                }
            }
        }
Example #2
0
		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;
		}
Example #3
0
		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);
			}
		}
Example #4
0
        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);
        }
Example #5
0
		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;
			}
		}
Example #7
0
		public virtual void Purge()
		{
			using (var connection = new SqlConnection(_connectionString))
			{
				string deleteCommandText = "DELETE TOP(@batchsize) FROM [Stardust].[Job] WHERE Created < DATEADD(HOUR, [email protected], 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;
            }
        }
Example #12
0
		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;
            }
        }
Example #15
0
		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);
				}
			}
		}
Example #16
0
		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);
			}
		}
Example #17
0
		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;
		}
Example #18
0
		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;
		}
Example #19
0
	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;
			}
		}
Example #20
0
		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;
			}
		}
Example #21
0
		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;
		}
Example #22
0
		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;
			}
		}
Example #23
0
        /**
        * @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;
        }
Example #24
0
		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;
		}
Example #25
0
		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 [email protected]
                 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;
            }
        }
Example #30
0
		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;
			}
		}