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 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 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 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 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 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();
}
}
}
private void button1_Click(object sender, EventArgs e)
{
if (textBox1.Text.Length == 0)
{
Common.ShowError("Error!", "Username can not be empty!");
return;
}
if (SQL_Help.ExecuteDataTable("select authority from user_data where username = @name;", connection, new MySqlParameter[] { new MySqlParameter("@name", textBox1.Text) }).Rows[0][0].ToString() == ((int)Common.UserType.ADMIN).ToString())
{
Common.ShowError("Failed!", "Operation denied: Administrator password can not be reset!\nIf you want to reset it, please contact the database administrator.");
return;
}
if (Common.ShowChoice("Password Reset Confirm", "Password for user:"******" will be set to " + default_password + "\nAre you sure to continue?") == DialogResult.Yes)
{
if (SQL_Help.ExecuteNonQuery("update user_data set password=SHA1(@1) where username=@2;",
connection, new MySqlParameter[] { new MySqlParameter("@1", default_password), new MySqlParameter("@2", textBox1.Text) }) > 0)
{
Common.ShowInfo("Done", "Reset Successful!");
}
}
}
private void course_delete_record_button_Click(object sender, EventArgs e)
{
if (Common.ShowChoice("Delete Confirm", string.Format("Are you sure to delete the course record ({0},{1},{2})", course_current_id_textBox.Text, course_current_name_textBox.Text, course_current_teacher_comboBox.Text) + "?", MessageBoxIcon.Warning) == DialogResult.Yes)
{
DeleteCourseFromOldTeacher(course_current_teacher_comboBox.Text.Substring(0, 5), course_current_id_textBox.Text);
int delete_result = SQL_Help.ExecuteNonQuery("delete from course_info where (id=@id and teacher_id=@teacher_id);", connection, new MySqlParameter[] {
new MySqlParameter("@id", MySqlDbType.VarChar)
{
Value = course_current_id_textBox.Text
},
new MySqlParameter("@teacher_id", MySqlDbType.VarChar)
{
Value = course_current_teacher_comboBox.Text.Substring(0, 5)
},
});
if (delete_result > 0)
{
Common.ShowInfo("Done", "Delete successfully!");
course_clear_button.PerformClick();
}
}
}
private void course_delete_button_Click(object sender, EventArgs e)
{
if (Common.ShowChoice("Delete Confirm", string.Format("Are you sure to delete the course ({0},{1})", course_current_id_textBox.Text, course_current_name_textBox.Text) + "?\nAll records about the course in the database will be deleted.", MessageBoxIcon.Warning) == DialogResult.Yes)
{
foreach (DataRow item in SQL_Help.ExecuteDataTable("select teacher_id from course_info where id=@id;", connection, new MySqlParameter[] { new MySqlParameter("@id", MySqlDbType.VarChar)
{
Value = course_current_id_textBox.Text
} }).Rows)
{
DeleteCourseFromOldTeacher(item[0].ToString(), course_current_id_textBox.Text);
}
int delete_result = SQL_Help.ExecuteNonQuery("delete from course_info where id=@id;", connection, new MySqlParameter[]
{ new MySqlParameter("@id", MySqlDbType.VarChar)
{
Value = course_current_id_textBox.Text
} });
if (delete_result > 0)
{
Common.ShowInfo("Done", "Delete successfully!");
course_clear_button.PerformClick();
}
}
}
private void add_button_Click(object sender, EventArgs e)
{
if (student_radioButton.Checked)
{
if (id_textbox.Text.Length == 0 || name_textbox.Text.Length == 0 ||
sex_comboBox.Text.Length == 0 || age_textbox.Text.Length == 0 ||
year_textbox.Text.Length == 0 || class_textBox.Text.Length == 0)
{
Common.ShowError("Format error!", "All the field should not be null! Please check again!");
return;
}
if (!Regex.IsMatch(id_textbox.Text, @"^\d{10}$"))
{
Common.ShowError("Format checking error!", "ID format error! The length of student ID should be 10!");
return;
}
if (!Regex.IsMatch(age_textbox.Text, @"^\d{2}$") && int.Parse(age_textbox.Text) >= 10 && int.Parse(age_textbox.Text) <= 50)
{
Common.ShowError("Format checking error!", "Entrance age format error! It should between 10 and 50!");
return;
}
if (!Regex.IsMatch(year_textbox.Text, @"^\d{4}$"))
{
Common.ShowError("Format error!", "Entrance year format error! \nPlease chech again!");
return;
}
int add_result = SQL_Help.ExecuteNonQuery("insert into user_data (username,authority) values(@1,0);" +
"insert into student_info values(@1,@2,@3,@4,@5,@6);", connection, new MySqlParameter[]
{ new MySqlParameter("@1", MySqlDbType.VarChar)
{
Value = id_textbox.Text
}, new MySqlParameter("@2", MySqlDbType.VarChar)
{
Value = name_textbox.Text
},
new MySqlParameter("@3", MySqlDbType.VarChar)
{
Value = sex_comboBox.Text
}, new MySqlParameter("@4", MySqlDbType.Int32)
{
Value = age_textbox.Text
},
new MySqlParameter("@5", MySqlDbType.Int32)
{
Value = year_textbox.Text
}, new MySqlParameter("@6", MySqlDbType.VarChar)
{
Value = class_textBox.Text
} });
if (add_result >= 0)
{
Common.ShowInfo("Done", "Add successful!");
}
}
else
{
if (id_textbox.Text.Length == 0 || name_textbox.Text.Length == 0)
{
Common.ShowError("Add error!", "All the field should not be null! Please check again!");
return;
}
if (!Regex.IsMatch(id_textbox.Text, @"^\d{5}$"))
{
Common.ShowError("Format checking error!", "ID format error! The length of teacher ID should be 5!");
return;
}
int add_result = SQL_Help.ExecuteNonQuery("insert into user_data (username,authority) values(@1,1);" +
"insert into teacher_info (id,name) values(@1,@2);", connection, new MySqlParameter[]
{ new MySqlParameter("@1", MySqlDbType.VarChar)
{
Value = id_textbox.Text
}, new MySqlParameter("@2", MySqlDbType.VarChar)
{
Value = name_textbox.Text
} });
if (add_result >= 0)
{
Common.ShowInfo("Done", "Add successful!");
}
}
}
private void batch_add_process()
{
for (int i = 0; i < dgv.Rows.Count; i++)
{
bool format_check = true;
int add_result;
if (!Regex.IsMatch(dgv.Rows[i].Cells["id"].Value.ToString(), @"^\d{10}$"))
{
Common.ShowError("Format checking error!", "ID format error! The length of student ID should be 10!\nID: " + dgv.Rows[i].Cells["id"].Value);
format_check = false;
}
if (!Regex.IsMatch(dgv.Rows[i].Cells["age"].Value.ToString(), @"^\d{2}$") && int.Parse(dgv.Rows[i].Cells["age"].Value.ToString()) >= 10 && int.Parse(dgv.Rows[i].Cells["age"].Value.ToString()) <= 50)
{
Common.ShowError("Format checking error!", "Entrance age format error! It should between 10 and 50!\nID: " + dgv.Rows[i].Cells["id"].Value);
format_check = false;
}
if (dgv.Rows[i].Cells["sex"].Value.ToString() != "Male" && dgv.Rows[i].Cells["sex"].Value.ToString() != "Female")
{
Common.ShowError("Format checking error!", "Sex format error! It should be either \"Male\" or \"Female\"!\nID: " + dgv.Rows[i].Cells["id"].Value);
format_check = false;
}
if (!Regex.IsMatch(dgv.Rows[i].Cells["year"].Value.ToString(), @"^\d{4}$"))
{
Common.ShowError("Format error!", "Entrance year format error! \nPlease chech again!");
format_check = false;
}
if (format_check)
{
add_result = SQL_Help.ExecuteNonQuery("insert into user_data (username,authority) values(@1,0);" +
"insert into student_info values(@1,@2,@3,@4,@5,@6);", connection, new MySqlParameter[]
{ new MySqlParameter("@1", MySqlDbType.VarChar)
{
Value = dgv.Rows[i].Cells["id"].Value
},
new MySqlParameter("@2", MySqlDbType.VarChar)
{
Value = dgv.Rows[i].Cells["name"].Value
},
new MySqlParameter("@3", MySqlDbType.VarChar)
{
Value = dgv.Rows[i].Cells["sex"].Value
},
new MySqlParameter("@4", MySqlDbType.Int32)
{
Value = dgv.Rows[i].Cells["age"].Value
},
new MySqlParameter("@5", MySqlDbType.Int32)
{
Value = dgv.Rows[i].Cells["year"].Value
},
new MySqlParameter("@6", MySqlDbType.VarChar)
{
Value = dgv.Rows[i].Cells["classname"].Value
} });
}
else
{
add_result = -1;
}
if (add_result > 0)
{
SetAddProgress(i, "Done");
}
else
{
SetAddProgress(i, "Failed");
}
}
}
private void apply_button_Click(object sender, EventArgs e)
{
if (choosing_new_year_textBox.Text.Length != 0 && !Regex.IsMatch(choosing_new_year_textBox.Text, @"^\d{4}$"))
{
Common.ShowError("Format error!", "Chosen year format error! \nPlease chech again!");
return;
}
bool confirm = true;
if (choosing_current_credit_textBox.Text != choosing_new_credit_textBox.Text)
{
confirm = Common.ShowChoice("Modify confirm", "The credits of the two courses are inconsistent! Continue?", MessageBoxIcon.Warning) == DialogResult.Yes;
}
if (confirm)
{
MySqlParameter score_parameter = new MySqlParameter("@score", MySqlDbType.Int32)
{
Value = DBNull.Value
};
if (choosing_new_score_textBox.Text.Length != 0)
{
if (!Regex.IsMatch(choosing_new_score_textBox.Text, @"^\d*$") && int.Parse(choosing_new_score_textBox.Text) >= 0 && int.Parse(choosing_new_score_textBox.Text) <= 100)
{
Common.ShowError("Score format error!", "Course score should be integer between 0 and 100! Please check again!");
return;
}
else
{
score_parameter.Value = choosing_new_score_textBox.Text;
}
}
int update_result = SQL_Help.ExecuteNonQuery("update course_choosing_info set teacher_id=@new_teacher_id,course_id=@new_course_id,chosen_year=@new_year,score=@score " +
"where (student_id=@student_id and teacher_id=@old_teacher_id and course_id=@old_course_id and chosen_year=@old_year);", connection, new MySqlParameter[]
{
new MySqlParameter("@new_teacher_id", MySqlDbType.VarChar)
{
Value = choosing_new_teacher_comboBox.Text.Substring(0, 5)
},
new MySqlParameter("@new_course_id", MySqlDbType.VarChar)
{
Value = choosing_new_course_ComboBox.Text.Substring(0, 7)
},
new MySqlParameter("@new_year", MySqlDbType.Int32)
{
Value = choosing_new_year_textBox.Text
},
new MySqlParameter("@student_id", MySqlDbType.VarChar)
{
Value = choosing_new_id_textBox.Text.Substring(0, 10)
},
new MySqlParameter("@old_teacher_id", MySqlDbType.VarChar)
{
Value = choosing_current_teacher_textBox.Text.Substring(0, 5)
},
new MySqlParameter("@old_course_id", MySqlDbType.VarChar)
{
Value = choosing_current_course_textBox.Text.Substring(0, 7)
},
new MySqlParameter("@old_year", MySqlDbType.Int32)
{
Value = choosing_current_year_textBox.Text
},
score_parameter,
});
if (update_result > 0)
{
Common.ShowInfo("Done!", "Modify successfully!");
Close();
}
}
}
private void student_apply_button_Click(object sender, EventArgs e)
{
if (student_new_id_textBox.Text.Length == 0 || student_new_name_textBox.Text.Length == 0 ||
student_new_age_textBox.Text.Length == 0 ||
student_new_year_textBox.Text.Length == 0 || student_new_class_comboBox.Text.Length == 0)
{
Common.ShowError("Format error!", "All the field should not be null! Please check again!");
return;
}
if (!Regex.IsMatch(student_new_id_textBox.Text, @"^\d{10}$"))
{
Common.ShowError("Format checking error!", "ID format error! The length of student ID should be 10!");
return;
}
if (!Regex.IsMatch(student_new_age_textBox.Text, @"^\d{2}$") && int.Parse(student_new_age_textBox.Text) >= 10 && int.Parse(student_new_age_textBox.Text) <= 50)
{
Common.ShowError("Format checking error!", "Entrance age format error! It should between 10 and 50!");
return;
}
if (!Regex.IsMatch(student_new_year_textBox.Text, @"^\d{4}$"))
{
Common.ShowError("Format error!", "Entrance year format error! \nPlease chech again!");
return;
}
int update_result = SQL_Help.ExecuteNonQuery("update student_info set id=@new_id,name=@new_name,sex=@new_sex,entrance_age=@new_age,entrance_year=@new_year,class=@new_class where id=@old_id;update user_data set username=@new_id where username=@old_id", connection, new MySqlParameter[]
{
new MySqlParameter("@new_id", MySqlDbType.VarChar)
{
Value = student_new_id_textBox.Text
},
new MySqlParameter("@new_name", MySqlDbType.VarChar)
{
Value = student_new_name_textBox.Text
},
new MySqlParameter("@new_age", MySqlDbType.Int32)
{
Value = student_new_age_textBox.Text
},
new MySqlParameter("@new_sex", MySqlDbType.VarChar)
{
Value = student_new_sex_comboBox.Text
},
new MySqlParameter("@new_year", MySqlDbType.Int32)
{
Value = student_new_year_textBox.Text
},
new MySqlParameter("@new_class", MySqlDbType.VarChar)
{
Value = student_new_class_comboBox.Text
},
new MySqlParameter("@old_id", MySqlDbType.VarChar)
{
Value = student_current_id_textBox.Text
}
});
if (update_result > 0)
{
Common.ShowInfo("Done!", "Modify successfully!");
student_current_id_textBox.Text = student_new_id_textBox.Text;
student_query_button.PerformClick();
}
}
private void course_apply_button_Click(object sender, EventArgs e)
{
course_delete_record_button.Enabled = false;
course_delete_button.Enabled = false;
if (course_new_id_textBox.Text.Length == 0 || course_new_name_textBox.Text.Length == 0 ||
course_new_teacher_comboBox.Text.Length == 0 || course_new_credit_textBox.Text.Length == 0 ||
course_new_grade_comboBox.Text.Length == 0)
{
Common.ShowError("Null Value Error", "Please ensure that all new fields except cancelled-year are not null!");
return;
}
if (!Regex.IsMatch(course_new_id_textBox.Text, @"^\d{7}$"))
{
Common.ShowError("Format error!", "ID format error! \nThe length of course ID should be 7!");
return;
}
if (!course_new_teacher_comboBox.Items.Contains(course_new_teacher_comboBox.Text))
{
Common.ShowError("Format error!", "Teacher ID format error! \nPlease select the correct item from the teacher combo list!");
return;
}
if (!Regex.IsMatch(course_new_credit_textBox.Text, @"^(([1-9]{1}\d*)|([0]{1}))(\.(\d)?)?$"))
{
Common.ShowError("Format error!", "Credit format error! \nThe credit should be integer or one place decimal!");
return;
}
if (course_new_year_textBox.Text.Length != 0 && !Regex.IsMatch(course_new_year_textBox.Text, @"^\d{4}$"))
{
Common.ShowError("Format error!", "Canceled year format error! \nPlease chech again!");
return;
}
if (SQL_Help.ExecuteDataTable("select id from course_info where (not id=@old and id=@new);", connection, new MySqlParameter[]
{
new MySqlParameter("@old", MySqlDbType.VarChar)
{
Value = course_current_id_textBox.Text
},
new MySqlParameter("@new", MySqlDbType.VarChar)
{
Value = course_new_id_textBox.Text
}
}).Rows.Count != 0)
{
Common.ShowError("ID update error!", string.Format("Duplicate course ID for {0}! Please change to another one.", course_new_id_textBox.Text));
return;
}
MySqlParameter year_parameter = new MySqlParameter("@year", MySqlDbType.Int32);
MySqlParameter new_id_parameter = new MySqlParameter("@id", MySqlDbType.VarChar)
{
Value = course_new_id_textBox.Text
};
if (!canceled_year_textbox_has_real_text)
{
year_parameter.Value = DBNull.Value;
}
else
{
year_parameter.Value = course_new_year_textBox.Text;
}
int apply_result_basic_info = SQL_Help.ExecuteNonQuery("update course_info set id=@id,name=@name,credit=@credit,grade_limit=@grade,canceled_year=@year where id=@old_id;", connection, new MySqlParameter[]
{
new_id_parameter,
new MySqlParameter("@name", MySqlDbType.VarChar)
{
Value = course_new_name_textBox.Text
},
new MySqlParameter("@credit", MySqlDbType.Double)
{
Value = course_new_credit_textBox.Text
},
new MySqlParameter("@grade", MySqlDbType.Int32)
{
Value = course_new_grade_comboBox.Text
},
year_parameter,
new MySqlParameter("@old_id", MySqlDbType.VarChar)
{
Value = course_current_id_textBox.Text
},
});
if (apply_result_basic_info <= 0)
{
return;
}
int apply_result_teacher_info = SQL_Help.ExecuteNonQuery("update course_info set teacher_id=@teacher_id where (id=@id and teacher_id=@old_teacher_id);", connection, new MySqlParameter[]
{
new MySqlParameter("@teacher_id", MySqlDbType.VarChar)
{
Value = course_new_teacher_comboBox.Text.Substring(0, 5)
},
new_id_parameter,
new MySqlParameter("@old_teacher_id", MySqlDbType.VarChar)
{
Value = course_current_teacher_comboBox.Text.Substring(0, 5)
}
});
int delete_from_old_result = int.MaxValue, add_to_new_result = int.MaxValue;
delete_from_old_result = DeleteCourseFromOldTeacher(course_current_teacher_comboBox.Text.Substring(0, 5), course_current_id_textBox.Text);
add_to_new_result = AddCourseToNewTeacher(course_new_teacher_comboBox.Text.Substring(0, 5), course_new_id_textBox.Text);
foreach (DataRow item in SQL_Help.ExecuteDataTable("select teacher_id from course_info where id=@id;", connection, new MySqlParameter[] { new_id_parameter }).Rows)
{
if (item[0].ToString() == course_new_teacher_comboBox.Text.Substring(0, 5))
{
continue;
}
DeleteCourseFromOldTeacher(item[0].ToString(), course_current_id_textBox.Text);
AddCourseToNewTeacher(item[0].ToString(), course_new_id_textBox.Text);
}
if (apply_result_basic_info > 0 && apply_result_teacher_info > 0 && delete_from_old_result > 0 && add_to_new_result > 0)
{
Common.ShowInfo("Done!", "Modify successfully!");
course_current_id_textBox.Text = course_new_id_textBox.Text;
course_query_button.PerformClick();
}
}
private void button1_Click(object sender, EventArgs e)
{
if (course_id_textBox.Text.Length == 0 || course_name_textBox.Text.Length == 0 ||
teacher_id_ComboBox.Text.Length == 0 || credit_textBox.Text.Length == 0 ||
grade_comboBox.Text.Length == 0)
{
Common.ShowError("Null Value Error", "Please ensure that the first five fields are not null!");
return;
}
if (!Regex.IsMatch(course_id_textBox.Text, @"^\d{7}$"))
{
Common.ShowError("Format error!", "ID format error! \nThe length of course ID should be 7!");
return;
}
if (!teacher_list.Contains(teacher_id_ComboBox.Text))
{
Common.ShowError("Format error!", "Teacher ID format error! \nPlease select the correct item from the teacher combo list!");
return;
}
if (!Regex.IsMatch(credit_textBox.Text, @"^(([1-9]{1}\d*)|([0]{1}))(\.(\d)?)?$"))
{
Common.ShowError("Format error!", "Credit format error! \nThe credit should be integer or one place decimal!");
return;
}
if (cancel_year_textBox.Text.Length != 0 && !Regex.IsMatch(cancel_year_textBox.Text, @"^\d{4}$"))
{
Common.ShowError("Format error!", "Canceled year format error! \nPlease chech again!");
return;
}
string teacher_current_courses;
string current_teacher_id = teacher_id_ComboBox.Text.Substring(0, 5);
DataTable dt_tcc = SQL_Help.ExecuteDataTable("select courses from teacher_info where id=@tid;", connection, new MySqlParameter[] { new MySqlParameter("@tid", current_teacher_id) });
teacher_current_courses = dt_tcc.Rows[0][0].ToString();
MySqlParameter year_parameter = new MySqlParameter("@year", MySqlDbType.Int32);
if (cancel_year_textBox.Text.Length == 0)
{
year_parameter.Value = DBNull.Value;
}
else
{
year_parameter.Value = cancel_year_textBox.Text;
}
if (SQL_Help.ExecuteNonQuery
("insert into course_info values (@id,@name,@teacher,@credit,@grade,@year); " +
"update teacher_info set courses = @new_course where id = @teacher;", connection, new MySqlParameter[]
{ new MySqlParameter("@id", MySqlDbType.VarChar)
{
Value = course_id_textBox.Text
},
new MySqlParameter("@name", MySqlDbType.VarChar)
{
Value = course_name_textBox.Text
},
new MySqlParameter("@teacher", MySqlDbType.VarChar)
{
Value = current_teacher_id
},
new MySqlParameter("@credit", MySqlDbType.Double)
{
Value = credit_textBox.Text
},
new MySqlParameter("@grade", MySqlDbType.Int32)
{
Value = grade_comboBox.Text
},
year_parameter,
new MySqlParameter("@new_course", MySqlDbType.VarChar)
{
Value = teacher_current_courses + (teacher_current_courses.Length == 0?"":",") + course_id_textBox.Text
} }) > 0)
{
Common.ShowInfo("Done", "Add successful!");
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();
course_id_textBox.Focus();
}
}