/// <summary> /// Updates the specified upload object. /// </summary> /// <param name="entity">The upload.</param> public void Update(Upload entity) { var upload = entity; Debug.Assert(upload != null, "upload != null"); using (var connection = new SqlConnection(DbConnection)) { using (var command = new SqlCommand("PaUploadUpdate", connection)) { var sqlParams = new List <SqlParameter>(); var paramReturnValue = new SqlParameter("@return_value", SqlDbType.Int) { Direction = ParameterDirection.ReturnValue }; sqlParams.Add(paramReturnValue); SqlHelper.AddIntPara(upload.UploadId, "@Id", sqlParams); SqlHelper.AddIntPara(upload.ProjectId, "@ProjectId", sqlParams); SqlHelper.AddDatePara(upload.DateUploaded, "@DateUploaded", sqlParams); SqlHelper.AddVarcharPara(upload.SourceFile, "@SourceFile", sqlParams); SqlHelper.AddIntPara(upload.Rows, "@Rows", sqlParams); SqlHelper.AddVarcharPara(upload.Status, "@Status", sqlParams); SqlHelper.AddVarcharPara(upload.Name, "@Name", sqlParams); SqlHelper.AddVarcharPara(upload.UploadedBy, "@UploadedBy", sqlParams); SqlHelper.AddBitPara(upload.IncludesOutcomes, "@IncludesOutcomes", sqlParams); SqlHelper.AddBitPara(upload.AdditionalReview, "@AdditionalReview", sqlParams); SqlHelper.AddBitPara(upload.InScope, "@InScope", sqlParams); SqlHelper.AddBitPara(upload.AcceptedFlag, "@AcceptedFlag", sqlParams); SqlHelper.AddBitPara(upload.RandomFlag, "@RandomFlag", sqlParams); SqlHelper.AddBitPara(upload.NationalFlag, "@NationalFlag", sqlParams); SqlHelper.AddVarcharPara(upload.ServerFile, "@ServerFile", sqlParams); SqlHelper.AddNullableDateParameter(upload.DueDate, "@DueDate", sqlParams); SqlHelper.AddVarcharPara(upload.UpdatedBy, "@UpdatedBy", sqlParams); SqlHelper.AddDatePara(DateTime.Now, "@UpdatedOn", sqlParams); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddRange(sqlParams.ToArray()); connection.Open(); command.ExecuteNonQuery(); } } }
/// <summary> /// Updates a set of reviews with the same outcomes. /// </summary> /// <param name="reviews">The reviews.</param> /// <param name="outcome">The outcome.</param> /// <param name="userId">The user identifier.</param> /// <exception cref="System.ApplicationException">Bulk Outcomes failed to update</exception> public void BulkOutcome(IEnumerable <int> reviews, Review outcome, string userId) { var dtReviews = SqlHelper.BuildIdTable(); SqlHelper.PopulateIdsTable(reviews, dtReviews); using (var connection = new SqlConnection(DbConnection)) { using (var command = new SqlCommand("PaReviewBulkOutcome", connection)) { var sqlParams = new List <SqlParameter>(); var paramReturnValue = new SqlParameter("@return_value", SqlDbType.Int) { Direction = ParameterDirection.ReturnValue }; sqlParams.Add(paramReturnValue); SqlHelper.AddTablePara(dtReviews, "@ReviewIds", "IntTableType", sqlParams); SqlHelper.AddVarcharPara(outcome.AssessmentCode, "@AssessmentCode", sqlParams); SqlHelper.AddVarcharPara(outcome.AssessmentAction, "@AssessmentAction", sqlParams); SqlHelper.AddVarcharPara(outcome.RecoveryReason, "@RecoveryReason", sqlParams); SqlHelper.AddVarcharPara(outcome.OutcomeCode, "@OutcomeCode", sqlParams); SqlHelper.AddVarcharPara(outcome.Comments, "@Comments", sqlParams); SqlHelper.AddNullableDateParameter(outcome.AssessmentDate, "@AssessmentDate", sqlParams); SqlHelper.AddNullableDateParameter(outcome.AssessmentActionDate, "@AssessmentActionDate", sqlParams); SqlHelper.AddNullableDateParameter(outcome.RecoveryReasonDate, "@RecoveryReasonDate", sqlParams); SqlHelper.AddNullableDateParameter(outcome.FinalOutcomeDate, "@FinalOutcomeDate", sqlParams); SqlHelper.AddVarcharPara(userId, "@UpdatedBy", sqlParams); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddRange(sqlParams.ToArray()); connection.Open(); command.ExecuteNonQuery(); if (((Int32)command.Parameters["@return_value"].Value) == -1) { throw new ApplicationException("Bulk Outcomes failed to update"); } } } }
/// <summary> /// Updates the specified review. /// </summary> /// <param name="entity">The entity.</param> public void Update(Review entity) { var review = entity; Debug.Assert(review != null, "review != null"); using (var connection = new SqlConnection(DbConnection)) { using (var command = new SqlCommand("PaReviewUpdate", connection)) { var sqlParams = new List <SqlParameter>(); var paramReturnValue = new SqlParameter("@return_value", SqlDbType.Int) { Direction = ParameterDirection.ReturnValue }; sqlParams.Add(paramReturnValue); SqlHelper.AddIntPara(review.ReviewId, "@ReviewId", sqlParams); SqlHelper.AddIntPara(review.ProjectId, "@ProjectId", sqlParams); SqlHelper.AddIntPara(review.UploadId, "@UploadId", sqlParams); SqlHelper.AddIntPara(review.ClaimSequenceNumber, "@ClaimSequenceNumber", sqlParams); SqlHelper.AddNullableBigIntPara(review.JobseekerId, "@JobseekerId", sqlParams); SqlHelper.AddNullableBigIntPara(review.ClaimId, "@ClaimId", sqlParams); SqlHelper.AddNullableBigIntPara(review.ActivityId, "@ActivityId", sqlParams); SqlHelper.AddVarcharPara(review.SiteCode, "@SiteCode", sqlParams); SqlHelper.AddVarcharPara(review.SiteName, "@SiteName", sqlParams); SqlHelper.AddVarcharPara(review.OrgName, "@OrgName", sqlParams); SqlHelper.AddVarcharPara(review.OrgCode, "@OrgCode", sqlParams); SqlHelper.AddVarcharPara(review.ESACode, "@ESACode", sqlParams); SqlHelper.AddVarcharPara(review.StateCode, "@StateCode", sqlParams); SqlHelper.AddVarcharPara(review.ManagedBy, "@ManagedBy", sqlParams); SqlHelper.AddNullableVarcharPara(review.OutcomeCode, "@OutcomeCode", sqlParams); SqlHelper.AddNullableVarcharPara(review.AssessmentCode, "@AssessmentCode", sqlParams); SqlHelper.AddNullableVarcharPara(review.RecoveryReason, "@RecoveryReason", sqlParams); SqlHelper.AddNullableVarcharPara(review.AssessmentAction, "@AssessmentAction", sqlParams); SqlHelper.AddVarcharPara(review.JobSeekerGivenName, "@JobSeekerGivenName", sqlParams); SqlHelper.AddVarcharPara(review.JobSeekerSurname, "@JobSeekerSurname", sqlParams); SqlHelper.AddVarcharPara(review.ClaimType, "@ClaimType", sqlParams); SqlHelper.AddVarcharPara(review.Comments, "@Comments", sqlParams); SqlHelper.AddBitPara(review.IsAdditionalReview, "@IsAdditionalReview", sqlParams); SqlHelper.AddBitPara(review.IsOutOfScope, "@IsOutOfScope", sqlParams); SqlHelper.AddBitPara(review.AutoSpecialClaim, "@AutoSpecialClaim", sqlParams); SqlHelper.AddBitPara(review.ManualSpecialClaim, "@ManualSpecialClaim", sqlParams); SqlHelper.AddMoneyPara(review.ClaimAmount, "@ClaimAmount", sqlParams); SqlHelper.AddMoneyPara(review.ClaimRecoveryAmount, "@ClaimRecoveryAmount", sqlParams); SqlHelper.AddNullableDateParameter(review.AssessmentDate, "@AssessmentDate", sqlParams); SqlHelper.AddNullableDateParameter(review.AssessmentActionDate, "@AssessmentActionDate", sqlParams); SqlHelper.AddNullableDateParameter(review.RecoveryReasonDate, "@RecoveryReasonDate", sqlParams); SqlHelper.AddNullableDateParameter(review.FinalOutcomeDate, "@FinalOutcomeDate", sqlParams); SqlHelper.AddNullableDateParameter(review.ClaimCreationDate, "@ClaimCreationDate", sqlParams); SqlHelper.AddVarcharPara(review.UpdatedBy, "@UpdatedBy", sqlParams); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddRange(sqlParams.ToArray()); connection.Open(); command.ExecuteNonQuery(); } } }
/// <summary> /// Adds the specified review. /// </summary> /// <param name="review">The review.</param> /// <returns> /// id of the new review /// </returns> public int Add(Review review) { var reviewId = review.ReviewId; if (reviewId == 0) { using (var connection = new SqlConnection(DbConnection)) { using (var command = new SqlCommand("PaReviewInsert", connection)) { var sqlParams = new List <SqlParameter>(); var paramReturnValue = new SqlParameter("@return_value", SqlDbType.Int) { Direction = ParameterDirection.ReturnValue }; sqlParams.Add(paramReturnValue); var paramId = new SqlParameter("@ReviewId", SqlDbType.Int) { Direction = ParameterDirection.InputOutput, Value = 0 }; sqlParams.Add(paramId); SqlHelper.AddIntPara(review.ProjectId, "@ProjectId", sqlParams); SqlHelper.AddIntPara(review.UploadId, "@UploadId", sqlParams); SqlHelper.AddIntPara(review.ClaimSequenceNumber, "@ClaimSequenceNumber", sqlParams); SqlHelper.AddNullableBigIntPara(review.JobseekerId, "@JobseekerId", sqlParams); SqlHelper.AddNullableBigIntPara(review.ClaimId, "@ClaimId", sqlParams); SqlHelper.AddNullableBigIntPara(review.ActivityId, "@ActivityId", sqlParams); SqlHelper.AddVarcharPara(review.JobSeekerGivenName, "@JobSeekerGivenName", sqlParams); SqlHelper.AddVarcharPara(review.JobSeekerSurname, "@JobSeekerSurname", sqlParams); SqlHelper.AddVarcharPara(review.SiteCode, "@SiteCode", sqlParams); SqlHelper.AddVarcharPara(review.SiteName, "@SiteName", sqlParams); SqlHelper.AddVarcharPara(review.OrgName, "@OrgName", sqlParams); SqlHelper.AddVarcharPara(review.OrgCode, "@OrgCode", sqlParams); SqlHelper.AddVarcharPara(review.ESACode, "@ESACode", sqlParams); SqlHelper.AddVarcharPara(review.StateCode, "@StateCode", sqlParams); SqlHelper.AddVarcharPara(review.ManagedBy, "@ManagedBy", sqlParams); SqlHelper.AddVarcharPara(review.ContractType, "@ContractType", sqlParams); SqlHelper.AddVarcharPara(review.ClaimType, "@ClaimType", sqlParams); SqlHelper.AddVarcharPara(review.Comments, "@Comments", sqlParams); SqlHelper.AddVarcharPara(review.AssessmentCode, "@AssessmentCode", sqlParams); SqlHelper.AddVarcharPara(review.RecoveryReason, "@RecoveryReason", sqlParams); SqlHelper.AddVarcharPara(review.AssessmentAction, "@AssessmentAction", sqlParams); SqlHelper.AddVarcharPara(review.OutcomeCode, "@OutcomeCode", sqlParams); SqlHelper.AddBitPara(review.IsAdditionalReview, "@IsAdditionalReview", sqlParams); SqlHelper.AddBitPara(review.IsOutOfScope, "@IsOutOfScope", sqlParams); SqlHelper.AddBitPara(review.AutoSpecialClaim, "@AutoSpecialClaim", sqlParams); SqlHelper.AddBitPara(review.ManualSpecialClaim, "@ManualSpecialClaim", sqlParams); SqlHelper.AddNullableDateParameter(review.ClaimCreationDate, "@ClaimCreationDate", sqlParams); SqlHelper.AddMoneyPara(review.ClaimAmount, "@ClaimAmount", sqlParams); SqlHelper.AddMoneyPara(review.ClaimRecoveryAmount, "@ClaimRecoveryAmount", sqlParams); SqlHelper.AddVarcharPara(review.CreatedBy, "@CreatedBy", sqlParams); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddRange(sqlParams.ToArray()); connection.Open(); command.ExecuteNonQuery(); if (((Int32)command.Parameters["@return_value"].Value) == 0) { reviewId = (int)paramId.Value; } } } } return(reviewId); }
public void Add(Upload upload) { Debug.Assert(upload != null, "upload != null"); using (var connection = new SqlConnection(DbConnection)) { using (var command = new SqlCommand("PaUploadInsert", connection)) { var sqlParams = new List <SqlParameter>(); var paramReturnValue = new SqlParameter("@return_value", SqlDbType.Int) { Direction = ParameterDirection.ReturnValue }; sqlParams.Add(paramReturnValue); //also get the Id back var paramUploadId = new SqlParameter("@UploadId", SqlDbType.Int) { Direction = ParameterDirection.InputOutput, Value = 0 }; sqlParams.Add(paramUploadId); SqlHelper.AddIntPara(upload.ProjectId, "@ProjectId", sqlParams); SqlHelper.AddDatePara(upload.DateUploaded, "@DateUploaded", sqlParams); SqlHelper.AddVarcharPara(upload.SourceFile, "@SourceFile", sqlParams); SqlHelper.AddVarcharPara(upload.Name, "@Name", sqlParams); SqlHelper.AddVarcharPara(upload.UploadedBy, "@UploadedBy", sqlParams); SqlHelper.AddBitPara(upload.IncludesOutcomes, "@IncludesOutcomes", sqlParams); SqlHelper.AddBitPara(upload.AdditionalReview, "@AdditionalReview", sqlParams); SqlHelper.AddBitPara(upload.InScope, "@InScope", sqlParams); SqlHelper.AddBitPara(upload.AcceptedFlag, "@AcceptedFlag", sqlParams); SqlHelper.AddBitPara(upload.RandomFlag, "@RandomFlag", sqlParams); SqlHelper.AddBitPara(upload.NationalFlag, "@NationalFlag", sqlParams); SqlHelper.AddNullableDateParameter(upload.DueDate, "@DueDate", sqlParams); SqlHelper.AddVarcharPara(upload.ServerFile, "@ServerFile", sqlParams); SqlHelper.AddVarcharPara(upload.CreatedBy, "@CreatedBy", sqlParams); SqlHelper.AddVarcharPara(upload.UpdatedBy, "@UpdatedBy", sqlParams); SqlHelper.AddDatePara(upload.CreatedOn, "@CreatedOn", sqlParams); SqlHelper.AddDatePara(upload.UpdatedOn, "@UpdatedOn", sqlParams); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddRange(sqlParams.ToArray()); connection.Open(); command.ExecuteNonQuery(); if (((Int32)command.Parameters["@return_value"].Value) != 0) { return; } upload.UploadId = (int)paramUploadId.Value; } } }