public void ChangeJNPOwner(long jnpID, int newOwnerUserID, int currentUserID, TransactionManager currentTransaction) { if (base.ValidateKeyField(jnpID)) { DbCommand commandWrapper = GetDbCommand("spr_ChangeJNPOwner"); try { commandWrapper.Parameters.Add(new SqlParameter("@JNPID", jnpID)); commandWrapper.Parameters.Add(new SqlParameter("@NewOwnerUserID", newOwnerUserID)); commandWrapper.Parameters.Add(new SqlParameter("@CheckInByUserID", currentUserID)); if (currentTransaction != null) { DatabaseUtility.ExecuteNonQuery(currentTransaction, commandWrapper); } else { ExecuteNonQuery(commandWrapper); } } catch (Exception ex) { currentTransaction.Rollback(); HandleException(ex); } } }
public void DeletePDCompetencyKSAFactor(long positionCompetencyKSAID, int userId) { TransactionManager currentTransaction = new TransactionManager(base.CurrentDatabase); currentTransaction.BeginTransaction(); DbCommand commandWrapper = GetDbCommand("spr_DeletePositionCompetencyKSAFactor"); try { commandWrapper.Parameters.Add(new SqlParameter("@PositionCompetencyKSAID", positionCompetencyKSAID)); commandWrapper.Parameters.Add(new SqlParameter("@userId", userId)); if (currentTransaction != null) { DatabaseUtility.ExecuteNonQuery(currentTransaction, commandWrapper); } else { ExecuteNonQuery(commandWrapper); } currentTransaction.Commit(); } catch (Exception ex) { currentTransaction.Rollback(); HandleException(ex); } }
public void Add(TransactionManager currentTransaction) { try { DbCommand commandWrapper = GetDbCommand("spr_AdvancePDStatus"); SqlParameter returnParam = new SqlParameter("@newWorkflowRecID", SqlDbType.Int); returnParam.Direction = ParameterDirection.Output; // get the new WorkflowRecID of the record commandWrapper.Parameters.Add(returnParam); commandWrapper.Parameters.Add(new SqlParameter("@positionDescriptionID", this._positionDescriptionID)); commandWrapper.Parameters.Add(new SqlParameter("@workflowStatusID", this._workflowStatusID)); commandWrapper.Parameters.Add(new SqlParameter("@createdByID", this._createdByID)); commandWrapper.Parameters.Add(new SqlParameter("@isCurrent", this._isCurrent)); if (currentTransaction != null) { DatabaseUtility.ExecuteNonQuery(currentTransaction, commandWrapper); } else { ExecuteNonQuery(commandWrapper); } this._workflowRecID = (int)returnParam.Value; } catch (Exception ex) { HandleException(ex); } }
public static int SynFinalFactorsFromJA(int userID, long?JNPID, long?JAID, long?JQID, TransactionManager currentTransaction) { int returnCode = -1; DbCommand commandWrapper = GetDbCommand("spr_SynFinalFactorsFromJA"); try { if (JNPID == null) { commandWrapper.Parameters.Add(new SqlParameter("@JNPID", DBNull.Value)); } else { commandWrapper.Parameters.Add(new SqlParameter("@JNPID", JNPID)); } if (JAID == null) { commandWrapper.Parameters.Add(new SqlParameter("@JAID", DBNull.Value)); } else { commandWrapper.Parameters.Add(new SqlParameter("@JAID", JAID)); } if (JAID == null) { commandWrapper.Parameters.Add(new SqlParameter("@JQID", DBNull.Value)); } else { commandWrapper.Parameters.Add(new SqlParameter("@JQID", JQID)); } commandWrapper.Parameters.Add(new SqlParameter("@UserID", userID)); if (currentTransaction != null) { returnCode = DatabaseUtility.ExecuteNonQuery(currentTransaction, commandWrapper); } else { returnCode = commandWrapper.ExecuteNonQuery(); } } catch (Exception ex) { if ((currentTransaction != null) && (currentTransaction.IsOpen)) { currentTransaction.Rollback(); } HandleException(ex); } return(returnCode); }
public static void DeleteProduct(int productId) { string connectionString = ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString; String commandText = @"DELETE FROM test_Products WHERE ProductId=@ProductId"; var parameterList = new List <SqlParameter>(); var commandType = CommandType.Text; parameterList.Add(DatabaseUtility.GetSqlParameter("ProductId", productId, SqlDbType.Int)); DatabaseUtility.ExecuteNonQuery(new SqlConnection(connectionString), commandText, commandType, parameterList.ToArray()); }
public static long SetCurrentWorkflowStatus(CRWorkflowStatus crw, TransactionManager currentTransaction) { long crwRecID = -1; DbCommand commandWrapper = GetDbCommand("spr_SetCurrentWorkflowStatus"); try { SqlParameter returnParam = new SqlParameter("@NewWorkflowRecID", SqlDbType.BigInt); returnParam.Direction = ParameterDirection.Output; // get the new JAWorkflowRecID of the record commandWrapper.Parameters.Add(returnParam); commandWrapper.Parameters.Add(new SqlParameter("@StaffingObjectID", crw.CRID)); commandWrapper.Parameters.Add(new SqlParameter("@StaffingObjectTypeID", enumStaffingObjectType.CR)); commandWrapper.Parameters.Add(new SqlParameter("@WorkflowStatusID", crw.CRWorkflowStatusID)); if (crw.CreatedByID == -1) { commandWrapper.Parameters.Add(new SqlParameter("@createdByID", DBNull.Value)); } else { commandWrapper.Parameters.Add(new SqlParameter("@createdByID", crw.CreatedByID)); } if (currentTransaction != null) { DatabaseUtility.ExecuteNonQuery(currentTransaction, commandWrapper); } else { ExecuteNonQuery(commandWrapper); } crwRecID = (long)returnParam.Value; } catch (Exception ex) { if ((currentTransaction != null) && (currentTransaction.IsOpen)) { currentTransaction.Rollback(); } HandleException(ex); } return(crwRecID); }
public void Update(SeriesOPMTitleEntity entity, TransactionManager currentTransaction) { if (base.ValidateKeyField(entity.SeriesID)) { DbCommand commandWrapper = GetDbCommand("spr_UpdateSeriesOPMTitle"); try { commandWrapper.Parameters.Add(new SqlParameter("@series", entity.SeriesID)); if (string.IsNullOrEmpty(entity.OPMTitle)) { commandWrapper.Parameters.Add(new SqlParameter("@seriesOPMTitle", DBNull.Value)); } else { commandWrapper.Parameters.Add(new SqlParameter("@seriesOPMTitle", entity.OPMTitle)); } if (string.IsNullOrEmpty(entity.OPMDescription)) { commandWrapper.Parameters.Add(new SqlParameter("@SeriesOPMTitleDescription", DBNull.Value)); } else { commandWrapper.Parameters.Add(new SqlParameter("@SeriesOPMTitleDescription", entity.OPMDescription)); } if (currentTransaction != null) { DatabaseUtility.ExecuteNonQuery(currentTransaction, commandWrapper); } else { ExecuteNonQuery(commandWrapper); } } catch (Exception ex) { currentTransaction.Rollback(); HandleException(ex); } } }
public void UpdatePDCompetencyKSAFactor(PositionCompetencyKSA positionCompetencyKSA, JQFactor jqFactor) { if (base.ValidateKeyField(positionCompetencyKSA.CompetencyKSAID)) { TransactionManager currentTransaction = new TransactionManager(base.CurrentDatabase); currentTransaction.BeginTransaction(); DbCommand commandWrapper = GetDbCommand("spr_UpdatePositionCompetencyKSAFactor"); try { commandWrapper.Parameters.Add(new SqlParameter("@PositionCompetencyKSAID", positionCompetencyKSA.CompetencyKSAID)); commandWrapper.Parameters.Add(new SqlParameter("@positionDescriptionID", positionCompetencyKSA.PositionDescriptionID)); if (positionCompetencyKSA.CompetencyKSA == null) { commandWrapper.Parameters.Add(new SqlParameter("@competencyKSA", DBNull.Value)); } else { commandWrapper.Parameters.Add(new SqlParameter("@competencyKSA", positionCompetencyKSA.CompetencyKSA)); } if (positionCompetencyKSA.Certification == null) { commandWrapper.Parameters.Add(new SqlParameter("@certification", DBNull.Value)); } else { commandWrapper.Parameters.Add(new SqlParameter("@certification", positionCompetencyKSA.Certification)); } if (positionCompetencyKSA.QualificationID < 0) { commandWrapper.Parameters.Add(new SqlParameter("@qualificationID", DBNull.Value)); } else { commandWrapper.Parameters.Add(new SqlParameter("@qualificationID", positionCompetencyKSA.QualificationID)); } if (positionCompetencyKSA.QualificationTypeID < 0) { commandWrapper.Parameters.Add(new SqlParameter("@qualificationTypeID", DBNull.Value)); } else { commandWrapper.Parameters.Add(new SqlParameter("@qualificationTypeID", positionCompetencyKSA.QualificationTypeID)); } commandWrapper.Parameters.Add(new SqlParameter("@createdByID", positionCompetencyKSA.CreatedByID)); commandWrapper.Parameters.Add(new SqlParameter("@createDate", positionCompetencyKSA.CreateDate)); commandWrapper.Parameters.Add(new SqlParameter("@updatedByID", positionCompetencyKSA.UpdatedByID)); commandWrapper.Parameters.Add(new SqlParameter("@updateDate", positionCompetencyKSA.UpdateDate)); if (positionCompetencyKSA.AssociatedPDDutyID == null) { commandWrapper.Parameters.Add(new SqlParameter("@AssociatedPDDutyID", DBNull.Value)); } else { commandWrapper.Parameters.Add(new SqlParameter("@AssociatedPDDutyID", positionCompetencyKSA.AssociatedPDDutyID)); } if (jqFactor.JQFactorTypeID < 0) { commandWrapper.Parameters.Add(new SqlParameter("@jQFactorTypeID", DBNull.Value)); } else { commandWrapper.Parameters.Add(new SqlParameter("@jQFactorTypeID", jqFactor.JQFactorTypeID)); } if (jqFactor.KSAID < 0) { commandWrapper.Parameters.Add(new SqlParameter("@KSAID", DBNull.Value)); } else { commandWrapper.Parameters.Add(new SqlParameter("@KSAID", jqFactor.KSAID)); } commandWrapper.Parameters.Add(new SqlParameter("@isSF", jqFactor.IsSF)); if (jqFactor.JQID < 0) { commandWrapper.Parameters.Add(new SqlParameter("@jQID", DBNull.Value)); } else { commandWrapper.Parameters.Add(new SqlParameter("@jQID", jqFactor.JQID)); } if (currentTransaction != null) { DatabaseUtility.ExecuteNonQuery(currentTransaction, commandWrapper); } else { ExecuteNonQuery(commandWrapper); } currentTransaction.Commit(); } catch (Exception ex) { currentTransaction.Rollback(); HandleException(ex); } } }
public long Add(JNPApproval entity, TransactionManager currentTransaction, SignatureType signaturetypeID) { long returnCode = -1; DbCommand commandWrapper = GetDbCommand("spr_AddJNPApproval"); try { SqlParameter returnParam = new SqlParameter("@newApprovalID", SqlDbType.BigInt); returnParam.Direction = ParameterDirection.Output; // get the new ApprovalID of the record commandWrapper.Parameters.Add(returnParam); if (entity.SupervisorID <= 0) { commandWrapper.Parameters.Add(new SqlParameter("@supervisorID", DBNull.Value)); } else { commandWrapper.Parameters.Add(new SqlParameter("@supervisorID", entity.SupervisorID)); } if (string.IsNullOrEmpty(entity.SupervisorFullName)) { commandWrapper.Parameters.Add(new SqlParameter("@supervisorFullName", DBNull.Value)); } else { commandWrapper.Parameters.Add(new SqlParameter("@supervisorFullName", entity.SupervisorFullName)); } if (string.IsNullOrEmpty(entity.SupervisorOrgTitle)) { commandWrapper.Parameters.Add(new SqlParameter("@supervisorOrgTitle", DBNull.Value)); } else { commandWrapper.Parameters.Add(new SqlParameter("@supervisorOrgTitle", entity.SupervisorOrgTitle)); } if (!entity.SupervisorSignDate.HasValue) { commandWrapper.Parameters.Add(new SqlParameter("@supervisorSignDate", DBNull.Value)); } else { commandWrapper.Parameters.Add(new SqlParameter("@supervisorSignDate", entity.SupervisorSignDate)); } if (entity.HRPersonnelID <= 0) { commandWrapper.Parameters.Add(new SqlParameter("@hRPersonnelID", DBNull.Value)); } else { commandWrapper.Parameters.Add(new SqlParameter("@hRPersonnelID", entity.HRPersonnelID)); } if (string.IsNullOrEmpty(entity.HRPersonnelFullName)) { commandWrapper.Parameters.Add(new SqlParameter("@hRPersonnelFullName", DBNull.Value)); } else { commandWrapper.Parameters.Add(new SqlParameter("@hRPersonnelFullName", entity.HRPersonnelFullName)); } if (string.IsNullOrEmpty(entity.HRPersonnelOrgTitle)) { commandWrapper.Parameters.Add(new SqlParameter("@hRPersonnelOrgTitle", DBNull.Value)); } else { commandWrapper.Parameters.Add(new SqlParameter("@hRPersonnelOrgTitle", entity.HRPersonnelOrgTitle)); } if (!entity.HRPersonnelSignDate.HasValue) { commandWrapper.Parameters.Add(new SqlParameter("@hRPersonnelSignDate", DBNull.Value)); } else { commandWrapper.Parameters.Add(new SqlParameter("@hRPersonnelSignDate", entity.HRPersonnelSignDate)); } if (entity.JNPID <= 0) { commandWrapper.Parameters.Add(new SqlParameter("@jNPID", DBNull.Value)); } else { commandWrapper.Parameters.Add(new SqlParameter("@jNPID", entity.JNPID)); } if (entity.JAID <= 0) { commandWrapper.Parameters.Add(new SqlParameter("@jAID", DBNull.Value)); } else { commandWrapper.Parameters.Add(new SqlParameter("@jAID", entity.JAID)); } if (entity.CRID <= 0) { commandWrapper.Parameters.Add(new SqlParameter("@cRID", DBNull.Value)); } else { commandWrapper.Parameters.Add(new SqlParameter("@cRID", entity.CRID)); } if (entity.JQID <= 0) { commandWrapper.Parameters.Add(new SqlParameter("@jQID", DBNull.Value)); } else { commandWrapper.Parameters.Add(new SqlParameter("@jQID", entity.JQID)); } commandWrapper.Parameters.Add(new SqlParameter("@signatureTypeID", (int)signaturetypeID)); if (currentTransaction != null) { DatabaseUtility.ExecuteNonQuery(currentTransaction, commandWrapper); } else { ExecuteNonQuery(commandWrapper); } returnCode = (long)returnParam.Value; } catch (Exception ex) { currentTransaction.Rollback(); HandleException(ex); } return(returnCode); }
public void Update(JNPApproval entity, TransactionManager currentTransaction, SignatureType signaturetypeID) { if (base.ValidateKeyField(entity.ID)) { DbCommand commandWrapper = GetDbCommand("spr_UpdateJNPApproval"); try { commandWrapper.Parameters.Add(new SqlParameter("@approvalID", entity.ID)); if (entity.SupervisorID <= 0) { commandWrapper.Parameters.Add(new SqlParameter("@supervisorID", DBNull.Value)); } else { commandWrapper.Parameters.Add(new SqlParameter("@supervisorID", entity.SupervisorID)); } if (string.IsNullOrEmpty(entity.SupervisorFullName)) { commandWrapper.Parameters.Add(new SqlParameter("@supervisorFullName", DBNull.Value)); } else { commandWrapper.Parameters.Add(new SqlParameter("@supervisorFullName", entity.SupervisorFullName)); } if (string.IsNullOrEmpty(entity.SupervisorOrgTitle)) { commandWrapper.Parameters.Add(new SqlParameter("@supervisorOrgTitle", DBNull.Value)); } else { commandWrapper.Parameters.Add(new SqlParameter("@supervisorOrgTitle", entity.SupervisorOrgTitle)); } if (!entity.SupervisorSignDate.HasValue) { commandWrapper.Parameters.Add(new SqlParameter("@supervisorSignDate", DBNull.Value)); } else { commandWrapper.Parameters.Add(new SqlParameter("@supervisorSignDate", entity.SupervisorSignDate)); } if (entity.HRPersonnelID <= 0) { commandWrapper.Parameters.Add(new SqlParameter("@hRPersonnelID", DBNull.Value)); } else { commandWrapper.Parameters.Add(new SqlParameter("@hRPersonnelID", entity.HRPersonnelID)); } if (string.IsNullOrEmpty(entity.HRPersonnelFullName)) { commandWrapper.Parameters.Add(new SqlParameter("@hRPersonnelFullName", DBNull.Value)); } else { commandWrapper.Parameters.Add(new SqlParameter("@hRPersonnelFullName", entity.HRPersonnelFullName)); } if (string.IsNullOrEmpty(entity.HRPersonnelOrgTitle)) { commandWrapper.Parameters.Add(new SqlParameter("@hRPersonnelOrgTitle", DBNull.Value)); } else { commandWrapper.Parameters.Add(new SqlParameter("@hRPersonnelOrgTitle", entity.HRPersonnelOrgTitle)); } if (!entity.HRPersonnelSignDate.HasValue) { commandWrapper.Parameters.Add(new SqlParameter("@hRPersonnelSignDate", DBNull.Value)); } else { commandWrapper.Parameters.Add(new SqlParameter("@hRPersonnelSignDate", entity.HRPersonnelSignDate)); } if (entity.JNPID <= 0) { commandWrapper.Parameters.Add(new SqlParameter("@jNPID", DBNull.Value)); } else { commandWrapper.Parameters.Add(new SqlParameter("@jNPID", entity.JNPID)); } if (entity.JAID <= 0) { commandWrapper.Parameters.Add(new SqlParameter("@jAID", DBNull.Value)); } else { commandWrapper.Parameters.Add(new SqlParameter("@jAID", entity.JAID)); } if (entity.CRID <= 0) { commandWrapper.Parameters.Add(new SqlParameter("@cRID", DBNull.Value)); } else { commandWrapper.Parameters.Add(new SqlParameter("@cRID", entity.CRID)); } if (entity.JQID <= 0) { commandWrapper.Parameters.Add(new SqlParameter("@jQID", DBNull.Value)); } else { commandWrapper.Parameters.Add(new SqlParameter("@jQID", entity.JQID)); } commandWrapper.Parameters.Add(new SqlParameter("@signatureTypeID", (int)signaturetypeID)); if (currentTransaction != null) { DatabaseUtility.ExecuteNonQuery(currentTransaction, commandWrapper); } else { ExecuteNonQuery(commandWrapper); } } catch (Exception ex) { currentTransaction.Rollback(); HandleException(ex); } } }