/// <summary>
/// Adds a Person to the database.
/// </summary>
/// <param name="person">The person to add</param>
/// <returns>The ID of the Person IFF successful</returns>
public static int AddPerson(Person person)
{
int personID = -1;
try
{
using (SqlConnection connection = HealthCareDBConnection.GetConnection())
{
string insertStatement =
"INSERT Person " +
"(ssn, lastName, middleInitial, firstName, dateOfBirth, gender, address, " +
"city, state, zip, phone) " +
"VALUES (@Ssn, @LastName, @MiddleInitial, @FirstName, @DateOfBirth, @Gender, " +
"@Address, @City, @State, @Zip, @Phone)";
using (SqlCommand insertCommand = new SqlCommand(insertStatement, connection))
{
insertCommand.Parameters.AddWithValue("@Ssn", person.Ssn);
insertCommand.Parameters.AddWithValue("@LastName", person.LastName);
if (person.MiddleInitial.Equals(null))
{
insertCommand.Parameters.AddWithValue("@MiddleInitial", DBNull.Value);
}
else
{
insertCommand.Parameters.AddWithValue("@MiddleInitial", person.MiddleInitial);
}
insertCommand.Parameters.AddWithValue("@FirstName", person.FirstName);
insertCommand.Parameters.AddWithValue("@DateOfBirth", person.DateOfBirth.Date);
insertCommand.Parameters.AddWithValue("@Gender", person.Gender);
insertCommand.Parameters.AddWithValue("@Address", person.Address);
insertCommand.Parameters.AddWithValue("@City", person.City);
insertCommand.Parameters.AddWithValue("@State", person.State);
insertCommand.Parameters.AddWithValue("@Zip", person.Zip);
insertCommand.Parameters.AddWithValue("@Phone", person.Phone);
connection.Open();
insertCommand.ExecuteNonQuery();
string selectStatement =
"SELECT IDENT_CURRENT('Person') FROM Person";
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
personID = Convert.ToInt32(selectCommand.ExecuteScalar());
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK);
}
return personID;
}
/// <summary>
/// Updates a patient.
/// </summary>
/// <param name="oldPatient">The original patient</param>
/// <param name="newPatient">The new patient with new data</param>
/// <returns>True IFF the old patient was updated</returns>
public static bool UpdatePatient(Person oldPatient, Person newPatient)
{
SqlConnection connection = HealthCareDBConnection.GetConnection();
string updateStatement = "UPDATE Person "
+ "SET Ssn = @NewSsn, "
+ "LastName = @NewLastName, "
+ "MiddleInitial = @NewMiddleInitial, "
+ "FirstName = @NewFirstName, "
+ "DateOfBirth = @NewDateOfBirth, "
+ "Gender = @NewGender, "
+ "Address = @NewAddress, "
+ "City = @NewCity, "
+ "State = @NewState, "
+ "Zip = @NewZip, "
+ "Phone = @NewPhone "
+ "WHERE PersonId = @OldPersonId "
+ "AND Ssn = @OldSsn "
+ "AND LastName = @OldLastName "
+ "AND (MiddleInitial = @OldMiddleInitial OR (MiddleInitial IS NULL AND @OldMiddleInitial IS NULL)) "
+ "AND FirstName = @OldFirstName "
+ "AND DateOfBirth = @OldDateOfBirth "
+ "AND Gender = @OldGender "
+ "AND Address = @OldAddress "
+ "AND City = @OldCity "
+ "AND State = @OldState "
+ "AND Zip = @OldZip "
+ "AND Phone = @OldPhone";
SqlCommand updateCommand = new SqlCommand(updateStatement, connection);
updateCommand.Parameters.AddWithValue("@NewSsn", newPatient.Ssn);
updateCommand.Parameters.AddWithValue("@NewLastName", newPatient.LastName);
if (newPatient.MiddleInitial.Equals(""))
updateCommand.Parameters.AddWithValue("@OldMiddleInitial", DBNull.Value);
else updateCommand.Parameters.AddWithValue("@NewMiddleInitial", newPatient.MiddleInitial);
updateCommand.Parameters.AddWithValue("@NewFirstName", newPatient.FirstName);
updateCommand.Parameters.AddWithValue("@NewDateOfBirth", newPatient.DateOfBirth);
updateCommand.Parameters.AddWithValue("@NewGender", newPatient.Gender);
updateCommand.Parameters.AddWithValue("@NewAddress", newPatient.Address);
updateCommand.Parameters.AddWithValue("@NewCity", newPatient.City);
updateCommand.Parameters.AddWithValue("@NewState", newPatient.State);
updateCommand.Parameters.AddWithValue("@NewZip", newPatient.Zip);
updateCommand.Parameters.AddWithValue("@NewPhone", newPatient.Phone);
try
{
connection.Open();
int count = updateCommand.ExecuteNonQuery();
if (count > 0)
return true;
else
return false;
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
/// <summary>
/// Edit a person record and fail if the record has been updated since the last access.
/// </summary>
/// <param name="oldPerson">The old person object to update</param>
/// <param name="newPerson">The new person with new data</param>
/// <returns>True IFF the person is updated</returns>
public static bool UpdatePerson(Person oldPerson, Person newPerson)
{
bool success = false;
int i = 0;
try
{
using (SqlConnection connection = HealthCareDBConnection.GetConnection())
{
string updateStatement =
"UPDATE Person SET " +
"ssn = @NewSsn, " +
"lastName = @NewLastName, " +
"middleInitial = @NewMiddleInitial, " +
"firstName = @NewFirstName, " +
"dateOfBirth = @NewDateOfBirth, " +
"gender = @NewGender, " +
"address = @NewAddress, " +
"city = @NewCity, " +
"state = @NewState, " +
"phone = @NewPhone " +
"WHERE personID = @OldPersonID " +
"AND ssn = @OldSsn " +
"AND lastName = @OldLastName " +
"AND (middleInitial = @OldMiddleInitial " +
"OR (middleInitial IS NULL AND @OldMiddleInitial IS NULL)) " +
"AND firstName = @OldFirstName " +
"AND cast(dateOfBirth as date) = @OldDateOfBirth " +
"AND gender = @OldGender " +
"AND address = @OldAddress " +
"AND city = @OldCity " +
"AND state = @OldState " +
"AND zip = @OldZip " +
"AND phone = @OldPhone";
using (SqlCommand updateCommand = new SqlCommand(updateStatement, connection))
{
if (newPerson.Ssn.Length == 9)
updateCommand.Parameters.AddWithValue("@NewSsn", newPerson.Ssn);
else
return false;
updateCommand.Parameters.AddWithValue("@NewLastName", newPerson.LastName);
if (newPerson.MiddleInitial.Equals(null))
updateCommand.Parameters.AddWithValue("@NewMiddleInitial", DBNull.Value);
else
updateCommand.Parameters.AddWithValue("@NewMiddleInitial", newPerson.MiddleInitial);
updateCommand.Parameters.AddWithValue("@NewFirstName", newPerson.FirstName);
updateCommand.Parameters.AddWithValue("@NewDateOfBirth", newPerson.DateOfBirth);
updateCommand.Parameters.AddWithValue("@NewGender", newPerson.Gender);
updateCommand.Parameters.AddWithValue("@NewAddress", newPerson.Address);
updateCommand.Parameters.AddWithValue("@NewCity", newPerson.City);
updateCommand.Parameters.AddWithValue("@NewState", newPerson.State);
if (newPerson.Zip.Length == 5 && (int.TryParse(newPerson.Zip, out i) == true))
updateCommand.Parameters.AddWithValue("@NewZip", newPerson.Zip);
else
return false;
updateCommand.Parameters.AddWithValue("@NewPhone", newPerson.Phone);
updateCommand.Parameters.AddWithValue("@OldPersonID", oldPerson.PersonId);
updateCommand.Parameters.AddWithValue("@OldSsn", oldPerson.Ssn);
updateCommand.Parameters.AddWithValue("@OldLastName", oldPerson.LastName);
if (oldPerson.MiddleInitial.Equals(null))
updateCommand.Parameters.AddWithValue("@OldMiddleInitial", DBNull.Value);
else
updateCommand.Parameters.AddWithValue("@OldMiddleInitial", oldPerson.MiddleInitial);
updateCommand.Parameters.AddWithValue("@OldFirstName", oldPerson.FirstName);
updateCommand.Parameters.AddWithValue("@OldDateOfBirth", oldPerson.DateOfBirth.Date);
updateCommand.Parameters.AddWithValue("@OldGender", oldPerson.Gender);
updateCommand.Parameters.AddWithValue("@OldAddress", oldPerson.Address);
updateCommand.Parameters.AddWithValue("@OldCity", oldPerson.City);
updateCommand.Parameters.AddWithValue("@OldState", oldPerson.State);
updateCommand.Parameters.AddWithValue("@OldZip", oldPerson.Zip);
updateCommand.Parameters.AddWithValue("@OldPhone", oldPerson.Phone);
connection.Open();
int count = updateCommand.ExecuteNonQuery();
if (count > 0)
success = true;
else
success = false;
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK);
}
return success;
}
internal static List<AppointmentInfo> GetAppointmentInfoWithoutDiagnosis()
{
List<AppointmentInfo> undiagnosedAppointmentList = new List<AppointmentInfo>();
string selectStatement = "select * from Appointment join Person on Appointment.personID = Person.personID where diagnosis is null;";
try
{
using (SqlConnection connection = HealthCareDBConnection.GetConnection())
{
try
{
connection.Open();
}
catch (SqlException ex)
{
throw ex;
}
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
AppointmentInfo undiagnosedAppointment = new AppointmentInfo();
Person undiagnosedPerson = new Person();
undiagnosedAppointment.AppointmentId = (int)reader["appointmentID"];
undiagnosedAppointment.Symptom = reader["symptom"].ToString();
undiagnosedAppointment.AppointmentDate = (DateTime)reader["appointmentDate"];
undiagnosedPerson.PersonId = (int)reader["personID"];
undiagnosedPerson.Ssn = reader["ssn"].ToString();
undiagnosedPerson.FirstName = reader["firstName"].ToString();
undiagnosedPerson.MiddleInitial = !DBNull.Value.Equals(reader["middleInitial"]) ? Convert.ToChar(reader["middleInitial"].ToString()) : Convert.ToChar(" ");
undiagnosedPerson.LastName = reader["lastName"].ToString();
undiagnosedPerson.DateOfBirth = (DateTime)reader["dateOfBirth"];
undiagnosedPerson.Gender = Convert.ToChar(reader["gender"].ToString());
undiagnosedAppointment.Person = undiagnosedPerson;
undiagnosedAppointmentList.Add(undiagnosedAppointment);
}
connection.Close();
}
}
}
catch (SqlException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
return undiagnosedAppointmentList;
}