private string Get_Department(string department_name)
        {
            if (department_name.Equals(""))
            {
                return("" + -1);
            }
            //MessageBox.Show(department_name);
            string query = "select department_id from department where department_name=@Dept;";
            string stringDep_id;

            try
            {
                stringDep_id = Database.Query(query, ("@Dept", department_name)).Rows[0].ItemArray.Select(x => x.ToString().Trim()).ToArray()[0];
            }
            catch
            {
                //Add in ability to add specified department
                if (MessageBox.Show("Could not find the specified department with name: " + department_name + "\nWould you like to add it?", "Warning!", MessageBoxButton.YesNo) == MessageBoxResult.Yes)
                {
                    query = "select department_id from department;";
                    int next_id = FindFirstNonIndex(query);
                    query = "insert into department (department_id, department_name) values(@next, @DeptName);";
                    Database.NonQuery(query, ("@next", next_id), ("@DeptName", department_name));
                    MessageBox.Show("Added Department with name: " + department_name);
                    stringDep_id = "" + next_id;
                }
                else
                {
                    stringDep_id = "-1";
                }
            }

            //MessageBox.Show(stringDep_id);
            return(stringDep_id);
        }
        private void Btn_emp_add_Click(object sender, RoutedEventArgs e)
        {
            string query;

            if (emp_field_id.Text.Equals(""))
            {
                if (emp_field_date.Text.Equals("") || emp_field_department.Text.Equals("") || emp_field_email.Text.Equals("") || emp_field_fname.Text.Equals("") || emp_field_lname.Text.Equals("") || emp_field_phone.Text.Equals("") || emp_field_ssn.Password.Equals("") || emp_field_type.Text.Equals(""))
                {
                    MessageBox.Show("Please fill out all fields except for the employee id field");
                }
                else
                {
                    string get_dep_id = Get_Department(emp_field_department.Text);
                    query = "select employee_id from employee;";
                    int next_emp_id = FindFirstNonIndex(query);
                    query = "insert into employee (employee_id, department_id, first_name, last_name, email, phone_number, employment_type, birthday, ssn) VALUES (" + next_emp_id + ", " + get_dep_id + ", '" + emp_field_fname.Text + "', '" + emp_field_lname.Text + "', '" + emp_field_email.Text + "', '" + emp_field_phone.Text + "', '" + emp_field_type.Text + "', '" + emp_field_date.Text + "', '" + emp_field_ssn.Password + "');";
                    Database.NonQuery(query);
                    if (!emp_combo_animal.Text.Equals("") && !emp_combo_animal.Text.Equals("None"))
                    {
                        query = "insert into handler (employee_id, animal_id) values (" + next_emp_id + ", " + Get_AnimalId(emp_combo_animal.Text) + ");";
                        Database.NonQuery(query);
                    }
                    MessageBox.Show("Added " + emp_field_fname.Text + " to the database.");
                }
            }
            else
            {
                MessageBox.Show("You are not allowed to specify the employee id.");
            }
        }
        private void Btn_emp_remove_Click(object sender, RoutedEventArgs e)
        {
            string query;

            if (emp_field_id.Text.Equals(""))
            {
                MessageBox.Show("Please specifiy the employee to update with their id");
            }
            else
            {
                try
                {
                    query = "select * from employee where employee_id=@EID;";
                    var employee_exists = Database.Query(query).Rows[0].ItemArray.Select(x => x.ToString().Trim()).ToArray()[0];
                    if (MessageBox.Show("Are you sure you want to remove employee with id: " + emp_field_id.Text + "?", "Warning!", MessageBoxButton.YesNo) == MessageBoxResult.Yes)
                    {
                        query = "delete from employee where employee_id=@EID";
                        Database.NonQuery(query, ("@EID", emp_field_id.Text));
                        query = "delete from handler where employee_id=@EID";
                        Database.NonQuery(query, ("@EID", emp_field_id.Text));
                    }
                }
                catch
                {
                    MessageBox.Show("Employee with id: " + emp_field_id + " does not exists.");
                }
            }
        }
