private void saveStaffButton_Click(object sender, System.EventArgs e) { PermissionLevel permissionLevel = PermissionLevel.Teacher; if (overseerCheckBox.Checked) { permissionLevel = PermissionLevel.Overseer; } if (!newMember) { SqlCommand comm = new SqlCommand("UPDATE Staff SET StaffName = @StaffName, PermissionLevel = @PermissionLevel, StaffUsername = @StaffUsername WHERE StaffId = @StaffId"); comm.Parameters.AddWithValue("@StaffName", staffNameBox.Text); comm.Parameters.AddWithValue("@PermissionLevel", (int)permissionLevel); comm.Parameters.AddWithValue("@StaffUsername", staffUsernameBox.Text); comm.Parameters.AddWithValue("@StaffId", Staff.GetStaffIdByName(staffName)); SqlTools.ExecuteNonQuery(comm); if (resetPasswordTickBox.Checked) { comm.CommandText = "UPDATE Staff SET StaffPassword = '' WHERE StaffId = @StaffId"; SqlTools.ExecuteNonQuery(comm); } comm.CommandText = "DELETE FROM StaffGroupsLink WHERE StaffId = @StaffId"; SqlTools.ExecuteNonQuery(comm); SqlParameter p = new SqlParameter("@GroupId", ""); comm.Parameters.Add(p); comm.CommandText = "INSERT INTO StaffGroupsLink (GroupId, StaffId) VALUES (@GroupId, @StaffId)"; foreach (string o in GroupList) { p.Value = Groups.GetGroupIdByName(o); SqlTools.ExecuteNonQuery(comm); } Close(); } else { SqlCommand comm = new SqlCommand("INSERT INTO Staff (StaffName, PermissionLevel, StaffUsername, StaffPassword) VALUES (@StaffName, @PermissionLevel, @StaffUsername, @StaffPassword)"); comm.Parameters.AddWithValue("@StaffName", staffNameBox.Text); comm.Parameters.AddWithValue("@PermissionLevel", (int)permissionLevel); comm.Parameters.AddWithValue("@StaffUsername", staffUsernameBox.Text); comm.Parameters.AddWithValue("@StaffPassword", ""); SqlTools.ExecuteNonQuery(comm); comm.Parameters.AddWithValue("@StaffId", Staff.GetStaffIdByName(staffNameBox.Text)); SqlParameter p = new SqlParameter("@GroupId", ""); comm.Parameters.Add(p); comm.CommandText = "INSERT INTO StaffGroupsLink (GroupId, StaffId) VALUES (@GroupId, @StaffId)"; foreach (string o in GroupList) { p.Value = Groups.GetGroupIdByName(o); SqlTools.ExecuteNonQuery(comm); } Close(); } }
public static void DeleteStaffMember(string staffName) { /* * Delete staff member by specified staff name, * and group links with that staff member in. */ int staffId = Staff.GetStaffIdByName(staffName); SqlCommand comm = new SqlCommand("DELETE FROM StaffGroupsLink WHERE StaffId = @StaffId"); comm.Parameters.AddWithValue("@StaffId", staffId); SqlTools.ExecuteNonQuery(comm); comm.CommandText = "DELETE FROM Staff WHERE StaffName = @StaffName"; comm.Parameters.AddWithValue("@StaffName", staffName); SqlTools.ExecuteNonQuery(comm); // Repopulate list. AdminForm.RefreshLists(); }
private void SaveGroupButton_Click(object sender, EventArgs e) { /* * SaveButton executes the SQL query needed for inserting * a new Group and its related staff members. * newGroup defines whether a group is being edited or * a new group is being created. */ if (!newGroup) { // Delete all cases of the group beforehand to avoid conflicts. SqlCommand comm = new SqlCommand("DELETE FROM StaffGroupsLink WHERE GroupId = @GroupId"); comm.Parameters.AddWithValue("@GroupId", groupId); SqlTools.ExecuteNonQuery(comm); SqlParameter staffId = new SqlParameter("@StaffId", ""); // Insert the new group-staff links with the selected staff comm.CommandText = "INSERT INTO StaffGroupsLink (GroupId, StaffId) VALUES (@GroupId, @StaffId)"; comm.Parameters.Add(staffId); foreach (string o in staffList) { // Loop over each Staff ID in the list. staffId.Value = Staff.GetStaffIdByName(o); SqlTools.ExecuteNonQuery(comm); } // Update with the new subject if changed comm.CommandText = "UPDATE Groups SET SubjectId = @SubjectId WHERE GroupId = @GroupId"; comm.Parameters.AddWithValue("@SubjectId", Subjects.GetSubjectIdByName(subjectsComboBox.SelectedItem.ToString())); SqlTools.ExecuteNonQuery(comm); // Update with the new Academic Year if changed comm.CommandText = "UPDATE Groups SET AcademicYearId = @AcademicYearId WHERE GroupId = @GroupId"; comm.Parameters.AddWithValue("@AcademicYearId", Groups.GetYearIdByName(academicYearComboBox.SelectedItem.ToString())); SqlTools.ExecuteNonQuery(comm); // Repopulate the list with the new group. AdminForm.RefreshLists(); Close(); } else { // New group if (groupNameTextBox.Text != "" && academicYearComboBox.SelectedIndex != -1 && subjectsComboBox.SelectedIndex != -1 && lecturerBox.Items.Count != 0) { // Insert the parameters into the query. SqlCommand comm = new SqlCommand("INSERT INTO Groups (GroupName, SubjectId, AcademicYearId) VALUES (@GroupName, @SubjectId, @AcademicYearId)"); comm.Parameters.AddWithValue("@GroupName", groupNameTextBox.Text); comm.Parameters.AddWithValue("@SubjectId", Subjects.GetSubjectIdByName(subjectsComboBox.SelectedItem.ToString())); comm.Parameters.AddWithValue("@AcademicYearId", Groups.GetYearIdByName(academicYearComboBox.SelectedItem.ToString())); SqlTools.ExecuteNonQuery(comm); SqlParameter StaffId = new SqlParameter("@StaffId", ""); comm.Parameters.Add(StaffId); // Get the newly created group ID comm.Parameters.AddWithValue("@GroupId", Groups.GetGroupIdByName(groupNameTextBox.Text)); comm.CommandText = "INSERT INTO StaffGroupsLink (GroupId, StaffId) VALUES (@GroupId, @StaffId)"; foreach (string o in lecturerBox.Items) { // Loop through the staff ID's and add them StaffId.Value = Staff.GetStaffIdByName(o); SqlTools.ExecuteNonQuery(comm); } AdminForm.RefreshLists(); Close(); } } }