public static List <CoursesEnrolled> dispCourse(string course)
{
List <CoursesEnrolled> CEList = new List <CoursesEnrolled>();
try
{
string sql = "select c.CourseNum,c.CourseName,c.Description,d.DepartmentName,c.Credits " +
"from Courses c inner join Departments d on (c.departmentID = d.departmentid) " +
"where 1 = 1 and c.coursenum = @course ";
List <SqlParameter> pList = new List <SqlParameter>();
SqlParameter p1 = new SqlParameter("@course", SqlDbType.VarChar);
p1.Value = course;
pList.Add(p1);
DataTable dt = DataAccess.GetManyRowsCols(sql, pList);
foreach (DataRow dr in dt.Rows)
{
CoursesEnrolled crm = new CoursesEnrolled();
crm.CourseNum = dr["CourseNum"].ToString();
crm.CourseName = dr["CourseName"].ToString();
crm.Description = dr["Description"].ToString();
crm.DepartmentName = dr["DepartmentName"].ToString();
crm.Credits = (int)dr["Credits"];
CEList.Add(crm);
}
}
catch (Exception)
{
throw;
}
return(CEList);
}
public static List <CoursesEnrolled> GetenrollmentForASemester(string semester, string StudentId)
{
List <CoursesEnrolled> ECList = new List <CoursesEnrolled>();
try
{
string sql = "select ce.CourseNum,c.CourseName,c.Description,d.DepartmentName,c.Credits,'-' Grade " +
",case when REVERSE(SUBSTRING(REVERSE(rtrim(ltrim(SemesterId))),0,5))= YEAR(GETDATE()) then 'In Progress' else 'Yet to Start' end Status " +
"from CourseEnrollments ce inner join Courses c on (ce.CourseNum = c.CourseNum) " +
"inner join Departments d on (d.DepartmentID = c.DepartmentID) " +
"where 1 = 1 and not exists(select 1 from CoursesCompleted cc where cc.StudentId = ce.StudentId and cc.CourseNum = ce.CourseNum) " +
"and ce.SemesterId = @semester and ce.studentid=@studentId " +
"union all " +
"select c.CourseNum,c.CourseName,c.Description,d.DepartmentName,c.Credits,CONVERT(varchar, cc.Grade) Grade, 'Completed' Status " +
"from CoursesCompleted cc inner join Courses c on(cc.CourseNum = c.CourseNum) " +
"inner join Departments d on(d.DepartmentID = c.DepartmentID) " +
"where 1 = 1 and cc.SemesterId = @semester and cc.studentid=@studentId ";
List <SqlParameter> PList = new List <SqlParameter>();
SqlParameter p1 = new SqlParameter("@semester", SqlDbType.VarChar);
SqlParameter p2 = new SqlParameter("@studentId", SqlDbType.VarChar);
p1.Value = semester;
p2.Value = StudentId;
PList.Add(p1);
PList.Add(p2);
DataTable dt = DataAccess.GetManyRowsCols(sql, PList);
foreach (DataRow dr in dt.Rows)
{
CoursesEnrolled crm = new CoursesEnrolled();
crm.CourseNum = dr["CourseNum"].ToString();
crm.CourseName = dr["CourseName"].ToString();
crm.Description = dr["Description"].ToString();
crm.DepartmentName = dr["DepartmentName"].ToString();
crm.Credits = (int)dr["Credits"];
crm.Grade = dr["Grade"].ToString();
crm.Status = dr["Status"].ToString();
ECList.Add(crm);
}
}
catch (Exception)
{
throw;
}
return(ECList);
}