Exemple #4
0
        private void Btn_species_add_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                //First, try to parse all of the entries into usable values.
                //If any can't be parsed or are empty, we stop.
                int    idVal     = int.Parse(txt_species_id.Text);
                string specName  = txt_species_name.Text;
                string specClass = txt_species_class.Text;

                if (specName != "" && specClass != "")
                {
                    //Make the base of the query
                    string query = "INSERT INTO SPECIES VALUES(@ID, @SPENAME, @CLASSNAME);";

                    //Then run the query while replacing the placeholders with our values.
                    bool valid = Database.NonQuery(query, ("@ID", idVal), ("@SPENAME", specName), ("@CLASSNAME", specClass));

                    if (valid)
                    {
                        MessageBox.Show("Added a Species.");
                    }
                }
                else
                {
                    MessageBox.Show("One or more boxes were left blank");
                }
            }
            catch
            {
                MessageBox.Show("One or more boxes are invalid because they are not numbers.");
            }
        }
Exemple #5
0
        private void Btn_animals_delete_Click(object sender, RoutedEventArgs e)
        {
            //Can only delete one entry at a time. For safety reasons. So you don't accidentally delete tons of records by leaving something filled out.
            //Use expert mode to delete more by different parameters.
            if (MessageBox.Show("Are you sure you want to delete this record?", "Warning!", MessageBoxButton.YesNo, MessageBoxImage.Warning) == MessageBoxResult.Yes)
            {
                try
                {
                    //First try to parse the id field
                    int idVal = int.Parse(txt_animal_id.Text);

                    //Create the query that delete the animal with the
                    //specified field
                    string query = "DELETE FROM ANIMAL WHERE animal_id=(@ID);";

                    //Check if the animal is listed in the handlers table or
                    //the animal_adoption table
                    bool notInHandler = Database.Query("SELECT * FROM HANDLER WHERE animal_id=(@ID);", ("@ID", idVal)).Rows.Count == 0;
                    bool notInAdopt   = Database.Query("SELECT * FROM ANIMAL_ADOPTION WHERE animal_id=(@ID);", ("@ID", idVal)).Rows.Count == 0;


                    //If it is not in those tables, it is safe to delete
                    if (notInAdopt && notInAdopt)
                    {
                        bool validID = Database.NonQuery(query, ("@ID", idVal));

                        //If the id wasn't valid, report that to the user
                        if (validID == false)
                        {
                            MessageBox.Show("Could not delete because no animal has the ID value");
                        }
                        else
                        {
                            MessageBox.Show("Deleted the animal.");
                        }
                    }

                    //Report whether the animal is listed in either the animal_adoption or
                    //Handlers table.
                    if (notInAdopt == false)
                    {
                        MessageBox.Show("This animal is still listed in Animal Adoption and can't be deleted");
                    }

                    if (notInHandler == false)
                    {
                        MessageBox.Show("This animal is still listed in Handlers and can't be deleted");
                    }
                }
                catch
                {
                    MessageBox.Show("The Animal Id is not a number.");
                }
            }
        }
