/// <summary>
/// Gets List of all patients
/// </summary>
/// <returns>List<Patient> of patients</returns>
public static List<Patient> GetAllPatients()
{
List<Patient> patientList = new List<Patient>();
const string selectStatement = "Select * from patients";
try
{
using (SqlConnection connection = NorthwindDbConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
Patient patient = new Patient();
patient.PatientId = (Int32)reader["patientID"];
patient.Address = reader["address"].ToString().Trim();
patient.City = reader["city"].ToString().Trim();
patient.Dob = (DateTime)reader["dob"];
patient.FirstName = reader["first_name"].ToString().Trim();
patient.Gender = reader["gender"].ToString().Trim();
patient.LastName = reader["last_name"].ToString().Trim();
patient.MiddleInitial = reader["middle_initial"].ToString().Trim();
patient.HomePhone = reader["home_phone"].ToString().Trim();
patient.WorkPhone = reader["work_phone"].ToString().Trim();
patient.Ssn = (Int32)reader["ssn"];
patient.State = reader["state"].ToString().Trim();
patient.Zip = (Int32)reader["zip"];
patient.Child = reader["child"].ToString().Trim();
patient.MotherId = (Int32)reader["motherID"];
patient.FatherId = (Int32)reader["fatherID"];
patientList.Add(patient);
}
}
}
}
}
catch (SqlException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
return patientList;
}
public NWViewPatient(int thePatientId)
{
InitializeComponent();
_controller = new NorthwindController();
try
{
this._patientId = thePatientId;
_thepatient = _controller.GetPatientsById(thePatientId);
}
catch (Exception exception)
{
MessageBox.Show(@"There was a problem loading this patient, please try again.");
this.Close();
}
}
/// <summary>
/// Add's a patient
/// </summary>
/// <param name="patient">Patient patient, patient to be added</param>
public static void AddPatients(Patient patient)
{
const string insertStatement = "INSERT into patients " +
" (address, city, dob, first_name, gender, last_name, middle_initial, home_phone, work_phone, ssn, state, zip) " +
" values(@address, @city, @dob, @first_name, @gender, @last_name, @middle_initial, @home_phone, @work_phone, @ssn, @state, @zip)";
try
{
using (SqlConnection connection = NorthwindDbConnection.GetConnection())
{
connection.Open();
using (SqlCommand insertCommand = new SqlCommand(insertStatement, connection))
{
insertCommand.Parameters.AddWithValue("@address", patient.Address);
insertCommand.Parameters.AddWithValue("@city", patient.City);
insertCommand.Parameters.AddWithValue("@dob", patient.Dob);
insertCommand.Parameters.AddWithValue("@first_name", patient.FirstName);
insertCommand.Parameters.AddWithValue("@gender", patient.Gender);
insertCommand.Parameters.AddWithValue("@last_name", patient.LastName);
insertCommand.Parameters.AddWithValue("@middle_initial", patient.MiddleInitial);
insertCommand.Parameters.AddWithValue("@home_phone", patient.HomePhone);
insertCommand.Parameters.AddWithValue("@work_phone", patient.WorkPhone);
insertCommand.Parameters.AddWithValue("@ssn", patient.Ssn);
insertCommand.Parameters.AddWithValue("@state", patient.State);
insertCommand.Parameters.AddWithValue("@zip", patient.Zip);
insertCommand.ExecuteNonQuery();
}
}
}
catch (SqlException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
}
public static int UpdatePatients(Patient oldpatient,Patient newPatient)
{
const string updateStatement = "Update patients set " +
" address = @address , city = @city, dob = @dob, first_name= @first_name, gender = @gender ," +
" last_name = @last_name, middle_initial =@middle_initial, home_phone = @home_phone, work_phone=@work_phone, ssn =@ssn, state =@state, zip=@zip " +
" " +
" where patientID = @patientID " +
" and " +
" address = @oldaddress and " +
" city = @oldcity and " +
" dob = @olddob and " +
" first_name = @oldfirst_name and " +
" gender = @oldgender and " +
" last_name = @oldlast_name and " +
" middle_initial = @oldmiddle_initial and " +
" home_phone = @oldhome_phone and " +
" work_phone = @oldwork_phone and " +
" ssn = @oldssn and " +
" state = @oldstate and " +
" zip = @oldzip";
try
{
using (SqlConnection connection = NorthwindDbConnection.GetConnection())
{
connection.Open();
using (SqlCommand updateCommand = new SqlCommand(updateStatement, connection))
{
updateCommand.Parameters.AddWithValue("@address", newPatient.Address);
updateCommand.Parameters.AddWithValue("@city", newPatient.City);
updateCommand.Parameters.AddWithValue("@dob", newPatient.Dob);
updateCommand.Parameters.AddWithValue("@first_name", newPatient.FirstName);
updateCommand.Parameters.AddWithValue("@gender", newPatient.Gender);
updateCommand.Parameters.AddWithValue("@last_name", newPatient.LastName);
updateCommand.Parameters.AddWithValue("@middle_initial", newPatient.MiddleInitial);
updateCommand.Parameters.AddWithValue("@home_phone", newPatient.HomePhone);
updateCommand.Parameters.AddWithValue("@work_phone", newPatient.WorkPhone);
updateCommand.Parameters.AddWithValue("@ssn", newPatient.Ssn);
updateCommand.Parameters.AddWithValue("@state", newPatient.State);
updateCommand.Parameters.AddWithValue("@zip", newPatient.Zip);
updateCommand.Parameters.AddWithValue("@patientID", newPatient.PatientId);
updateCommand.Parameters.AddWithValue("@oldpatientID", oldpatient.PatientId);
updateCommand.Parameters.AddWithValue("@oldaddress", oldpatient.Address);
updateCommand.Parameters.AddWithValue("@oldcity", oldpatient.City);
updateCommand.Parameters.AddWithValue("@olddob", oldpatient.Dob);
updateCommand.Parameters.AddWithValue("@oldfirst_name", oldpatient.FirstName);
updateCommand.Parameters.AddWithValue("@oldgender", oldpatient.Gender);
updateCommand.Parameters.AddWithValue("@oldlast_name", oldpatient.LastName);
updateCommand.Parameters.AddWithValue("@oldmiddle_initial", oldpatient.MiddleInitial);
updateCommand.Parameters.AddWithValue("@oldhome_phone", oldpatient.HomePhone);
updateCommand.Parameters.AddWithValue("@oldwork_phone", oldpatient.WorkPhone);
updateCommand.Parameters.AddWithValue("@oldssn", oldpatient.Ssn);
updateCommand.Parameters.AddWithValue("@oldstate", oldpatient.State);
updateCommand.Parameters.AddWithValue("@oldzip", oldpatient.Zip);
//NorthwindDbConnection.ViewString(updateCommand);
return updateCommand.ExecuteNonQuery();
}
}
}
catch (SqlException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Returns a list of patients that match with first name and last name
/// </summary>
/// <param name="firstName"></param>
/// <param name="lastName"></param>
/// <param name="dateOfBirth"></param>
/// <returns></returns>
public static List<Patient> searchPatients(string lastName, string firstName, string dob)
{
List<Patient> patients = new List<Patient>();
string selectStatement =
"SELECT * " +
"FROM patients " +
"WHERE (last_name = @lastName AND first_name = @firstName) OR (dob = @dob) OR (dob = @dob AND last_name = @lastName)";
try
{
using (SqlConnection connection = NorthwindDbConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
selectCommand.Parameters.AddWithValue("@lastName", lastName);
selectCommand.Parameters.AddWithValue("@firstName", firstName);
selectCommand.Parameters.AddWithValue("@dob", dob);
using (SqlDataReader dataReader = selectCommand.ExecuteReader())
{
int patientIDOrdinal = dataReader.GetOrdinal("patientID");
int ssnOrdinal = dataReader.GetOrdinal("ssn");
int last_nameOrdinal = dataReader.GetOrdinal("last_name");
int middle_initialOrdinal = dataReader.GetOrdinal("middle_initial");
int first_nameOrdinal = dataReader.GetOrdinal("first_name");
int dobOrdinal = dataReader.GetOrdinal("dob");
int genderOrdinal = dataReader.GetOrdinal("gender");
int addressOrdinal = dataReader.GetOrdinal("address");
int cityOrdinal = dataReader.GetOrdinal("city");
int stateOrdinal = dataReader.GetOrdinal("state");
int zipOrdinal = dataReader.GetOrdinal("zip");
int home_phoneOrdinal = dataReader.GetOrdinal("home_phone");
int work_phoneOrdinal = dataReader.GetOrdinal("work_phone");
int childOrdinal = dataReader.GetOrdinal("child");
int motherIDOrdinal = dataReader.GetOrdinal("motherID");
int fatherIDOrdinal = dataReader.GetOrdinal("fatherID");
while (dataReader.Read())
{
Patient patient = new Patient();
patient.PatientId = dataReader.GetInt32(patientIDOrdinal);
patient.Ssn = dataReader.GetInt32(ssnOrdinal);
patient.LastName = dataReader.GetString(last_nameOrdinal);
if (!dataReader.IsDBNull(middle_initialOrdinal))
patient.MiddleInitial = dataReader.GetString(middle_initialOrdinal);
else
patient.MiddleInitial = string.Empty;
patient.FirstName = dataReader.GetString(first_nameOrdinal);
patient.Dob = dataReader.GetDateTime(dobOrdinal);
patient.Gender = dataReader.GetString(genderOrdinal);
patient.Address = dataReader.GetString(addressOrdinal);
patient.City = dataReader.GetString(cityOrdinal);
patient.State = dataReader.GetString(stateOrdinal);
patient.Zip = dataReader.GetInt32(zipOrdinal);
patient.HomePhone = dataReader.GetString(home_phoneOrdinal);
if (!dataReader.IsDBNull(work_phoneOrdinal))
patient.WorkPhone = dataReader.GetString(work_phoneOrdinal);
else
patient.WorkPhone = string.Empty;
if (!dataReader.IsDBNull(childOrdinal))
patient.Child = dataReader.GetString(childOrdinal);
else
patient.Child = string.Empty;
if (!dataReader.IsDBNull(motherIDOrdinal))
patient.MotherId = dataReader.GetInt32(motherIDOrdinal);
else
patient.MotherId = -1;
if (!dataReader.IsDBNull(fatherIDOrdinal))
patient.FatherId = dataReader.GetInt32(fatherIDOrdinal);
else
patient.FatherId = -1;
patients.Add(patient);
}
}
}
}
}
catch (SqlException sqlException)
{
throw sqlException;
}
catch (Exception exception)
{
throw exception;
}
return patients;
}
private void SubmitButton_Click(object sender, EventArgs e)
{
Patient newPatient = new Patient();
int ssn, zip;
if ((femaleRadioButton.Checked == false) & (maleRadioButton.Checked == false))
{
MessageBox.Show(
@"Please select gender.");
}
else
{
long homePhone;
String gender;
if ((femaleRadioButton.Checked == true))
{
gender = "F";
}
else
{
gender = "M";
}
if ((int.TryParse(ssnTextBox.Text, out ssn)) & (ssnTextBox.Text.Length == 9))
{
if ((int.TryParse(zipTextBox.Text, out zip)) & (zipTextBox.Text.Length == 5))
{
if ((long.TryParse(homePhoneTextBox.Text, out homePhone)) & (homePhoneTextBox.Text.Length >= 10))
{
if ((firstNameTextBox.Text != "") & (lastNameTextBox.Text != "") & (ssn != 0) & (zip != 0) &
(homePhoneTextBox.Text != "") &
(addressTextBox.Text != "") & (cityTextBox.Text != "") & (StateComboBox.Text != ""))
{
//Check Details
newPatient.FirstName = firstNameTextBox.Text;
newPatient.MiddleInitial = middleInitialTextBox.Text;
newPatient.LastName = lastNameTextBox.Text;
newPatient.Gender = gender;
newPatient.Ssn = ssn;
newPatient.Zip = zip;
newPatient.HomePhone = homePhoneTextBox.Text;
newPatient.WorkPhone = workPhoneTextBox.Text;
newPatient.Dob = dateTimePicker.Value.Date;
newPatient.Address = addressTextBox.Text;
newPatient.City = cityTextBox.Text;
newPatient.State = StateComboBox.Text;
try
{
_controller.AddPatients(newPatient);
MessageBox.Show(@"The patient has been added.");
this.Close();
}
catch (Exception exception)
{
MessageBox.Show(
@"There was a problem adding the Patient into the Database, please contact the admin with this message." +
exception);
}
}
else
{
MessageBox.Show(
@"Phone number needs to be numbers only, remove any non digit characters like -'s or ()'s.");
}
}
else
{
MessageBox.Show(
@"Please check all the required fields and make sure you entered the proper information.");
}
}
else
{
MessageBox.Show(
@"Please enter Zip as a number and that you have entered the right amount of numbers. Please check.");
}
}
else
{
MessageBox.Show(
@"Please enter Social as a number with no dashes, or you may not have entered the right amount of numbers. Please check.");
}
}
}
internal int UpdatePatients(Patient oldpatient, Patient newPatient)
{
return PatientDal.UpdatePatients(oldpatient, newPatient);
}
internal void AddPatients(Patient patient)
{
PatientDal.AddPatients(patient);
}
