/// <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);
}