Exemple #6
0
        private void Btn_animals_update_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                //First try to parse all of the fields
                int idVal    = int.Parse(txt_animal_id.Text);
                int habIdVal = int.Parse(txt_animal_habitat_id.Text);
                int speIdVal = int.Parse(txt_animal_species_id.Text);

                string animalName = txt_animal_name.Text;
                string animalDate = date_animal_birthday.Text;

                int wei    = int.Parse(txt_animal_weight.Text);
                int dietID = int.Parse(txt_animal_diet_id.Text);

                if (animalName != "" && animalDate != "")
                {
                    //Create the query to update an animal that has the
                    //specified id value leaving placeholders.
                    string query = "UPDATE ANIMAL " +
                                   "SET habitat_id=(@HABID), " +
                                   "species_id=(@SPEID), " +
                                   "animal_name=(@NAME), " +
                                   "birthday=(@BIRTHDAY), " +
                                   "weight=(@WEIGHT), " +
                                   "diet_id=(@DIETID), " +
                                   "WHERE animal_id=(@ID);";

                    //Replace the placeholders with the users values and
                    //then run the query.
                    bool valid = Database.NonQuery(query, ("@ID", idVal), ("@HABID", habIdVal), ("@SPEID", speIdVal),
                                                   ("@NAME", animalName), ("@BIRTHDAY", animalDate), ("@WEIGHT", wei),
                                                   ("@DIETID", dietID));

                    if (valid)
                    {
                        MessageBox.Show("Updated an Animal.");
                    }
                }
                else
                {
                    MessageBox.Show("One or more boxes were left blank.");
                }
            }
            catch
            {
                MessageBox.Show("One or more boxes are invalid because they are not numbers");
            }
        }
        private void Btn_diet_delete_Click(object sender, RoutedEventArgs e)
        {
            //Can only delete one entry at a time. For safety reasons. So you don't accidentally delete tons of records by leaving something filled out.
            //Use expert mode to delete more by different parameters.
            if (MessageBox.Show("Are you sure you want to delete this record?", "Warning!", MessageBoxButton.YesNo, MessageBoxImage.Warning) == MessageBoxResult.Yes)
            {
                try
                {
                    //First, parse the id value
                    int idVal = int.Parse(txt_diet_id.Text);

                    //Create the delete query with a placeholder for the id.
                    string query = "DELETE FROM DIET WHERE diet_id=(@ID);";

                    //Check if the diet is used by an animal
                    bool notInAnimal = Database.Query("SELECT * FROM ANIMAL WHERE diet_id=(@ID);", ("@ID", idVal)).Rows.Count == 0;

                    //Delete the diet if it isn't used by anything.
                    if (notInAnimal)
                    {
                        //run the query after replacing the placeholder with the
                        //id value
                        bool validID = Database.NonQuery(query, ("@ID", idVal));

                        //If the query did not work, it is due to an invalid id value.
                        //Report this to the user.
                        if (validID == false)
                        {
                            MessageBox.Show("Could not delete because no diet has the ID value");
                        }
                        else
                        {
                            MessageBox.Show("Deleted a Diet.");
                        }
                    }

                    //If the diet is used by an animal, do not delete it.
                    if (notInAnimal == false)
                    {
                        MessageBox.Show("This Diet is still listed in Animal and can't be deleted");
                    }
                }
                catch
                {
                    MessageBox.Show("The Diet Id is not a number or is invalid.");
                }
            }
        }
 private void Submit(string query)
 {
     // If the query starts with select, we want to perform a normal query
     if (query.StartsWith("select", StringComparison.OrdinalIgnoreCase))
     {
         datagrid_expmode.ItemsSource = Database.Query(query)?.DefaultView;
     }
     else
     {
         // otherwise we want to perform what is known as a nonquery. This does not return any rows, only the rows affected
         if (Database.NonQuery(query))
         {
             MessageBox.Show("Query executed successfully!");
         }
     }
 }
