/// <summary> /// Method that indicates if the visit has been finalized. /// Visits are finalized when a final diagnosis has been recorded. /// </summary> /// <param name="appointmentId">The ID of the appointment.</param> /// <returns>True if there is a final diagnosis for the specified appointment, false otherwise.</returns> public bool VisitIsFinal(int appointmentId) { if (appointmentId < 0) { throw new ArgumentException("The appointment ID cannot be negative.", "appointmentId"); } string selectStatement = "SELECT @NumberOfFinalizedDiagnoses = COUNT(diagnosisName) " + "FROM Diagnosis " + "WHERE appointmentId = @AppointmentId " + "AND isFinal = 1"; using (SqlConnection connection = ClinicDBConnection.GetConnection()) { connection.Open(); using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection)) { SqlParameter countParameter = new SqlParameter("@NumberOfFinalizedDiagnoses", SqlDbType.Bit) { Direction = ParameterDirection.Output }; selectCommand.Parameters.Add(countParameter); selectCommand.Parameters.AddWithValue("@AppointmentId", appointmentId); selectCommand.ExecuteNonQuery(); return(Convert.ToInt32(countParameter.Value) > 0); } } }
/// <summary> /// Method that orders lab tests. /// </summary> /// <param name="appointmentId">The ID of the appointment.</param> /// <param name="labTest">An object representing the type of test that is being ordered.</param> public void OrderLabTest(int appointmentId, LabTest labTest) { if (appointmentId < 0) { throw new ArgumentException("The appointment ID cannot be negative.", "appointmentId"); } if (labTest == null) { throw new ArgumentNullException("labTest", "The lab test cannot be null."); } string insertStatement = "INSERT ConductedLabTest (appointmentId, testCode) " + "VALUES (@AppointmentId, @TestCode)"; using (SqlConnection connection = ClinicDBConnection.GetConnection()) { connection.Open(); using (SqlCommand insertCommand = new SqlCommand(insertStatement, connection)) { insertCommand.Parameters.AddWithValue("@AppointmentId", appointmentId); insertCommand.Parameters.AddWithValue("@TestCode", labTest.TestCode); insertCommand.ExecuteNonQuery(); } } }
/// <summary> /// This returns a list of all statuses /// </summary> /// <returns>List of all status types</returns> public List <Status> GetAllStatusTypes() { List <Status> statuses = new List <Status>(); using (SqlConnection connection = ClinicDBConnection.GetConnection()) { connection.Open(); string selectStatement = "SELECT id, status FROM status_code"; using (SqlCommand select = new SqlCommand(selectStatement, connection)) { using (SqlDataReader reader = select.ExecuteReader()) { while (reader.Read()) { Status status = new Status(); status.StatusDescription = reader["status"].ToString(); status.StatusID = (int)reader["id"]; statuses.Add(status); } } } connection.Close(); } return(statuses); }
/// <summary> /// Returns the employeeID equal to the accepted personID. /// Note that the password returned is the hashed password in the database, not plaintext. /// </summary> /// <param name="personID">ID of the person</param> /// <returns>Employee object</returns> public Employee GetEmployeeBy_PersonID(int personID) { Employee employee = new Employee(); string selectStatement = "SELECT id, person_id, password, username " + "FROM users " + "WHERE person_id = @personID "; using (SqlConnection connection = ClinicDBConnection.GetConnection()) { connection.Open(); using (SqlCommand command = new SqlCommand(selectStatement, connection)) { command.Parameters.AddWithValue("@personID", personID); using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { { employee.EmployeeID = (int)reader["id"]; employee.PersonId = (int)reader["person_id"]; employee.UserName = reader["username"].ToString(); employee.HashedPassword = (byte[])reader["password"]; } } } } connection.Close(); } return(employee); }
/// <summary> /// Returns a list of all Doctors /// </summary> /// <returns>list of all Doctors</returns> public static List <Doctor> GetAllDoctors() { List <Doctor> doctors = new List <Doctor>(); string selectStatement = "SELECT * FROM doctor;"; using (SqlConnection connection = ClinicDBConnection.GetConnection()) { connection.Open(); using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection)) { using (SqlDataReader reader = selectCommand.ExecuteReader()) { while (reader.Read()) { Doctor doctor = new Doctor { DoctorId = (int)reader["id"], PersonId = (int)reader["person_id"] }; PopulatePersonalInformation(doctor); doctors.Add(doctor); } } } connection.Close(); } return(doctors); }
/// <summary> /// This method allows users to add employee values to the database /// </summary> /// <param name="addedEmployee">The employee object to add to the db.</param> public void AddEmployee(Employee addedEmployee) { int employeeID = -1; string addEmployee = "INSERT users ( username, password, person_id) " + "VALUES (@username, HASHBYTES('SHA2_256', @password), @person_id) "; string employeeIDStatement = "SELECT IDENT_CURRENT('users') FROM users"; try { SqlConnection connection = ClinicDBConnection.GetConnection(); connection.Open(); using (SqlCommand insertCommand = new SqlCommand(addEmployee, connection)) { insertCommand.Parameters.AddWithValue("username", addedEmployee.UserName); insertCommand.Parameters.AddWithValue("password", addedEmployee.Password); insertCommand.Parameters.AddWithValue("person_id", addedEmployee.PersonId); insertCommand.ExecuteNonQuery(); using (SqlCommand selectCommand = new SqlCommand(employeeIDStatement, connection)) { employeeID = Convert.ToInt32(selectCommand.ExecuteScalar()); } } } catch (Exception ex) { MessageBox.Show(ex.Message, "Error " + addedEmployee.PersonId.ToString(), MessageBoxButtons.OK); } }
/// <summary> /// Gets all the test codes in the database. /// </summary> /// <returns>List of all Tests</returns> public static List <Test> GetAllTestCodes() { List <Test> tests = new List <Test>(); using (SqlConnection connection = ClinicDBConnection.GetConnection()) { connection.Open(); string selectStatement = "SELECT id, code FROM test_code"; using (SqlCommand select = new SqlCommand(selectStatement, connection)) { using (SqlDataReader reader = select.ExecuteReader()) { while (reader.Read()) { Test test = new Test { Code = reader["code"].ToString(), TestCodeID = (int)reader["id"] }; tests.Add(test); } } } connection.Close(); } return(tests); }
/// <summary> /// Method that determines if a visit has been entered in to the DB. /// </summary> /// <param name="appointmentId">The ID of the appointment.</param> /// <returns>true if there is a visit in the DB matching the appointment ID</returns> public bool IsVisitPresent(int appointmentId) { if (appointmentId < 0) { throw new ArgumentException("The appointment ID cannot be negative."); } string selectStatement = "SELECT @NumberOfVisits = COUNT(appointmentId) " + "FROM Visit " + "WHERE appointmentId = @AppointmentId "; using (SqlConnection connection = ClinicDBConnection.GetConnection()) { connection.Open(); using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection)) { SqlParameter countParameter = new SqlParameter("@NumberOfVisits", SqlDbType.Int, 1) { Direction = ParameterDirection.Output }; selectCommand.Parameters.Add(countParameter); selectCommand.Parameters.AddWithValue("@AppointmentId", appointmentId); selectCommand.ExecuteNonQuery(); return(Convert.ToInt32(countParameter.Value) > 0); } } }
/// <summary> /// This method will change the accpted nurse status to the value memeber /// </summary> /// <param name="nurseID">Nurse ID value</param> /// <param name="valueMember">Value Member</param> public void ChangeStatus(int nurseID, int valueMember) { string updateStatus = "UPDATE nurse " + "SET status_id = @valuemember " + "WHERE id = @nurseID"; try { using (SqlConnection connection = ClinicDBConnection.GetConnection()) { connection.Open(); using (SqlCommand updateCommand = new SqlCommand(updateStatus, connection)) { updateCommand.Parameters.AddWithValue("@valuemember", valueMember); updateCommand.Parameters.AddWithValue("@nurseID", nurseID); updateCommand.ExecuteNonQuery(); } connection.Close(); } } catch (SqlException ex) { throw ex; } }
/// <summary> /// Returns a Patient equal to the accepted ID /// </summary> /// <param name="patientID">Accepted Patient ID</param> /// <returns>returns a patient equal to the accepted patientID</returns> public Patient GetPatientByID(int patientID) { string selectStatement = "SELECT * FROM patient WHERE id = @patientID AND status_id = 1; "; Patient patient = new Patient(); using (SqlConnection connection = ClinicDBConnection.GetConnection()) { connection.Open(); using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection)) { selectCommand.Parameters.AddWithValue("@patientID", @patientID); using (SqlDataReader reader = selectCommand.ExecuteReader()) { while (reader.Read()) { patient.PatientID = (int)reader["id"]; patient.PersonId = (int)reader["personal_information_id"]; patient.Status = (int)reader["status_id"]; } PopulatePersonalInformation(patient); } } connection.Close(); return(patient); } }
/// <summary> /// Gets a list of all patients with a firstname equal to the accepted string value /// </summary> /// <param name="firstname">Accepted Lastname value</param> /// <returns>All patients with a firstname equal to firstname</returns> public List <Patient> GetPatientByFirstName_Only(string @firstname) { List <Patient> patients = new List <Patient>(); string selectStatement = "SELECT patient.id, personal_information_id FROM patient " + "JOIN person person ON personal_information_id = person.id " + "WHERE person.id IN (SELECT id FROM person WHERE first_name = @firstname_clean)" + " AND status_id = 1; "; using (SqlConnection connection = ClinicDBConnection.GetConnection()) { connection.Open(); using (SqlCommand command = new SqlCommand(selectStatement, connection)) { command.Parameters.AddWithValue("firstname_clean", @firstname); using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { Patient patient = new Patient { PatientID = (int)reader["id"], PersonId = (int)reader["personal_information_id"] }; PopulatePersonalInformation(patient); patients.Add(patient); } } } connection.Close(); } return(patients); }
/// <summary> /// Gets all patients from DB with personal information populated /// </summary> /// <returns>List of all patients</returns> public List <Patient> GetAllPatientsWith_ActiveStatus() { List <Patient> patients = new List <Patient>(); string selectStatement = "SELECT * FROM patient " + "WHERE status_id = 1;"; using (SqlConnection connection = ClinicDBConnection.GetConnection()) { connection.Open(); using (SqlCommand command = new SqlCommand(selectStatement, connection)) { using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { Patient patient = new Patient { PatientID = (int)reader["id"], PersonId = (int)reader["personal_information_id"], Status = (int)reader["status_id"] }; PopulatePersonalInformation(patient); patients.Add(patient); } } } connection.Close(); } return(patients); }
/// <summary> /// Returns appointents equal to the patient ID /// </summary> /// <param name="selectedRowPatientID">Patient ID value</param> /// <returns>List of appointments</returns> public List <Appointment> GetAppointmentsByPatientID(int selectedRowPatientID) { List <Appointment> appointments = new List <Appointment>(); string selectStatement = "Select id, scheduled_datetime, reason_for_visit, doctor_id, patient_id FROM appointment WHERE patient_id = @id_clean"; using (SqlConnection connection = ClinicDBConnection.GetConnection()) { connection.Open(); using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection)) { selectCommand.Parameters.AddWithValue("@id_clean", selectedRowPatientID); using (SqlDataReader reader = selectCommand.ExecuteReader()) { while (reader.Read()) { Appointment appointment = new Appointment(); appointment.Doctor = new Doctor(); appointment.Patient = new Patient(); appointment.AppointmentID = (int)reader["id"]; appointment.Scheduled_Date = (DateTime)reader["scheduled_datetime"]; appointment.Reason_For_Visit = reader["reason_for_visit"].ToString(); appointment.Doctor = GetDoctorByID((int)reader["doctor_id"]); appointment.Patient = GetPatientByID((int)reader["patient_id"]); appointments.Add(appointment); } } } connection.Close(); } return(appointments); }
/// <summary> /// Returns a list of Patients equal to the accepted DateTime /// </summary> /// <param name="dateOfBirth">Accepted DateTime value - shorted to just Date</param> /// <returns>List of Patients equal to the accepted DateTime</returns> public List <Patient> GetAllPatients_DOB(DateTime dateOfBirth) { List <Patient> patients = new List <Patient>(); string selectStatement = "SELECT patient.id, personal_information_id FROM patient " + "JOIN person person ON personal_information_id = person.id " + "WHERE person.id IN (SELECT id FROM person WHERE date_of_birth = @dateOfBirthdate_clean) AND status_id = 1;"; using (SqlConnection connection = ClinicDBConnection.GetConnection()) { connection.Open(); using (SqlCommand command = new SqlCommand(selectStatement, connection)) { command.Parameters.AddWithValue("dateOfBirthdate_clean", @dateOfBirth); using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { Patient patient = new Patient { PatientID = (int)reader["id"], PersonId = (int)reader["personal_information_id"] }; PopulatePersonalInformation(patient); patients.Add(patient); } } } connection.Close(); } return(patients); }
/// <summary> /// Method returns the role of the person for the given username. /// The role can be nurse or administrator. /// </summary> /// <param name="username">username of the person</param> /// <returns>the role of the person</returns> public string GetRole(string username) { if (string.IsNullOrEmpty(username)) { throw new ArgumentNullException("username", "The username cannot be null or empty."); } string selectStatement = "SELECT @Role = role " + "FROM Credential " + "WHERE userName = @Username"; using (SqlConnection connection = ClinicDBConnection.GetConnection()) { connection.Open(); using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection)) { SqlParameter roleParameter = new SqlParameter("@Role", SqlDbType.VarChar, 25) { Direction = ParameterDirection.Output }; selectCommand.Parameters.Add(roleParameter); selectCommand.Parameters.AddWithValue("@Username", username); selectCommand.ExecuteNonQuery(); return(roleParameter.Value.ToString()); } } }
/// <summary> /// Method returns the username given personId. /// </summary> /// <param name="inputedPersonId">personId of the person</param> /// <returns>the username of the person</returns> public string GetUsername(int inputedPersonId) { if (inputedPersonId < 0) { throw new ArgumentException("The person ID cannot be negative.", "inputedPersonId"); } string selectStatement = "SELECT @Username = userName " + "FROM Credential " + "WHERE personId = @InputedPersonId"; using (SqlConnection connection = ClinicDBConnection.GetConnection()) { connection.Open(); using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection)) { SqlParameter usernameParameter = new SqlParameter("@Username", SqlDbType.VarChar, 25) { Direction = ParameterDirection.Output }; selectCommand.Parameters.Add(usernameParameter); selectCommand.Parameters.AddWithValue("@InputedPersonId", inputedPersonId); selectCommand.ExecuteNonQuery(); return(usernameParameter.Value.ToString()); } } }
/// <summary> /// Returns a Doctor equal to the doctorID /// </summary> /// <param name="doctorID">Doctor ID value</param> /// <returns>returns Doctor equal to the accepted DoctorID</returns> public static Doctor GetDoctorByID(int doctorID) { string selectStatement = "SELECT * FROM doctor WHERE id = @doctorID;"; Doctor doctor = new Doctor(); using (SqlConnection connection = ClinicDBConnection.GetConnection()) { connection.Open(); using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection)) { selectCommand.Parameters.AddWithValue("@doctorID", @doctorID); using (SqlDataReader reader = selectCommand.ExecuteReader()) { while (reader.Read()) { doctor.DoctorId = (int)reader["id"]; doctor.PersonId = (int)reader["person_id"]; } PopulatePersonalInformation(doctor); } } connection.Close(); return(doctor); } }
/// <summary> /// Method that can be used to add a diagnosis to the database. /// </summary> /// <param name="appointmentId">The ID of the appointment.</param> /// <param name="diagnosisName">A string describing the diagnosis.</param> /// <param name="isFinal">A boolean value indicating if the diagnosis is final.</param> public void AddDiagnosis(int appointmentId, string diagnosisName, bool isFinal) { if (appointmentId < 0) { throw new ArgumentException("The appointment ID cannot be negative.", "appointmentId"); } if (string.IsNullOrEmpty(diagnosisName)) { throw new ArgumentNullException("diagnosisName", "The diagnosis name cannot be null or empty."); } string insertStatement = "INSERT Diagnosis (appointmentId, diagnosisName, isFinal) " + "VALUES (@AppointmentId, @DiagnosisName, @IsFinal)"; using (SqlConnection connection = ClinicDBConnection.GetConnection()) { connection.Open(); using (SqlCommand insertCommand = new SqlCommand(insertStatement, connection)) { insertCommand.Parameters.AddWithValue("@AppointmentId", appointmentId); insertCommand.Parameters.AddWithValue("@DiagnosisName", diagnosisName); insertCommand.Parameters.AddWithValue("@IsFinal", isFinal); insertCommand.ExecuteNonQuery(); } } }
/// <summary> /// Gets all the appointments for a specified patient. /// </summary> /// <param name="patient">Patient object</param> /// <returns>a List of appointment objects</returns> public List <Appointment> GetAllAppointmentsForPatient(Patient patient) { List <Appointment> appointments = new List <Appointment>(); string selectStatement = "SELECT * FROM appointment WHERE patient_id = @patientID;"; using (SqlConnection connection = ClinicDBConnection.GetConnection()) { connection.Open(); using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection)) { selectCommand.Parameters.AddWithValue("@patientID", patient.PatientID); using (SqlDataReader reader = selectCommand.ExecuteReader()) { while (reader.Read()) { Appointment appointment = new Appointment { AppointmentID = (int)reader["id"], Scheduled_Date = (DateTime)reader["scheduled_datetime"], Reason_For_Visit = reader["reason_for_visit"].ToString(), Doctor = new Doctor { DoctorId = (int)reader["doctor_id"] }, Patient = patient }; PopulatePersonalInformation(appointment.Doctor); appointments.Add(appointment); } } } connection.Close(); } return(appointments); }
/// <summary> /// This method returns status description equal to the accepted ID /// </summary> /// <param name="statusID">Status ID value</param> /// <returns>String rep of the status value</returns> public string GetStatusByID(int statusID) { string status_description = ""; string selectStatement = "Select status FROM status_code " + "WHERE id = @statusID"; using (SqlConnection connection = ClinicDBConnection.GetConnection()) { connection.Open(); using (SqlCommand command = new SqlCommand(selectStatement, connection)) { command.Parameters.AddWithValue("statusID", statusID); using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { { status_description = reader["status"].ToString(); } } } } connection.Close(); } return(status_description); }
/// <summary> /// Changes the employee credentials in the database.. /// </summary> /// <param name="edited_employee">the new employee object</param> /// <param name="old_employee">the existing employee object</param> /// <returns>Yes/No if update operation was successful</returns> public bool EditEmployeeInfo(Employee edited_employee, Employee old_employee) { string updateStatement = "UPDATE users " + "SET username = @new_username, " + "password = HASHBYTES('SHA2_256', @new_password) " + "WHERE username = @old_username AND " + "password = @old_password AND " + "id = @old_id AND " + "person_id = @old_personID;"; using (SqlConnection connection = ClinicDBConnection.GetConnection()) { connection.Open(); using (SqlCommand update = new SqlCommand(updateStatement, connection)) { update.Parameters.AddWithValue("@new_username", edited_employee.UserName); update.Parameters.AddWithValue("@new_password", edited_employee.Password); update.Parameters.AddWithValue("@old_username", old_employee.UserName); update.Parameters.AddWithValue("@old_password", old_employee.HashedPassword); update.Parameters.AddWithValue("@old_id", old_employee.EmployeeID); update.Parameters.AddWithValue("@old_personID", old_employee.PersonId); int count = update.ExecuteNonQuery(); connection.Close(); return(count > 0); } } }
/// <summary> /// Method that adds the specified Visit object to the database. /// </summary> /// <param name="theVisit">The Visit object being added to the database.</param> public void AddVisit(Visit theVisit) { if (theVisit == null) { throw new ArgumentNullException("theVisit", "The visit cannot be null."); } string insertStatement = "INSERT Visit (appointmentId, weight, systolicBloodPressure, diastolicBloodPressure, bodyTemperature, pulse, symptoms, nurseId) " + "VALUES (@AppointmentId, @Weight, @SystolicBloodPressure, @DiastolicBloodPressure, @BodyTemperature, @Pulse, @Symptoms, @NurseId)"; using (SqlConnection connection = ClinicDBConnection.GetConnection()) { connection.Open(); using (SqlCommand insertCommand = new SqlCommand(insertStatement, connection)) { insertCommand.Parameters.AddWithValue("@AppointmentId", theVisit.AppointmentId); insertCommand.Parameters.AddWithValue("@Weight", theVisit.Weight); insertCommand.Parameters.AddWithValue("@SystolicBloodPressure", theVisit.SystolicBloodPressure); insertCommand.Parameters.AddWithValue("@DiastolicBloodPressure", theVisit.DiastolicBloodPressure); insertCommand.Parameters.AddWithValue("@BodyTemperature", theVisit.BodyTemperature); insertCommand.Parameters.AddWithValue("@Pulse", theVisit.Pulse); insertCommand.Parameters.AddWithValue("@Symptoms", theVisit.Symptoms); insertCommand.Parameters.AddWithValue("@NurseId", theVisit.NurseId); insertCommand.ExecuteNonQuery(); } } }
/// <summary> /// Method that returns true if the specified doctor is unavailable at the specified start time /// assuming appointments are structured such that only start time is needed to confirm no overlap. /// </summary> /// <param name="doctorId">The ID of the doctor in question.</param> /// <param name="startDateTime">The date and time of the appointment in question.</param> /// <returns>True if the specified doctor is booked at the specified time, false otherwise.</returns> public bool DoctorIsBooked(int doctorId, DateTime startDateTime) { if (doctorId < 0) { throw new ArgumentException("The doctor's ID cannot be negative.", "doctorId"); } if (startDateTime == null) { throw new ArgumentNullException("startDateTime", "The start date and time of the appointment cannot be null."); } string selectStatement = "SELECT @NumberOfAppointments = COUNT(appointmentId) " + "FROM Appointment " + "WHERE doctorId = @DoctorId " + "AND startDateTime = @StartDateTime"; using (SqlConnection connection = ClinicDBConnection.GetConnection()) { connection.Open(); using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection)) { SqlParameter countParameter = new SqlParameter("@NumberOfAppointments", SqlDbType.Int, 1) { Direction = ParameterDirection.Output }; selectCommand.Parameters.Add(countParameter); selectCommand.Parameters.AddWithValue("@DoctorId", doctorId); selectCommand.Parameters.AddWithValue("@StartDateTime", startDateTime); selectCommand.ExecuteNonQuery(); return(Convert.ToInt32(countParameter.Value) > 0); } } }
/// <summary> /// Method that deletes the specified appointment from the database. /// </summary> /// <param name="appointmentId">The ID of the appointment being deleted from the database.</param> public void DeleteAppointment(int appointmentId) { if (appointmentId < 0) { throw new ArgumentException("The appointment ID cannot be negative.", "appointmentId"); } if (this.visitSource.IsVisitPresent(appointmentId)) { throw new ArgumentException("The specified appointment already has a visit associated with it; therefore, it cannot be deleted.", "appointmentId"); } string deleteStatement = "DELETE FROM Appointment " + "WHERE appointmentId = @AppointmentId"; using (SqlConnection connection = ClinicDBConnection.GetConnection()) { connection.Open(); using (SqlCommand deleteCommand = new SqlCommand(deleteStatement, connection)) { deleteCommand.Parameters.AddWithValue("@AppointmentId", appointmentId); deleteCommand.ExecuteNonQuery(); } } }
/// <summary> /// Retrieves all nurses from the database /// </summary> /// <returns>List of all nurses</returns> public List <Nurse> GetAllNurses() { string selectStatement = "SELECT n.*, username, password, u.id " + "FROM users AS u " + "JOIN nurse AS n ON n.person_id = u.person_id;"; List <Nurse> nurses = new List <Nurse>(); using (SqlConnection connection = ClinicDBConnection.GetConnection()) { connection.Open(); using (SqlCommand command = new SqlCommand(selectStatement, connection)) { using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { Nurse nurse = new Nurse { NurseID = (int)reader["id"], PersonId = (int)reader["person_id"], StatusID = (int)reader["status_id"], UserName = reader["username"].ToString(), // EmployeeID = (int)reader["users.id"] }; PopulatePersonalInformation(nurse); nurses.Add(nurse); } } } connection.Close(); } return(nurses); }
/// <summary> /// Gets a specific nurse by their id /// </summary> /// <param name="id">is the id used to select the nurse</param> /// <returns>Nurse object</returns> public static Nurse GetNurseByID(int id) { string selectStatement = "SELECT n.*, username, password, u.id " + "FROM users AS u " + "JOIN nurse AS n ON n.person_id = u.person_id " + "WHERE n.id = @NurseID;"; Nurse nurse = new Nurse(); using (SqlConnection connection = ClinicDBConnection.GetConnection()) { connection.Open(); using (SqlCommand command = new SqlCommand(selectStatement, connection)) { command.Parameters.AddWithValue("@NurseID", id); using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { nurse.NurseID = (int)reader["id"]; nurse.PersonId = (int)reader["person_id"]; nurse.StatusID = (int)reader["status_id"]; nurse.UserName = reader["username"].ToString(); PopulatePersonalInformation(nurse); } } } connection.Close(); } return(nurse); }
private static Person PopulatePersonalInformation(Person person) { string selectStatement = "SELECT * FROM person WHERE id = @personID;"; using (SqlConnection connection = ClinicDBConnection.GetConnection()) { connection.Open(); using (SqlCommand command = new SqlCommand(selectStatement, connection)) { command.Parameters.AddWithValue("@personID", person.PersonId); using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { person.LastName = reader["last_name"].ToString(); person.FirstName = reader["first_name"].ToString(); person.DateOfBirth = (DateTime)reader["date_of_birth"]; person.SocialSecurityNumber = reader["ssn"].ToString(); person.Gender = reader["gender"].ToString(); person.StreetAddress = reader["street_address"].ToString(); person.Phone = reader["phone"].ToString(); person.Zipcode = reader["zipcode"].ToString(); } } } connection.Close(); } return(person); }
/// <summary> /// This method will return a list of all Nurses with a lastname equal the the accepted lastname /// </summary> /// <param name="lastname">Nurse last name</param> /// <returns>List of all nurses returned by query</returns> public List <Nurse> GetAllNursesByLastname(string lastname) { List <Nurse> nurses = new List <Nurse>(); string selectStatement = "SELECT nurse.id, person_id, status_id FROM nurse " + "JOIN person person ON person_id = person.id " + "WHERE person.id IN (SELECT id FROM person WHERE last_name = @lastname_clean)"; using (SqlConnection connection = ClinicDBConnection.GetConnection()) { connection.Open(); using (SqlCommand command = new SqlCommand(selectStatement, connection)) { command.Parameters.AddWithValue("lastname_clean", @lastname); using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { Nurse nurse = new Nurse { NurseID = (int)reader["id"], PersonId = (int)reader["person_id"], StatusID = (int)reader["status_id"] }; PopulatePersonalInformation(nurse); nurses.Add(nurse); } } } connection.Close(); } return(nurses); }
/// <summary> /// Method that deletes the specified patient from the Person and Patient tables in the database. /// </summary> /// <param name="thePatient">An object representing the patient being deleted from the database.</param> public void DeletePatient(Patient thePatient) { if (thePatient == null) { throw new ArgumentNullException("thePatient", "The patient object cannot be null."); } string deletePersonStatement = "DELETE FROM Person " + "WHERE personId = @PersonId"; string deletePatientStatement = "DELETE FROM Patient " + "WHERE patientId = @PatientId"; using (SqlConnection connection = ClinicDBConnection.GetConnection()) { connection.Open(); SqlCommand deleteCommand = connection.CreateCommand(); SqlTransaction transaction; //Start a local transaction transaction = connection.BeginTransaction("DeletePatient"); deleteCommand.Connection = connection; deleteCommand.Transaction = transaction; deleteCommand.Parameters.AddWithValue("@PersonId", thePatient.PersonId); deleteCommand.Parameters.AddWithValue("@PatientId", thePatient.PatientId); try { deleteCommand.CommandText = deletePatientStatement; deleteCommand.ExecuteNonQuery(); deleteCommand.CommandText = deletePersonStatement; deleteCommand.ExecuteNonQuery(); //Attemp to commit the transaction transaction.Commit(); Console.WriteLine("Data was deleted from both tables."); } catch (Exception ex) { Console.WriteLine("Commit Exception Type: {0}", ex.GetType()); Console.WriteLine(" Message: {0}", ex.Message); // Attemp to roll back the transaction try { transaction.Rollback(); } catch (Exception ex2) { Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType()); Console.WriteLine(" Message: {0}", ex2.Message); } throw new ArgumentException("The patient was not deleted."); } } }
/// <summary> /// This method adds the accepted nurse to the database /// </summary> /// <param name="addedNurse">The Nurse object to add</param> /// <returns>The Nurse object</returns> public Nurse AddNurse(Nurse addedNurse) { Nurse returnedNurse = new Nurse(); int addedNurse_PersonalInfoID = -1; try { using (SqlConnection connection = ClinicDBConnection.GetConnection()) { connection.Open(); using (SqlTransaction transaction = connection.BeginTransaction()) { string insertPerson = "INSERT PERSON (last_name, first_name, date_of_birth, ssn, gender, street_address, phone, zipcode)" + "VALUES(@lastName, @firstName, @DOB, @SSN, @Gender, @streetAddress, @phoneNumber, @Zipcode)"; string insertNurse = "INSERT Nurse(person_id, status_id) VALUES (@personalID, @status_id)"; using (SqlCommand insertPersonCommand = new SqlCommand(insertPerson, connection)) { insertPersonCommand.Transaction = transaction; insertPersonCommand.Parameters.AddWithValue("lastName", addedNurse.LastName); insertPersonCommand.Parameters.AddWithValue("firstName", addedNurse.FirstName); insertPersonCommand.Parameters.AddWithValue("DOB", addedNurse.DateOfBirth); insertPersonCommand.Parameters.AddWithValue("SSN", addedNurse.SocialSecurityNumber); insertPersonCommand.Parameters.AddWithValue("Gender", addedNurse.Gender); insertPersonCommand.Parameters.AddWithValue("streetAddress", addedNurse.StreetAddress); insertPersonCommand.Parameters.AddWithValue("phoneNumber", addedNurse.Phone); insertPersonCommand.Parameters.AddWithValue("Zipcode", addedNurse.Zipcode); insertPersonCommand.ExecuteNonQuery(); string selectStatement = "SELECT IDENT_CURRENT('Person') FROM Person"; using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection)) { selectCommand.Transaction = transaction; addedNurse_PersonalInfoID = Convert.ToInt32(selectCommand.ExecuteScalar()); } } using (SqlCommand insertNurseCommand = new SqlCommand(insertNurse, connection)) { insertNurseCommand.Transaction = transaction; insertNurseCommand.Parameters.AddWithValue("@personalID", addedNurse_PersonalInfoID); insertNurseCommand.Parameters.AddWithValue("status_id", addedNurse.StatusID); insertNurseCommand.ExecuteNonQuery(); } transaction.Commit(); addedNurse.PersonId = addedNurse_PersonalInfoID; } } } catch (Exception ex) { MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK); } return(addedNurse); }