public List <LessonInstructors> GetAllLessonInstructors(string userID)
{
List <LessonInstructors> listMod = new List <LessonInstructors>();
MySqlConnection con = new MySqlConnection(DbCon.connectionString);
string sqlInsert = "SELECT studentlessons.id,studentlessons.classID,studentlessons.moduleID,studentlessons.title,studentlessons.description,studentlessons.`code`,studentlessons.type,studentlessons.credit,IFNULL(tutors.fname,'Add Teaching Staff') AS fname,IFNULL(tutors.onames,'Add Teaching Staff') AS onames,IFNULL(tutors.sname,'Add Teaching Stafff') AS sname,IFNULL(tutors.userId,'0') AS tutorId,studentlessonsinstructors.id AS lesstutorId FROM studentlessons INNER JOIN studentlessonsinstructors ON studentlessons.id = studentlessonsinstructors.lessonID INNER JOIN tutors ON tutors.userId = studentlessonsinstructors.instructorID ";
MySqlDataReader dr = null;
MySqlCommand cmd;
con.Open();
cmd = new MySqlCommand(sqlInsert, con);
dr = cmd.ExecuteReader();
while (dr.Read()) //iterate through the records in the result dataset
{
LessonInstructors Mod = new LessonInstructors();
Mod.LessonID = dr.GetInt32(0);
Mod.ClassID = dr.GetInt32(1);
Mod.ModuleID = dr.GetInt32(2);
Mod.LessonTitle = dr.GetString(3);
Mod.LessonDescription = dr.GetString(4);
Mod.Code = dr.GetString(5);
Mod.Type = dr.GetString(6);
Mod.Credit = dr.GetInt32(7);
Mod.FName = dr.GetString(8);
Mod.ONames = dr.GetString(9);
Mod.SName = dr.GetString(10);
Mod.TutorID = dr.GetString(11);
Mod.ID = dr.GetInt32(12);
if (dr.GetString(8) == "Assign a Teaching Staff")
{
Mod.TutorName = "Assign a Teaching Staff";
}
else
{
Mod.TutorName = dr.GetString(8) + " " + dr.GetString(10);
}
listMod.Add(Mod);
}
con.Close();
return(listMod);
}
public bool UpdateLessonInstructor(LessonInstructors upLessInstr)
{
bool result = false;
MySqlConnection con = new MySqlConnection(DbCon.connectionString);
string sqlUpdate = "UPDATE `studentlessonsinstructors` SET `lessonID`=@lessonID,`instructorID`=@instructorID,`lastModified`=@lastModified WHERE id = @lessonID";
con.Open();
MySqlCommand cmd = new MySqlCommand(sqlUpdate, con);
cmd.Parameters.AddWithValue("@lessonID", upLessInstr.LessonID);
cmd.Parameters.AddWithValue("@instructorID", upLessInstr.TutorID);
cmd.Parameters.AddWithValue("@lastModified", upLessInstr.LastModified);
if (cmd.ExecuteNonQuery() > 0)
{
result = true;
}
con.Close();
return(result);
}
public bool AddLessonInstructor(LessonInstructors insLessInstr, string userID)
{
bool result = false;
MySqlConnection con = new MySqlConnection(DbCon.connectionString);
string sqlInsert = "INSERT INTO `studentlessonsinstructors`( `lessonID`, `instructorID`, `createdDate`, `lastModified`) VALUES (@lessonID,@instructorID,@createdDate,@lastModified)";
con.Open();
MySqlCommand cmd = new MySqlCommand(sqlInsert, con);
cmd.Parameters.AddWithValue("@lessonID", insLessInstr.LessonID);
cmd.Parameters.AddWithValue("@instructorID", insLessInstr.TutorID);
cmd.Parameters.AddWithValue("@createdDate", insLessInstr.DateAssigned);
cmd.Parameters.AddWithValue("@lastModified", insLessInstr.LastModified);
int affectedrows = cmd.ExecuteNonQuery();
if (affectedrows > 0)
{
result = true;
}
con.Close();
return(result);
}
