/// <summary>
/// This method inserts a new Appointment defined by the user into the SQL database
/// </summary>
/// <param name="appointment">Appointment object that will be created</param>
/// <returns>integer ApptID </returns>
public static int CreateAppointment(Appointment appointment)
{
SqlConnection connection = HealthCareUWGDBConnection.GetConnection();
string insertStatement =
"INSERT Appointment " +
"(PatientID, DoctorID, apptDateTime, Reason, IsCheckedIn) " +
"VALUES (@PatientID, @DoctorID, @apptDateTime, @Reason, @IsCheckedIn)";
SqlCommand insertCommand = new SqlCommand(insertStatement, connection);
insertCommand.Parameters.AddWithValue("@PatientID", appointment.PatientID);
insertCommand.Parameters.AddWithValue("@DoctorID", appointment.DoctorID);
insertCommand.Parameters.AddWithValue("@apptDateTime", appointment.apptDateTime);
insertCommand.Parameters.AddWithValue("@Reason", appointment.Reason);
insertCommand.Parameters.AddWithValue("@IsCheckedIn", appointment.isCheckedIn);
try
{
connection.Open();
insertCommand.ExecuteNonQuery();
string selectStatement =
"SELECT IDENT_CURRENT('Appointment') FROM Appointment";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
int ApptID = Convert.ToInt32(selectCommand.ExecuteScalar());
return(ApptID);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
/// <summary>
/// Gets a list holding the diagnoses held in the sql database
/// </summary>
/// <returns>List of Diagnoses objects</returns>
public static List <Diagnoses> GetDiagnosesList()
{
List <Diagnoses> diagnosesList = new List <Diagnoses>();
SqlConnection connection = HealthCareUWGDBConnection.GetConnection();
string selectStatement =
"SELECT DiagnosesCode, DiagnosesName FROM Diagnoses Order by DiagnosesCode";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
SqlDataReader reader = null;
try
{
connection.Open();
reader = selectCommand.ExecuteReader();
while (reader.Read())
{
Diagnoses diag = new Diagnoses();
diag.DiagnosesCode = (int)reader["DiagnosesCode"];
diag.DiagnosesName = reader["DiagnosesName"].ToString();
diagnosesList.Add(diag);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
reader.Close();
connection.Close();
}
return(diagnosesList);
}
/// <summary>
/// updates an appointment that it is updated correctly
/// </summary>
/// <param name="appointment">appointment to be updated</param>
/// <returns>true or false depending on if updated correctly</returns>
public static bool appointmentCheckedIn(Appointment appointment)
{
SqlConnection connection = HealthCareUWGDBConnection.GetConnection();
string updateStatement =
"Update Appointment SET " +
"IsCheckedIn = @IsCheckedIn " +
"WHERE ApptID = @ApptID";
SqlCommand updateCommand = new SqlCommand(updateStatement, connection);
updateCommand.Parameters.AddWithValue("@ApptID", appointment.ApptID);
updateCommand.Parameters.AddWithValue("@IsCheckedIn", appointment.isCheckedIn);
try
{
connection.Open();
int count = updateCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
//get list of available tests
public static List <Test> GetTests()
{
List <Test> testList = new List <Test>();
SqlConnection connection = HealthCareUWGDBConnection.GetConnection();
string selectStatement =
"SELECT TestCode, TestName FROM LabTests Order by TestName";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
SqlDataReader reader = null;
try
{
connection.Open();
reader = selectCommand.ExecuteReader();
while (reader.Read())
{
Test test = new Test();
test.TestCode = (int)reader["TestCode"];
test.TestName = reader["TestName"].ToString();
testList.Add(test);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
reader.Close();
connection.Close();
}
return(testList);
}
// Updates the Patient's information
public static bool UpdatePatient(Person oldPerson, Person newPerson)
{
SqlConnection connection = HealthCareUWGDBConnection.GetConnection();
string updateStatement =
"UPDATE Person SET " +
"DOB = @NewDOB, " +
"FName = @NewFName, " +
"LName = @NewLName, " +
"Street = @NewStreet, " +
"City = @NewCity, " +
"State = @NewState, " +
"ZipCode = @NewZipCode, " +
"PhoneNum = @NewPhoneNum, " +
"SSN = @NewSSN " +
"WHERE PersonID = @OldPersonID";
SqlCommand updateCommand = new SqlCommand(updateStatement, connection);
updateCommand.Parameters.AddWithValue("@NewDOB", newPerson.DOB);
updateCommand.Parameters.AddWithValue("@NewFName", newPerson.FName);
updateCommand.Parameters.AddWithValue("@NewLName", newPerson.LName);
updateCommand.Parameters.AddWithValue("@NewStreet", newPerson.Street);
updateCommand.Parameters.AddWithValue("@NewCity", newPerson.City);
updateCommand.Parameters.AddWithValue("@NewState", newPerson.State);
updateCommand.Parameters.AddWithValue("@NewZipCode", newPerson.ZipCode);
updateCommand.Parameters.AddWithValue("@NewPhoneNum", newPerson.PhoneNum);
updateCommand.Parameters.AddWithValue("@NewSSN", newPerson.SSN);
updateCommand.Parameters.AddWithValue("@OldPersonID", oldPerson.PersonID);
updateCommand.Parameters.AddWithValue("@OldDOB", oldPerson.DOB);
updateCommand.Parameters.AddWithValue("@OldFName", oldPerson.FName);
updateCommand.Parameters.AddWithValue("@OldLName", oldPerson.LName);
updateCommand.Parameters.AddWithValue("@OldStreet", oldPerson.Street);
updateCommand.Parameters.AddWithValue("@OldCity", oldPerson.City);
updateCommand.Parameters.AddWithValue("@OldState", oldPerson.State);
updateCommand.Parameters.AddWithValue("@OldZipCode", oldPerson.ZipCode);
updateCommand.Parameters.AddWithValue("@OldPhoneNum", oldPerson.PhoneNum);
updateCommand.Parameters.AddWithValue("@OldSSN", oldPerson.SSN);
try
{
connection.Open();
int count = updateCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
/// <summary>
/// Creates a list of patients based on a full name and last name
/// </summary>
/// <param name="FName">Patiens first name</param>
/// <param name="LName">Patients last name</param>
/// <returns>List of patients</returns>
public static List <Patient> SearchPatientByFullName(string FName, string LName)
{
List <Patient> patientList = new List <Patient>();
SqlConnection connection = HealthCareUWGDBConnection.GetConnection();
string selectStatement =
"SELECT p.PersonID, pt.PatientID, p.FName, p.LName, p.FName +' ' + p.LName AS 'FullName', p.DOB, p.Street, p.City, p.State, p.ZipCode, p.PhoneNum, p.SSN " +
"FROM Person p JOIN Patients pt " +
"ON p.PersonID = pt.PersonID " +
"WHERE p.FName = @FName AND p.LName = @LName";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@FName", FName.ToString());
selectCommand.Parameters.AddWithValue("@LName", LName.ToString());
SqlDataReader reader = null;
try
{
connection.Open();
reader = selectCommand.ExecuteReader();
while (reader.Read())
{
Patient patient = new Patient();
patient.PersonID = (int)reader["PersonID"];
patient.PatientID = (int)reader["PatientID"];
patient.FullName = reader["FullName"].ToString();
patient.FName = reader["FName"].ToString();
patient.LName = reader["LName"].ToString();
patient.DOB = reader["DOB"].ToString();
patient.Street = reader["Street"].ToString();
patient.City = reader["City"].ToString();
patient.State = reader["State"].ToString();
patient.ZipCode = reader["ZipCode"].ToString();
patient.PhoneNum = reader["PhoneNum"].ToString();
patient.SSN = reader["SSN"].ToString();
patientList.Add(patient);
}
}
catch (SqlException ex)
{
throw ex;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
throw ex;
}
finally
{
if (connection != null)
{
connection.Close();
}
if (reader != null)
{
reader.Close();
}
}
return(patientList);
}
//submit an order for a test
public static void OrderTest(int apptID, DateTime testDate, int testCode)
{
SqlConnection connection = HealthCareUWGDBConnection.GetConnection();
string insertStatement =
"INSERT INTO TestResults " +
"(TestCode, ApptID, TestDate) " +
"VALUES (@TestCode, @ApptID, @TestDate)";
SqlCommand insertCommand = new SqlCommand(insertStatement, connection);
insertCommand.Parameters.AddWithValue("@TestCode", testCode);
insertCommand.Parameters.AddWithValue("@ApptID", apptID);
insertCommand.Parameters.AddWithValue("@TestDate", testDate);
try
{
connection.Open();
insertCommand.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
/// <summary>
/// adds a patient to the database
/// </summary>
/// <param name="patient">the patient that will be added</param>
/// <returns>integer that will be used as a response control</returns>
public static int AddPatient(Patient patient)
{
SqlConnection connection = HealthCareUWGDBConnection.GetConnection();
string insertStatement =
"INSERT Patients " +
"(PersonID) " +
"VALUES (@PersonID)";
SqlCommand insertCommand = new SqlCommand(insertStatement, connection);
insertCommand.Parameters.AddWithValue("@PersonID", patient.PersonID);
try
{
connection.Open();
insertCommand.ExecuteNonQuery();
string selectStatement =
"SELECT IDENT_CURRENT('Patients') FROM Patients";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
int patientID = Convert.ToInt32(selectCommand.ExecuteScalar());
return(patientID);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
/// <summary>
/// Gets a list of visits based on patient id
/// </summary>
/// <param name="patientID">patient id used to search for patients</param>
/// <returns>list of visit objects</returns>
public static List <Visit> GetPatientVisits(string patientID)
{
List <Visit> visitList = new List <Visit>();
SqlConnection connection = HealthCareUWGDBConnection.GetConnection();
string selectStatement =
"SELECT v.VisitID, v.ApptID, a.ApptDateTime, v.SysBP, v.DiaBP, v.BodyTemp, v.Pulse, v.Symptoms, v.NurseID, v.DiagnosesCode, Diagnoses.DiagnosesName " +
"FROM Visits v JOIN Appointment a " +
"ON v.ApptID = a.ApptID " +
"LEFT OUTER JOIN Diagnoses on v.DiagnosesCode = Diagnoses.DiagnosesCode " +
"WHERE a.PatientID = @PatientID";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@PatientID", patientID);
SqlDataReader reader = null;
try
{
connection.Open();
reader = selectCommand.ExecuteReader();
while (reader.Read())
{
Visit visit = new Visit();
visit.VisitID = (int)reader["VisitID"];
visit.ApptID = reader["ApptID"].ToString();
visit.ApptDateTime = (DateTime)reader["ApptDateTime"];
visit.SysBP = reader["SysBP"].ToString();
visit.DiaBP = reader["DiaBP"].ToString();
visit.BodyTemp = reader["BodyTemp"].ToString();
visit.Pulse = reader["Pulse"].ToString();
visit.Symptoms = reader["Symptoms"].ToString();
visit.NurseID = reader["NurseID"].ToString();
visit.DiagnosesCode = (int)reader["DiagnosesCode"];
visit.DiagnosesName = reader["DiagnosesName"].ToString();
visitList.Add(visit);
}
}
catch (SqlException ex)
{
throw ex;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
throw ex;
}
finally
{
if (connection != null)
{
connection.Close();
}
if (reader != null)
{
reader.Close();
}
}
return(visitList);
}
/// <summary>
/// Gets an appointment based on the appointment ID
/// </summary>
/// <param name="apptID">appoint id that will be used to get the appointment</param>
/// <returns>Appointment object based on the requested appointment</returns>
public static Appointment GetAppointment(int apptID)
{
Appointment appointment = new Appointment();
SqlConnection connection = HealthCareUWGDBConnection.GetConnection();
string selectStatement =
"SELECT ApptID, PatientID, DoctorID, apptDateTime, Reason, IsCheckedIn " +
"FROM Appointment " +
"WHERE ApptID = @ApptID";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@ApptID", apptID);
SqlDataReader reader = null;
try
{
connection.Open();
reader = selectCommand.ExecuteReader();
if (reader.Read())
{
appointment.ApptID = (int)reader["ApptID"];
appointment.PatientID = (int)reader["PatientID"];
appointment.DoctorID = (int)reader["DoctorID"];
appointment.apptDateTime = reader["apptDateTime"].ToString();
appointment.Reason = reader["Reason"].ToString();
appointment.isCheckedIn = reader["IsCheckedIn"].ToString();
}
else
{
appointment = null;
}
}
catch (SqlException ex)
{
throw ex;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
throw ex;
}
finally
{
if (connection != null)
{
connection.Close();
}
if (reader != null)
{
reader.Close();
}
}
return(appointment);
}
//Updates the Visit information
public static bool UpdateVisit(Visit oldVisit, Visit newVisit)
{
SqlConnection connection = HealthCareUWGDBConnection.GetConnection();
string updateStatement =
"UPDATE Visits SET " +
"SysBP = @NewSysBP, " +
"DiaBP = @NewDiaBP, " +
"BodyTemp = @NewBodyTemp, " +
"Pulse = @NewPulse, " +
"Symptoms = @NewSymptoms, " +
"NurseID = @NewNurseID, " +
"DiagnosesCode = @NewDiagnoses " +
"WHERE VisitID = @OldVisitID";
SqlCommand updateCommand = new SqlCommand(updateStatement, connection);
updateCommand.Parameters.AddWithValue("@NewSysBP", newVisit.SysBP);
updateCommand.Parameters.AddWithValue("@NewDiaBP", newVisit.DiaBP);
updateCommand.Parameters.AddWithValue("@NewBodyTemp", newVisit.BodyTemp);
updateCommand.Parameters.AddWithValue("@NewPulse", newVisit.Pulse);
updateCommand.Parameters.AddWithValue("@NewSymptoms", newVisit.Symptoms);
updateCommand.Parameters.AddWithValue("@NewNurseID", newVisit.NurseID);
updateCommand.Parameters.AddWithValue("@NewDiagnoses", newVisit.DiagnosesCode);
updateCommand.Parameters.AddWithValue("@OldVisitID", oldVisit.VisitID);
updateCommand.Parameters.AddWithValue("@OldSysBP", oldVisit.SysBP);
updateCommand.Parameters.AddWithValue("@OldDiaBP", oldVisit.DiaBP);
updateCommand.Parameters.AddWithValue("@OldBodyTemp", oldVisit.BodyTemp);
updateCommand.Parameters.AddWithValue("@OldPulse", oldVisit.Pulse);
updateCommand.Parameters.AddWithValue("@OldSymptoms", oldVisit.Symptoms);
updateCommand.Parameters.AddWithValue("@OldNurseID", oldVisit.NurseID);
updateCommand.Parameters.AddWithValue("@OldDiagnoses", oldVisit.DiagnosesCode);
try
{
connection.Open();
int count = updateCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
/// <summary>
/// Gets a list of tests based on the patient id
/// </summary>
/// <param name="patientID">patient id that will be used to search for tests</param>
/// <returns>list of test objects</returns>
public static List <Test> GetPatientTests(string patientID)
{
List <Test> testList = new List <Test>();
SqlConnection connection = HealthCareUWGDBConnection.GetConnection();
string selectStatement =
"SELECT t.TestCode, lt.TestName, t.TestDate, t.ApptID, t.Result " +
"FROM TestResults t JOIN Appointment a " +
"ON a.ApptID = t.ApptID " +
"JOIN LabTests lt " +
"ON lt.TestCode = t.TestCode " +
"WHERE a.PatientID = @PatientID";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@PatientID", patientID);
SqlDataReader reader = null;
try
{
connection.Open();
reader = selectCommand.ExecuteReader();
while (reader.Read())
{
Test test = new Test();
test.TestCode = (int)reader["TestCode"];
test.TestName = reader["TestName"].ToString();
test.TestDate = reader["TestDate"].ToString();
test.ApptID = reader["ApptID"].ToString();
test.Result = reader["Result"].ToString();
testList.Add(test);
}
}
catch (SqlException ex)
{
throw ex;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
throw ex;
}
finally
{
if (connection != null)
{
connection.Close();
}
if (reader != null)
{
reader.Close();
}
}
return(testList);
}
/// <summary>
/// Gets a list of patient appointments based on the patient id submitted
/// </summary>
/// <param name="patientID">the patient ID that will be used to search for the appointments</param>
/// <returns>list of appointment objects</returns>
public static List <Appointment> GetPatientAppointments(string patientID)
{
List <Appointment> appointmentList = new List <Appointment>();
SqlConnection connection = HealthCareUWGDBConnection.GetConnection();
string selectStatement =
"SELECT ApptID, PatientID, DoctorID, apptDateTime, Reason " +
"FROM Appointment " +
"WHERE PatientID = @PatientID and apptDateTime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) and IsCheckedIn = 0 " +
"ORDER BY apptDateTime desc";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@PatientID", patientID);
SqlDataReader reader = null;
try
{
connection.Open();
reader = selectCommand.ExecuteReader();
while (reader.Read())
{
Appointment appointment = new Appointment();
appointment.ApptID = (int)reader["ApptID"];
appointment.PatientID = (int)reader["PatientID"];
appointment.DoctorID = (int)reader["DoctorID"];
appointment.apptDateTime = reader["apptDateTime"].ToString();
appointment.Reason = reader["Reason"].ToString();
appointmentList.Add(appointment);
}
}
catch (SqlException ex)
{
throw ex;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
throw ex;
}
finally
{
if (connection != null)
{
connection.Close();
}
if (reader != null)
{
reader.Close();
}
}
return(appointmentList);
}
// Gets the Gets the User's information via the Username and Password
public static UserInfo GetUser(string username, string password)
{
String encryptedPassword = GetSHA1HashData(password);
UserInfo user = new UserInfo();
SqlConnection connection = HealthCareUWGDBConnection.GetConnection();
string selectStatement =
"SELECT Username, Password, AdminID, DoctorID, NurseID " +
"FROM UserInfo " +
"WHERE Username = @Username AND Password = @Password";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@Username", username);
selectCommand.Parameters.AddWithValue("@Password", encryptedPassword);
try
{
connection.Open();
SqlDataReader reader =
selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (reader.Read())
{
user.userID = reader["Username"].ToString();
user.userPassword = reader["Password"].ToString();
if (reader.IsDBNull(reader.GetOrdinal("AdminID")) == false)
{
user.AdminID = (int)reader["AdminID"];
}
if (reader.IsDBNull(reader.GetOrdinal("DoctorID")) == false)
{
user.DoctorID = (int)reader["DoctorID"];
}
if (reader.IsDBNull(reader.GetOrdinal("NurseID")) == false)
{
user.NurseID = (int)reader["NurseID"];
}
}
else
{
user = null;
}
reader.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(user);
}
// Gets the Gets the Patient's information via the Person ID
public static Person GetPatient(int personID)
{
Person person = new Person();
SqlConnection connection = HealthCareUWGDBConnection.GetConnection();
string selectStatement =
"Select PersonID, DOB, FName, LName, Street, City, State, ZipCode, PhoneNum, SSN " +
"FROM Person " +
"WHERE PersonID = @PersonID";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@PersonID", personID);
try
{
connection.Open();
SqlDataReader reader =
selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (reader.Read())
{
person.PersonID = (int)reader["PersonID"];
//Stores formatted date into DOB
person.DOB = String.Format("{0:yyyy-MM-dd}", reader["DOB"]);
person.FName = reader["FName"].ToString();
person.LName = reader["LName"].ToString();
person.Street = reader["Street"].ToString();
person.City = reader["City"].ToString();
person.State = reader["State"].ToString();
person.ZipCode = reader["ZipCode"].ToString();
person.PhoneNum = reader["PhoneNum"].ToString();
person.SSN = reader["SSN"].ToString();
}
else
{
person = null;
}
reader.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(person);
}
/// <summary>
/// Gets the visit based on the visit id
/// </summary>
/// <param name="visitID">visit id used to find the visit</param>
/// <returns>a visit object</returns>
public static Visit GetVisit(int visitID)
{
Visit visit = new Visit();
SqlConnection connection = HealthCareUWGDBConnection.GetConnection();
string selectStatement =
"SELECT v.VisitID, v.ApptID, v.SysBP, v.DiaBP, v.BodyTemp, v.Pulse, v.Symptoms, v.NurseID, v.DiagnosesCode " +
"FROM Visits v " +
"WHERE v.VisitID = @VisitID";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@VisitID", visitID);
try
{
connection.Open();
SqlDataReader reader =
selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (reader.Read())
{
visit.VisitID = (int)reader["VisitID"];
visit.ApptID = reader["ApptID"].ToString();
visit.SysBP = reader["SysBP"].ToString();
visit.DiaBP = reader["DiaBP"].ToString();
visit.BodyTemp = reader["BodyTemp"].ToString();
visit.Pulse = reader["Pulse"].ToString();
visit.Symptoms = reader["Symptoms"].ToString();
visit.NurseID = reader["NurseID"].ToString();
visit.DiagnosesCode = (int)reader["DiagnosesCode"];
}
else
{
visit = null;
}
reader.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(visit);
}
// ValidLogin returns true or false depending if the UserID and Password combination matches.
public static bool ValidLogin(string UserID, string Password)
{
Boolean isValid = false;
// Encryptes incoming password to SHA1 Hash
String encryptedPassword = GetSHA1HashData(Password);
UserInfo ui = new UserInfo();
SqlConnection con = HealthCareUWGDBConnection.GetConnection();
string selectStatement =
"SELECT Username " +
"FROM UserInfo " +
"WHERE Username = @Username AND Password = @Password";
try
{
con.Open();
SqlCommand selectCommand = new SqlCommand(selectStatement, con);
selectCommand.Parameters.AddWithValue("@Username", UserID);
selectCommand.Parameters.AddWithValue("@Password", encryptedPassword);
SqlDataAdapter da = new SqlDataAdapter(selectCommand);
DataTable dt = new DataTable();
da.Fill(dt);
if (dt.Rows.Count == 1)
{
isValid = true;
}
else
{
isValid = false;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
con.Close();
return(isValid);
}
/// <summary>
/// gets a list of the Doctors from the SQL database
/// </summary>
/// <returns>list of Doctor objects</returns>
public static List <Doctor> GetDoctors()
{
List <Doctor> docList = new List <Doctor>();
string selectStatement =
"SELECT p.PersonID, d.DoctorID, p.FName +' ' + p.LName AS 'FullName' " +
"FROM Person p JOIN Doctors d " +
"ON p.PersonID = d.PersonID";
try
{
using (SqlConnection connection = HealthCareUWGDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
Doctor doctor = new Doctor();
doctor.PersonID = (int)reader["PersonID"];
doctor.DoctorID = (int)reader["DoctorID"];
doctor.FullName = reader["FullName"].ToString();
docList.Add(doctor);
}
}
}
}
}
catch (SqlException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
return(docList);
}
/// <summary>
/// Gets a list of all patients from the sql database
/// </summary>
/// <returns>a list of patients</returns>
public static List <Patient> GetPatients()
{
List <Patient> patientList = new List <Patient>();
string selectStatement =
"SELECT p.PersonID, pt.PatientID, p.FName +' ' + p.LName AS 'FullName' " +
"FROM Person p JOIN Patients pt " +
"ON p.PersonID = pt.PersonID";
try
{
using (SqlConnection connection = HealthCareUWGDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
Patient patient = new Patient();
patient.PersonID = (int)reader["PersonID"];
patient.PatientID = (int)reader["PatientID"];
patient.FullName = reader["FullName"].ToString();
patientList.Add(patient);
}
}
}
}
}
catch (SqlException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
return(patientList);
}
// Inserts into the Person table
public static int AddPerson(Person person)
{
SqlConnection connection = HealthCareUWGDBConnection.GetConnection();
string insertStatement =
"INSERT Person " +
"(DOB, FName, LName, Street, City, State, ZipCode, PhoneNum, SSN) " +
"VALUES (@DOB, @FName, @LName, @Street, @City, @State, @ZipCode, @PhoneNum, @SSN)";
SqlCommand insertCommand = new SqlCommand(insertStatement, connection);
insertCommand.Parameters.AddWithValue("@DOB", person.DOB);
insertCommand.Parameters.AddWithValue("@FName", person.FName);
insertCommand.Parameters.AddWithValue("@LName", person.LName);
insertCommand.Parameters.AddWithValue("@Street", person.Street);
insertCommand.Parameters.AddWithValue("@City", person.City);
insertCommand.Parameters.AddWithValue("@State", person.State);
insertCommand.Parameters.AddWithValue("@ZipCode", person.ZipCode);
insertCommand.Parameters.AddWithValue("@PhoneNum", person.PhoneNum);
insertCommand.Parameters.AddWithValue("@SSN", person.SSN);
try
{
connection.Open();
insertCommand.ExecuteNonQuery();
string selectStatement =
"SELECT IDENT_CURRENT('Person') FROM Person";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
int personID = Convert.ToInt32(selectCommand.ExecuteScalar());
return(personID);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
// Inserts into the Visits table
public static int AddVisit(Visit visit)
{
SqlConnection connection = HealthCareUWGDBConnection.GetConnection();
string insertStatement =
"INSERT INTO Visits " +
"(ApptID, SysBP, DiaBP, BodyTemp, Pulse, Symptoms, NurseID, DiagnosesCode) " +
"VALUES (@ApptID, @SysBP, @DiaBP, @BodyTemp, @Pulse, @Symptoms, @NurseID, @DiagnosesCode)";
SqlCommand insertCommand = new SqlCommand(insertStatement, connection);
insertCommand.Parameters.AddWithValue("@ApptID", visit.ApptID);
insertCommand.Parameters.AddWithValue("@SysBP", visit.SysBP);
insertCommand.Parameters.AddWithValue("@DiaBP", visit.DiaBP);
insertCommand.Parameters.AddWithValue("@BodyTemp", visit.BodyTemp);
insertCommand.Parameters.AddWithValue("@Pulse", visit.Pulse);
insertCommand.Parameters.AddWithValue("@Symptoms", visit.Symptoms);
insertCommand.Parameters.AddWithValue("@NurseID", visit.NurseID);
insertCommand.Parameters.AddWithValue("@DiagnosesCode", visit.DiagnosesCode);
try
{
connection.Open();
insertCommand.ExecuteNonQuery();
string selectStatement =
"SELECT IDENT_CURRENT('Visits') FROM Visits";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
int personID = Convert.ToInt32(selectCommand.ExecuteScalar());
return(personID);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
//update the test result
public static bool UpdateTest(Test test)
{
SqlConnection connection = HealthCareUWGDBConnection.GetConnection();
string updateStatement =
"Update TestResults SET " +
"Result = @Result " +
"WHERE ApptID = @ApptID " +
"AND TestCode = @TestCode AND testDate = @TestDate ";
SqlCommand updateCommand = new SqlCommand(updateStatement, connection);
updateCommand.Parameters.AddWithValue("@ApptID", test.ApptID);
updateCommand.Parameters.AddWithValue("@TestCode", test.TestCode);
updateCommand.Parameters.AddWithValue("@TestDate", test.TestDate);
updateCommand.Parameters.AddWithValue("@Result", test.Result);
try
{
connection.Open();
int count = updateCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
/// <summary>
/// Gets the diagnoses based on the diagnoses code
/// </summary>
/// <param name="diagnosesCode"> diagnoses code to use for finding the string</param>
/// <returns>a string</returns>
public static string GetDiagnoses(string diagnosesCode)
{
string diagnoses = "";
SqlConnection connection = HealthCareUWGDBConnection.GetConnection();
string selectStatement =
"SELECT DiagnosesName " +
"FROM Diagnoses " +
"WHERE DiagnosesCode = @DiagnosesCode";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@DiagnosesCode", diagnosesCode);
try
{
connection.Open();
SqlDataReader reader =
selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (reader.Read())
{
diagnoses = reader["DiagnosesName"].ToString();
}
else
{
diagnoses = "";
}
reader.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(diagnoses);
}
/// <summary>
/// Gets the current patient info based on the patient id
/// </summary>
/// <param name="patientID">the id of the patient whose information is being retrieved</param>
/// <returns>patient object with requested patient information</returns>
public static Patient CurrentPatientInfo(string patientID)
{
Patient patient = new Patient();
SqlConnection connection = HealthCareUWGDBConnection.GetConnection();
string selectStatement =
"SELECT p.PersonID, pt.PatientID, p.FName, p.LName, p.FName +' ' + p.LName AS 'FullName', p.DOB, p.Street, p.City, p.State, p.ZipCode, p.PhoneNum, p.SSN " +
"FROM Person p JOIN Patients pt " +
"ON p.PersonID = pt.PersonID " +
"WHERE pt.PatientID = @PatientID";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@PatientID", patientID);
SqlDataReader reader = null;
try
{
connection.Open();
reader = selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (reader.Read())
{
patient.PersonID = (int)reader["PersonID"];
patient.PatientID = (int)reader["PatientID"];
patient.FullName = reader["FullName"].ToString();
patient.FName = reader["FName"].ToString();
patient.LName = reader["LName"].ToString();
patient.DOB = reader["DOB"].ToString();
patient.Street = reader["Street"].ToString();
patient.City = reader["City"].ToString();
patient.State = reader["State"].ToString();
patient.ZipCode = reader["ZipCode"].ToString();
patient.PhoneNum = reader["PhoneNum"].ToString();
patient.SSN = reader["SSN"].ToString();
}
else
{
patient = null;
}
}
catch (SqlException ex)
{
MessageBox.Show("it broke at sql and variable is " + patientID + ex.ToString());
throw ex;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
throw ex;
}
finally
{
if (connection != null)
{
connection.Close();
}
if (reader != null)
{
reader.Close();
}
}
return(patient);
}
