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); }