bool check_command(SqlCommand command)
{
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>
/// 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);
}
}
/// <summary>
/// Function excute query
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
private string execCount(string query)
{
SqlCommand command = new SqlCommand(query, dB.GetConnection);
dB.openConnection();
String count = command.ExecuteScalar().ToString();
dB.closeConnection();
return(count);
}
/// <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();
}