Exemple #9
0
        private void Btn_habitat_delete_Click(object sender, RoutedEventArgs e)
        {
            if (MessageBox.Show("Are you sure you want to delete this record?", "Warning!", MessageBoxButton.YesNo, MessageBoxImage.Warning) == MessageBoxResult.Yes)
            {
                try
                {
                    //Parse the id of the habitat from the text boxes
                    int idVal = int.Parse(txt_habitat_id.Text);

                    //Next, build the query to delete the habitat at the
                    //specified id value.
                    string query = "DELETE FROM HABITAT WHERE habitat_id=(@ID);";

                    //Determine if the habitat is used by an animal
                    bool notInAnimal = Database.Query("SELECT * FROM ANIMAL WHERE habitat_id=(@ID);", ("@ID", idVal)).Rows.Count == 0;

                    //If the habitat is used by an animal, do not delete it
                    //and report that to the user
                    if (notInAnimal)
                    {
                        //Replace the placeholder, and run the query
                        bool validID = Database.NonQuery(query, ("@ID", idVal));

                        //If the query fails, the id was invalid. Report that to the
                        //user.
                        if (validID == false)
                        {
                            MessageBox.Show("Could not delete because no habitat has the ID value");
                        }
                        else
                        {
                            MessageBox.Show("Deleted a Habitat.");
                        }
                    }

                    //Report to the user if the habitat is still used by an animal.
                    if (notInAnimal == false)
                    {
                        MessageBox.Show("This Habitat is still listed in Animal and can't be deleted");
                    }
                }
                catch
                {
                    MessageBox.Show("The Habitat Id is not a number.");
                }
            }
        }
        private void Btn_diet_update_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                //First, try to parse the values entered by the user.
                int    idVal         = int.Parse(txt_diet_id.Text);
                string dType         = txt_diet_type.Text;
                string dRestrictions = txt_diet_restrictions.Text;
                string dPrimary      = txt_diet_primary_food.Text;
                string dSecondary    = txt_diet_secondary_food.Text;
                string dTreats       = txt_diet_treats.Text;

                //If any of boxes are blank, do not insert and inform the user
                if (dType != "" && dRestrictions != "" && dPrimary != "" &&
                    dSecondary != "" && dTreats != "")
                {
                    //Create the update query with placeholders
                    string query = "UPDATE DIET " +
                                   "SET dietary_type=(@DTYPE), " +
                                   "restrictions=(@RES), " +
                                   "primary_food=(@PRIM), " +
                                   "secondary_food=(@SECO), " +
                                   "treats=(@TREATS) " +
                                   "WHERE diet_id=(@ID);";

                    //Replace the placeholders with the user's values and
                    //run the query.
                    bool valid = Database.NonQuery(query, ("@ID", idVal), ("@DTYPE", dType), ("@RES", dRestrictions),
                                                   ("@PRIM", dPrimary), ("@SECO", dSecondary), ("@TREATS", dTreats));

                    if (valid)
                    {
                        MessageBox.Show("Updated a Diet.");
                    }
                }
                else
                {
                    MessageBox.Show("One or more boxes are blank.");
                }
            }
            catch
            {
                MessageBox.Show("One or more boxes are invalid because they are not numbers");
            }
        }
        private void Btn_emp_update_Click(object sender, RoutedEventArgs e)
        {
            string query;

            if (emp_field_id.Text.Equals(""))
            {
                MessageBox.Show("Please specifiy the employee to update with their id");
            }
            else
            {
                string get_dep_id = Get_Department(emp_field_department.Text);
                query = "select * from employee where employee_id=@EID;";
                try
                {
                    var testEmployee = Database.Query(query, ("@EID", emp_field_id.Text)).Rows[0].ItemArray.Select(x => x.ToString().Trim()).ToArray()[0];
                    query = "update employee set employee_id=" + emp_field_id.Text + ((!get_dep_id.Equals("-1")) ? ", department_id=" + get_dep_id : "") + ((!emp_field_ssn.Password.Equals("")) ? ", ssn=" + emp_field_ssn.Password : "") + ((!emp_field_email.Text.Equals("")) ? ", email='" + emp_field_email.Text + "'" : "") + ((!emp_field_lname.Text.Equals("")) ? ", last_name='" + emp_field_lname.Text + "'" : "") + ((!emp_field_fname.Text.Equals("")) ? ", first_name='" + emp_field_fname.Text + "'" : "") + ((!emp_field_phone.Text.Equals("")) ? ", phone_number='" + emp_field_phone.Text + "'" : "") + ((!emp_field_type.Text.Equals("")) ? ", employment_type='" + emp_field_type.Text + "'" : "") + ((!emp_field_date.Text.Equals("")) ? ", birthday='" + emp_field_date.Text + "'" : "") + " where employee_id=" + emp_field_id.Text + ";";
                    Database.NonQuery(query);
                    if (!emp_combo_animal.Text.Equals(""))
                    {
                        if (emp_combo_animal.Text.Equals("None"))
                        {
                            query = "delete from handler where employee_id=" + emp_field_id.Text;
                            Database.NonQuery(query);
                        }
                        else
                        {
                            if (isHandlerOf("", emp_field_id.Text))
                            {
                                query = "delete from handler where employee_id=@EID;";
                                Database.NonQuery(query, ("@EID", emp_field_id.Text));
                            }
                            query = "insert into handler (employee_id, animal_id) values (@EID, @Animal);";
                            Database.NonQuery(query, ("@EID", emp_field_id.Text), ("@Animal", Get_AnimalId(emp_combo_animal.Text)));
                        }
                    }
                    MessageBox.Show("Updated Employee with id: " + emp_field_id.Text);
                }
                catch
                {
                    //Add in ability to add specified department
                    MessageBox.Show("Could not find the specified employee with id: " + emp_field_id.Text);
                }
            }
        }
