internal void EraseClassFromClasses(GestioneClass Class)
{
//EraseAllStudentsOfAClass(Class);
using (DbConnection conn = dl.Connect())
{
// delete all the references in link table between students and classes
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "DELETE FROM Classes_Students" +
" WHERE Classes_Students.idClass=" + Class.IdClass +
";";
cmd.ExecuteNonQuery();
// erase class from Classes_SchoolSubjects
cmd.CommandText = "DELETE FROM Classes_SchoolSubjects" +
" WHERE Classes_SchoolSubjects.idClass=" + Class.IdClass +
";";
cmd.ExecuteNonQuery();
// erase class from Classes_Tests
cmd.CommandText = "DELETE FROM Classes_Tests" +
" WHERE Classes_Tests.idClass=" + Class.IdClass +
";";
cmd.ExecuteNonQuery();
// erase class from table Classes
cmd.CommandText = "DELETE FROM Classes" +
" WHERE Classes.idClass=" + Class.IdClass +
";";
cmd.ExecuteNonQuery();
cmd.Dispose();
}
}
internal void EraseAllStudentsOfAClass(GestioneClass Class)
{
using (DbConnection conn = dl.Connect())
{
// erase all the info in tables linked to student
// erase all the grades of the students of the class
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "DELETE FROM Grades WHERE idStudent IN" +
"(SELECT Students.idStudent FROM Students" +
" JOIN Classes_Students ON Students.idStudent = Classes_Students.idStudent" +
" WHERE Classes_Students.idClass=" + Class.IdClass + ");";
cmd.ExecuteNonQuery();
// erase all the questions of the students of the class
cmd.CommandText = "DELETE FROM StudentsQuestions WHERE idStudent IN" +
"(SELECT Students.idStudent FROM Students" +
" JOIN Classes_Students ON Students.idStudent = Classes_Students.idStudent" +
" WHERE Classes_Students.idClass=" + Class.IdClass + ");";
cmd.ExecuteNonQuery();
// erase all the answers of students of the class
cmd.CommandText = "DELETE FROM StudentsAnswers WHERE idStudent IN" +
"(SELECT Students.idStudent FROM Students" +
" JOIN Classes_Students ON Students.idStudent = Classes_Students.idStudent" +
" WHERE Classes_Students.idClass=" + Class.IdClass + ");";
cmd.ExecuteNonQuery();
// erase all the tests of students of the class
cmd.CommandText = "DELETE FROM StudentsTests WHERE idStudent IN" +
"(SELECT Students.idStudent FROM Students" +
" JOIN Classes_Students ON Students.idStudent = Classes_Students.idStudent" +
" WHERE Classes_Students.idClass=" + Class.IdClass + ");";
cmd.ExecuteNonQuery();
// delete all the photos of students of the class
cmd.CommandText = "DELETE FROM StudentsPhotos WHERE StudentsPhotos.idStudentsPhoto IN" +
"(SELECT StudentsPhotos_Students.idStudentsPhoto" +
" FROM StudentsPhotos, StudentsPhotos_Students, Classes_Students" +
" WHERE StudentsPhotos_Students.idStudent = Classes_Students.idStudent" +
" AND StudentsPhotos.idStudentsPhoto = StudentsPhotos_Students.idStudentsPhoto" +
" AND Classes_Students.idClass=" + Class.IdClass + ");";
cmd.ExecuteNonQuery();
// delete all the references in link table to photos of students of the class
cmd.CommandText = "DELETE FROM StudentsPhotos_Students WHERE idStudent IN" +
"(SELECT StudentsPhotos_Students.idStudent" +
" FROM StudentsPhotos_Students, Classes_Students" +
" WHERE StudentsPhotos_Students.idStudent = Classes_Students.idStudent" +
" AND Classes_Students.idClass=" + Class.IdClass + ");";
cmd.ExecuteNonQuery();
// delete all the students in class
// AFTER THIS idStudent OF DELETED IN NOT AVAILABLE ANY LONGER
cmd.CommandText = "DELETE FROM Students WHERE idStudent IN" +
"(SELECT Students.idStudent FROM Students" +
" JOIN Classes_Students ON Students.idStudent = Classes_Students.idStudent" +
" WHERE Classes_Students.idClass=" + Class.IdClass + ");";
cmd.ExecuteNonQuery();
cmd.Dispose();
}
}
