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(); }
public static BindingList <string> GetStaff(string groupName) { /* * getStaff returns a List of strings of * the staff within group specified. * * Arguments: * groupName (string): Name of the group to find the staff members of. */ BindingList <string> staffList = new BindingList <string>(); // Returns a table with the staff members at that GroupID using StaffGroupsLink table. SqlCommand command = new SqlCommand("SELECT Staff.StaffName FROM StaffGroupsLink INNER JOIN Staff ON StaffGroupsLink.StaffId=Staff.StaffId INNER JOIN Groups ON StaffGroupsLink.GroupId=Groups.GroupId WHERE Groups.GroupName = @GroupName"); command.Parameters.AddWithValue("@GroupName", groupName); // Load the returned table into a new DataTable. // This is used to grab rows and avoid doing a lot of // sql querys which is time consuming. DataTable dt = SqlTools.GetTable(command); for (int i = 0; i < dt.Rows.Count; i++) { staffList.Add(dt.Rows[i]["StaffName"].ToString()); } return(staffList); }
public static double[] GetSubjectMLR(int subjectId) { /* * GetSubjectMLR gets the subject MLR line based from previous results of that subject. * This is stored in the database and refreshed only when an admin modifies previous subject * results. This is to avoid heavy processing at runtime - the line will never * change until new exam results are added. * Read more at GradeUtils.calculateGrade(). * * Arguments: * SubjectId (int): Id of the subject to get the MLR of. */ SqlCommand command = new SqlCommand("SELECT SubjectWeightConstant, SubjectHomeworkWeight, SubjectTestWeight, SubjectMTGWeight FROM Subjects WHERE SubjectId = @SubjectId"); command.Parameters.AddWithValue("@SubjectId", subjectId); DataTable dt = SqlTools.GetTable(command); try { // Try to return values. They might not exist for the subject yet so it could throw an error. return(new double[] { (double)dt.Rows[0]["SubjectWeightConstant"], (double)dt.Rows[0]["SubjectHomeworkWeight"], (double)dt.Rows[0]["SubjectTestWeight"], (double)dt.Rows[0]["SubjectMTGWeight"] }); } catch (Exception) { // If it doesn't exist, don't worry, just return nothing. return(new double[] { 0, 0, 0, 0 }); } }
private void PopulateList() { SqlCommand comm = new SqlCommand(); if (isHomework) { comm.CommandText = "SELECT HomeworkName FROM Homeworks WHERE GroupId=@GroupId"; comm.Parameters.AddWithValue("@GroupId", groupId); DataTable dt = SqlTools.GetTable(comm); for (int i = 0; i < dt.Rows.Count; i++) { AssignmentsList.Add((string)dt.Rows[i]["HomeworkName"]); } } else { comm.CommandText = "SELECT TestName FROM Tests WHERE GroupId=@GroupId"; comm.Parameters.AddWithValue("@GroupId", groupId); DataTable dt = SqlTools.GetTable(comm); for (int i = 0; i < dt.Rows.Count; i++) { AssignmentsList.Add((string)dt.Rows[i]["TestName"]); } } }
public static void PopulateList() { StudentList.Clear(); DataTable dt = SqlTools.GetTable("SELECT StudentName FROM Students"); for (int i = 0; i < dt.Rows.Count; i++) { StudentList.Add((string)dt.Rows[i]["StudentName"]); } }
public static int GetStudentIdByName(string studentName) { /* * Get a student's database ID by their name */ SqlCommand comm = new SqlCommand("SELECT StudentId FROM Students WHERE StudentName=@StudentName"); comm.Parameters.AddWithValue("@StudentName", studentName); return((int)SqlTools.GetTable(comm).Rows[0]["StudentId"]); }
private void UpdateTable() { dt = SqlTools.GetTable(command); // Hide ID rows and add user friendly headers PreviousResultView.DataSource = dt; PreviousResultView.Columns["ResultId"].Visible = false; PreviousResultView.Columns["SubjectId"].Visible = false; PreviousResultView.Columns["HomeworkResult"].HeaderText = "Homework grade average"; PreviousResultView.Columns["TestResult"].HeaderText = "Test grade average"; PreviousResultView.Columns["MTGResult"].HeaderText = "Minimum target grade"; PreviousResultView.Columns["FinalResult"].HeaderText = "Final grade"; }
public static PermissionLevel GetPermissionLevel(int staffId) { /* * Get the permission level of the staff member based on staff ID. */ SqlCommand comm = new SqlCommand("SELECT PermissionLevel FROM Staff WHERE StaffId = @StaffId"); comm.Parameters.AddWithValue("@StaffId", staffId); DataTable dt = SqlTools.GetTable(comm); return((PermissionLevel)dt.Rows[0]["PermissionLevel"]); }
public static string GetSubjectName(string groupName) { /* * Gets the subject assinged to a group. */ SqlCommand command = new SqlCommand("SELECT Subjects.SubjectName FROM Groups INNER JOIN Subjects ON Groups.SubjectId=Subjects.SubjectID WHERE Groups.GroupName = @GroupName"); command.Parameters.AddWithValue("@GroupName", groupName); DataTable dt = SqlTools.GetTable(command); return(dt.Rows[0]["SubjectName"].ToString()); }
public static int GetStudentTableId(string studentName) { /* * GetStudentTableId gets the actual identity value assigned to * a student in the database by the student name. */ SqlCommand comm = new SqlCommand("SELECT StudentId FROM Students WHERE StudentName = @StudentName"); comm.Parameters.AddWithValue("@StudentName", studentName); DataTable dt = SqlTools.GetTable(comm); return((int)dt.Rows[0]["StudentId"]); }
public static string GetStaffNameById(int staffId) { /* * Returns the string of the StaffName based * on the given string staffId. */ SqlCommand comm = new SqlCommand("SELECT StaffName FROM Staff WHERE StaffId = @StaffId"); comm.Parameters.AddWithValue("@StaffId", staffId); DataTable dt = SqlTools.GetTable(comm); return((string)dt.Rows[0]["StaffName"]); }
public static int GetStaffIdByUsername(string staffUsername) { /* * Returns the integer of the StaffId based * on the given string staffUsername. */ SqlCommand comm = new SqlCommand("SELECT StaffId FROM Staff WHERE StaffUsername = @StaffUsername"); comm.Parameters.AddWithValue("@StaffUsername", staffUsername); DataTable dt = SqlTools.GetTable(comm); return((int)dt.Rows[0]["StaffId"]); }
private void PopulateGroupList() { teacherGroups.Clear(); SqlCommand command = new SqlCommand("SELECT Groups.GroupName FROM StaffGroupsLink INNER JOIN Staff ON StaffGroupsLink.StaffId=Staff.StaffId INNER JOIN Groups ON StaffGroupsLink.GroupId=Groups.GroupId WHERE StaffGroupsLink.StaffId = @StaffId"); command.Parameters.AddWithValue("@StaffId", StaffId); DataTable dt = SqlTools.GetTable(command); for (int i = 0; i < dt.Rows.Count; i++) { teacherGroups.Add((string)dt.Rows[i]["GroupName"]); } }
private void PopulateStudentsList(int GroupId) { studentsList.Clear(); SqlCommand command = new SqlCommand("SELECT StudentName FROM StudentGroupsLink INNER JOIN Students ON StudentGroupsLink.StudentId=Students.StudentId WHERE StudentGroupsLink.GroupId=@GroupId"); command.Parameters.AddWithValue("@GroupId", GroupId); DataTable dt = SqlTools.GetTable(command); for (int i = 0; i < dt.Rows.Count; i++) { studentsList.Add((string)dt.Rows[i]["StudentName"]); } }
public static string GetUsername(string studentName) { /* * getUsername gets the college username of the * specified student defined by string * studentName. */ SqlCommand comm = new SqlCommand("SELECT StudentCollegeId FROM Students WHERE StudentName = @StudentName"); comm.Parameters.AddWithValue("@StudentName", studentName); DataTable dt = SqlTools.GetTable(comm); return((string)dt.Rows[0]["StudentCollegeId"]); }
public static string GetUsername(string staffName) { /* * getUsername gets the login username of the * specified staff member defined by string * staffName. */ SqlCommand comm = new SqlCommand("SELECT StaffUsername FROM Staff WHERE StaffName = @StaffName"); comm.Parameters.AddWithValue("@StaffName", staffName); DataTable dt = SqlTools.GetTable(comm); return((string)dt.Rows[0]["StaffUsername"]); }
public static void PopulateList() { /* * Populates the global list, SubjectList, * with the subjects in the Subjects table. */ SubjectList.Clear(); DataTable dt = SqlTools.GetTable("SELECT SubjectName FROM Subjects"); for (int i = 0; i < dt.Rows.Count; i++) { SubjectList.Add((string)dt.Rows[i]["SubjectName"]); } }
public static void PopulateList() { StaffList.Clear(); DataTable dt = SqlTools.GetTable("SELECT StaffName FROM Staff"); for (int i = 0; i < dt.Rows.Count; i++) { string name = dt.Rows[i]["StaffName"].ToString(); // Make sure to skip system admin account - we're the admin. if (name != "System Admin") { StaffList.Add(name); } } }
public static int GetGroupIdByName(string groupName) { /* * GetSubjectIdByName gets the SubjectId assigned to a * subject name and returns the ID. * * Arguments: * subjectName (string): Name of the subject. */ SqlCommand command = new SqlCommand("SELECT GroupId FROM Groups WHERE GroupName = @GroupName"); command.Parameters.AddWithValue("@GroupName", groupName); DataTable dt = SqlTools.GetTable(command); return((int)dt.Rows[0]["GroupId"]); }
public static int GetYearIdByName(string academicYearName) { /* * GetYearIdByName gets the AcademicYearId assigned to a * year name and returns the ID. * * Arguments: * academicYearName (string): Name of the academic year. */ SqlCommand command = new SqlCommand("SELECT AcademicYearId FROM AcademicYears WHERE AcademicYearName = @AcademicYearName"); command.Parameters.AddWithValue("@AcademicYearName", academicYearName); DataTable dt = SqlTools.GetTable(command); return((int)dt.Rows[0]["AcademicYearId"]); }
public static void PopulateList() { /* * PopulateList gets all the Groups * available in the database and adds them * to the List<string> GroupList. */ // Make sure list is clear of all entries. GroupList.Clear(); DataTable dt = SqlTools.GetTable("SELECT GroupName FROM Groups"); for (int i = 0; i < dt.Rows.Count; i++) { GroupList.Add(dt.Rows[i]["GroupName"].ToString()); } }
public static string GetAcademicYear(string groupName) { /* * getAcademicYear gets the academic year * assigned to a group and returns the join * result. * * Arguments: * groupName (string): Name of the group to find the year of. */ SqlCommand command = new SqlCommand("SELECT AcademicYears.AcademicYearName FROM Groups INNER JOIN AcademicYears ON Groups.AcademicYearId=AcademicYears.AcademicYearId WHERE Groups.GroupName = @GroupName"); command.Parameters.AddWithValue("@GroupName", groupName); DataTable dt = SqlTools.GetTable(command); return(dt.Rows[0]["AcademicYearName"].ToString()); }
private void SearchButton_Click(object sender, EventArgs e) { SqlCommand comm = new SqlCommand("SELECT StaffName FROM Staff WHERE StaffUsername = @StaffID"); comm.Parameters.AddWithValue("@StaffID", staffIDTextBox.Text); try { DataTable dt = SqlTools.GetTable(comm); staffNameLabel.Text = $"Name: { dt.Rows[0]["StaffName"]}"; staffName = dt.Rows[0]["StaffName"].ToString(); validID = true; } catch { // Do nothing. SQL will fail if the ID is incorrect but no operation // needs to be made. } }
+ subjectMLRLine[3] * MTG; // Minimum target grade weight calculation. /* * Get the ID of any assignment - there are only two different types of assignment, * homeworks and tests, so just check if it's a homework or not */ public static int getAssignmentId(string assignmentName, bool isHomework) { SqlCommand comm = new SqlCommand(); if (isHomework) { comm.CommandText = "SELECT HomeworkId FROM Homeworks WHERE HomeworkName=@HomeworkName"; comm.Parameters.AddWithValue("@HomeworkName", assignmentName); DataTable dt = SqlTools.GetTable(comm); return((int)dt.Rows[0]["HomeworkId"]); } else { comm.CommandText = "SELECT TestId FROM Tests WHERE TestName=@TestName"; comm.Parameters.AddWithValue("@TestName", assignmentName); DataTable dt = SqlTools.GetTable(comm); return((int)dt.Rows[0]["TestId"]); } }
public static void PopulateYears(ComboBox cb, string academicYear = null) { /* * Fill the combo box with all the available years * that can be assigned to a group. * If the group is pre-existing, make sure it is * preselected. */ DataTable dt = SqlTools.GetTable("SELECT AcademicYearName FROM AcademicYears"); for (int i = 0; i < dt.Rows.Count; i++) { cb.Items.Add(dt.Rows[i]["AcademicYearName"].ToString()); } if (academicYear != null) { cb.SelectedIndex = cb.FindStringExact(academicYear); } }
private void PopulateSubjects() { /* * Fill the combo box with all the subjects * that can be assigned to a group. * If the group is pre-existing, make sure * it is preselected. */ DataTable dt = SqlTools.GetTable("SELECT SubjectName FROM Subjects"); for (int i = 0; i < dt.Rows.Count; i++) { subjectsComboBox.Items.Add(dt.Rows[i]["SubjectName"].ToString()); } if (!newGroup) { subjectsComboBox.SelectedIndex = subjectsComboBox.FindStringExact(Subject); } }
public static int GetSubjectId(int GroupId) { /* * GetSubjectId gets the SubjectId assigned to a * group. * * Arguments: * GetSubjectId (int): Id of the group. */ SqlCommand command = new SqlCommand(); command.CommandText = "SELECT Subjects.SubjectId FROM Subjects "; command.CommandText += "INNER JOIN Groups ON Subjects.SubjectId=Groups.SubjectId "; command.CommandText += "WHERE Groups.GroupId=@GroupId"; command.Parameters.AddWithValue("@GroupId", GroupId); DataTable dt = SqlTools.GetTable(command); return((int)dt.Rows[0]["SubjectId"]); }
public static BindingList <string> GetGroups(string staffName) { /* * getGroups returns a List of strings of * the groups within staff member specified. * * Arguments: * staffName (string): Name of the group to find the staff members of. */ BindingList <string> groupList = new BindingList <string>(); // Returns a table with the staff members at that GroupID using StaffGroupsLink table. SqlCommand command = new SqlCommand("SELECT Groups.GroupName FROM StaffGroupsLink INNER JOIN Staff ON StaffGroupsLink.StaffId=Staff.StaffId INNER JOIN Groups ON StaffGroupsLink.GroupId=Groups.GroupId WHERE Staff.StaffName = @StaffName"); command.Parameters.AddWithValue("@StaffName", staffName); DataTable dt = SqlTools.GetTable(command); for (int i = 0; i < dt.Rows.Count; i++) { groupList.Add(dt.Rows[i]["GroupName"].ToString()); } return(groupList); }
private void studentsListBox_SelectedIndexChanged(object sender, EventArgs e) { SqlCommand comm = new SqlCommand(); comm.Parameters.AddWithValue("@StudentId", Students.GetStudentIdByName(studentsListBox.SelectedItem.ToString())); comm.Parameters.AddWithValue("@AssignmentId", assignmentId); if (isHomework) { comm.CommandText = "SELECT FinalGrade FROM HomeworkResults WHERE StudentId=@StudentId AND HomeworkId=@AssignmentId"; } else { comm.CommandText = "SELECT FinalGrade FROM TestResults WHERE StudentId=@StudentId AND TestId=@AssignmentId"; } try { string grade = (string)SqlTools.GetTable(comm).Rows[0]["FinalGrade"]; resultsComboBox.SelectedItem = grade; } catch (Exception) { // There isn't a result for them yet. } }
private void LoginButton_Click(object sender, EventArgs e) { string username = usernameBox.Text; Debug.WriteLine(username); // Run plain-text password through algorithm string password = HashingAlgorithm(passwordBox.Text); try { SqlCommand comm = new SqlCommand("SELECT StaffPassword FROM Staff WHERE StaffUsername = @StaffUsername"); comm.Parameters.AddWithValue("@StaffUsername", username); DataTable dt = SqlTools.GetTable(comm); // If password needs to be reset, don't continue if ((string)dt.Rows[0]["StaffPassword"] == "") { ResetPasswordForm pwForm = new ResetPasswordForm(username); pwForm.ShowDialog(); pwForm.Dispose(); } else { if ((string)dt.Rows[0]["StaffPassword"] == password) { switch (Staff.GetPermissionLevel(username)) { case PermissionLevel.Overseer: int staffId = Staff.GetStaffIdByUsername(username); OverseerForm of = new OverseerForm { StaffId = staffId, isTutor = false }; Hide(); of.FormClosed += (s, args) => Close(); of.Show(); break; case PermissionLevel.Admin: AdminForm af = new AdminForm(); Hide(); af.FormClosed += (s, args) => Close(); af.Show(); break; case PermissionLevel.Teacher: staffId = Staff.GetStaffIdByUsername(username); TeacherMainForm tf = new TeacherMainForm { StaffId = staffId, StaffName = Staff.GetStaffNameById(staffId), }; Hide(); tf.FormClosed += (s, args) => Close(); tf.Show(); break; default: MessageBox.Show("Future system will have lower permission levels"); break; } } else { // The password must be wrong if the username is right. invalidPasswordLabel.Show(); } } } catch (Exception ex) { Debug.WriteLine(ex); // Unfortunately, we have to assume the username is wrong. // The end-user can't have done much else wrong - except a bad // username. invalidUsernameLabel.Show(); throw ex; } }