Exemple #12
0
        private void Btn_species_delete_Click(object sender, RoutedEventArgs e)
        {
            if (MessageBox.Show("Are you sure you want to delete this record?", "Warning!", MessageBoxButton.YesNo, MessageBoxImage.Warning) == MessageBoxResult.Yes)
            {
                try
                {
                    //First, parse the id if you can.
                    int idVal = int.Parse(txt_species_id.Text);

                    //Create the query to delete that species
                    string query = "DELETE FROM SPECIES WHERE species_id=(@ID);";

                    //Lastly, check if the species is used. If it is,
                    //Don't delete this species.
                    bool notInAnimal = Database.Query("SELECT * FROM ANIMAL WHERE species_id=(@ID);", ("@ID", idVal)).Rows.Count == 0;

                    if (notInAnimal)
                    {
                        //If the id does not exist, let the user know.
                        bool validID = Database.NonQuery(query, ("@ID", idVal));

                        if (validID == false)
                        {
                            MessageBox.Show("Could not delete because no species has the ID value");
                        }
                        else
                        {
                            MessageBox.Show("Deleted a Species");
                        }
                    }

                    //Report to the user that the habitat is still used by
                    //animals in the zoo.
                    if (notInAnimal == false)
                    {
                        MessageBox.Show("This Habitat is still listed in Animal and can't be deleted");
                    }
                }
                catch
                {
                    MessageBox.Show("The species Id is not a number.");
                }
            }
        }
Exemple #13
0
        private void Btn_animals_add_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                //First, try to parse all of the fields
                int    idVal      = int.Parse(txt_animal_id.Text);
                int    habIdVal   = int.Parse(txt_animal_habitat_id.Text);
                int    speIdVal   = int.Parse(txt_animal_species_id.Text);
                string animalName = txt_animal_name.Text;
                string animalDate = date_animal_birthday.Text;
                int    wei        = int.Parse(txt_animal_weight.Text);
                int    dietID     = int.Parse(txt_animal_diet_id.Text);

                //Prevent adding into the table if the animal has no name
                //or birthday.
                if (animalName != "" && animalDate != "")
                {
                    //Create the query with placeholders that will insert into the table
                    string query = "INSERT INTO ANIMAL VALUES(@ID, @HABID, @SPEID, @NAME, @BIRTHDAY, @WEIGHT, @DIETID);";

                    //Replace the placeholders with the values to insert and run the query
                    bool valid = Database.NonQuery(query, ("@ID", idVal), ("@HABID", habIdVal), ("@SPEID", speIdVal),
                                                   ("@NAME", animalName), ("@BIRTHDAY", animalDate), ("@WEIGHT", wei),
                                                   ("@DIETID", dietID));

                    if (valid)
                    {
                        MessageBox.Show("Added an Animal.");
                    }
                }
                else
                {
                    MessageBox.Show("One or more boxes were left blank.");
                }
            }
            catch
            {
                MessageBox.Show("One or more boxes are invalid because they are not numbers");
            }
        }
        private void Btn_diet_add_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                //First, try to parse all of the fields
                int    idVal         = int.Parse(txt_diet_id.Text);
                string dType         = txt_diet_type.Text;
                string dRestrictions = txt_diet_restrictions.Text;
                string dPrimary      = txt_diet_primary_food.Text;
                string dSecondary    = txt_diet_secondary_food.Text;
                string dTreats       = txt_diet_treats.Text;

                //If any of boxes are blank, do not insert and inform the user
                if (dType != "" && dRestrictions != "" && dPrimary != "" &&
                    dSecondary != "" && dTreats != "")
                {
                    //Create the query to insert with placeholders
                    string query = "INSERT INTO DIET VALUES(@ID, @DTYPE, @RES, @PRIM, @SECO, @TREATS); ";

                    //Replace the placeholders with the user's values and
                    //run the query
                    bool valid = Database.NonQuery(query, ("@ID", idVal), ("@DTYPE", dType), ("@RES", dRestrictions),
                                                   ("@PRIM", dPrimary), ("@SECO", dSecondary), ("@TREATS", dTreats));

                    if (valid)
                    {
                        MessageBox.Show("Added a Diet.");
                    }
                }
                else
                {
                    MessageBox.Show("One or more boxes are blank.");
                }
            }
            catch
            {
                MessageBox.Show("One or more boxes are invalid because they are not numbers");
            }
        }
