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