private int AddCourseToNewTeacher(string teacher_id, string course_id) { DataTable old_courses = SQL_Help.ExecuteDataTable("select courses from teacher_info where id=@id;", connection, new MySqlParameter[] { new MySqlParameter("@id", MySqlDbType.VarChar) { Value = teacher_id } }); List <string> old_courses_list = new List <string>(old_courses.Rows[0][0].ToString().Split(',')); if (old_courses_list[0].Length == 0) { old_courses_list.Clear(); } old_courses_list.Add(course_id); string new_courses = string.Join(",", old_courses_list.ToArray()); return(SQL_Help.ExecuteNonQuery("update teacher_info set courses=@courses where id=@id;", connection, new MySqlParameter[] { new MySqlParameter("@id", MySqlDbType.VarChar) { Value = teacher_id }, new MySqlParameter("@courses", MySqlDbType.Text) { Value = new_courses } })); }
private void course_id_textBox_Leave(object sender, EventArgs e) { if (course_id_textBox.Text.Length == 0) { return; } DataTable dt = SQL_Help.ExecuteDataTable("select distinct name,credit,grade_limit,canceled_year from course_info where id=@id;", connection, new MySqlParameter[] { new MySqlParameter("@id", MySqlDbType.VarChar) { Value = course_id_textBox.Text } }); if (dt.Rows.Count == 0) { cancel_year_textBox.ReadOnly = false; course_name_textBox.ReadOnly = false; credit_textBox.ReadOnly = false; grade_comboBox.Enabled = true; course_name_textBox.Clear(); credit_textBox.Clear(); grade_comboBox.Text = ""; cancel_year_textBox.Clear(); } else { course_name_textBox.Text = dt.Rows[0][0].ToString(); credit_textBox.Text = dt.Rows[0][1].ToString(); grade_comboBox.Text = dt.Rows[0][2].ToString(); cancel_year_textBox.Text = dt.Rows[0][3].ToString(); course_name_textBox.ReadOnly = true; credit_textBox.ReadOnly = true; grade_comboBox.Enabled = false; cancel_year_textBox.ReadOnly = true; } }
private void course_comboBox_TextChanged(object sender, EventArgs e) { if (course_comboBox.Items.Contains(course_comboBox.Text)) { teacher_comboBox.Items.Clear(); year_comboBox.Items.Clear(); DataTable teacher_dt = SQL_Help.ExecuteDataTable("select distinct course_info.teacher_id,teacher_info.name " + "from course_info join teacher_info on (teacher_info.id=course_info.teacher_id) " + "where course_info.id = @course_id;", connection, new MySqlParameter[] { new MySqlParameter("@course_id", MySqlDbType.VarChar) { Value = course_comboBox.Text.Substring(0, 7) } }); for (int i = 0; i < teacher_dt.Rows.Count; i++) { teacher_comboBox.Items.Add(string.Format("{0}({1})", teacher_dt.Rows[i][0], teacher_dt.Rows[i][1])); } if (teacher_dt.Rows.Count != 0) { teacher_comboBox.Text = teacher_comboBox.Items[0].ToString(); } } else { teacher_comboBox.Items.Clear(); year_comboBox.Items.Clear(); } }
private void teacher_query_button_Click(object sender, EventArgs e) { if (teacher_current_id_textBox.Text.Length == 0) { Common.ShowError("Null value error!", "ID can not be empty!"); return; } teacher_current_id_textBox.ReadOnly = true; MySqlParameter teacher_id_parameter = new MySqlParameter("@id", MySqlDbType.VarChar) { Value = teacher_current_id_textBox.Text }; DataTable current_info_dt = SQL_Help.ExecuteDataTable("select name from teacher_info where id=@id;", connection, new MySqlParameter[] { teacher_id_parameter }); if (current_info_dt.Rows.Count == 0) { Common.ShowError("Empty query result!", "No corresponding teacher found with id = " + teacher_current_id_textBox.Text + ".\nPlease check again."); teacher_current_id_textBox.ReadOnly = false; } else { teacher_new_name_textBox.Text = teacher_current_name_textBox.Text = current_info_dt.Rows[0][0].ToString(); teacher_new_id_textBox.Text = teacher_current_id_textBox.Text; teacher_new_info_groupBox.Enabled = true; teacher_apply_button.Enabled = true; teacher_delete_button.Enabled = true; } DataTable teach_course_dt = SQL_Help.ExecuteDataTable("select id,name from course_info where teacher_id=@id;", connection, new MySqlParameter[] { teacher_id_parameter }); teacher_courses_textBox.Clear(); for (int i = 0; i < teach_course_dt.Rows.Count; i++) { teacher_courses_textBox.Text += string.Format("{0},{1}" + Environment.NewLine, teach_course_dt.Rows[i][0].ToString(), teach_course_dt.Rows[i][1].ToString()); } }
private void ModifyInfo_Load(object sender, EventArgs e) { switch (role) { case Common.UserType.STUDENT: Enabled = false; break; case Common.UserType.TEACHER: student_info_tabPage.Parent = null; course_info_tabPage.Parent = null; course_choosing_info_tabPage.Parent = null; break; case Common.UserType.ADMIN: break; default: break; } DataTable teacher_dt = SQL_Help.ExecuteDataTable("select id,name from teacher_info;", connection); for (int i = 0; i < teacher_dt.Rows.Count; i++) { course_teacher_mapping.Add(teacher_dt.Rows[i][0].ToString(), teacher_dt.Rows[i][1].ToString()); course_new_teacher_comboBox.Items.Add(string.Format("{0}({1})", teacher_dt.Rows[i][0].ToString(), teacher_dt.Rows[i][1].ToString())); } course_new_year_textBox.Text = "Optional"; course_new_year_textBox.ForeColor = Color.Gray; }
private void RefreshYearData() { DataTable year_dt = SQL_Help.ExecuteDataTable("select distinct chosen_year from course_choosing_info where (teacher_id=@teacher_id and course_id=@course_id);", connection, new MySqlParameter[] { new MySqlParameter("@teacher_id", MySqlDbType.VarChar) { Value = current_teacher_id }, new MySqlParameter("@course_id", MySqlDbType.VarChar) { Value = course_comboBox.Text.Substring(0, 7) } }); if (year_dt.Rows.Count == 0) { Common.ShowInfo("Empty query result!", "Currently there is no student in course " + course_comboBox.Text + "!"); ClearDGV(); return; } for (int i = 0; i < year_dt.Rows.Count; i++) { year_comboBox.Items.Add(year_dt.Rows[i][0]); } year_comboBox.Text = year_comboBox.Items[0].ToString(); }
private void teacher_comboBox_TextChanged(object sender, EventArgs e) { DataTable year_dt = SQL_Help.ExecuteDataTable("select distinct chosen_year " + "from course_choosing_info " + "where course_id = @cid and teacher_id = @tid;", connection, new MySqlParameter[] { new MySqlParameter("@cid", MySqlDbType.VarChar) { Value = course_comboBox.Text.Substring(0, 7) }, new MySqlParameter("@tid", MySqlDbType.VarChar) { Value = (year_comboBox.Items.Count == 0?teacher_comboBox.Items[0].ToString():teacher_comboBox.Text).Substring(0, 5) } }); year_comboBox.Items.Clear(); for (int i = 0; i < year_dt.Rows.Count; i++) { year_comboBox.Items.Add(string.Format("{0}", year_dt.Rows[i][0])); } if (year_dt.Rows.Count != 0) { year_comboBox.Text = year_comboBox.Items[0].ToString(); } }
private void class_ComboBox_SelectedIndexChanged(object sender, EventArgs e) { alternative_listBox.Items.Clear(); if (class_ComboBox.Text == "All class...") { for (int i = 0; i < all_student_items.Count; i++) { alternative_listBox.Items.Add(all_student_items[i]); } return; } int index = class_combobox_items.IndexOf(class_ComboBox.Text); if (index != -1) { DataTable student_dt = SQL_Help.ExecuteDataTable("select id,name,class from student_info where class = @classname", connection, new MySqlParameter[] { new MySqlParameter("@classname", MySqlDbType.VarChar) { Value = class_ComboBox.Text } }); for (int i = 0; i < student_dt.Rows.Count; i++) { alternative_listBox.Items.Add(student_dt.Rows[i][0] + "(" + student_dt.Rows[i][1] + "," + student_dt.Rows[i][2] + ")"); } } else { alternative_listBox.Items.Clear(); } }
private void AddCourseChoosingInfo_Load(object sender, EventArgs e) { year_textBox.Text = "Input chosen year here..."; year_textBox.ForeColor = Color.Gray; student_id_ComboBox.Text = "Input student id here to add single student..."; student_id_ComboBox.ForeColor = Color.Gray; course_dt = SQL_Help.ExecuteDataTable("select distinct id,name from course_info;", connection); for (int i = 0; i < course_dt.Rows.Count; i++) { string comboBox_item = course_dt.Rows[i][0] + "(" + course_dt.Rows[i][1] + ")"; course_id_ComboBox.Items.Add(comboBox_item); course_combobox_items.Add(comboBox_item); } class_dt = SQL_Help.ExecuteDataTable("select distinct class from student_info;", connection); for (int i = 0; i < class_dt.Rows.Count; i++) { string comboBox_item = class_dt.Rows[i][0].ToString(); class_ComboBox.Items.Add(comboBox_item); class_combobox_items.Add(comboBox_item); } class_ComboBox.Items.Add("All class..."); DataTable student_dt = SQL_Help.ExecuteDataTable("select id,name,class from student_info;", connection); for (int i = 0; i < student_dt.Rows.Count; i++) { string comboBox_item = student_dt.Rows[i][0] + "(" + student_dt.Rows[i][1] + "," + student_dt.Rows[i][2] + ")"; all_student_items.Add(comboBox_item); student_id_ComboBox.Items.Add(comboBox_item); } }
private void MainWindow_Load(object sender, EventArgs e) { user_text_menuitem.Text = "User: "******"Role: Student"; queryTeacherInformationToolStripMenuItem.Visible = false; break; case Common.UserType.TEACHER: role_text_menuitem.Text = "Role: Teacher"; submitScoreToolStripMenuItem.Visible = true; break; case Common.UserType.ADMIN: role_text_menuitem.Text = "Role: Administrator"; addAccountToolStripMenuItem.Visible = true; resetUserPasswordToolStripMenuItem.Visible = true; addCourseToolStripMenuItem.Visible = true; addCourseChoosingInfoToolStripMenuItem.Visible = true; modifyInformationToolStripMenuItem.Visible = true; break; default: role_text_menuitem.Text = "Role: Null"; break; } }
private void CourseAddProcess_Load(object sender, EventArgs e) { for (int i = 0; i < students.Count; i++) { DataRow dr = students_dt.NewRow(); dr["state"] = "Ready"; dr["student"] = students[i].ToString(); dr["teacher"] = teacher; dr["course"] = course; dr["year"] = year; students_dt.Rows.Add(dr); } BindingSource bs = new BindingSource(); bs.DataSource = students_dt; dgv.DataSource = bs; dgv.Font = new Font("微软雅黑", 10.8F); string[] head_texts = { "Student Info", "Teacher Info", "Course Info", "Chosen year" }; string[] column_names = { "student", "teacher", "course", "year" }; for (int i = 1; i < dgv.Columns.Count; i++) { dgv.Columns[i].HeaderText = head_texts[i - 1]; dgv.Columns[i].Name = column_names[i - 1]; dgv.Columns[i].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells; } //for (int i = 0; i < dgv.Rows.Count; i++) //{ // dgv.Rows[i].Cells["state"].Style.ForeColor = Color.Goldenrod; // students_dt.Rows[i]["state"] = "Ready"; //} DataTable course_limit = SQL_Help.ExecuteDataTable("select grade_limit,canceled_year from course_info where id=@id", connection, new MySqlParameter[] { new MySqlParameter("@id", MySqlDbType.VarChar) { Value = course.Substring(0, 7) } }); grade_limit = course_limit.Rows[0][0].ToString(); cancel_limit = course_limit.Rows[0][1].ToString(); if (DateTime.Now.Month <= 8) { school_year_label.Text = "School year: " + (DateTime.Now.Year - 1) + "-" + DateTime.Now.Year; if (DateTime.Now.Month <= 2) { semester_label.Text = "Semester: 1"; } else { semester_label.Text = "Semester: 2"; } } else { school_year_label.Text = "School year: " + DateTime.Now.Year + "-" + (DateTime.Now.Year + 1); semester_label.Text = "Semester: 1"; } }
private void dgv_CellContentClick(object sender, DataGridViewCellEventArgs e) { if (e.RowIndex >= 0 && dgv.Rows[e.RowIndex].Cells[e.ColumnIndex] is DataGridViewButtonCell) { if (dgv.Columns[e.ColumnIndex].DefaultCellStyle.NullValue.ToString() == "Modify") { using (ChoosingInfoModify cim = new ChoosingInfoModify() { connection = connection, year = dgv.Rows[e.RowIndex].Cells["Chosen Year"].Value.ToString(), score = dgv.Rows[e.RowIndex].Cells["Score"].Value.ToString(), student = string.Format("{0}({1})", dgv.Rows[e.RowIndex].Cells["Student ID"].Value, dgv.Rows[e.RowIndex].Cells["Student Name"].Value), teacher = string.Format("{0}({1})", dgv.Rows[e.RowIndex].Cells["Teacher ID"].Value, dgv.Rows[e.RowIndex].Cells["Teacher Name"].Value), course = string.Format("{0}({1})", dgv.Rows[e.RowIndex].Cells["Course ID"].Value, dgv.Rows[e.RowIndex].Cells["Course Name"].Value), credit = dgv.Rows[e.RowIndex].Cells["Credit"].Value.ToString(), role = Common.UserType.ADMIN }) { cim.ShowDialog(); choosing_clear_button.PerformClick(); choosing_query_button.PerformClick(); } } else if (dgv.Columns[e.ColumnIndex].DefaultCellStyle.NullValue.ToString() == "Delete") { if (Common.ShowChoice("Delete Confirm", string.Format("Are you sure to delete the record ({0},{1},{2},{3})", dgv.Rows[e.RowIndex].Cells["Student Name"].Value, dgv.Rows[e.RowIndex].Cells["Course Name"].Value, dgv.Rows[e.RowIndex].Cells["Teacher Name"].Value, dgv.Rows[e.RowIndex].Cells["Chosen Year"].Value) + "?", MessageBoxIcon.Warning) == DialogResult.Yes) { int delete_result = SQL_Help.ExecuteNonQuery("delete from course_choosing_info where (course_id=@course_id and teacher_id=@teacher_id and student_id=@student_id and chosen_year=@year);", connection, new MySqlParameter[] { new MySqlParameter("@course_id", MySqlDbType.VarChar) { Value = dgv.Rows[e.RowIndex].Cells["Course ID"].Value }, new MySqlParameter("@teacher_id", MySqlDbType.VarChar) { Value = dgv.Rows[e.RowIndex].Cells["Teacher ID"].Value }, new MySqlParameter("@student_id", MySqlDbType.VarChar) { Value = dgv.Rows[e.RowIndex].Cells["Student ID"].Value }, new MySqlParameter("@year", MySqlDbType.Int32) { Value = dgv.Rows[e.RowIndex].Cells["Chosen Year"].Value }, }); if (delete_result > 0) { Common.ShowInfo("Done", "Delete successfully!"); choosing_clear_button.PerformClick(); choosing_query_button.PerformClick(); } } } } }
private void CourseAverageQueryDialog_Load(object sender, EventArgs e) { DataTable course_dt = SQL_Help.ExecuteDataTable("select distinct id,name from course_info;", connection); for (int i = 0; i < course_dt.Rows.Count; i++) { course_comboBox.Items.Add(string.Format("{0}({1})", course_dt.Rows[i][0], course_dt.Rows[i][1])); } course_comboBox.Text = course_comboBox.Items[0].ToString(); }
public void QueryForLogin() { using (MySqlConnection connection = SQL_Help.getConnection(Common.UserType.STUDENT)) { connection.Open(); LoginResult(SQL_Help.ExecuteDataTable("select authority from user_data where username = @name and password = SHA1(@pwd)", connection, new MySqlParameter[] { new MySqlParameter("@name", username.Text), new MySqlParameter("@pwd", password.Text) })); connection.Close(); } }
private void teacher_apply_button_Click(object sender, EventArgs e) { if (teacher_new_id_textBox.Text.Length == 0 || teacher_new_name_textBox.Text.Length == 0) { Common.ShowError("Format error!", "All the field should not be null! Please check again!"); return; } if (!Regex.IsMatch(teacher_new_id_textBox.Text, @"^\d{5}$")) { Common.ShowError("Format checking error!", "ID format error! The length of teacher ID should be 5!"); return; } if (teacher_new_name_textBox.TextLength == 0) { Common.ShowError("Format checking error!", "Name format error! Teacher name cannot be empty!"); return; } MySqlParameter teacher_id_parameter = new MySqlParameter("@id", MySqlDbType.VarChar) { Value = teacher_current_id_textBox.Text }; MySqlParameter teacher_new_id_parameter = new MySqlParameter("@new_id", MySqlDbType.VarChar) { Value = teacher_new_id_textBox.Text }; int id_modify_result = SQL_Help.ExecuteNonQuery("update teacher_info set id=@new_id,name=@new_name where id=@id;", connection, new MySqlParameter[] { teacher_id_parameter, teacher_new_id_parameter, new MySqlParameter("@new_name", MySqlDbType.VarChar) { Value = teacher_new_name_textBox.Text } }); if (id_modify_result < 0) { return; } if (teacher_new_id_textBox.Text == teacher_current_id_textBox.Text) { Common.ShowInfo("Done!", "Modify successfully!"); teacher_query_button.PerformClick(); return; } int other_modify_result = SQL_Help.ExecuteNonQuery("update user_data set username=@new_id where username=@id;update course_info set teacher_id=@new_id where teacher_id=@id;", connection, new MySqlParameter[] { teacher_new_id_parameter, teacher_id_parameter }); if (other_modify_result >= 0) { Common.ShowInfo("Done!", "Modify successfully!"); teacher_current_id_textBox.Text = teacher_new_id_textBox.Text; teacher_query_button.PerformClick(); } }
private void queryStudentInformationToolStripMenuItem_Click(object sender, EventArgs e) { ClearDGV(); string condition = ""; string basic_query_command = "select id as 'Student ID',name as 'Student Name',sex as 'Sex',entrance_age as 'Entrance Age',entrance_year as 'Entrance Year',class as 'Class' from student_info"; if (role == Common.UserType.STUDENT) { info_dt = SQL_Help.ExecuteDataTable(basic_query_command + " where id=@id;", connection, new MySqlParameter[] { new MySqlParameter("@id", MySqlDbType.VarChar) { Value = current_user_name } }); } else { condition = Common.ShowInput("Please enter student ID or name: ", "Query for student info", "Empty for querying all student."); if (condition == null) { return; } if (condition.Length == 0) { info_dt = SQL_Help.ExecuteDataTable(basic_query_command + ";", connection); } else { info_dt = SQL_Help.ExecuteDataTable(basic_query_command + " where(id=@condition or name=@condition);", connection, new MySqlParameter[] { new MySqlParameter("@condition", MySqlDbType.VarChar) { Value = condition } }); } } if (info_dt.Rows.Count == 0) { Common.ShowError("Empty query result", "No such student found with ID or name is " + condition + "!"); return; } dgv.DataSource = new BindingSource() { DataSource = info_dt }; DataColumn show_role_column = new DataColumn("Role") { Caption = "Role", DefaultValue = "Student" }; info_dt.Columns.Add(show_role_column); }
private void ChooseClassDialog_Load(object sender, EventArgs e) { DataTable class_dt = SQL_Help.ExecuteDataTable("select distinct class from student_info;", connection); for (int i = 0; i < class_dt.Rows.Count; i++) { comboBox1.Items.Add(class_dt.Rows[i][0]); } comboBox1.Items.Add("All Class"); comboBox1.Text = comboBox1.Items[0].ToString(); }
private void Add_Course_Load(object sender, EventArgs e) { DataTable teacher_dt = SQL_Help.ExecuteDataTable("select id,name from teacher_info;", connection); for (int i = 0; i < teacher_dt.Rows.Count; i++) { string comboBox_item = teacher_dt.Rows[i][0] + "(" + teacher_dt.Rows[i][1] + ")"; teacher_id_ComboBox.Items.Add(comboBox_item); teacher_list.Add(comboBox_item); } }
private void singleStudentToolStripMenuItem_Click(object sender, EventArgs e) { ClearDGV(); string condition = ""; string basic_query_command = "select student_id as 'Student ID',name as 'Student Name',round(sum(cs)/sum(credit),2) as 'Weighted average score' from " + "(select distinct student_id,credit*score as cs,credit,course_id,chosen_year,course_choosing_info.teacher_id " + "from course_choosing_info join course_info on (course_choosing_info.course_id=course_info.id) " + "where course_choosing_info.score is not null) as a join student_info on (a.student_id=student_info.id) "; string basic_query_command_end = " group by student_id;"; if (role == Common.UserType.STUDENT) { info_dt = SQL_Help.ExecuteDataTable(basic_query_command + " where student_id=@id" + basic_query_command_end, connection, new MySqlParameter[] { new MySqlParameter("@id", MySqlDbType.VarChar) { Value = current_user_name } }); } else { condition = Common.ShowInput("Please enter student ID or name: ", "Query for student info", "Empty for querying all student."); if (condition == null) { return; } if (condition.Length == 0) { info_dt = SQL_Help.ExecuteDataTable(basic_query_command + basic_query_command_end, connection); } else { info_dt = SQL_Help.ExecuteDataTable(basic_query_command + " where(student_id=@condition or name=@condition)" + basic_query_command_end, connection, new MySqlParameter[] { new MySqlParameter("@condition", MySqlDbType.VarChar) { Value = condition } }); } } if (info_dt.Rows.Count == 0) { Common.ShowError("Empty query result", "No such student found with ID or name is " + condition + "!"); return; } dgv.DataSource = new BindingSource() { DataSource = info_dt }; }
//public static Dictionary<string, string> choosing_data_table_columns_name_mapping = new Dictionary<string, string>() //{ // ["chosen_year"] = "Chosen Year", // ["score"] = "Score", // ["student_id"] = "Student", // ["teacher_id"] = "Teacher", // ["course_id"] = "Course", // ["name"] = "Name", // ["name2"] = "Course Name", // ["name1"] = "Teacher Name", // ["credit"] = "Credit" //}; private void choosing_query_button_Click(object sender, EventArgs e) { choosing_query_button.Enabled = false; if (choosing_student_id_textbox.Text.Length == 0) { Common.ShowError("Null value error", "Student ID can not be null!"); return; } student_choosing_dt = SQL_Help.ExecuteDataTable( "select distinct course_choosing_info.course_id as 'Course ID',course_info.name as 'Course Name',course_choosing_info.student_id as 'Student ID',student_info.name as 'Student Name',course_choosing_info.teacher_id as 'Teacher ID',teacher_info.name as 'Teacher Name',course_choosing_info.chosen_year as 'Chosen Year',course_choosing_info.score as 'Score',course_info.credit as 'Credit' " + "from course_choosing_info join course_info join student_info join teacher_info " + "on (course_choosing_info.course_id=course_info.id and course_choosing_info.teacher_id=teacher_info.id and course_choosing_info.student_id=student_info.id) " + "where course_choosing_info.student_id = @sid;", connection, new MySqlParameter[] { new MySqlParameter("@sid", MySqlDbType.VarChar) { Value = choosing_student_id_textbox.Text } }); //student_choosing_dt.Columns["chosen_year"].SetOrdinal(4); //student_choosing_dt.Columns["score"].SetOrdinal(3); if (student_choosing_dt.Rows.Count == 0) { Common.ShowError("Empty query result error", "No records found with student_id = " + choosing_student_id_textbox.Text + "!"); choosing_clear_button.PerformClick(); return; } dgv.DataSource = new BindingSource() { DataSource = student_choosing_dt }; //for (int i = 0; i < student_choosing_dt.Columns.Count; i++) //{ // dgv.Columns[i].HeaderText = choosing_data_table_columns_name_mapping[student_choosing_dt.Columns[i].Caption]; //} //dgv.Columns["score"].DisplayIndex = dgv.Columns.Count - 1; //dgv.Columns["chosen_year"].DisplayIndex = dgv.Columns.Count - 1; DataGridViewButtonColumn modify_btn = new DataGridViewButtonColumn(); modify_btn.HeaderText = "Modify"; modify_btn.Name = "modify"; modify_btn.DefaultCellStyle.NullValue = "Modify"; dgv.Columns.Add(modify_btn); dgv.Columns["modify"].DisplayIndex = 0; DataGridViewButtonColumn delete_btn = new DataGridViewButtonColumn(); delete_btn.HeaderText = "Delete"; delete_btn.Name = "delete"; delete_btn.DefaultCellStyle.NullValue = "Delete"; dgv.Columns.Add(delete_btn); dgv.Columns["delete"].DisplayIndex = 1; }
private void studentsInSameClassToolStripMenuItem_Click(object sender, EventArgs e) { ClearDGV(); string basic_query_command = "select class as 'Class',round(avg(was),2) as \"Class Weighted Average Score\" " + "from (select student_id as 'Student ID',name as 'Student Name',round(sum(cs)/sum(credit),2) as was,class " + "from (select distinct student_id,credit*score as cs,credit,course_id,chosen_year,course_choosing_info.teacher_id " + "from course_choosing_info join course_info on (course_choosing_info.course_id=course_info.id) " + "where course_choosing_info.score is not null) as a join student_info on (a.student_id=student_info.id) " + "where student_id in (select student_info.id from student_info "; string basic_query_command_end = " ) group by student_id) as b group by class;"; using (ChooseClassDialog ccd = new ChooseClassDialog() { connection = connection }) { if (ccd.ShowDialog() == DialogResult.OK) { if (ccd.result != "All Class") { info_dt = SQL_Help.ExecuteDataTable(basic_query_command + "where class=@class" + basic_query_command_end, connection, new MySqlParameter[] { new MySqlParameter("@class", MySqlDbType.VarChar) { Value = ccd.result } }); } else { info_dt = SQL_Help.ExecuteDataTable(basic_query_command + basic_query_command_end, connection); } } else { return; } } if (info_dt.Rows.Count == 0) { Common.ShowError("Empty query result", "Class not found or this class has no score information can be queried!"); return; } dgv.DataSource = new BindingSource() { DataSource = info_dt }; }
private void student_delete_button_Click(object sender, EventArgs e) { if (Common.ShowChoice("Delete Confirm", string.Format("Are you sure to delete the student ({0},{1})", student_current_id_textBox.Text, student_current_name_textBox.Text) + "?\nAll information about the student in the database will be deleted", MessageBoxIcon.Warning) == DialogResult.Yes) { int delete_result = SQL_Help.ExecuteNonQuery("delete from student_info where id=@id;delete from user_data where username=@id;", connection, new MySqlParameter[] { new MySqlParameter("@id", MySqlDbType.VarChar) { Value = student_current_id_textBox.Text } }); if (delete_result > 0) { Common.ShowInfo("Done", "Delete successfully!"); student_clear_button.PerformClick(); } } }
private void add_process() { int add_result; int year_lower_bound = Convert.ToInt32(grade_limit) - 1; int year_upper_bound = (cancel_limit == "") ? int.MaxValue : Convert.ToInt32(cancel_limit); for (int i = 0; i < dgv.Rows.Count; i++) { DataTable entry_year_dt = SQL_Help.ExecuteDataTable("select entrance_year from student_info where id=@id;", connection, new MySqlParameter[] { new MySqlParameter("@id", MySqlDbType.VarChar) { Value = students[i].ToString().Substring(0, 10) } }); int entry_year = Convert.ToInt32(entry_year_dt.Rows[0][0]); bool lower_bound_satisfied = Convert.ToInt32(year) - entry_year >= year_lower_bound; bool upper_bound_satisfied = Convert.ToInt32(year) <= year_upper_bound; if (!lower_bound_satisfied || !upper_bound_satisfied) { string error_msg = "Students: " + students[i] + ":"; error_msg += (lower_bound_satisfied ? "" : "\nThis course is only available for students whose grade is larger than " + grade_limit + ".") + (upper_bound_satisfied ? "" : "\nThis course is only available before " + cancel_limit + "."); Common.ShowError("Time error!", error_msg); add_result = -1; } else { add_result = SQL_Help.ExecuteNonQuery("insert into course_choosing_info (student_id,teacher_id,course_id,chosen_year) values(@1,@2,@3,@4);", connection, new MySqlParameter[] { new MySqlParameter("@1", MySqlDbType.VarChar) { Value = dgv.Rows[i].Cells["student"].Value.ToString().Substring(0, 10) }, new MySqlParameter("@2", MySqlDbType.VarChar) { Value = dgv.Rows[i].Cells["teacher"].Value.ToString().Substring(0, 5) }, new MySqlParameter("@3", MySqlDbType.VarChar) { Value = dgv.Rows[i].Cells["course"].Value.ToString().Substring(0, 7) }, new MySqlParameter("@4", MySqlDbType.Int32) { Value = dgv.Rows[i].Cells["year"].Value.ToString() } }); } SetAddProgress(i, add_result > 0 ? "Done" : "Failed"); } }
private void SubmitScore_Load(object sender, EventArgs e) { DataTable course_dt = SQL_Help.ExecuteDataTable("select id,name from course_info where teacher_id=@teacher_id;", connection, new MySqlParameter[] { new MySqlParameter("@teacher_id", MySqlDbType.VarChar) { Value = current_teacher_id } }); for (int i = 0; i < course_dt.Rows.Count; i++) { string course_item = string.Format("{0}({1})", course_dt.Rows[i][0], course_dt.Rows[i][1]); course_list_items.Add(course_item); course_comboBox.Items.Add(course_item); } }
private void submitScoreToolStripMenuItem_Click(object sender, EventArgs e) { DataTable teacher_dt = SQL_Help.ExecuteDataTable("select id,name from teacher_info where id=@id;", connection, new MySqlParameter[] { new MySqlParameter("@id", MySqlDbType.VarChar) { Value = user_text_menuitem.Text.Substring(user_text_menuitem.Text.Length - 5, 5) } }); using (SubmitScore ss = new SubmitScore(teacher_dt.Rows[0][0].ToString(), teacher_dt.Rows[0][1].ToString()) { connection = connection }) { ss.ShowDialog(); } }
private void apply_Click(object sender, EventArgs e) { DataTable dt = SQL_Help.ExecuteDataTable("select authority from user_data where username = @name and password = SHA1(@pwd)", connection, new MySqlParameter[] { new MySqlParameter("@name", username), new MySqlParameter("@pwd", old_pwd.Text) }); if (dt.Rows.Count == 0) { Common.ShowError("Password Changing Error", "Old Password is not correct!"); } else { SQL_Help.ExecuteNonQuery("update user_data set password = SHA1(@pwd) where username = @name", connection, new MySqlParameter[] { new MySqlParameter("@name", username), new MySqlParameter("@pwd", new_pwd.Text) }); Common.ShowInfo("Done!", "Password changing successful!"); DialogResult = DialogResult.OK; Close(); } }
private void course_id_ComboBox_TextChanged(object sender, EventArgs e) { teacher_id_comboBox.Items.Clear(); int index = course_combobox_items.IndexOf(course_id_ComboBox.Text); if (index != -1) { DataTable teacher_dt = SQL_Help.ExecuteDataTable("select id,name from teacher_info where id in (select teacher_id from course_info where id=@id);", connection, new MySqlParameter[] { new MySqlParameter("@id", MySqlDbType.VarChar) { Value = course_id_ComboBox.Text.Substring(0, 7) } }); for (int i = 0; i < teacher_dt.Rows.Count; i++) { teacher_id_comboBox.Items.Add(teacher_dt.Rows[i][0].ToString() + "(" + teacher_dt.Rows[i][1].ToString() + ")"); } teacher_id_comboBox.Text = teacher_id_comboBox.Items[0].ToString(); //teacher_id_textBox.Text = course_dt.Rows[index][2] + "("+ teacher_dt.Rows[0][0].ToString()+")"; } }
private void course_query_button_Click(object sender, EventArgs e) { if (course_current_id_textBox.Text.Length == 0) { Common.ShowError("Null value error!", "ID can not be empty!"); return; } course_current_id_textBox.ReadOnly = true; DataTable current_info_dt = SQL_Help.ExecuteDataTable("select * from course_info where id=@id;", connection, new MySqlParameter[] { new MySqlParameter("@id", MySqlDbType.VarChar) { Value = course_current_id_textBox.Text } }); if (current_info_dt.Rows.Count == 0) { Common.ShowError("Empty query result!", "No corresponding course found with id = " + course_current_id_textBox.Text + ".\nPlease check again."); course_current_id_textBox.ReadOnly = false; } else { course_new_name_textBox.Text = course_current_name_textBox.Text = current_info_dt.Rows[0][1].ToString(); //course_new_teacher_comboBox.Text = course_current_teacher_comboBox.Text = current_info_dt.Rows[0][2].ToString() + string.Format("({0})", course_teacher_mapping[current_info_dt.Rows[0][2].ToString()]); for (int i = 0; i < current_info_dt.Rows.Count; i++) { course_current_teacher_comboBox.Items.Add(current_info_dt.Rows[i][2].ToString() + string.Format("({0})", course_teacher_mapping[current_info_dt.Rows[i][2].ToString()])); } course_new_teacher_comboBox.Text = course_current_teacher_comboBox.Text = course_current_teacher_comboBox.Items[0].ToString(); course_current_teacher_comboBox.Enabled = true; course_new_credit_textBox.Text = course_current_credit_textBox.Text = current_info_dt.Rows[0][3].ToString(); course_new_year_textBox.Text = course_current_year_textBox.Text = current_info_dt.Rows[0][5].ToString(); canceled_year_textbox_has_real_text = course_current_year_textBox.Text.Length != 0; if (canceled_year_textbox_has_real_text) { course_new_year_textBox.ForeColor = Color.Black; } course_new_grade_comboBox.Text = course_current_grade_textBox.Text = current_info_dt.Rows[0][4].ToString(); course_new_id_textBox.Text = course_current_id_textBox.Text; course_new_info_groupBox.Enabled = true; course_apply_button.Enabled = true; course_delete_button.Enabled = true; course_delete_record_button.Enabled = true; } }
private void singleCourseToolStripMenuItem_Click(object sender, EventArgs e) { using (CourseAverageQueryDialog caqd = new CourseAverageQueryDialog() { connection = connection }) { if (caqd.ShowDialog() == DialogResult.OK) { info_dt = SQL_Help.ExecuteDataTable("select course_id as 'Course ID',course_info.name as 'Course Name',course_choosing_info.teacher_id as 'Teacher ID',teacher_info.name as 'Teacher Name',chosen_year as 'Chosen year',round(avg(score),2) as 'Course Average Score' " + "from course_choosing_info join teacher_info join course_info on (course_choosing_info.course_id=course_info.id and course_choosing_info.teacher_id=teacher_info.id) " + "where course_id=@cid and course_choosing_info.teacher_id=@tid and chosen_year=@year and score is not null;", connection, new MySqlParameter[] { new MySqlParameter("@cid", MySqlDbType.VarChar) { Value = caqd.course.Substring(0, 7) }, new MySqlParameter("@tid", MySqlDbType.VarChar) { Value = caqd.teacher.Substring(0, 5) }, new MySqlParameter("@year", MySqlDbType.Int32) { Value = caqd.year } }); } else { return; } } if (info_dt.Rows.Count == 0) { Common.ShowError("Empty query result", "Course not found or this course has no score information can be queried!"); return; } dgv.DataSource = new BindingSource() { DataSource = info_dt }; }
private void teacher_delete_button_Click(object sender, EventArgs e) { string delete_notice = ((teacher_courses_textBox.TextLength == 0) ? "" : "This teacher still have some course! \nIf continue, all the information about the courses that this teacher teach will be deleted!") + string.Format("Are you sure to delete teacher ({0},{1})?", teacher_current_id_textBox.Text, teacher_current_name_textBox.Text); if (Common.ShowChoice("Delete Confirm", delete_notice, MessageBoxIcon.Warning) == DialogResult.Yes) { MySqlParameter teacher_id_parameter = new MySqlParameter("@id", MySqlDbType.VarChar) { Value = teacher_current_id_textBox.Text }; if (SQL_Help.ExecuteNonQuery("delete from teacher_info where id=@id;delete from course_info where teacher_id=@id;delete from user_data where username=@id;", connection, new MySqlParameter[] { teacher_id_parameter }) > 0) { Common.ShowInfo("Done", "Delete successfully!"); teacher_clear_button.PerformClick(); } } }