/// <summary>
/// Gets a list of courses by teacher id.
/// </summary>
/// <param name="teacherIDCheck">The teacher id to check.</param>
/// <returns>A list of courses taught by the teacher</returns>
public List <Course> GetCoursesByTeacherID(string teacherIDCheck)
{
MySqlConnection conn = DbConnection.GetConnection();
List <Course> coursesTaught = new List <Course>();
using (conn)
{
conn.Open();
GradedItemDAL gradedStuff = new GradedItemDAL();
var selectQuery = "select courses.* from courses, teacher_teaches_courses WHERE teacher_teaches_courses.courses_CRN = courses.CRN AND teacher_teaches_courses.teacher_uid = @teacherUID";
using (MySqlCommand cmd = new MySqlCommand(selectQuery, conn))
{
cmd.Parameters.AddWithValue("@teacherUID", teacherIDCheck);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
int CRNOrdinal = reader.GetOrdinal("CRN");
int courseNameOrdinal = reader.GetOrdinal("course_name");
int sectionNumberOrdinal = reader.GetOrdinal("section_num");
int creditHoursOrdinal = reader.GetOrdinal("credit_hours");
int maxSeatsOrdinal = reader.GetOrdinal("seats_max");
int locationOrdinal = reader.GetOrdinal("location");
while (reader.Read())
{
int CRN = reader[CRNOrdinal] == DBNull.Value ? default(int) : reader.GetInt32(CRNOrdinal);
string courseName = reader[courseNameOrdinal] == DBNull.Value
? default(string)
: reader.GetString(courseNameOrdinal);
string sectionNumber = reader[sectionNumberOrdinal] == DBNull.Value
? default(string)
: reader.GetString(sectionNumberOrdinal);
int creditHours = reader[creditHoursOrdinal] == DBNull.Value
? default(int)
: reader.GetInt32(creditHoursOrdinal);
int maxSeats = reader[maxSeatsOrdinal] == DBNull.Value
? default(int)
: reader.GetInt32(maxSeatsOrdinal);
string location = reader[locationOrdinal] == DBNull.Value
? default(string)
: reader.GetString(locationOrdinal);
List <GradedItem> listOfGrades = gradedStuff.GetGradedItemsByCRN(CRN);
CourseInfo currCourseInfo =
new CourseInfo(courseName, location, creditHours, CRN, sectionNumber);
Course currentCourse = new Course(listOfGrades, currCourseInfo, maxSeats);
coursesTaught.Add(currentCourse);
}
return(coursesTaught);
}
}
}
}
/// <summary>
/// Gets a list of courses by department name.
/// </summary>
/// <param name="departmentName">Name of the department to check</param>
/// <returns>A list of courses taught by the selected department</returns>
public List <Course> GetCoursesByDepartmentName(string departmentName)
{
MySqlConnection conn = DbConnection.GetConnection();
List <Course> deptCourses = new List <Course>();
using (conn)
{
GradedItemDAL gradedStuff = new GradedItemDAL();
conn.Open();
var selectQuery = String.Empty;
if (departmentName.Equals("All Departments"))
{
selectQuery = "SELECT courses.* FROM courses, dept_offers_courses WHERE courses.CRN = dept_offers_courses.courses_CRN";
}
else
{
selectQuery = "SELECT courses.* FROM courses, dept_offers_courses WHERE courses.CRN = dept_offers_courses.courses_CRN AND dept_offers_courses.dept_name = @deptName";
}
using (MySqlCommand cmd = new MySqlCommand(selectQuery, conn))
{
cmd.Parameters.AddWithValue("@deptName", departmentName);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
int CRNOrdinal = reader.GetOrdinal("CRN");
int courseNameOrdinal = reader.GetOrdinal("course_name");
int sectionNumberOrdinal = reader.GetOrdinal("section_num");
int creditHoursOrdinal = reader.GetOrdinal("credit_hours");
int maxSeatsOrdinal = reader.GetOrdinal("seats_max");
int locationOrdinal = reader.GetOrdinal("location");
while (reader.Read())
{
int CRN = reader[CRNOrdinal] == DBNull.Value ? default(int) : reader.GetInt32(CRNOrdinal);
string courseName = reader[courseNameOrdinal] == DBNull.Value ? default(string) : reader.GetString(courseNameOrdinal);
string sectionNumber = reader[sectionNumberOrdinal] == DBNull.Value ? default(string) : reader.GetString(sectionNumberOrdinal);
int creditHours = reader[creditHoursOrdinal] == DBNull.Value ? default(int) : reader.GetInt32(creditHoursOrdinal);
int maxSeats = reader[maxSeatsOrdinal] == DBNull.Value ? default(int) : reader.GetInt32(maxSeatsOrdinal);
string location = reader[locationOrdinal] == DBNull.Value
? default(string)
: reader.GetString(locationOrdinal);
List <GradedItem> listOfGrades = gradedStuff.GetGradedItemsByCRN(CRN);
CourseInfo currCourseInfo = new CourseInfo(courseName, location, creditHours, CRN, sectionNumber);
Course currentCourse = new Course(listOfGrades, currCourseInfo, maxSeats);
deptCourses.Add(currentCourse);
}
}
return(deptCourses);
}
}
}
/// <summary>
/// Gets the course by its CRN.
/// </summary>
/// <param name="CRN">The CRN.</param>
/// <returns>The course with the selected CRN</returns>
public Course GetCourseByCRN(int CRN)
{
MySqlConnection conn = DbConnection.GetConnection();
using (conn)
{
GradedItemDAL gradedStuff = new GradedItemDAL();
conn.Open();
var selectQuery = "SELECT * from courses WHERE courses.CRN = @CRNCheck";
using (MySqlCommand cmd = new MySqlCommand(selectQuery, conn))
{
cmd.Parameters.AddWithValue("@CRNCheck", CRN);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
int courseNameOrdinal = reader.GetOrdinal("course_name");
int sectionNumberOrdinal = reader.GetOrdinal("section_num");
int creditHoursOrdinal = reader.GetOrdinal("credit_hours");
int maxSeatsOrdinal = reader.GetOrdinal("seats_max");
int locationOrdinal = reader.GetOrdinal("location");
while (reader.Read())
{
string courseName = reader[courseNameOrdinal] == DBNull.Value ? default(string) : reader.GetString(courseNameOrdinal);
string sectionNumber = reader[sectionNumberOrdinal] == DBNull.Value ? default(string) : reader.GetString(sectionNumberOrdinal);
int creditHours = reader[creditHoursOrdinal] == DBNull.Value ? default(int) : reader.GetInt32(creditHoursOrdinal);
int maxSeats = reader[maxSeatsOrdinal] == DBNull.Value ? default(int) : reader.GetInt32(maxSeatsOrdinal);
string location = reader[locationOrdinal] == DBNull.Value
? default(string)
: reader.GetString(locationOrdinal);
List <GradedItem> listOfGrades = gradedStuff.GetGradedItemsByCRN(CRN);
CourseInfo currCourseInfo = new CourseInfo(courseName, location, creditHours, CRN, sectionNumber);
Course currentCourse = new Course(listOfGrades, currCourseInfo, maxSeats);
return(currentCourse);
}
}
}
conn.Close();
}
return(null);
}