public TeacherRoleModule GetTeacherRoleModule(int id)
{
string retriveListString = "SELECT t.FirstName, r.RoleName, m.ModuleName, tr.ID FROM teacherRoles tr " +
"JOIN Teachers t ON tr.TeacherID = t.TeacherID " +
"JOIN Roles r ON tr.RoleID = r.RoleID " +
"JOIN Modules m ON tr.ModuleID = m.ModuleID " +
"AND tr.ID = " + id
;
SqlCommand cmd = new SqlCommand(retriveListString, con);
try
{
con.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
TeacherRoleModule teacherRole = new TeacherRoleModule();
while (reader.Read())
{
teacherRole.FirstName = reader.GetString(0);
teacherRole.RoleName = reader.GetString(1);
teacherRole.ModuleName = reader.GetString(2);
teacherRole.ID = reader.GetInt32(3);
}
return(teacherRole);
}
}
finally
{
con.Close();
}
}
public TeacherRoleModule ReadTeacherRole(SqlDataReader reader)
{
TeacherRoleModule teacherRole = new TeacherRoleModule();
while (reader.Read())
{
teacherRole.ID = reader.GetInt32(0);
teacherRole.FirstName = reader.GetString(1);
teacherRole.ModuleName = reader.GetString(2);
teacherRole.RoleName = reader.GetString(3);
}
return(teacherRole);
}
public List <TeacherRoleModule> GetSemesterModule(int facultyid, int semesterid)
{
string retriveListString = "SELECT t.FirstName, t.LastName, r.RoleName, m.ModuleName, tr.ID FROM teacherRoles tr " +
"JOIN Teachers t ON tr.TeacherID = t.TeacherID " +
"JOIN Roles r ON tr.RoleID = r.RoleID " +
"JOIN Modules m ON tr.ModuleID = m.ModuleID AND m.FacultyID = " + facultyid +
" AND m.semesterid = " + semesterid;
SqlCommand cmd = new SqlCommand(retriveListString, con);
try
{
List <TeacherRoleModule> teacherModulesList = new List <TeacherRoleModule>();
con.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.HasRows)
{
while (reader.Read())
{
TeacherRoleModule teacherRole = new TeacherRoleModule();
teacherRole.FirstName = reader.GetString(0);
teacherRole.LastName = reader.GetString(1);
teacherRole.RoleName = reader.GetString(2);
teacherRole.ModuleName = reader.GetString(3);
teacherRole.ID = reader.GetInt32(4);
teacherModulesList.Add(teacherRole);
}
reader.NextResult();
}
}
return(teacherModulesList);
}
finally
{
con.Close();
}
}