public List<CourseSelectView> GetAvailableCoursesForStudent(int studentId, int departmentId)
{
string query = "SELECT C.CourseId, C.CourseCode, C.CourseName, C.CourseCredit FROM Courses C WHERE C.DepartmentId =@deptId AND C.CourseId NOT IN(SELECT E.CourseId FROM Enrollments E WHERE E.IsCurrent =1 AND E.StudentId =@stdId) ORDER BY C.CourseName ASC";
using (connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand(query, connection))
{
command.Parameters.Clear();
command.Parameters.Add("deptId", sqlDbType: SqlDbType.Int);
command.Parameters["deptId"].Value = departmentId;
command.Parameters.Add("stdId", sqlDbType: SqlDbType.Int);
command.Parameters["stdId"].Value = studentId;
List<CourseSelectView> courseSelectViews = new List<CourseSelectView>();
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
CourseSelectView aView = new CourseSelectView
{
CourseId = Convert.ToInt32(reader["CourseId"].ToString()),
CourseCode = reader["CourseCode"].ToString(),
CourseName = reader["CourseName"].ToString(),
CourseCredit = Convert.ToDecimal(reader["CourseCredit"].ToString())
};
courseSelectViews.Add(aView);
}
return courseSelectViews;
}
}
public List<CourseSelectView> GetCoursesByDepartment(int departmentId)
{
string query = "SELECT CourseId, CourseCode, CourseName, CourseCredit FROM Courses WHERE DepartmentId =@deptId ORDER BY CourseName ASC";
using (connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand(query, connection))
{
command.Parameters.Clear();
command.Parameters.Add("deptId", sqlDbType: SqlDbType.Int);
command.Parameters["deptId"].Value = departmentId;
List<CourseSelectView> courseSelectViews = new List<CourseSelectView>();
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
CourseSelectView aView = new CourseSelectView
{
CourseId = Convert.ToInt32(reader["CourseId"].ToString()),
CourseCode = reader["CourseCode"].ToString(),
CourseName = reader["CourseName"].ToString(),
CourseCredit = Convert.ToDecimal(reader["CourseCredit"].ToString())
};
courseSelectViews.Add(aView);
}
return courseSelectViews;
}
}