public Exception GetCareerByMatter(ref Matter matter) { using (OracleConnection oracleConnection = Connection.GetInstance().ConnectionDB()) { OracleCommand oracleCommand = new OracleCommand(); oracleCommand.Connection = oracleConnection; oracleCommand.CommandText = "PACK_R_CAREER_MATTER.GET_CAREER_BY_MATTER"; oracleCommand.CommandType = CommandType.StoredProcedure; oracleCommand.Parameters.Add("MATTER_ID_PARAMETER", OracleDbType.Byte).Value = matter.MatterId; OracleDataReader oracleDataReader; Exception exceptionToReturn = null; try { oracleConnection.Open(); oracleDataReader = oracleCommand.ExecuteReader(); while (oracleDataReader.Read()) { Career career = new Career(); career = MappingCareer(oracleDataReader); matter.Career.Add(career); } return(exceptionToReturn); } catch (Exception exception) { exceptionToReturn = exception; return(exceptionToReturn); } } }
public bool PasswordRecovery(string email, out byte[] passwordHash, out byte[] salt) { using (OracleConnection oracleConnection = Connection.GetInstance().ConnectionDB()) { OracleCommand oracleCommand = new OracleCommand(); oracleCommand.Connection = oracleConnection; oracleCommand.CommandText = "PASSWORD_RECUPERATION"; oracleCommand.CommandType = CommandType.StoredProcedure; oracleCommand.Parameters.Add("EMAIL_PARAMETER", OracleDbType.Varchar2).Value = email; oracleCommand.Parameters.Add("PASSWORD_OUT_PARAMETER", OracleDbType.Blob).Direction = ParameterDirection.Output; oracleCommand.Parameters.Add("SALT_OUT_PARAMETER", OracleDbType.Blob).Direction = ParameterDirection.Output; passwordHash = null; salt = null; try { oracleConnection.Open(); oracleCommand.ExecuteNonQuery(); passwordHash = (byte[])(oracleCommand.Parameters["PASSWORD_OUT_PARAMETER"].Value); salt = (byte[])(oracleCommand.Parameters["SALT_OUT_PARAMETER"].Value); return(true); } catch (Exception) { return(false); } } }
public Exception GetMattersByTeacherFilterBySemester(ref Teacher teacher, byte semesterLogId) { using (OracleConnection oracleConnection = Connection.GetInstance().ConnectionDB()) { OracleCommand oracleCommand = new OracleCommand(); oracleCommand.Connection = oracleConnection; oracleCommand.CommandText = "PACK_R_TEACHER_MATTER.GET_MATTERS_BY_TEACHER_FILTER_BY_SEMESTER"; oracleCommand.CommandType = CommandType.StoredProcedure; oracleCommand.Parameters.Add("TEACHER_ID_PARAMETER", OracleDbType.Byte).Value = teacher.TeacherId; oracleCommand.Parameters.Add("SEMESTER_ID_PARAMETER", OracleDbType.Byte).Value = semesterLogId; OracleDataReader oracleDataReader; Exception exceptionToReturn = null; try { oracleConnection.Open(); oracleDataReader = oracleCommand.ExecuteReader(); while (oracleDataReader.Read()) { Matter matter = new Matter(); matter = MappingMatter(oracleDataReader); teacher.Matter.Add(matter); } return(exceptionToReturn); } catch (Exception exception) { exceptionToReturn = exception; return(exceptionToReturn); } } }
public Exception GetCareersByFaculty(ref Faculty faculty) { using (OracleConnection oracleConnection = Connection.GetInstance().ConnectionDB()) { OracleCommand oracleCommand = new OracleCommand(); oracleCommand.Connection = oracleConnection; oracleCommand.CommandText = "PACK_CAREER.GET_CAREERS_BY_FACULTY"; oracleCommand.CommandType = CommandType.StoredProcedure; oracleCommand.Parameters.Add("FACULTY_ID_PARAMETER", OracleDbType.Byte).Value = faculty.FacultyId; OracleDataReader oracleDataReader; Exception exceptionToReturn = null; try { oracleConnection.Open(); oracleDataReader = oracleCommand.ExecuteReader(); while (oracleDataReader.Read()) { Career career = new Career(); career = Mapping(oracleDataReader); faculty.Career.Add(career); } return(exceptionToReturn); } catch (Exception exception) { exceptionToReturn = exception; return(exceptionToReturn); } } }
public Exception GetActiveFacultiesList(ref ICollection <Faculty> faculties) { using (OracleConnection oracleConnection = Connection.GetInstance().ConnectionDB()) { OracleCommand oracleCommand = new OracleCommand(); oracleCommand.Connection = oracleConnection; oracleCommand.CommandText = "PACK_FACULTY.GET_ACTIVE_FACULTIES_LIST"; oracleCommand.CommandType = CommandType.StoredProcedure; OracleDataReader oracleDataReader; Exception exceptionToReturn = null; try { oracleConnection.Open(); oracleDataReader = oracleCommand.ExecuteReader(); while (oracleDataReader.Read()) { Faculty faculty = new Faculty(); faculty = Mapping(oracleDataReader); faculties.Add(faculty); } return(exceptionToReturn); } catch (Exception exception) { exceptionToReturn = exception; return(exceptionToReturn); } } }
public Exception GetActiveCareersList(ref ICollection <Career> careers) { using (OracleConnection oracleConnection = Connection.GetInstance().ConnectionDB()) { OracleCommand oracleCommand = new OracleCommand(); oracleCommand.Connection = oracleConnection; oracleCommand.CommandText = "PACK_CAREER.GET_ACTIVE_CAREERS_LIST"; oracleCommand.CommandType = CommandType.StoredProcedure; OracleDataReader oracleDataReader; Exception exceptionToReturn = null; try { oracleConnection.Open(); oracleDataReader = oracleCommand.ExecuteReader(); while (oracleDataReader.Read()) { Career career = new Career(); career = Mapping(oracleDataReader); careers.Add(career); } return(exceptionToReturn); } catch (Exception exception) { exceptionToReturn = exception; return(exceptionToReturn); } } }
public Exception GetScores(ref ICollection <Score> scores) { using (OracleConnection oracleConnection = Connection.GetInstance().ConnectionDB()) { OracleCommand oracleCommand = new OracleCommand(); oracleCommand.Connection = oracleConnection; oracleCommand.CommandText = "PACK_SCORE.GET_SCORES"; oracleCommand.CommandType = CommandType.StoredProcedure; OracleDataReader oracleDataReader; Exception exceptionToReturn = null; try { oracleConnection.Open(); oracleDataReader = oracleCommand.ExecuteReader(); while (oracleDataReader.Read()) { Score score = new Score(); score = Mapping(oracleDataReader); scores.Add(score); } return(exceptionToReturn); } catch (Exception exception) { exceptionToReturn = exception; return(exceptionToReturn); } } }
public Exception GetScoresBySemester(ref SemesterLog semesterLog) { using (OracleConnection oracleConnection = Connection.GetInstance().ConnectionDB()) { OracleCommand oracleCommand = new OracleCommand(); oracleCommand.Connection = oracleConnection; oracleCommand.CommandText = "PACK_SCORE.GET_SCORES_BY_SEMESTER"; oracleCommand.CommandType = CommandType.StoredProcedure; oracleCommand.Parameters.Add("SEMESTER_ID_PARAMETER", OracleDbType.Byte).Value = semesterLog.SemesterLogId; OracleDataReader oracleDataReader; Exception exceptionToReturn = null; try { oracleConnection.Open(); oracleDataReader = oracleCommand.ExecuteReader(); while (oracleDataReader.Read()) { Score score = new Score(); score = Mapping(oracleDataReader); semesterLog.Score.Add(score); } return(exceptionToReturn); } catch (Exception exception) { exceptionToReturn = exception; return(exceptionToReturn); } } }
public Exception AddTeacher(Teacher teacher) { using (OracleConnection oracleConnection = Connection.GetInstance().ConnectionDB()) { OracleCommand oracleCommand = new OracleCommand(); oracleCommand.Connection = oracleConnection; oracleCommand.CommandText = "PACK_TEACHER.ADD_TEACHER"; oracleCommand.CommandType = CommandType.StoredProcedure; oracleCommand.Parameters.Add("TEACHER_ID_PARAMETER", OracleDbType.Byte).Value = teacher.TeacherId; oracleCommand.Parameters.Add("FIRST_NAME_PARAMETER", OracleDbType.Varchar2).Value = teacher.FirstName; oracleCommand.Parameters.Add("SECOND_NAME_PARAMETER", OracleDbType.Varchar2).Value = teacher.SecondName; oracleCommand.Parameters.Add("FIRST_SURNAME_PARAMETER", OracleDbType.Varchar2).Value = teacher.FirstSurname; oracleCommand.Parameters.Add("SECOND_SURNAME_PARAMETER", OracleDbType.Varchar2).Value = teacher.SecondSurname; oracleCommand.Parameters.Add("IS_UPC_TEACHER_PARAMETER", OracleDbType.Boolean).Value = teacher.IsUpcTeacher; Exception exceptionToReturn = null; try { oracleConnection.Open(); oracleCommand.ExecuteNonQuery(); return(exceptionToReturn); } catch (Exception exception) { exceptionToReturn = exception; return(exceptionToReturn); } } }
public Exception GetAllTeachersList(ref ICollection <Teacher> teachers) { using (OracleConnection oracleConnection = Connection.GetInstance().ConnectionDB()) { OracleCommand oracleCommand = new OracleCommand(); oracleCommand.Connection = oracleConnection; oracleCommand.CommandText = "PACK_TEACHER.GET_ALL_TEACHERS_LIST"; oracleCommand.CommandType = CommandType.StoredProcedure; OracleDataReader oracleDataReader; Exception exceptionToReturn = null; try { oracleConnection.Open(); oracleDataReader = oracleCommand.ExecuteReader(); while (oracleDataReader.Read()) { Teacher teacher = new Teacher(); teacher = Mapping(oracleDataReader); teachers.Add(teacher); } return(exceptionToReturn); } catch (Exception exception) { exceptionToReturn = exception; return(exceptionToReturn); } } }
public Exception GetReviewsByTeacher(ref Teacher teacher) { using (OracleConnection oracleConnection = Connection.GetInstance().ConnectionDB()) { OracleCommand oracleCommand = new OracleCommand(); oracleCommand.Connection = oracleConnection; oracleCommand.CommandText = "PACK_REVIEW.GET_REVIEW_BY_TEACHER"; oracleCommand.CommandType = CommandType.StoredProcedure; oracleCommand.Parameters.Add("TEACHER_ID_PARAMETER", OracleDbType.Byte).Value = teacher.TeacherId; OracleDataReader oracleDataReader; Exception exceptionToReturn = null; try { oracleConnection.Open(); oracleDataReader = oracleCommand.ExecuteReader(); while (oracleDataReader.Read()) { Review review = new Review(); review = Mapping(oracleDataReader); teacher.Review.Add(review); } return(exceptionToReturn); } catch (Exception exception) { exceptionToReturn = exception; return(exceptionToReturn); } } }
public Exception GetSemesterLog(ref ICollection <SemesterLog> semesterLogs) { using (OracleConnection oracleConnection = Connection.GetInstance().ConnectionDB()) { OracleCommand oracleCommand = new OracleCommand(); oracleCommand.Connection = oracleConnection; oracleCommand.CommandText = "PACK_SEMESTER_LOG.GET_SEMESTER_LOG"; oracleCommand.CommandType = CommandType.StoredProcedure; OracleDataReader oracleDataReader; Exception exceptionToReturn = null; try { oracleConnection.Open(); oracleDataReader = oracleCommand.ExecuteReader(); while (oracleDataReader.Read()) { SemesterLog semesterLog = new SemesterLog(); semesterLog = Mapping(oracleDataReader); semesterLogs.Add(semesterLog); } return(exceptionToReturn); } catch (Exception exception) { exceptionToReturn = exception; return(exceptionToReturn); } } }
public Exception AddRTeacherMatter(string matterId, byte semesterLogId, byte teacherId) { using (OracleConnection oracleConnection = Connection.GetInstance().ConnectionDB()) { OracleCommand oracleCommand = new OracleCommand(); oracleCommand.Connection = oracleConnection; oracleCommand.CommandText = "PACK_R_TEACHER_MATTER.ADD_R_TEACHER_MATTER"; oracleCommand.CommandType = CommandType.StoredProcedure; oracleCommand.Parameters.Add("TEACHER_ID_PARAMETER", OracleDbType.Byte).Value = teacherId; oracleCommand.Parameters.Add("MATTER_ID_PARAMETER", OracleDbType.Varchar2).Value = matterId; oracleCommand.Parameters.Add("SEMESTER_ID_PARAMETER", OracleDbType.Byte).Value = semesterLogId; Exception exceptionToReturn = null; try { oracleConnection.Open(); oracleCommand.ExecuteNonQuery(); return(exceptionToReturn); } catch (Exception exception) { exceptionToReturn = exception; return(exceptionToReturn); } } }
public Exception GetRecentReviews(ref ICollection <Review> reviews) { using (OracleConnection oracleConnection = Connection.GetInstance().ConnectionDB()) { OracleCommand oracleCommand = new OracleCommand(); oracleCommand.Connection = oracleConnection; oracleCommand.CommandText = "PACK_REVIEW.GET_RECENT_REVIEW"; oracleCommand.CommandType = CommandType.StoredProcedure; OracleDataReader oracleDataReader; Exception exceptionToReturn = null; try { oracleConnection.Open(); oracleDataReader = oracleCommand.ExecuteReader(); while (oracleDataReader.Read()) { Review review = new Review(); review = Mapping(oracleDataReader); reviews.Add(review); } return(exceptionToReturn); } catch (Exception exception) { exceptionToReturn = exception; return(exceptionToReturn); } } }
public Exception GetReviewsByCommonUser(ref CommonUser commonUser) { using (OracleConnection oracleConnection = Connection.GetInstance().ConnectionDB()) { OracleCommand oracleCommand = new OracleCommand(); oracleCommand.Connection = oracleConnection; oracleCommand.CommandText = "PACK_REVIEW.GET_REVIEW_BY_COMMON_USER"; oracleCommand.CommandType = CommandType.StoredProcedure; oracleCommand.Parameters.Add("COMMON_USER_ID", OracleDbType.Long).Value = commonUser.CommonUserId; OracleDataReader oracleDataReader; Exception exceptionToReturn = null; try { oracleConnection.Open(); oracleDataReader = oracleCommand.ExecuteReader(); while (oracleDataReader.Read()) { Review review = new Review(); review = Mapping(oracleDataReader); commonUser.Review.Add(review); } return(exceptionToReturn); } catch (Exception exception) { exceptionToReturn = exception; return(exceptionToReturn); } } }
public bool UserNotExist(string email) { using (OracleConnection oracleConnection = Connection.GetInstance().ConnectionDB()) { OracleCommand oracleCommand = new OracleCommand(); oracleCommand.Connection = oracleConnection; oracleCommand.CommandText = "PACK_COMMON_USER.CHECK_USER_IS_REGISTERED"; oracleCommand.CommandType = CommandType.StoredProcedure; oracleCommand.Parameters.Add("EMAIL_PARAMETER", OracleDbType.Varchar2).Value = email; oracleCommand.Parameters.Add("RETURN_VALUE", OracleDbType.Int32).Direction = ParameterDirection.ReturnValue; try { oracleConnection.Open(); oracleCommand.ExecuteNonQuery(); oracleConnection.Close(); } catch (Exception ex) { oracleConnection.Close(); } if (oracleCommand.Parameters["RETURN_VALUE"].Value == null) { return(false); } else { return(true); } } }
public Exception Register(ref CommonUser commonUser) { using (OracleConnection oracleConnection = Connection.GetInstance().ConnectionDB()) { OracleCommand oracleCommand = new OracleCommand(); oracleCommand.Connection = oracleConnection; oracleCommand.CommandText = "PACK_COMMON_USER.REGISTER_COMMON_USER"; oracleCommand.CommandType = CommandType.StoredProcedure; oracleCommand.Parameters.Add("FIRST_NAME_PARAMETER", OracleDbType.Varchar2).Value = commonUser.FirstName; oracleCommand.Parameters.Add("SECOND_NAME_PARAMETER", OracleDbType.Varchar2).Value = commonUser.SecondName; oracleCommand.Parameters.Add("FIRST_SURNAME_PARAMETER", OracleDbType.Varchar2).Value = commonUser.FirstSurname; oracleCommand.Parameters.Add("SECOND_SURNAME_PARAMETER", OracleDbType.Varchar2).Value = commonUser.SecondSurname; oracleCommand.Parameters.Add("EMAIL_PARAMETER", OracleDbType.Varchar2).Value = commonUser.Email; oracleCommand.Parameters.Add("PASSWORD_PARAMETER", OracleDbType.Blob).Value = commonUser.Password; oracleCommand.Parameters.Add("SALT_PARAMETER", OracleDbType.Blob).Value = commonUser.Salt; Exception exceptionToReturn = null; try { oracleConnection.Open(); oracleCommand.ExecuteNonQuery(); oracleConnection.Close(); return(exceptionToReturn); } catch (Exception exeption) { exceptionToReturn = exeption; oracleConnection.Close(); return(exceptionToReturn); } } }
public Exception Login(string email, ref CommonUser commonUser) { using (OracleConnection oracleConnection = Connection.GetInstance().ConnectionDB()) { OracleCommand oracleCommand = new OracleCommand(); oracleCommand.Connection = oracleConnection; oracleCommand.CommandText = "PACK_COMMON_USER.LOGIN"; oracleCommand.CommandType = CommandType.StoredProcedure; oracleCommand.Parameters.Add("EMAIL", OracleDbType.Varchar2).Value = email; OracleDataReader oracleDataReader; Exception exceptionToReturn = null; try { oracleConnection.Open(); oracleDataReader = oracleCommand.ExecuteReader(); while (oracleDataReader.Read()) { commonUser = Mapping(oracleDataReader); } return(exceptionToReturn); } catch (Exception exception) { exceptionToReturn = exception; return(exceptionToReturn); } } }
public Exception AddReview(Review review) { using (OracleConnection oracleConnection = Connection.GetInstance().ConnectionDB()) { OracleCommand oracleCommand = new OracleCommand(); oracleCommand.Connection = oracleConnection; oracleCommand.CommandText = "PACK_REVIEW.ADD_REVIEW"; oracleCommand.CommandType = CommandType.StoredProcedure; oracleCommand.Parameters.Add("TEACHER_ID_PARAMETER", OracleDbType.Byte).Value = review.TeacherId; oracleCommand.Parameters.Add("COMMON_USER_ID_PARAMETER", OracleDbType.Long).Value = review.CommonUserId; oracleCommand.Parameters.Add("MATTER_ID_PARAMETER", OracleDbType.Varchar2).Value = review.MatterId; oracleCommand.Parameters.Add("SEMESTER_ID_PARAMETER", OracleDbType.Byte).Value = review.SemesterId; oracleCommand.Parameters.Add("CONTENT_PARAMETER", OracleDbType.NClob).Value = review.Content; oracleCommand.Parameters.Add("KNOWLEDGE_PARAMETER", OracleDbType.Byte).Value = review.Knowledge; oracleCommand.Parameters.Add("NATURALNESS_PARAMETER", OracleDbType.Byte).Value = review.Naturalness; oracleCommand.Parameters.Add("ATTITUDE_PARAMETER", OracleDbType.Byte).Value = review.Attitude; oracleCommand.Parameters.Add("MOTIVATION_PRODUCE_PARAMETER", OracleDbType.Byte).Value = review.MotivationProduce; oracleCommand.Parameters.Add("MATTER_GUIDELINES_PARAMETER", OracleDbType.Byte).Value = review.MatterGuidelines; oracleCommand.Parameters.Add("WEIGHTED_AVERAGE_PARAMETER", OracleDbType.Decimal).Value = review.WeightedAverage; oracleCommand.Parameters.Add("IS_ANONYMOUS_PARAMETER", OracleDbType.Boolean).Value = review.IsAnonymous; oracleCommand.Parameters.Add("FAIL_ANY_MATTER_PARAMETER", OracleDbType.Boolean).Value = review.FailAnyMatter; Exception exceptionToReturn = null; try { oracleConnection.Open(); oracleCommand.ExecuteNonQuery(); oracleConnection.Close(); return(exceptionToReturn); } catch (Exception exeption) { exceptionToReturn = exeption; oracleConnection.Close(); return(exceptionToReturn); } } }
public Exception UpdateUserInformation(string email, CommonUser commonUser) { using (OracleConnection oracleConnection = Connection.GetInstance().ConnectionDB()) { OracleCommand oracleCommandObtain = new OracleCommand(); OracleCommand oracleCommandUpdate = new OracleCommand(); oracleCommandObtain.Connection = oracleConnection; oracleCommandObtain.CommandText = "PACK_COMMON_USER.UPDATE_USER_INFORMARTION"; oracleCommandObtain.CommandType = CommandType.StoredProcedure; oracleCommandUpdate.Parameters.Add("EMAIL_OLD_PARAMETER", OracleDbType.Varchar2).Value = email; oracleCommandUpdate.Connection = oracleConnection; oracleCommandUpdate.CommandText = "PACK_COMMON_USER.UPDATE_USER_INFORMARTION"; oracleCommandUpdate.CommandType = CommandType.StoredProcedure; oracleCommandUpdate.Parameters.Add("FIRST_NAME_PARAMETER", OracleDbType.Varchar2).Value = commonUser.FirstName; oracleCommandUpdate.Parameters.Add("SECOND_NAME_PARAMETER", OracleDbType.Varchar2).Value = commonUser.SecondName; oracleCommandUpdate.Parameters.Add("FIRST_SURNAME_PARAMETER", OracleDbType.Varchar2).Value = commonUser.FirstSurname; oracleCommandUpdate.Parameters.Add("SECOND_SURNAME_PARAMETER", OracleDbType.Varchar2).Value = commonUser.SecondSurname; oracleCommandUpdate.Parameters.Add("EMAIL_NEW_PARAMETER", OracleDbType.Varchar2).Value = commonUser.Email; oracleCommandUpdate.Parameters.Add("PASSWORD_PARAMETER", OracleDbType.Blob).Value = commonUser.Password; oracleCommandUpdate.Parameters.Add("SALT_PARAMETER", OracleDbType.Blob).Value = commonUser.Salt; Exception exceptionToReturn = null; try { oracleConnection.Open(); oracleCommandObtain.ExecuteNonQuery(); oracleCommandUpdate.ExecuteNonQuery(); oracleConnection.Close(); return(exceptionToReturn); } catch (Exception exeption) { exceptionToReturn = exeption; oracleConnection.Close(); return(exceptionToReturn); } } }