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."); } } }
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."); } }
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."); } } }
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!"); } } }
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); } } }
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."); } } }
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"); } }
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"); } }
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"); } }
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"); } }