public List <CourseStaticViewModel> GetCourseInfo(int departmentId) { string query = "SELECT Course.Code,Course.Name,Semester.Name As Semester," + "ISNULL(Teacher.Name,'Not Assigned Yet') AS TeacherAssign" + " FROM Course LEFT JOIN CourseAssignToTeacher ON Course.Id=CourseAssignToTeacher.Id" + " LEFT JOIN Teacher ON CourseAssignToTeacher.TeacherId=Teacher.Id" + " LEFT JOIN Semester ON Course.SemesterId=Semester.Id WHERE" + " Course.DepartmentId=" + departmentId + ""; Command = new SqlCommand(query, Connection); Connection.Open(); Reader = Command.ExecuteReader(); List <CourseStaticViewModel> courseStaticViewModels = new List <CourseStaticViewModel>(); while (Reader.Read()) { CourseStaticViewModel courseStaticViewModel = new CourseStaticViewModel(); courseStaticViewModel.Code = Reader["Code"].ToString(); courseStaticViewModel.Name = Reader["Name"].ToString(); courseStaticViewModel.Semester = Reader["Semester"].ToString(); courseStaticViewModel.Teacher = Reader["TeacherAssign"].ToString(); courseStaticViewModels.Add(courseStaticViewModel); } Reader.Close(); Connection.Close(); return(courseStaticViewModels); }
// proma public List <CourseStaticViewModel> GetCourseStatic(int?departmentId) { Command = new SqlCommand { CommandText = "SELECT * FROM CourseView WHERE DepartmentId = '" + departmentId + "'", Connection = Connection }; Connection.Open(); Reader = Command.ExecuteReader(); List <CourseStaticViewModel> courseStatics = new List <CourseStaticViewModel>(); while (Reader.Read()) { CourseStaticViewModel courseStatic = new CourseStaticViewModel { Code = Reader["Code"].ToString(), Name = Reader["Name"].ToString(), Semester = Reader["Semester"].ToString(), AssignedTo = Reader["AssignedTo"].ToString(), Assign = Reader["Assign"].ToString(), DepartmentId = (int)Reader["DepartmentId"], }; courseStatics.Add(courseStatic); } Reader.Close(); Connection.Close(); return(courseStatics); }
public ActionResult Course(int departmentid) { var courseStatic = from c1 in db.Courses join ct in db.CourseAssinToTeachers on c1.CourseCode equals ct.CourseCode where (c1.DepartmentId == departmentid) && (ct.DepartmentId == departmentid) select new { c1.CourseCode, c1.CourseName, c1.SemesterName, ct.TeacherName }; List <CourseStaticViewModel> coursesList = new List <CourseStaticViewModel>(); foreach (var item in courseStatic) { CourseStaticViewModel course = new CourseStaticViewModel(); course.CoruseCode = item.CourseCode; course.CourseName = item.CourseName; course.Semester = item.SemesterName; course.TeacherName = item.TeacherName; coursesList.Add(course); } return(View(coursesList)); }