public List <ViewAssignCourse> GetAllAssignCourses(int deptId)
{
string query = @"SELECT c.CourseCode, c.CourseName,s.SemesterName,t.TeacherName FROM TeacherAsign ta right JOIN Course c ON c.id=ta.CourseID and ta.Status !='UnAssign' left JOIN Teachers t ON t.Id=ta.TeacherID Inner JOIN Semester s ON c.SemesterID=s.Id where c.DepartmentID='" + deptId + "' order by CourseCode";
cmd = new SqlCommand(query, connection);
connection.Open();
sqlDataReader = cmd.ExecuteReader();
List <ViewAssignCourse> courseList = new List <ViewAssignCourse>();
while (sqlDataReader.Read())
{
ViewAssignCourse viewAssignCourse = new ViewAssignCourse();
viewAssignCourse.CourseCode = sqlDataReader["CourseCode"].ToString();
viewAssignCourse.CourseName = sqlDataReader["CourseName"].ToString();
viewAssignCourse.CourseSemester = sqlDataReader["SemesterName"].ToString();
viewAssignCourse.AssignTeacherName = sqlDataReader["TeacherName"].ToString();
if (viewAssignCourse.AssignTeacherName == "")
{
viewAssignCourse.AssignTeacherName = "Not Assigned Yet";
}
courseList.Add(viewAssignCourse);
}
connection.Close();
return(courseList);
}
public List <ViewAssignCourse> GetAllAssignCourses(int deptId)
{
string query = "SELECT CourseTable.CourseCode, CourseTable.CourseName,SemesterTable.SemesterName,TeacherTable.TeacherName"
+ " FROM AssignTeacherTable"
+ " Right JOIN CourseTable ON CourseTable.id=AssignTeacherTable.CourseID"
+ " LEFT JOIN TeacherTable ON TeacherTable.Id=AssignTeacherTable.TeacherID"
+ " Inner JOIN SemesterTable ON CourseTable.SemesterID=SemesterTable.Id"
+ " where CourseTable.DepartmentID='" + deptId + "' and AssignTeacherTable.Status='Assign' order by CourseCode";
_command = new SqlCommand(query, _connection);
_connection.Open();
_sqlDataReader = _command.ExecuteReader();
List <ViewAssignCourse> listOfItems = new List <ViewAssignCourse>();
while (_sqlDataReader.Read())
{
ViewAssignCourse viewAssignCourse = new ViewAssignCourse();
viewAssignCourse.CourseCode = _sqlDataReader["CourseCode"].ToString();
viewAssignCourse.CourseName = _sqlDataReader["CourseName"].ToString();
viewAssignCourse.CourseSemester = _sqlDataReader["SemesterName"].ToString();
viewAssignCourse.AssignTeacherName = _sqlDataReader["TeacherName"].ToString();
if (viewAssignCourse.AssignTeacherName == "")
{
viewAssignCourse.AssignTeacherName = "Not Assigned Yet";
}
listOfItems.Add(viewAssignCourse);
}
_connection.Close();
return(listOfItems);
}
