/// <summary>
/// Function to insert a new student
/// </summary>
/// <param name="id"></param>
/// <param name="firstname"></param>
/// <param name="lastname"></param>
/// <param name="birthdate"></param>
/// <param name="gender"></param>
/// <param name="phone"></param>
/// <param name="address"></param>
/// <param name="picture"></param>
/// <returns></returns>
public bool insertStudent(int id, string firstname, string lastname, DateTime birthdate, string gender, string phone, string address, MemoryStream picture)
{
SqlCommand command = new SqlCommand("INSERT INTO student (id, firstname, lastname, birthdate, gender, phone, address, picture)"
+ " VALUES (@ID, @Fname, @Lname, @Bdate, @Gender, @Phone, @Address, @Picture)", dB.GetConnection);
command.Parameters.Add("@ID", System.Data.SqlDbType.Int).Value = id;
command.Parameters.Add("@Fname", System.Data.SqlDbType.VarChar).Value = firstname;
command.Parameters.Add("@Lname", System.Data.SqlDbType.VarChar).Value = lastname;
command.Parameters.Add("@Bdate", System.Data.SqlDbType.DateTime).Value = birthdate;
command.Parameters.Add("@Gender", System.Data.SqlDbType.VarChar).Value = gender;
command.Parameters.Add("@Phone", System.Data.SqlDbType.VarChar).Value = phone;
command.Parameters.Add("@Address", System.Data.SqlDbType.VarChar).Value = address;
command.Parameters.Add("@Picture", System.Data.SqlDbType.Image).Value = picture.ToArray();
dB.openConnection();
if ((command.ExecuteNonQuery() == 1))
{
dB.closeConnection(); return(true);
}
else
{
dB.closeConnection(); return(false);
}
}
bool check_command(SqlCommand command)
{
dB.openConnection();
bool result = (command.ExecuteNonQuery() == 1);
dB.closeConnection();
return(result);
}
/// <summary>
/// Add new course
/// </summary>
/// <param name="id"></param>
/// <param name="courseName"></param>
/// <param name="hoursNumber"></param>
/// <param name="description"></param>
/// <returns></returns>
public bool insertCourse(int id, string courseName, int hoursNumber, string description)
{
SqlCommand command = new SqlCommand("INSERT INTO course (id, label, period, description) VALUES (@id,@label,@per,@desc)", dB.GetConnection);
command.Parameters.Add("@id", SqlDbType.Int).Value = id;
command.Parameters.Add("@label", SqlDbType.VarChar).Value = courseName;
command.Parameters.Add("@per", SqlDbType.Int).Value = hoursNumber;
command.Parameters.Add("@desc", SqlDbType.VarChar).Value = description;
dB.openConnection();
if ((command.ExecuteNonQuery() == 1))
{
return(true);
}
else
{
return(false);
}
}
bool check_command(SqlCommand command)
{
dB.openConnection();
if ((command.ExecuteNonQuery() == 1))
{
dB.closeConnection(); return(true);
}
else
{
dB.closeConnection(); return(false);
}
}
/// <summary>
/// Insert score
/// </summary>
/// <param name="studentID"></param>
/// <param name="courseID"></param>
/// <param name="scoreValue"></param>
/// <param name="description"></param>
/// <returns></returns>
public bool insertScore(int studentID, int courseID, float scoreValue, string description)
{
SqlCommand command = new SqlCommand("INSERT INTO score (student_id, course_id, student_score, description) VALUES (@sid,@cid,@scr" +
",@descr)", dB.GetConnection);
command.Parameters.Add("@sid", SqlDbType.Int).Value = studentID;
command.Parameters.Add("@cid", SqlDbType.Int).Value = courseID;
command.Parameters.Add("@scr", SqlDbType.Float).Value = scoreValue;
command.Parameters.Add("@descr", SqlDbType.VarChar).Value = description;
dB.openConnection();
if ((command.ExecuteNonQuery() == 1))
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
///
/// </summary>
private void loadList()
{
Score score = new Score();
dB.openConnection();
DataTable table = score.getAvgScoreByCourse();
count = table.Rows.Count;
clearList();
for (int i = 0; i < table.Rows.Count; i++)
{
//Name Course] , AVG(score.student_score) AS [Average Grade]
string temp = Math.Round(Convert.ToDouble(table.Rows[i]["Average Grade"].ToString()), 2).ToString();
listBoxStaticCourse.Items.Add(table.Rows[i]["label"].ToString() + ":\n" + temp + "\n");
}
SqlCommand command = new SqlCommand("select * from result ", dB.GetConnection);
DataTable table1 = new DataTable();
SqlDataAdapter adap = new SqlDataAdapter(command);
adap.Fill(table1);
int index = table1.Rows.Count;
int numberFail = 0;
for (int i = 0; i < index; i++)
{
if (table1.Rows[i]["Result"].ToString().Trim() == "Fail")
{
numberFail++;
}
}
float percentFail = (numberFail * 100) / index;
float percentPass = 100 - percentFail;
labelFail.Text = "Fail: " + percentFail + "%";
labelPass.Text = "Pass: "******"%";
}
/// <summary>
/// Load dataGirdView from table Student, Course, Score
/// </summary>
private void loadDataGirdView()
{
try
{
SqlCommand clearrecord = new SqlCommand("Delete from result", dB.GetConnection);
dB.openConnection();
clearrecord.ExecuteNonQuery();
Score score = new Score();
SqlCommand command = new SqlCommand("Select * From course order by label", dB.GetConnection);
DataTable table = new DataTable();
table.Load(command.ExecuteReader());
for (int i = 0; i < table.Rows.Count; i++)
{
SqlCommand deleteCol = new SqlCommand("Alter Table result drop column if exists" + "[" + table.Rows[i]["label"].ToString() + "]", dB.GetConnection);
deleteCol.ExecuteNonQuery();
SqlCommand command1 = new SqlCommand("ALTER TABLE result add " + "[" + table.Rows[i]["label"].ToString() + "]" + " float", dB.GetConnection);
command1.ExecuteNonQuery();
}
SqlCommand deleteCol1 = new SqlCommand("alter table result drop column if exists" + "[Average Score]", dB.GetConnection);
deleteCol1.ExecuteNonQuery();
SqlCommand deleteCol2 = new SqlCommand("alter table result drop column if exists" + "[Result]", dB.GetConnection);
deleteCol2.ExecuteNonQuery();
SqlCommand addAvg = new SqlCommand("Alter table result Add [Average Score] float", dB.GetConnection);
addAvg.ExecuteNonQuery();
SqlCommand addRe = new SqlCommand("Alter table result Add [Result] nvarchar(50)", dB.GetConnection);
addRe.ExecuteNonQuery();
SqlCommand query = new SqlCommand("Select * from student", dB.GetConnection);
DataTable table1 = new DataTable();
table1.Load(query.ExecuteReader());
foreach (DataRow row in table1.Rows)
{
int id = Int32.Parse(row["id"].ToString());
DataTable diem = score.getScoreById(id);
float totalScore = 0, avgScore;
SqlCommand command3 = new SqlCommand("INSERT into result(Student_ID,firstname,lastname) VALUES(@id,@fn,@ln)", dB.GetConnection);
command3.Parameters.Add("@id", SqlDbType.NChar).Value = row["id"].ToString();
command3.Parameters.Add("@fn", SqlDbType.NChar).Value = row["firstname"].ToString();
command3.Parameters.Add("@ln", SqlDbType.NChar).Value = row["lastname"].ToString();
command3.ExecuteNonQuery();
for (int i = 0; i < table.Rows.Count; i++)
{
SqlCommand command2 = new SqlCommand("Update result Set " + "[" + table.Rows[i]["label"].ToString() + "]" + " = @value where Student_ID = " + row["id"].ToString(), dB.GetConnection);
float temp = float.Parse(diem.Rows[i]["student_score"].ToString());
command2.Parameters.Add("@value", SqlDbType.Float).Value = temp;
command2.ExecuteNonQuery();
totalScore += float.Parse(diem.Rows[i]["student_score"].ToString());
}
avgScore = totalScore / table.Rows.Count;
SqlCommand insertAvg = new SqlCommand("Update result set [Average Score] = " + avgScore + "where Student_ID = " + row["id"].ToString(), dB.GetConnection);
insertAvg.ExecuteNonQuery();
if (avgScore >= 5)
{
SqlCommand insertRe = new SqlCommand("Update result set [Result] = N'Pass'" + "where Student_ID = " + row["id"].ToString(), dB.GetConnection);
insertRe.ExecuteNonQuery();
}
else
{
SqlCommand insertRe = new SqlCommand("Update result set [Result] = N'Fail'" + "where Student_ID = " + row["id"].ToString(), dB.GetConnection);
insertRe.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
dB.closeConnection();
SqlCommand command4 = new SqlCommand("Select * from result", dB.GetConnection);
DataTable table4 = new DataTable();
dB.openConnection();
table4.Load(command4.ExecuteReader());
dataGridViewResult.DataSource = table4;
for (int i = 3; i < dataGridViewResult.Columns.Count; i++)
{
dataGridViewResult.Columns[i].DefaultCellStyle.Format = "N2";
}
dB.closeConnection();
}