public Course GetCourseByID(int?id)
{
int nonNullId = id ?? throw new ArgumentNullException(nameof(id));
Course course = new Course();
Teacher teacher = new Teacher();
SqlCommand cmd = ADO_NETconfig.StoredProcedureCommand("spGetCourseByID", sqlConn);
cmd.Parameters.AddWithValue("@CourseID", nonNullId);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
course.CourseID = Convert.ToInt32(reader["CourseID"]);
course.CourseName = reader["CourseName"].ToString();
course.LevelYear = Convert.ToInt32(reader["LevelYear"].ToString());
course.TeacherID = Convert.ToInt32(reader["TeacherID"].ToString());
teacher.TeacherID = (int)reader["TeacherID"];
teacher.TeacherName = (string)reader["TeacherName"];
course.Teachers = teacher;
}
ADO_NETconfig.CloseReader(reader);
return(course);
}
public Timetable GetTimetableByID(int?id)
{
int notNullId = id ?? throw new ArgumentNullException(nameof(id));
Timetable timetable = new Timetable();
Student student = new Student();
Course course = new Course();
SqlCommand cmd = ADO_NETconfig.StoredProcedureCommand("spGetTimetableByID", sqlConn);
cmd.Parameters.AddWithValue("@TimetableID", notNullId);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
timetable.TimetableID = Convert.ToInt32(reader["TimetableID"]);
timetable.StudentID = Convert.ToInt32(reader["StudentID"]);
timetable.DayOfTheWeek = reader["DayOfTheWeek"].ToString();
timetable.TimeInterval = reader["TimeInterval"].ToString();
student.StudentID = Convert.ToInt32(reader["StudentID"]);
student.StudentName = reader["StudentName"].ToString();
course.CourseID = Convert.ToInt32(reader["CourseID"]);
course.CourseName = reader["CourseName"].ToString();
timetable.Students = student;
timetable.Courses = course;
}
ADO_NETconfig.CloseReader(reader);
return(timetable);
}
public IEnumerable <Course> GetAllCourses()
{
List <Course> listOfCourses = new List <Course>();
SqlCommand cmd = ADO_NETconfig.StoredProcedureCommand("spGetAllCourses", sqlConn);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Course course = new Course();
course.CourseID = Convert.ToInt32(reader["CourseID"]);
course.CourseName = reader["CourseName"].ToString();
course.LevelYear = Convert.ToInt32(reader["LevelYear"]);
course.TeacherID = Convert.ToInt32(reader["TeacherID"]);
Teacher teacher = new Teacher();
teacher.TeacherID = (int)reader["TeacherID"];
teacher.TeacherName = (string)reader["TeacherName"];
course.Teachers = teacher;
listOfCourses.Add(course);
}
ADO_NETconfig.CloseReader(reader);
if (sqlConn.State != ConnectionState.Closed)
{
ADO_NETconfig.CloseConn(sqlConn);
}
return(listOfCourses);
}
public IEnumerable <Grade> GetGradesByStudentID(int?id)
{
int notNullId = id ?? throw new ArgumentNullException(nameof(id));
List <Grade> listOfGrades = new List <Grade>();
SqlCommand cmd = ADO_NETconfig.StoredProcedureCommand("spGetGradeByStudentID", sqlConn);
cmd.Parameters.AddWithValue("@StudentID", notNullId);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Grade grade = new Grade();
grade.GradeID = Convert.ToInt32(reader["GradeID"]);
grade.StudentID = Convert.ToInt32(reader["StudentID"]);
grade.SemesterID = Convert.ToInt32(reader["SemesterID"]);
grade.CourseID = Convert.ToInt32(reader["CourseID"]);
grade.Mark = Convert.ToDecimal(reader["Mark"]);
grade.DateOfMark = Convert.ToDateTime(reader["DateOfMark"]);
grade.GradingWeight = Convert.ToDecimal(reader["GradingWeight"]);
grade.Observations = reader["Observations"].ToString();
Student student = new Student();
student.StudentID = Convert.ToInt32(reader["StudentID"]);
student.StudentName = reader["StudentName"].ToString();
Semester semester = new Semester();
semester.SemesterID = Convert.ToInt32(reader["SemesterID"]);
semester.SemesterNumber = Convert.ToInt32(reader["SemesterNumber"]);
semester.SchoolYear = reader["SchoolYear"].ToString();
Course course = new Course();
course.CourseID = Convert.ToInt32(reader["CourseID"]);
course.CourseName = reader["CourseName"].ToString();
course.LevelYear = Convert.ToInt32(reader["LevelYear"]);
grade.Students = student;
grade.Semesters = semester;
grade.Courses = course;
listOfGrades.Add(grade);
}
ADO_NETconfig.CloseReader(reader);
if (sqlConn.State != ConnectionState.Closed)
{
ADO_NETconfig.CloseConn(sqlConn);
}
return(listOfGrades);
}
public IEnumerable <SelectListItem> SemestersList()
{
List <SelectListItem> list = new List <SelectListItem>();
SqlCommand cmd = ADO_NETconfig.StoredProcedureCommand("spGetAllSemesters", sqlConn);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
list.Add(new SelectListItem
{
Text = reader["SemesterNumber"].ToString() + "/" + reader["SchoolYear"].ToString(),
Value = reader["SemesterID"].ToString()
});
}
ADO_NETconfig.CloseReader(reader);
return(list);
}
public IEnumerable <SelectListItem> StudentsList()
{
List <SelectListItem> list = new List <SelectListItem>();
SqlCommand cmd = ADO_NETconfig.StoredProcedureCommand("spGetAllStudents", sqlConn);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
list.Add(new SelectListItem
{
Text = reader["StudentName"].ToString(),
Value = reader["StudentID"].ToString()
});
}
ADO_NETconfig.CloseReader(reader);
return(list);
}
public IEnumerable <Homework> GetHomeworkByStudentID(int?id)
{
int notNullId = id ?? throw new ArgumentNullException(nameof(id));
List <Homework> listOfHomeworks = new List <Homework>();
SqlCommand cmd = ADO_NETconfig.StoredProcedureCommand("spGetHomeworkByStudentID", sqlConn);
cmd.Parameters.AddWithValue("@StudentID", notNullId);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Homework homework = new Homework();
homework.HomeworkID = Convert.ToInt32(reader["HomeworkID"]);
homework.StudentID = Convert.ToInt32(reader["StudentID"]);
homework.CourseID = Convert.ToInt32(reader["CourseID"]);
homework.DateOfHomework = Convert.ToDateTime(reader["DateOfHomework"]);
homework.DueDate = Convert.ToDateTime(reader["DueDate"]);
homework.Details = reader["Details"].ToString();
homework.HomeworkStatus = reader["HomeworkStatus"].ToString();
Student student = new Student();
student.StudentID = Convert.ToInt32(reader["StudentID"]);
student.StudentName = reader["StudentName"].ToString();
Course course = new Course();
course.CourseID = Convert.ToInt32(reader["CourseID"]);
course.CourseName = reader["CourseName"].ToString();
homework.Students = student;
homework.Courses = course;
listOfHomeworks.Add(homework);
}
ADO_NETconfig.CloseReader(reader);
if (sqlConn.State != ConnectionState.Closed)
{
ADO_NETconfig.CloseConn(sqlConn);
}
return(listOfHomeworks);
}
public Semester GetSemesterByID(int?id)
{
int notNullId = id ?? throw new ArgumentNullException(nameof(id));
Semester semester = new Semester();
SqlCommand cmd = ADO_NETconfig.StoredProcedureCommand("spGetSemesterByID", sqlConn);
cmd.Parameters.AddWithValue("@SemesterID", notNullId);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
semester.SemesterID = Convert.ToInt32(reader["SemesterID"]);
semester.SemesterNumber = Convert.ToInt32(reader["SemesterNumber"]);
semester.SchoolYear = reader["SchoolYear"].ToString();
}
ADO_NETconfig.CloseReader(reader);
return(semester);
}
public Teacher GetTeacherByID(int?id)
{
int notNullId = id ?? throw new ArgumentNullException(nameof(id));
Teacher teacher = new Teacher();
SqlCommand cmd = ADO_NETconfig.StoredProcedureCommand("spGetTeacherByID", sqlConn);
cmd.Parameters.AddWithValue("@TeacherID", notNullId);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
teacher.TeacherID = Convert.ToInt32(reader["TeacherID"]);
teacher.TeacherName = reader["TeacherName"].ToString();
teacher.TeacherEmail = reader["TeacherEmail"].ToString();
teacher.TeacherPhone = reader["TeacherPhone"].ToString();
}
ADO_NETconfig.CloseReader(reader);
return(teacher);
}
public Student GetStudentByID(int?id)
{
int notNullId = id ?? throw new ArgumentNullException(nameof(id));
Student student = new Student();
SqlCommand cmd = ADO_NETconfig.StoredProcedureCommand("spGetStudentsByID", sqlConn);
cmd.Parameters.AddWithValue("@StudentID", notNullId);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
student.StudentID = Convert.ToInt32(reader["StudentID"]);
student.StudentName = reader["StudentName"].ToString();
student.StudentPhoto = reader["StudentPhoto"].ToString();
student.Observations = reader["Observations"].ToString();
}
ADO_NETconfig.CloseReader(reader);
return(student);
}
public IEnumerable <Homework> GetAllHomeworks()
{
List <Homework> listOfHomeworks = new List <Homework>();
SqlCommand cmd = ADO_NETconfig.StoredProcedureCommand("spGetAllHomeworks", sqlConn);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Homework homework = new Homework();
homework.HomeworkID = Convert.ToInt32(reader["HomeworkID"]);
homework.StudentID = Convert.ToInt32(reader["StudentID"]);
homework.CourseID = Convert.ToInt32(reader["CourseID"]);
homework.DateOfHomework = Convert.ToDateTime(reader["DateOfHomework"]);
homework.DueDate = Convert.ToDateTime(reader["DueDate"]);
homework.Details = reader["Details"].ToString();
homework.HomeworkStatus = reader["HomeworkStatus"].ToString();
Student student = new Student();
student.StudentID = Convert.ToInt32(reader["StudentID"]);
student.StudentName = reader["StudentName"].ToString();
Course course = new Course();
course.CourseID = Convert.ToInt32(reader["CourseID"]);
course.CourseName = reader["CourseName"].ToString();
homework.Students = student;
homework.Courses = course;
listOfHomeworks.Add(homework);
}
ADO_NETconfig.CloseReader(reader);
if (sqlConn.State != ConnectionState.Closed)
{
ADO_NETconfig.CloseConn(sqlConn);
}
return(listOfHomeworks);
}
public IList <Timetable> GetTimetableByCourse(string subject)
{
List <Timetable> listOfTimetables = new List <Timetable>();
SqlCommand cmd = ADO_NETconfig.StoredProcedureCommand("spGetTimetableByCourse", sqlConn);
cmd.Parameters.AddWithValue("@CourseName", subject);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Timetable timetable = new Timetable();
timetable.TimetableID = Convert.ToInt32(reader["TimetableID"]);
timetable.StudentID = Convert.ToInt32(reader["StudentID"]);
timetable.DayOfTheWeek = reader["DayOfTheWeek"].ToString();
timetable.TimeInterval = reader["TimeInterval"].ToString();
Student student = new Student();
student.StudentID = Convert.ToInt32(reader["StudentID"]);
student.StudentName = reader["StudentName"].ToString();
Course course = new Course();
course.CourseID = Convert.ToInt32(reader["CourseID"]);
course.CourseName = reader["CourseName"].ToString();
timetable.Students = student;
timetable.Courses = course;
listOfTimetables.Add(timetable);
}
ADO_NETconfig.CloseReader(reader);
if (sqlConn.State != ConnectionState.Closed)
{
ADO_NETconfig.CloseConn(sqlConn);
}
return(listOfTimetables);
}
