public static List <StudentScore1> Search(string KeyWord)
{
//DataBase.DB();
List <StudentScore1> scoreSearch = new List <StudentScore1>();
int subUserId = GetSubUserId();
try{
string query = "SELECT s.stdId,s.name,s.gender,sc.scoreId, sc.quiz, sc.homework, sc.assignment, sc.midterm,sc.final FROM score sc INNER JOIN student s ON s.stdId= sc.stdId WHERE s.subUserId= @subUserid AND s.name like '" + KeyWord + "%' or s.stdId like '" + KeyWord + "%' ";
MySqlCommand cmd = new MySqlCommand(query, Database.connection);
cmd.Prepare();
cmd.Parameters.AddWithValue("@subUserId", subUserId);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
StudentScore1 p = new StudentScore1();
p.scoreId = Int16.Parse(reader["scoreId"].ToString());
p.stdId = Int16.Parse(reader["stdId"].ToString());
p.stdName = reader["name"].ToString();
p.gender = Convert.ToBoolean(reader["gender"].ToString());
p.quiz = Int16.Parse(controlNULL(reader["quiz"].ToString()));
p.homework = Int16.Parse(controlNULL(reader["homework"].ToString()));
p.assignment = Int16.Parse(controlNULL(reader["assignment"].ToString()));
p.midterm = Int16.Parse(controlNULL(reader["midterm"].ToString()));
p.final = Int16.Parse(controlNULL(reader["final"].ToString()));
scoreSearch.Add(p);
}
reader.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
return(scoreSearch);
}
private void SC_List_Load(object sender, EventArgs e)
{
Database.Open();
myScore = StudentScore1.getSelected(SC_List.ScoreId);
//StudentScoreDB.EditLoad();
lbName.Text = myScore.stdName;
txtHomework.Text = myScore.Homework.ToString();
txtQuiz.Text = myScore.Quiz.ToString();
txtAssignment.Text = myScore.Assignment.ToString();
txtMidterm.Text = myScore.Midterm.ToString();
txtAttendent.Text = "0";
txtFinal.Text = myScore.Final.ToString();
}
public void LoadSearch(string keyword)
{
score = StudentScore1.Search(keyword);
string gender;
StudentScoreList.Rows.Clear();
foreach (StudentScore1 s in score)
{
if (s.gender == true)
{
gender = "M";
}
else
{
gender = "F";
}
StudentScoreList.Rows.Add(s.scoreId, s.stdId, s.stdName, gender, s.Quiz, s.Homework, s.Assignment, s.Midterm, s.Final, (s.Quiz + s.Homework + s.Assignment + s.Midterm + s.Final));
}
}
private void btnUpdate_Click(object sender, EventArgs e)
{
StudentScore1 sc = new StudentScore1();
try {
sc.scoreId = myScore.scoreId;
sc.Homework = Int16.Parse(txtHomework.Text);
sc.Quiz = Int16.Parse(txtQuiz.Text);
sc.Assignment = Int16.Parse(txtAssignment.Text);
sc.Midterm = Int16.Parse(txtMidterm.Text);
sc.Final = Int16.Parse(txtFinal.Text);
StudentScore1.Update(sc);
(this.Owner as StudentScore).btnList_Click(sender, e);
}
catch (Exception)
{
MessageBox.Show("Please fill all score", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
public static void Update(StudentScore1 score)
{
string query = "UPDATE score SET quiz=@quiz, homework=@homework, assignment=@assignment, midterm=@midterm, final=@final WHERE scoreId=@scoreId";
MySqlCommand cmd = new MySqlCommand(query, Database.connection);
cmd.Prepare();
cmd.Parameters.AddWithValue("@scoreId", score.scoreId);
cmd.Parameters.AddWithValue("@quiz", score.Quiz);
cmd.Parameters.AddWithValue("@homework", score.Homework);
cmd.Parameters.AddWithValue("@assignment", score.Assignment);
cmd.Parameters.AddWithValue("@midterm", score.Midterm);
cmd.Parameters.AddWithValue("@final", score.Final);
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
public static StudentScore1 getSelected(int id)
{
StudentScore1 s = new StudentScore1();
string query = "SELECT s.name, sc.scoreId ,sc.quiz,sc.homework, sc.assignment, sc.midterm, sc.final FROM score sc INNER JOIN student s ON s.stdId= sc.stdId WHERE sc.scoreId=@scoreId";
MySqlCommand cmd = new MySqlCommand(query, Database.connection);
cmd.Prepare();
cmd.Parameters.AddWithValue("@scoreId", id);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
s.scoreId = Int16.Parse(controlNULL(reader["scoreId"].ToString()));
s.stdName = reader["name"].ToString();
s.quiz = Int16.Parse(controlNULL(reader["quiz"].ToString()));
s.homework = Int16.Parse(controlNULL(reader["homework"].ToString()));
s.assignment = Int16.Parse(controlNULL(reader["assignment"].ToString()));
s.midterm = Int16.Parse(controlNULL(reader["midterm"].ToString()));
s.final = Int16.Parse(controlNULL(reader["final"].ToString()));
}
reader.Close();
return(s);
}
