Exemplo n.º 1
0
        /// <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;
        }
Exemplo n.º 2
0
        /// <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();
            }
        }
Exemplo n.º 3
0
        /// <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;
        }
Exemplo n.º 4
0
        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;

        }