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 <Student> GetAllStudents()
{
List <Student> listOfStudents = new List <Student>();
SqlCommand cmd = ADO_NETconfig.StoredProcedureCommand("spGetAllStudents", sqlConn);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Student student = new Student();
student.StudentID = Convert.ToInt32(reader["StudentID"]);
student.StudentName = reader["StudentName"].ToString();
student.StudentPhoto = reader["StudentPhoto"].ToString();
student.Observations = reader["Observations"].ToString();
listOfStudents.Add(student);
}
if (sqlConn.State != ConnectionState.Closed)
{
ADO_NETconfig.CloseConn(sqlConn);
}
return(listOfStudents);
}
public IEnumerable <Teacher> GetAllTeachers()
{
List <Teacher> listOfTeachers = new List <Teacher>();
SqlCommand cmd = ADO_NETconfig.StoredProcedureCommand("spGetAllTeachers", sqlConn);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Teacher teacher = new Teacher();
teacher.TeacherID = Convert.ToInt32(reader["TeacherID"]);
teacher.TeacherName = reader["TeacherName"].ToString();
teacher.TeacherEmail = reader["TeacherEmail"].ToString();
teacher.TeacherPhone = reader["TeacherPhone"].ToString();
listOfTeachers.Add(teacher);
}
if (sqlConn.State != ConnectionState.Closed)
{
ADO_NETconfig.CloseConn(sqlConn);
}
return(listOfTeachers);
}
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 <UserAccount> GetAllUserAccounts()
{
List <UserAccount> listOfUsers = new List <UserAccount>();
SqlCommand cmd = ADO_NETconfig.StoredProcedureCommand("spGetAllUsers", sqlConn);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
UserAccount userAccount = new UserAccount();
userAccount.UserID = Convert.ToInt32(reader["UserID"]);
userAccount.FirstName = reader["FirstName"].ToString();
userAccount.LastName = reader["LastName"].ToString();
userAccount.Email = reader["Email"].ToString();
userAccount.Username = reader["Username"].ToString();
userAccount.Password = reader["Password"].ToString();
userAccount.ConfirmPassword = reader["ConfirmPassword"].ToString();
listOfUsers.Add(userAccount);
}
if (sqlConn.State != ConnectionState.Closed)
{
ADO_NETconfig.CloseConn(sqlConn);
}
return(listOfUsers);
}
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 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 void DeleteTimetable(int?id)
{
int notNullId = id ?? throw new ArgumentNullException(nameof(id));
SqlCommand cmd = ADO_NETconfig.StoredProcedureCommand("spDeleteTimetable", sqlConn);
cmd.Parameters.AddWithValue("@TimetableID", notNullId);
cmd.ExecuteNonQuery();
if (sqlConn.State != ConnectionState.Closed)
{
ADO_NETconfig.CloseConn(sqlConn);
}
}
public void AddSemester(Semester semester)
{
Semester notNullSemester = semester ?? throw new ArgumentNullException(nameof(semester));
SqlCommand cmd = ADO_NETconfig.StoredProcedureCommand("spAddSemester", sqlConn);
cmd.Parameters.AddWithValue("@SemesterNumber", notNullSemester.SemesterNumber);
cmd.Parameters.AddWithValue("@SchoolYear", notNullSemester.SchoolYear);
cmd.ExecuteNonQuery();
if (sqlConn.State != ConnectionState.Closed)
{
ADO_NETconfig.CloseConn(sqlConn);
}
}
public void AddTeacher(Teacher teacher)
{
Teacher notNullTeacher = teacher ?? throw new ArgumentNullException(nameof(teacher));
SqlCommand cmd = ADO_NETconfig.StoredProcedureCommand("spAddTeacher", sqlConn);
cmd.Parameters.AddWithValue("@TeacherName", notNullTeacher.TeacherName);
cmd.Parameters.AddWithValue("@TeacherEmail", (object)notNullTeacher.TeacherEmail ?? DBNull.Value);
cmd.Parameters.AddWithValue("@TeacherPhone", (object)notNullTeacher.TeacherPhone ?? DBNull.Value);
cmd.ExecuteNonQuery();
if (sqlConn.State != ConnectionState.Closed)
{
ADO_NETconfig.CloseConn(sqlConn);
}
}
public void AddStudent(Student student)
{
Student notNullStudent = student ?? throw new ArgumentNullException(nameof(student));
SqlCommand cmd = ADO_NETconfig.StoredProcedureCommand("spAddStudent", sqlConn);
cmd.Parameters.AddWithValue("@StudentName", notNullStudent.StudentName);
cmd.Parameters.AddWithValue("@StudentPhoto", (object)notNullStudent.StudentPhoto ?? DBNull.Value);
cmd.Parameters.AddWithValue("@Observations", (object)notNullStudent.Observations ?? DBNull.Value);
cmd.ExecuteNonQuery();
if (sqlConn.State != ConnectionState.Closed)
{
ADO_NETconfig.CloseConn(sqlConn);
}
}
public void AddTimetable(Timetable timetable)
{
Timetable notNullTimetable = timetable ?? throw new ArgumentNullException(nameof(timetable));
SqlCommand cmd = ADO_NETconfig.StoredProcedureCommand("spAddTimetable", sqlConn);
cmd.Parameters.AddWithValue("@StudentID", notNullTimetable.StudentID);
cmd.Parameters.AddWithValue("@DayOfTheWeek", notNullTimetable.DayOfTheWeek);
cmd.Parameters.AddWithValue("@TimeInterval", notNullTimetable.TimeInterval);
cmd.Parameters.AddWithValue("@CourseID", notNullTimetable.CourseID);
cmd.ExecuteNonQuery();
if (sqlConn.State != ConnectionState.Closed)
{
ADO_NETconfig.CloseConn(sqlConn);
}
}
public void AddCourse(Course course)
{
Course nonNullCourse = course ?? throw new ArgumentNullException(nameof(course));
SqlCommand cmd = ADO_NETconfig.StoredProcedureCommand("spAddCourse", sqlConn);
cmd.Parameters.AddWithValue("@CourseName", nonNullCourse.CourseName);
cmd.Parameters.AddWithValue("@LevelYear", nonNullCourse.LevelYear);
cmd.Parameters.AddWithValue("@TeacherID", nonNullCourse.TeacherID);
cmd.ExecuteNonQuery();
if (sqlConn.State != ConnectionState.Closed)
{
ADO_NETconfig.CloseConn(sqlConn);
}
}
public void AddUser(UserAccount userAccount)
{
UserAccount notNullUA = userAccount ?? throw new ArgumentNullException(nameof(userAccount));
SqlCommand cmd = ADO_NETconfig.StoredProcedureCommand("spAddUserAccounts", sqlConn);
cmd.Parameters.AddWithValue("@FirstName", notNullUA.FirstName);
cmd.Parameters.AddWithValue("@LastName", notNullUA.LastName);
cmd.Parameters.AddWithValue("@Email", notNullUA.Email);
cmd.Parameters.AddWithValue("@Username", notNullUA.Username);
cmd.Parameters.AddWithValue("@Password", notNullUA.Password);
cmd.Parameters.AddWithValue("@ConfirmPassword", notNullUA.ConfirmPassword);
cmd.ExecuteNonQuery();
if (sqlConn.State != ConnectionState.Closed)
{
ADO_NETconfig.CloseConn(sqlConn);
}
}
public void AddHomework(Homework homework)
{
Homework notNullHomework = homework ?? throw new ArgumentNullException(nameof(homework));
SqlCommand cmd = ADO_NETconfig.StoredProcedureCommand("spAddHomework", sqlConn);
cmd.Parameters.AddWithValue("@StudentID", notNullHomework.StudentID);
cmd.Parameters.AddWithValue("@CourseID", notNullHomework.CourseID);
cmd.Parameters.AddWithValue("@DateOfHomework", notNullHomework.DateOfHomework);
cmd.Parameters.AddWithValue("@DueDate", notNullHomework.DueDate);
cmd.Parameters.AddWithValue("@Details", notNullHomework.Details);
cmd.Parameters.AddWithValue("@HomeworkStatus", notNullHomework.HomeworkStatus);
cmd.ExecuteNonQuery();
if (sqlConn.State != ConnectionState.Closed)
{
ADO_NETconfig.CloseConn(sqlConn);
}
}
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 <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 void AddGrade(Grade grade)
{
Grade notNullGrade = grade ?? throw new ArgumentNullException(nameof(grade));
SqlCommand cmd = ADO_NETconfig.StoredProcedureCommand("spAddGrade", sqlConn);
cmd.Parameters.AddWithValue("@StudentID", notNullGrade.StudentID);
cmd.Parameters.AddWithValue("@SemesterID", notNullGrade.SemesterID);
cmd.Parameters.AddWithValue("@CourseID", notNullGrade.CourseID);
cmd.Parameters.AddWithValue("@Mark", notNullGrade.Mark);
cmd.Parameters.AddWithValue("@DateOfMark", notNullGrade.DateOfMark);
cmd.Parameters.AddWithValue("@GradingWeight", notNullGrade.GradingWeight);
cmd.Parameters.AddWithValue("@Observations", (object)notNullGrade.Observations ?? DBNull.Value);
cmd.ExecuteNonQuery();
if (sqlConn.State != ConnectionState.Closed)
{
ADO_NETconfig.CloseConn(sqlConn);
}
}
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 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 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 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 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);
}
public IEnumerable <Semester> GetAllSemesters()
{
List <Semester> listOfSemesters = new List <Semester>();
SqlCommand cmd = ADO_NETconfig.StoredProcedureCommand("spGetAllSemesters", sqlConn);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Semester semester = new Semester();
semester.SemesterID = Convert.ToInt32(reader["SemesterID"]);
semester.SemesterNumber = Convert.ToInt32(reader["SemesterNumber"]);
semester.SchoolYear = reader["SchoolYear"].ToString();
listOfSemesters.Add(semester);
}
if (sqlConn.State != ConnectionState.Closed)
{
ADO_NETconfig.CloseConn(sqlConn);
}
return(listOfSemesters);
}