Exemple #15
0
        private void Btn_habitat_update_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                //Try to parse the fields
                int    idVal   = int.Parse(txt_habitat_id.Text);
                string habName = txt_habitat_name.Text;
                int    humiVal = int.Parse(txt_habitat_humidity.Text);
                int    tempVal = int.Parse(txt_habitat_temperature.Text);

                //If the habitat_name is blank, do not update the query
                //and report to the user why.
                if (habName != "")
                {
                    string query = "UPDATE HABITAT " +
                                   "SET habitat_name=(@HABNAME), " +
                                   "humidity=(@HUMIVAL), " +
                                   "temperature=(@TEMP), " +
                                   "WHERE habitat_id=(@ID);";

                    bool valid = Database.NonQuery(query, ("@ID", idVal), ("@HABNAME", habName), ("@HUMI", humiVal),
                                                   ("@TEMP", tempVal));

                    if (valid)
                    {
                        MessageBox.Show("Updated a Habitat.");
                    }
                }
                else
                {
                    MessageBox.Show("One or more boxes were left blank.");
                }
            }
            catch
            {
                MessageBox.Show("One or more boxes are invalid because they are not numbers");
            }
        }
Exemple #16
0
        private void Btn_habitat_add_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                //First, try to parse all of the fields
                int    idVal   = int.Parse(txt_habitat_id.Text);
                string habName = txt_habitat_name.Text;
                int    humiVal = int.Parse(txt_habitat_humidity.Text);
                int    tempVal = int.Parse(txt_habitat_temperature.Text);

                //If the habitat name is blank, do not insert
                //and relay that to the user
                if (habName != "")
                {
                    //First create the base query to insert with placeholders
                    string query = "INSERT INTO HABITAT VALUES(@ID, @HABNAME, @HUMI, @TEMP);";

                    //Replace the placeholders with the user's values and
                    //then run the query.
                    bool valid = Database.NonQuery(query, ("@ID", idVal), ("@HABNAME", habName), ("@HUMI", humiVal),
                                                   ("@TEMP", tempVal));

                    if (valid)
                    {
                        MessageBox.Show("Added a Habitat.");
                    }
                }
                else
                {
                    MessageBox.Show("One or more boxes were left blank.");
                }
            }
            catch
            {
                MessageBox.Show("One or more boxes are invalid because they are not numbers");
            }
        }
Exemple #17
0
        private void Btn_species_update_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                //First try to parse all fields
                int    idVal     = int.Parse(txt_species_id.Text);
                string specName  = txt_species_name.Text;
                string specClass = txt_species_class.Text;

                //If species_name or species_class are empty,
                //do not update. Otherwise create a query to update
                //the species at the specified id.
                if (specName != "" && specClass != "")
                {
                    string query = "UPDATE SPECIES " +
                                   "SET species_name=(@SPENAME), " +
                                   "class_name=(@CLASSNAME) " +
                                   "WHERE species_id=(@ID);";

                    bool valid = Database.NonQuery(query, ("@ID", idVal), ("@SPENAME", specName), ("@CLASSNAME", specClass));

                    if (valid)
                    {
                        MessageBox.Show("Update an Species.");
                    }
                }
                else
                {
                    MessageBox.Show("One or more boxes were left blank");
                }
            }
            catch
            {
                MessageBox.Show("One or more boxes are invalid because they are not numbers");
            }
        }