/// <summary> /// get a person by their person id /// </summary> /// <param name="personID"></param> /// <returns>a person searched by personID</returns> public Person GetPersonByID(int personID) { Person person = new Person(); string selectStatement = "SELECT personID, lastName, firstName, dateOfBirth, streetAddress, city, stateCode, zipCode, phoneNumber, ssn " + "FROM Person " + "WHERE personID = @personID"; using (SqlConnection connection = HealthcareDBConnection.GetConnection()) { connection.Open(); using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection)) { selectCommand.Parameters.AddWithValue("@personID", personID); using (SqlDataReader reader = selectCommand.ExecuteReader()) { while (reader.Read()) { person.PersonID = (int)reader["personID"]; person.LastName = (string)reader["lastName"]; person.FirstName = (string)reader["firstName"]; person.DateOfBirth = (DateTime)reader["dateOfBirth"]; person.StreetAddress = (string)reader["streetAddress"]; person.City = (string)reader["city"]; person.StateCode = (string)reader["stateCode"]; person.ZipCode = (int)reader["zipCode"]; person.PhoneNumber = (string)reader["phoneNumber"]; person.SSN = (string)reader["ssn"]; } } } return(person); } }
/// <summary> /// Retrieves doctorID by personID /// </summary> /// <param name="personID">Person ID to search</param> /// <returns>Doctor of that person ID</returns> public Doctor GetDoctorByPersonID(int personID) { Doctor doctor = new Doctor(); string selectStatement = "SELECT doctorID, personID " + "FROM doctor " + "WHERE personID = @personID"; using (SqlConnection connection = HealthcareDBConnection.GetConnection()) { connection.Open(); using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection)) { selectCommand.Parameters.AddWithValue("@personID", personID); using (SqlDataReader reader = selectCommand.ExecuteReader()) { while (reader.Read()) { doctor.DoctorID = (int)reader["doctorID"]; doctor.PersonID = (int)reader["personID"]; } } } } return(doctor); }
/// <summary> /// returs list of all nurses /// </summary> /// <returns>list of all nurses</returns> public List <Nurse> GetAllNurses() { List <Nurse> nurseList = new List <Nurse>(); string selectStatement = "SELECT p.lastName, p.firstName, p.dateOfBirth, n.nurseID FROM person p JOIN nurse n ON p.personID = n.personID"; using (SqlConnection connection = HealthcareDBConnection.GetConnection()) { connection.Open(); using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection)) { using (SqlDataReader reader = selectCommand.ExecuteReader()) { while (reader.Read()) { Nurse nurse = new Nurse(); nurse.LastName = reader["lastName"].ToString(); nurse.FirstName = reader["firstName"].ToString(); nurse.DateOfBirth = (DateTime)reader["dateOfBirth"]; nurse.NurseID = Convert.ToInt32(reader["nurseID"]); nurseList.Add(nurse); } } } } return(nurseList); }
/// <summary> /// Get all available tests /// </summary> /// <returns>A list of all available tests</returns> public List <Test> GetAllTests() { List <Test> testList = new List <Test>(); string selectStatement = "SELECT * " + "FROM test "; using (SqlConnection connection = HealthcareDBConnection.GetConnection()) { connection.Open(); using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection)) { using (SqlDataReader reader = selectCommand.ExecuteReader()) { while (reader.Read()) { Test test = new Test(); test.TestCode = (string)reader["testCode"]; test.TestName = (string)reader["testName"]; testList.Add(test); } } } } return(testList); }
/// <summary> /// Returns all the specialties of a doctor /// </summary> /// <param name="docID"></param> /// <returns></returns> public List <Specialty> GetSpecialtyByDoctorID(int docID) { List <Specialty> specialties = new List <Specialty>(); string selectStatement = "SELECT specialtyName " + "FROM specialty " + "INNER JOIN Doctor " + "ON Doctor.DoctorID = Specialty.DoctorID " + "WHERE Doctor.doctorID = @docID"; using (SqlConnection connection = HealthcareDBConnection.GetConnection()) { connection.Open(); using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection)) { selectCommand.Parameters.AddWithValue("@docID", docID); using (SqlDataReader reader = selectCommand.ExecuteReader()) { while (reader.Read()) { Specialty specialty = new Specialty(); specialty.SpecialityName = reader["specialtyName"].ToString(); specialties.Add(specialty); } } } return(specialties); } }
public Appointment GetAppointmentByAppointmentID(int appointmentID) { Appointment appointment = new Appointment(); string selectStatement = "SELECT a.appointmentID, a.patientID, (p.firstName + ' ' + p.lastName) AS name, a.dateTime, a.reasonForVisit FROM Appointment a JOIN doctor d" + " ON a.doctorID = d.doctorID JOIN person p ON p.personID = d.personID WHERE appointmentID = @appmtID"; using (SqlConnection connection = HealthcareDBConnection.GetConnection()) { connection.Open(); using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection)) { selectCommand.Parameters.AddWithValue("@appmtID", appointmentID); using (SqlDataReader reader = selectCommand.ExecuteReader()) { while (reader.Read()) { appointment.AppointmentID = (int)reader["appointmentID"]; appointment.PatientID = (int)reader["patientID"]; appointment.DoctorName = reader["name"].ToString(); appointment.DateTime = (DateTime)reader["dateTime"]; appointment.ReasonForVisit = (string)reader["reasonForVisit"]; } } } } return(appointment); }
/// <summary> /// retireve a person's login information /// </summary> /// <param name="username">As a string</param> /// <param name="password">As a string</param> /// <returns></returns> public DataTable GetLogin(string username, string password) { HashingService hashing = new HashingService(); DataTable dt = new DataTable(); string selectStatement = "SELECT l.personID, l.userName, l.password, (p.firstName + ' ' + p.lastName) AS name" + " FROM login l JOIN person p ON p.personID = l.personID WHERE userName = @username AND password = @password"; using (SqlConnection connection = HealthcareDBConnection.GetConnection()) { connection.Open(); SqlCommand sc = new SqlCommand(selectStatement, connection); sc.Parameters.AddWithValue("@username", username); sc.Parameters.AddWithValue("@password", hashing.PasswordHashing(password)); SqlDataReader reader = sc.ExecuteReader(); dt.Columns.Add("personID", typeof(int)); dt.Columns.Add("userName", typeof(string)); dt.Columns.Add("password", typeof(string)); dt.Columns.Add("name", typeof(string)); dt.Load(reader); } return(dt); }
/// <summary> /// return a list of all states /// </summary> /// <returns>list of 50 states</returns> public List <State> GetAllStates() { List <State> allStates = new List <State>(); string selectStatement = "SELECT * FROM state"; using (SqlConnection connection = HealthcareDBConnection.GetConnection()) { connection.Open(); SqlCommand sc = new SqlCommand(selectStatement, connection); using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection)) { using (SqlDataReader reader = selectCommand.ExecuteReader()) { while (reader.Read()) { State state = new State(); state.StateCode = reader["stateCode"].ToString(); state.StateName = reader["stateName"].ToString(); allStates.Add(state); } } } } return(allStates); }
/// <summary> /// return true if user is a nurse /// </summary> /// <param name="personID">As an integer</param> /// <returns>true if user is a nurse</returns> public Boolean IsNurse(int personID) { DataTable dt = new DataTable(); Boolean isNurse = false; string selectStatement = "SELECT CASE WHEN EXISTS(SELECT * FROM nurse n WHERE n.personID = p.personID)" + " THEN 'is nurse' ELSE 'not nurse' END FROM person p WHERE p.personID = @personID"; using (SqlConnection connection = HealthcareDBConnection.GetConnection()) { connection.Open(); SqlCommand sc = new SqlCommand(selectStatement, connection); sc.Parameters.AddWithValue("@personID", personID); using (SqlDataReader reader = sc.ExecuteReader()) { while (reader.Read()) { if (reader.GetValue(0).ToString() == "is nurse") { isNurse = true; } } } return(isNurse); } }
/// <summary> /// return a list of patients by date of birth /// </summary> /// <param name="dob"></param> /// <returns>list of patients searched by date of birth</returns> public List <Patient> GetPatientsByDOB(DateTime dob) { List <Patient> searchList = new List <Patient>(); string selectStatement = "SELECT p.lastName, p.firstName, p.dateOfBirth, pa.patientID FROM person p JOIN patient pa ON p.personID = pa.personID WHERE CAST(p.dateOfBirth as DATE) = @DOB"; using (SqlConnection connection = HealthcareDBConnection.GetConnection()) { connection.Open(); using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection)) { selectCommand.Parameters.AddWithValue("@DOB", dob.ToString("yyyy-MM-dd")); using (SqlDataReader reader = selectCommand.ExecuteReader()) { while (reader.Read()) { Patient patient = new Patient(); patient.LastName = reader["lastName"].ToString(); patient.FirstName = reader["firstName"].ToString(); patient.DateOfBirth = (DateTime)reader["dateOfBirth"]; patient.PatientID = Convert.ToInt32(reader["patientID"]); searchList.Add(patient); } } } } return(searchList); }
/// <summary> /// Retrieves a person by the doctorsID /// </summary> /// <param name="docID">doctorID to search</param> /// <returns>Person of that doctorID</returns> public Person GetPersonByDoctorID(int docID) { Person person = new Person(); string selectStatement = "SELECT lastName, firstName, doctorID " + "FROM Person " + "INNER JOIN Doctor " + "ON Doctor.PersonID = Person.PersonID " + "WHERE doctorID = @docID"; using (SqlConnection connection = HealthcareDBConnection.GetConnection()) { connection.Open(); using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection)) { selectCommand.Parameters.AddWithValue("@docID", docID); using (SqlDataReader reader = selectCommand.ExecuteReader()) { while (reader.Read()) { person.LastName = (string)reader["lastName"]; person.FirstName = (string)reader["firstName"]; } } } return(person); } }
/// <summary> /// Retrieves all doctors in DB /// </summary> /// <returns>List of all doctors</returns> public List <Doctor> GetDoctors() { List <Doctor> doctorList = new List <Doctor>(); string selectStatement = "SELECT doctor.doctorID, doctor.personID " + "FROM doctor"; using (SqlConnection connection = HealthcareDBConnection.GetConnection()) { connection.Open(); using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection)) { using (SqlDataReader reader = selectCommand.ExecuteReader()) { while (reader.Read()) { Doctor doctor = new Doctor(); doctor.DoctorID = (int)reader["doctorID"]; doctor.PersonID = (int)reader["personID"]; doctorList.Add(doctor); } } } } return(doctorList); }
/// <summary> /// return a state name based on its code /// </summary> /// <param name="stateCode"></param> /// <returns>name of a state</returns> public String FindStateNamebyCode(string stateCode) { string stateName = ""; using (SqlConnection connection = HealthcareDBConnection.GetConnection()) { connection.Open(); using (SqlCommand selectCommand = new SqlCommand("SELECT stateName FROM state where stateCode = @code", connection)) { selectCommand.Parameters.AddWithValue("@code", stateCode); stateName = selectCommand.ExecuteScalar().ToString(); } } return(stateName); }
/// <summary> /// return whether nurse is active or inactive /// </summary> /// <param name="nurseID"></param> /// <returns>1 for active, 0 for inactive</returns> public int GetNurseStatus(int nurseID) { int active = 0; using (SqlConnection connection = HealthcareDBConnection.GetConnection()) { connection.Open(); using (SqlCommand selectCommand = new SqlCommand("SELECT active_status FROM nurse where nurseID = @id", connection)) { selectCommand.Parameters.AddWithValue("@id", nurseID); active = Convert.ToInt32(selectCommand.ExecuteScalar()); } } return(active); }
/// <summary> /// add a new nurse /// </summary> /// <param name="person"></param> public Boolean AddNurse(Person person, String active) { Boolean success = false; using (SqlConnection connection = HealthcareDBConnection.GetConnection()) { connection.Open(); SqlTransaction transaction = connection.BeginTransaction(); try { string insertStatement = "INSERT Person(lastName, firstName, dateOfBirth, streetAddress, city, stateCode, zipCode, phoneNumber, ssn) " + "VALUES(@lastName, @firstName, @dateOfBirth, @streetAddress, @city, @stateCode, @zipCode, @phoneNumber, @ssn)"; SqlCommand insertCommand = new SqlCommand(insertStatement, connection, transaction); insertCommand.Parameters.AddWithValue("@lastName", person.LastName); insertCommand.Parameters.AddWithValue("@firstName", person.FirstName); insertCommand.Parameters.AddWithValue("@dateOfBirth", person.DateOfBirth.Date); insertCommand.Parameters.AddWithValue("@streetAddress", person.StreetAddress); insertCommand.Parameters.AddWithValue("@city", person.City); insertCommand.Parameters.AddWithValue("@stateCode", person.StateCode); insertCommand.Parameters.AddWithValue("@zipCode", person.ZipCode); insertCommand.Parameters.AddWithValue("@phoneNumber", person.PhoneNumber); insertCommand.Parameters.AddWithValue("@ssn", person.SSN); insertCommand.ExecuteNonQuery(); insertCommand = new SqlCommand("INSERT nurse (personID) VALUES ((SELECT IDENT_CURRENT('person')))", connection, transaction); insertCommand.ExecuteNonQuery(); SqlCommand updateCommand = new SqlCommand("UPDATE nurse SET active_status = @active WHERE nurseID = (SELECT IDENT_CURRENT('nurse'))", connection, transaction); updateCommand.Parameters.AddWithValue("@active", active); updateCommand.ExecuteNonQuery(); transaction.Commit(); success = true; } catch { transaction.Rollback(); } } return(success); }
/// <summary> /// Add a login to the system /// </summary> /// <param name="login">A login object</param> public void AddLogin(Login login) { SqlConnection connection = HealthcareDBConnection.GetConnection(); string insertStatement = "INSERT Login " + "(personID, userName, password) " + "VALUES (@personID, @userName, @password)"; SqlCommand insertCommand = new SqlCommand(insertStatement, connection); insertCommand.Parameters.AddWithValue("@personID", login.PersonID); insertCommand.Parameters.AddWithValue("@userName", login.UserName); insertCommand.Parameters.AddWithValue("@password", login.Password); connection.Open(); insertCommand.ExecuteNonQuery(); string selectStatement = "SELECT IDENT_CURRENT('Login') FROM Login"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); }
/// <summary> /// Get a personID from a patientID /// </summary> /// <param name="patientID"></param> /// <returns>a personID </returns> public int GetPersonID(int patientID) { int id; string selectStatement = "SELECT p.personID FROM person p JOIN patient pa ON pa.personID = p.personID WHERE pa.patientID = @patientID"; using (SqlConnection connection = HealthcareDBConnection.GetConnection()) { connection.Open(); using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection)) { selectCommand.Parameters.AddWithValue("@patientID", patientID); id = (int)selectCommand.ExecuteScalar(); } return(id); } }
/// <summary> /// Updates a nurses information /// </summary> /// <param name="personID"></param> /// <param name="lName"></param> /// <param name="fName"></param> /// <param name="dob"></param> /// <param name="streetAddress"></param> /// <param name="city"></param> /// <param name="state"></param> /// <param name="zipCode"></param> /// <param name="phone"></param> /// <param name="ssn"></param> public void UpdateNurse(int personID, string lName, string fName, DateTime dob, string streetAddress, string city, string state, int zipCode, string phoneNumber, string ssn, string active) { string updateStatement = "UPDATE person " + "SET firstName = @fName, lastName = @lName, dateOfBirth = @dob, " + "stateCode = @stateCode, streetAddress = @streetAddress, city = @city, " + "zipCode = @zip, phoneNumber = @phoneNumber, ssn = @ssn " + "WHERE personID = @pID"; using (SqlConnection connection = HealthcareDBConnection.GetConnection()) { connection.Open(); SqlTransaction transaction = connection.BeginTransaction(); try { SqlCommand updateCommand = new SqlCommand(updateStatement, connection, transaction); updateCommand.Parameters.AddWithValue("@pID", personID); updateCommand.Parameters.AddWithValue("@lName", lName); updateCommand.Parameters.AddWithValue("@fName", fName); updateCommand.Parameters.AddWithValue("@dob", dob.Date); updateCommand.Parameters.AddWithValue("@streetAddress", streetAddress); updateCommand.Parameters.AddWithValue("@city", city); updateCommand.Parameters.AddWithValue("@stateCode", state); updateCommand.Parameters.AddWithValue("@zip", zipCode); updateCommand.Parameters.AddWithValue("@phoneNumber", phoneNumber); updateCommand.Parameters.AddWithValue("@ssn", ssn); updateCommand.ExecuteNonQuery(); updateCommand = new SqlCommand("UPDATE nurse SET active_status = @active WHERE personID = @pID", connection, transaction); updateCommand.Parameters.AddWithValue("@active", active); updateCommand.Parameters.AddWithValue("@pID", personID); updateCommand.ExecuteNonQuery(); transaction.Commit(); } catch { transaction.Rollback(); } } }
public void UpdateAppointment(Appointment appointment) { string updateStatement = "UPDATE appointment " + "SET reasonForVisit = @reason, doctorID = @docID, dateTime = @datetime " + "WHERE appointmentID = @appmtID"; using (SqlConnection connection = HealthcareDBConnection.GetConnection()) { connection.Open(); SqlCommand updateCommand = new SqlCommand(updateStatement, connection); updateCommand.Parameters.AddWithValue("@reason", appointment.ReasonForVisit); updateCommand.Parameters.AddWithValue("@docID", appointment.DoctorID); updateCommand.Parameters.AddWithValue("@dateTime", appointment.DateTime); updateCommand.Parameters.AddWithValue("@appmtID", appointment.AppointmentID); updateCommand.ExecuteNonQuery(); } }
/// <summary> /// Adds appointment to the database /// </summary> /// <param name="appointment">appointment to add</param> /// <returns></returns> public int AddAppointment(Appointment appointment) { SqlConnection connection = HealthcareDBConnection.GetConnection(); string insertStatement = "INSERT Appointment " + "(patientID, doctorID, dateTime, reasonForVisit) " + "VALUES (@patientID, @doctorID, @dateTime, @reasonForVisit)"; SqlCommand insertCommand = new SqlCommand(insertStatement, connection); insertCommand.Parameters.AddWithValue("@patientID", appointment.PatientID); insertCommand.Parameters.AddWithValue("@doctorID", appointment.DoctorID); insertCommand.Parameters.AddWithValue("@dateTime", appointment.DateTime); insertCommand.Parameters.AddWithValue("@reasonForVisit", appointment.ReasonForVisit); connection.Open(); insertCommand.ExecuteNonQuery(); string selectStatement = "SELECT IDENT_CURRENT('Appointment') FROM Appointment"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); int appointmentID = Convert.ToInt32(selectCommand.ExecuteScalar()); return(appointmentID); }
/// <summary> /// Gets a list of patients by their last name and DOB /// </summary> /// <param name="dateOfBirth"></param> /// <param name="lname"></param> /// <returns>list of patients based on last name and DOB</returns> public List <Patient> GetPatientsByDOBandLastName(DateTime dateOfBirth, string lname) { List <Patient> searchList = new List <Patient>(); string selectStatement = "SELECT p.lastName, p.firstName, p.dateOfBirth, pa.patientID " + "FROM person p " + "JOIN patient pa " + "ON p.personID = pa.personID " + "WHERE p.lastName = @lname AND p.dateOfBirth = @dateOfBirth"; using (SqlConnection connection = HealthcareDBConnection.GetConnection()) { connection.Open(); using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection)) { selectCommand.Parameters.AddWithValue("@lname", lname); selectCommand.Parameters.AddWithValue("@dateOfBirth", dateOfBirth); using (SqlDataReader reader = selectCommand.ExecuteReader()) { while (reader.Read()) { Patient patient = new Patient(); patient.LastName = reader["lastName"].ToString(); patient.FirstName = reader["firstName"].ToString(); patient.DateOfBirth = (DateTime)reader["dateOfBirth"]; patient.PatientID = Convert.ToInt32(reader["patientID"]); searchList.Add(patient); } } } } return(searchList); }
/// <summary> /// Insert a new test into the test result table indicating it has been ordered /// </summary> /// <param name="testToOrder"> the test to be ordered</param> public void OrderTest(Test testToOrder) { string insertStatement = "INSERT INTO testResult(visitID,testCode,testDate,results,normal)VALUES(@visitID,@testCode,@testDate,@results,@normal);"; DateTime now = DateTime.Now; using (SqlConnection connection = HealthcareDBConnection.GetConnection()) { connection.Open(); using (SqlCommand insertCommand = new SqlCommand(insertStatement, connection)) { insertCommand.Transaction = connection.BeginTransaction(); insertCommand.Parameters.AddWithValue("@visitID", testToOrder.VisitId); insertCommand.Parameters.AddWithValue("@testCode", testToOrder.TestCode); insertCommand.Parameters.AddWithValue("@testDate", now); insertCommand.Parameters.AddWithValue("@results", DBNull.Value); insertCommand.Parameters.AddWithValue("@normal", DBNull.Value); insertCommand.ExecuteNonQuery(); insertCommand.Transaction.Commit(); } } }
/// <summary> /// Gets an appointment with the doctor displayed and returns a datatable. /// </summary> /// <param name="patientID"></param> /// <returns></returns> public DataTable GetAppointmentsAndDoctorByPatientID(int patientID) { DataTable dataTable = new DataTable(); string selectStatement = "SELECT appointmentID AS AppointmentID, patientID AS PatientID, person.lastName AS Doctor, dateTime AS 'Appointment Time', reasonForVisit AS Reason " + "FROM Appointment " + "JOIN Doctor ON Doctor.doctorID = appointment.doctorID " + "JOIN Person ON Person.personID = Doctor.personID " + "WHERE patientID = @patientID " + "ORDER BY dateTime"; using (SqlConnection connection = HealthcareDBConnection.GetConnection()) { connection.Open(); using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection)) { selectCommand.Parameters.AddWithValue("@patientID", patientID); dataTable.Load(selectCommand.ExecuteReader()); } } return(dataTable); }
/// <summary> /// Get specific test's assigned to a visit /// </summary> /// <param name="visitId">as an integer</param> /// <returns>a list of tests</returns> public List <Test> GetTestsByVisitId(int visitId) { List <Test> testList = new List <Test>(); string selectStatement = "SELECT t.testName, tr.testCode, tr.results, tr.normal, tr.testDate, tr.performDate " + "FROM testResult AS tr " + "JOIN test AS t ON tr.testCode = t.testCode " + "WHERE tr.visitID = @visitID"; using (SqlConnection connection = HealthcareDBConnection.GetConnection()) { connection.Open(); using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection)) { selectCommand.Parameters.AddWithValue("@visitID", visitId); using (SqlDataReader reader = selectCommand.ExecuteReader()) { while (reader.Read()) { Test test = new Test(); test.TestCode = (string)reader["testCode"]; test.TestName = (string)reader["testName"]; test.Results = reader["results"] as string; test.Normal = reader["normal"] as bool?; test.TestDate = (DateTime)reader["testDate"]; test.PerformDate = reader["performDate"] as DateTime?; test.VisitId = visitId; testList.Add(test); } } } } return(testList); }
/// <summary> /// Returns true if the doctor already has an appointment at that date/time /// </summary> /// <param name="doctorID">doctor to search</param> /// <param name="dateTime">time to search</param> /// <returns>True if doctor is already scheduled</returns> public bool CheckIfDoctorHasAppointmentScheduled(int doctorID, DateTime dateTime) { Doctor doctor = new Doctor(); Appointment appointment = new Appointment(); string selectStatement = "SELECT doctor.doctorID, dateTime " + "FROM doctor " + "INNER JOIN appointment " + "ON appointment.doctorID = doctor.doctorID " + "WHERE doctor.doctorID = @doctorID AND dateTime = @dateTime"; using (SqlConnection connection = HealthcareDBConnection.GetConnection()) { connection.Open(); using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection)) { selectCommand.Parameters.AddWithValue("@doctorID", doctorID); selectCommand.Parameters.AddWithValue("@dateTime", dateTime); using (SqlDataReader reader = selectCommand.ExecuteReader()) { while (reader.Read()) { appointment.DoctorID = (int)reader["doctorID"]; appointment.DateTime = (DateTime)reader["dateTime"]; } } } if (appointment.DoctorID == doctorID && appointment.DateTime == dateTime) { return(true); } else { return(false); } } }
/// <summary> /// Gets a visit by the appointmentID /// </summary> /// <param name="apptID">appointmentID to search</param> /// <returns>Visit of that appointmentID</returns> public Visit GetVisitByAppt(int apptID) { Visit visit = new Visit(); using (SqlConnection connection = HealthcareDBConnection.GetConnection()) { connection.Open(); using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection)) { selectCommand.Parameters.AddWithValue("@apptID", apptID); using (SqlDataReader reader = selectCommand.ExecuteReader()) { while (reader.Read()) { visit.VisitID = reader["visitID"] as int? ?? 0; visit.AppointmentID = (int)reader["appointmentID"]; visit.Status = reader["status"] as int? ?? 0; visit.PatientID = (int)reader["patientID"]; visit.DoctorName = (string)reader["doctorName"]; visit.DoctorID = (int)reader["doctorID"]; visit.DateTime = (DateTime)reader["dateTime"]; visit.Weight = reader["weight"] as decimal? ?? 0; visit.Temp = reader["temp"] as decimal? ?? 0; visit.SystolicBP = reader["systolicBP"] as int? ?? 0; visit.DiastolicBP = reader["diastolicBP"] as int? ?? 0; visit.Pulse = reader["pulse"] as int? ?? 0; visit.Symptoms = reader["symptoms"] as string; visit.InitialDiagnosis = reader["initialDiagnosis"] as string; visit.FinalDiagnosis = reader["finalDiagnosis"] as string; } } } } return(visit); }
/// <summary> /// Gets all the appointments a patient is scheduled for /// </summary> /// <param name="patientID">patient id for search</param> /// <returns></returns> public List <Appointment> GetAppointmentsByPatientID(int patientID) { List <Appointment> appointments = new List <Appointment>(); string selectStatement = "SELECT appointmentID, patientID, doctorID, dateTime, reasonForVisit " + "FROM Appointment " + "WHERE patientID = @patientID " + "ORDER BY dateTime"; using (SqlConnection connection = HealthcareDBConnection.GetConnection()) { connection.Open(); using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection)) { selectCommand.Parameters.AddWithValue("@patientID", patientID); using (SqlDataReader reader = selectCommand.ExecuteReader()) { while (reader.Read()) { Appointment appointment = new Appointment(); appointment.AppointmentID = (int)reader["appointmentID"]; appointment.PatientID = (int)reader["patientID"]; appointment.DoctorID = (int)reader["doctorID"]; appointment.DateTime = (DateTime)reader["dateTime"]; appointment.ReasonForVisit = (string)reader["reasonForVisit"]; appointments.Add(appointment); } } } } return(appointments); }
/// <summary> /// Delete the patient from the db /// </summary> /// <param name="patientID">patientID as an integer</param> /// <returns></returns> public Boolean DeletePatient(int patientID) { Boolean success = false; int personID; using (SqlConnection connection = HealthcareDBConnection.GetConnection()) { connection.Open(); SqlTransaction transaction = connection.BeginTransaction(); try { SqlCommand selectCommand = new SqlCommand("SELECT p.personID FROM person p JOIN patient pa ON pa.personID = p.personID WHERE pa.patientID = @patientID", connection, transaction); selectCommand.Parameters.AddWithValue("@patientID", patientID); personID = (int)selectCommand.ExecuteScalar(); SqlCommand deleteCommand = new SqlCommand("DELETE FROM patient WHERE patientID = @patientID", connection, transaction); deleteCommand.Parameters.AddWithValue("@patientId", patientID); deleteCommand.ExecuteNonQuery(); deleteCommand = new SqlCommand("DELETE FROM person WHERE personID = @personID", connection, transaction); deleteCommand.Parameters.AddWithValue("@personID", personID); deleteCommand.ExecuteNonQuery(); transaction.Commit(); success = true; } catch { transaction.Rollback(); } } return(success); }
/// <summary> /// Update an existing test /// </summary> /// <param name="visitID">visitid as an integer</param> /// <param name="testCode">the test code as string</param> /// <param name="result">as string</param> /// <param name="normal">as bool</param> public void UpdateTestResult(int visitID, string testCode, string result, bool normal, DateTime performDate) { string updateStatement = "UPDATE testResult SET results = @result, normal = @normal, performDate = @performDate WHERE visitID = @visitID AND testCode = @testCode;"; DateTime now = DateTime.Now; using (SqlConnection connection = HealthcareDBConnection.GetConnection()) { connection.Open(); using (SqlCommand updateCommand = new SqlCommand(updateStatement, connection)) { updateCommand.Transaction = connection.BeginTransaction(); updateCommand.Parameters.AddWithValue("@visitID", visitID); updateCommand.Parameters.AddWithValue("@testCode", testCode); updateCommand.Parameters.AddWithValue("@result", result); updateCommand.Parameters.AddWithValue("@normal", normal); updateCommand.Parameters.AddWithValue("@performDate", performDate); updateCommand.ExecuteNonQuery(); updateCommand.Transaction.Commit(); } } }
/// <summary> /// update a patients personal information /// </summary> /// <param name="personID"></param> /// <param name="lastName"></param> /// <param name="zipCode"></param> /// <param name="firstName"></param> /// <param name="street"></param> /// <param name="city"></param> /// <param name="state"></param> /// <param name="phone"></param> public void UpdatePatient(int personID, string lastName, int zipCode, string firstName, string street, string city, string state, string phone, string ssn, DateTime dob) { string updateStatement = "UPDATE person SET firstName = @firstName, lastName = @lastName, stateCode = @stateCode, phoneNumber = @phone, streetAddress = @street, " + "city = @city, zipCode = @zip, ssn = @ssn, dateOfBirth = @dob WHERE personID = @id"; using (SqlConnection connection = HealthcareDBConnection.GetConnection()) { connection.Open(); using (SqlCommand updateCommand = new SqlCommand(updateStatement, connection)) { updateCommand.Parameters.AddWithValue("@id", personID); updateCommand.Parameters.AddWithValue("@lastName", lastName); updateCommand.Parameters.AddWithValue("@firstName", firstName); updateCommand.Parameters.AddWithValue("@street", street); updateCommand.Parameters.AddWithValue("@city", city); updateCommand.Parameters.AddWithValue("@stateCode", state); updateCommand.Parameters.AddWithValue("@zip", zipCode); updateCommand.Parameters.AddWithValue("@phone", phone); updateCommand.Parameters.AddWithValue("@ssn", ssn); updateCommand.Parameters.AddWithValue("@dob", dob.Date); updateCommand.ExecuteNonQuery(); } } }