public static void DeleteSubject(string subjectName) { /* * Deletes the specified subject and groups * with that subject, and those groups linked with staff. */ int subjectId = GetSubjectIdByName(subjectName); SqlCommand comm = new SqlCommand("SELECT GroupId FROM Groups WHERE SubjectId = @SubjectId"); comm.Parameters.AddWithValue("@SubjectId", subjectId); DataTable dt = SqlTools.GetTable(comm); SqlCommand deleteCommand = new SqlCommand(); SqlParameter groupId = new SqlParameter("@GroupId", ""); deleteCommand.Parameters.Add(groupId); for (int i = 0; i < dt.Rows.Count; i++) { deleteCommand.CommandText = "DELETE FROM StaffGroupsLink WHERE GroupId = @GroupId"; groupId.Value = (int)dt.Rows[i]["GroupId"]; SqlTools.ExecuteNonQuery(deleteCommand); } comm.CommandText = "DELETE FROM Groups WHERE SubjectId = @SubjectId"; SqlTools.ExecuteNonQuery(comm); comm.CommandText = "DELETE FROM Subjects WHERE SubjectName = @SubjectName"; comm.Parameters.AddWithValue("@SubjectName", subjectName); SqlTools.ExecuteNonQuery(comm); AdminForm.RefreshLists(); }
private void continueButton_Click(object sender, EventArgs e) { SqlCommand command = new SqlCommand(); SqlParameter group = new SqlParameter("@GroupId", groupId); command.Parameters.Add(group); if (isAssignment) { command.CommandText = "INSERT INTO Homeworks VALUES (@HomeworkName, @GroupId)"; command.Parameters.AddWithValue("@HomeworkName", assignmentNameTextBox.Text); SqlTools.ExecuteNonQuery(command); } else { command.CommandText = "INSERT INTO Tests VALUES (@TestName, @GroupId)"; command.Parameters.AddWithValue("@TestName", assignmentNameTextBox.Text); SqlTools.ExecuteNonQuery(command); } EditResultsForm erf = new EditResultsForm(Students, groupId, isAssignment, assignmentNameTextBox.Text); Hide(); erf.ShowDialog(); erf.Dispose(); }
public static void AddNewSubject(string subjectName) { /* * Adds a new subject to the table. */ SqlCommand comm = new SqlCommand("INSERT INTO Subjects (SubjectName) VALUES (@SubjectName)"); comm.Parameters.AddWithValue("@SubjectName", subjectName); SqlTools.ExecuteNonQuery(comm); AdminForm.RefreshLists(); }
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 DeleteStudent(string studentName) { /* * Deletes the student with the specified student name * from the students table and any links to it. */ int studentId = GetStudentTableId(studentName); SqlCommand comm = new SqlCommand("DELETE FROM Students WHERE StudentId = @StudentId"); comm.Parameters.AddWithValue("@StudentId", studentId); SqlTools.ExecuteNonQuery(comm); AdminForm.RefreshLists(); }
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(); }
public static void EditStudent(string studentName, string studentCollegeId, int academicYearId, BindingList <string> GroupList, out int newstudentId, int studentId = -1) { /* * Adds or edits a student. */ SqlCommand comm = new SqlCommand(); comm.Parameters.AddWithValue("@StudentName", studentName); comm.Parameters.AddWithValue("@StudentCollegeId", studentCollegeId); comm.Parameters.AddWithValue("@AcademicYearId", academicYearId); if (studentId == -1) { // If a new student is to be added, insert all values. comm.CommandText = "INSERT INTO Students (StudentName, StudentCollegeId, AcademicYearId) "; comm.CommandText += "VALUES (@StudentName, @StudentCollegeId, @AcademicYearId)"; SqlTools.ExecuteNonQuery(comm); // Look up new student's ID. comm.Parameters.AddWithValue("@StudentId", GetStudentIdByName(studentName)); } else { comm.CommandText = "UPDATE Students SET StudentName = @StudentName, AcademicYearId = @AcademicYearId, StudentCollegeId = @StudentCollegeId WHERE StudentId=@StudentId"; comm.Parameters.AddWithValue("@StudentId", studentId); SqlTools.ExecuteNonQuery(comm); // It is easier to just delete all the matches in group links and re-add them then evaluate what has changed. comm.CommandText = "DELETE FROM StudentGroupsLink WHERE StudentId=@StudentId"; SqlTools.ExecuteNonQuery(comm); } // Both conditions need the groups to be inserted. // Define a new parameter which has a value that can be changed (group ID) SqlParameter idParameter = new SqlParameter("@GroupId", 0); comm.Parameters.Add(idParameter); foreach (string item in GroupList) { // For each group, insert it into the link table. idParameter.Value = Groups.GetGroupIdByName(item); comm.CommandText = "INSERT INTO StudentGroupsLink VALUES (@StudentId, @GroupId)"; SqlTools.ExecuteNonQuery(comm); } // Return the new student ID (stays the same if a new user was not added) newstudentId = studentId; }
private void savePasswordButton_Click(object sender, EventArgs e) { string password = pwBox.Text; if (password.Length > 0) { if (password == pwBoxConfirm.Text) { SqlCommand comm = new SqlCommand("UPDATE Staff SET StaffPassword = @password WHERE StaffUsername = @StaffUsername"); comm.Parameters.AddWithValue("@password", LoginForm.HashingAlgorithm(password)); comm.Parameters.AddWithValue("@StaffUsername", username); SqlTools.ExecuteNonQuery(comm); } } else { MessageBox.Show("Password cannot be empty!"); } this.Close(); }
public static void DeleteGroup(string groupName) { /* * DeleteGroup deletes the given group from * the database. * * Arguments: * groupName (string): The name of the group to delete. */ int groupId = GetGroupIdByName(groupName); // Delete all references to Group in StaffGroupsLink SqlCommand comm = new SqlCommand("DELETE FROM StaffGroupsLink WHERE GroupId = @GroupId"); comm.Parameters.AddWithValue("@GroupId", groupId); SqlTools.ExecuteNonQuery(comm); // Delete from Groups table comm.CommandText = "DELETE FROM Groups WHERE GroupId = @GroupId"; SqlTools.ExecuteNonQuery(comm); // Repopulate the list to view affected groups. AdminForm.RefreshLists(); }
private void saveResultButton_Click(object sender, EventArgs e) { /* * Insert result into results table */ SqlCommand comm = new SqlCommand(); comm.Parameters.AddWithValue("@StudentId", Students.GetStudentIdByName(studentsListBox.SelectedItem.ToString())); comm.Parameters.AddWithValue("@FinalGrade", (string)resultsComboBox.SelectedItem); comm.Parameters.AddWithValue("@AssignmentId", assignmentId); // Update the result, however if the affected rowcount is 0, insert a new result as there is nothing to update. if (isHomework) { comm.CommandText = "UPDATE HomeworkResults SET FinalGrade=@FinalGrade WHERE StudentId=@StudentId AND HomeworkId=@AssignmentId "; comm.CommandText += "IF @@ROWCOUNT = 0 INSERT INTO HomeworkResults VALUES (@assignmentId, @StudentId, @FinalGrade)"; SqlTools.ExecuteNonQuery(comm); } else { comm.CommandText = "UPDATE TestResults SET FinalGrade=@FinalGrade WHERE StudentId=@StudentId AND TestId=@AssignmentId "; comm.CommandText += "IF @@ROWCOUNT = 0 INSERT INTO TestResults VALUES (@assignmentId, @StudentId, @FinalGrade)"; SqlTools.ExecuteNonQuery(comm); } }
public static void UpdateMTGs(int studentId, Dictionary <int, string> grades) { /* * Update a students minimum target grades. */ // Delete all the mtg's. This is easier than finding what changed and updating them. SqlCommand comm = new SqlCommand("DELETE FROM MinimumTargetGrades WHERE StudentId=@StudentId"); comm.Parameters.AddWithValue("@StudentId", studentId); SqlTools.ExecuteNonQuery(comm); comm.CommandText = "INSERT INTO MinimumTargetGrades VALUES (@StudentId, @SubjectId, @Grade)"; SqlParameter subjectParameter = new SqlParameter("@SubjectId", 0); SqlParameter gradeParameter = new SqlParameter("@Grade", 0); comm.Parameters.Add(subjectParameter); comm.Parameters.Add(gradeParameter); // For each key, add the subject and minimum target grade. foreach (int subjectId in grades.Keys) { subjectParameter.Value = subjectId; gradeParameter.Value = grades[subjectId]; SqlTools.ExecuteNonQuery(comm); } }
public static void UpdateSubjectMLR(int subjectId) { /* * UpdateSubjectMLR is responsible for re-running the MLR line * calculation for a subject. This is a difficult calcuation, * so this shouldn't be done too often. After the calcuation is * done, store the values of each variable in the MLR equation * in the database. */ List <double> homeworkResults = new List <double>(); List <double> testResults = new List <double>(); List <double> minimumTargetGrades = new List <double>(); List <double> finalResults = new List <double>(); /* Populate all the lists. * Remember to convert grades to their double eqv. using the dictonary. */ SqlCommand command = new SqlCommand("SELECT HomeworkResult FROM PreviousResults WHERE SubjectId = @SubjectId"); command.Parameters.AddWithValue("@SubjectId", subjectId); DataTable dt = SqlTools.GetTable(command); for (int i = 0; i < dt.Rows.Count; i++) { homeworkResults.Add(Grades[(string)dt.Rows[i]["HomeworkResult"]]); } command.CommandText = "SELECT TestResult FROM PreviousResults WHERE SubjectId = @SubjectId"; dt = SqlTools.GetTable(command); for (int i = 0; i < dt.Rows.Count; i++) { testResults.Add(Grades[(string)dt.Rows[i]["TestResult"]]); } command.CommandText = "SELECT MTGResult FROM PreviousResults WHERE SubjectId = @SubjectId"; dt = SqlTools.GetTable(command); for (int i = 0; i < dt.Rows.Count; i++) { minimumTargetGrades.Add(Grades[(string)dt.Rows[i]["MTGResult"]]); } command.CommandText = "SELECT FinalResult FROM PreviousResults WHERE SubjectId = @SubjectId"; dt = SqlTools.GetTable(command); for (int i = 0; i < dt.Rows.Count; i++) { finalResults.Add(Grades[(string)dt.Rows[i]["FinalResult"]]); } /* * Calculate the MLR line. * Returned double array: * MLRLine[0]: Constant * MLRLine[1]: Homework result weight * MLRLine[2]: Test result weight * MLRLine[3]: Minimum target grade weight */ double[] MLRLine = GradeUtils.calculateSubjectMLR(homeworkResults, testResults, minimumTargetGrades, finalResults); /* * Setup the SQL command. * SubjectID doesn't change here. */ command.CommandText = "UPDATE Subjects SET SubjectWeightConstant = @SubjectWeightConstant, " + "SubjectHomeworkWeight = @SubjectHomeworkWeight, " + "SubjectTestWeight = @SubjectTestWeight, " + "SubjectMTGWeight = @SubjectMTGWeight WHERE SubjectID = @SubjectID"; // Add each parameter. command.Parameters.AddWithValue("@SubjectWeightConstant", MLRLine[0]); command.Parameters.AddWithValue("@SubjectHomeworkWeight", MLRLine[1]); command.Parameters.AddWithValue("@SubjectTestWeight", MLRLine[2]); command.Parameters.AddWithValue("@SubjectMTGWeight", MLRLine[3]); // Finally, execute the update SqlTools.ExecuteNonQuery(command); }
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(); } } }