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(); } }