public static Student Get(int studentId)
{
Student st;
using (var con = new SQLiteConnection(DbInfo.ConnectionString))
{
con.Open();
using (var cmd = new SQLiteCommand(@"SELECT * FROM Student WHERE id = @studentId", con))
{
cmd.Parameters.AddWithValue("@studentId", studentId);
using (var reader = cmd.ExecuteReader())
{
reader.Read();
st = new Student(int.Parse(reader["Id"].ToString()))
{
FirstName = (string)reader["FirstName"],
LastName = (string)reader["LastName"],
Email = (string)reader["Email"],
Telephone = (string)reader["Telephone"],
Birthday = DateTime.ParseExact((string)reader["Birthday"], "o", System.Globalization.CultureInfo.InvariantCulture)
};
}
}
using (var cmd = new SQLiteCommand(@"SELECT * FROM CourseGrade WHERE studentId = @studentId", con))
{
cmd.Parameters.AddWithValue("@studentId", studentId);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var cg = new CourseGrade
{
CourseId = (int)reader["CourseId"],
StudentId = (int)reader["StudentId"],
First = (double)reader["First"],
Second = (double)reader["Second"],
Third = (double)reader["Third"],
Final = (double)reader["Final"]
};
st.Grades.Add(cg.CourseId, cg);
}
}
}
}
return(st);
}
public static Student[] GetAll()
{
var result = new Dictionary <int, Student>();
using (var con = new SQLiteConnection(DbInfo.ConnectionString))
{
con.Open();
using (var cmd = new SQLiteCommand(@"SELECT * FROM Student", con))
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var st = new Student(int.Parse(reader["Id"].ToString()))
{
FirstName = (string)reader["FirstName"],
LastName = (string)reader["LastName"],
Email = (string)reader["Email"],
Telephone = (string)reader["Telephone"],
Birthday = DateTime.ParseExact((string)reader["Birthday"], "o", System.Globalization.CultureInfo.InvariantCulture)
};
result.Add(st.Id, st);
}
}
using (var cmd = new SQLiteCommand(@"SELECT * FROM CourseGrade", con))
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var cg = new CourseGrade
{
CourseId = (int)reader["CourseId"],
StudentId = (int)reader["StudentId"],
First = (double)reader["First"],
Second = (double)reader["Second"],
Third = (double)reader["Third"],
Final = (double)reader["Final"]
};
result[cg.StudentId].Grades.Add(cg.CourseId, cg);
}
}
}
return(result.Values.ToArray());
}
private void btnEnrollInCourse_Click(object sender, EventArgs e)
{
var selectedCourse = cbxCourses.SelectedItem as Course;
if (selectedCourse == null || _student.Grades.ContainsKey(selectedCourse.Id))
{
return;
}
var grade = new CourseGrade {
CourseId = selectedCourse.Id, StudentId = _student.Id, First = 0.0f, Second = 0.0f, Third = 0.0f, Final = 0.0f
};
var gradeItem = new ListViewItem {
Tag = grade
};
gradeItem.SubItems.AddRange(new string[6] {
"", "", "", "", "", ""
});
UpdateGradeItem(gradeItem);
lstCourses.Items.Add(gradeItem);
lstCourses.Items[lstCourses.Items.Count - 1].Selected = true;
_student.Grades.Add(grade.CourseId, grade);
UpdateInfo();
}
