//~~~~~~~~~~~~~~~~~~~~~~~~~~~ Get Students Per Course Insert ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
public static List <StudentPerCourse> GetStudenPerCoursesInsert()
{
List <StudentPerCourse> studentPerCourses = new List <StudentPerCourse>();
string query = "select c.CourseId, Stream, s.StudentId, FirstName, LastName from Student s " +
"left join CourseStudent cs on cs.StudentId = s.StudentId " +
"left join Course c on c.CourseId = cs.CourseId " +
"order by c.CourseId";
using (SqlConnection con = new SqlConnection(ConString))
{
con.Open();
SqlCommand sqlCommand = new SqlCommand(query, con);
SqlDataReader reader = sqlCommand.ExecuteReader();
int count = 0;
while (reader.Read())
{
count++;
int? idCourse = string.IsNullOrWhiteSpace(reader[0].ToString()) ? (int?)null : Convert.ToInt32(reader[0].ToString());
string stream = reader[1].ToString();
int idStudent = Convert.ToInt32(reader[2]);
string firstName = reader[3].ToString();
string lastName = reader[4].ToString();
StudentPerCourse SPC = new StudentPerCourse(count, idCourse, stream, idStudent, firstName, lastName);
studentPerCourses.Add(SPC);
}
}
return(studentPerCourses);
}
//------------- Get All the Students per Course
public static List <StudentPerCourse> GetStudentPerCourses()
{
List <StudentPerCourse> studentPerCourses = new List <StudentPerCourse>();
string query = "select Title, Stream, Type, FirstName, LastName from Student s " +
"inner join CourseStudent cs on cs.StudentId = s.StudentId " +
"inner join Course c on c.CourseId = cs.CourseId " +
"order by Title, LastName";
using (SqlConnection con = new SqlConnection(ConString))
{
con.Open();
SqlCommand sqlCommand = new SqlCommand(query, con);
SqlDataReader reader = sqlCommand.ExecuteReader();
int count = 0;
while (reader.Read())
{
count++;
string title = reader[0].ToString();
string stream = reader[1].ToString();
string type = reader[2].ToString();
string firstName = reader[3].ToString();
string lastName = reader[4].ToString();
StudentPerCourse SPC = new StudentPerCourse(count, title, stream, type, firstName, lastName);
studentPerCourses.Add(SPC);
}
}
return(studentPerCourses);
}
public static void InsertData()
{
ShowInsertData();
string input = CheckCorrectValueInsertMenu();
if (input == "1")
{
//Insert Students
Console.WriteLine();
Student.InsertStudents();
Student.Output();
Exit.CheckToExitFolderInput();
}
else if (input == "2")
{
//Insert Trainers
Console.WriteLine();
Trainer.InsertTrainers();
Trainer.Output();
Exit.CheckToExitFolderInput();
}
else if (input == "3")
{
//Insert Assignments
Console.WriteLine();
Assignment.InsertAssignment();
Assignment.Output();
Exit.CheckToExitFolderInput();
}
else if (input == "4")
{
//Insert Courses
Console.WriteLine();
Course.InsertCourses();
Course.Output();
Exit.CheckToExitFolderInput();
}
else if (input == "5")
{
//Insert Students Per Course
Console.WriteLine();
Course.Output();
StudentPerCourse.OutputInsert();
StudentPerCourse.InsertStudentPerCourses();
Console.WriteLine();
StudentPerCourse.OutputInsert();
Exit.CheckToExitFolderInput();
}
else if (input == "6")
{
//Insert Trainers Per Course
Console.WriteLine();
Course.Output();
TrainerPerCourse.OutputInsert();
TrainerPerCourse.InsertTrainerPerCourses();
TrainerPerCourse.OutputInsert();
Exit.CheckToExitFolderInput();
}
else if (input == "7")
{
//Insert Assignments Per Student Per Course
Console.WriteLine();
Exit.CheckToExitFolderInput();
}
else if (input == "8")
{
//Exit
Exit.CheckToExitFolderInput();
}
}
public static void ExportData()
{
ShowExportData();
string input = CheckCorrectValueExporttMenu();
if (input == "1")
{
//Students List
Student.Output();
Exit.CheckToExitFolderSyntetic();
}
else if (input == "2")
{
//Trainers List
Trainer.Output();
Exit.CheckToExitFolderSyntetic();
}
else if (input == "3")
{
//Assignmgents List
Assignment.Output();
Exit.CheckToExitFolderSyntetic();
}
else if (input == "4")
{
//Courses List
Course.Output();
Exit.CheckToExitFolderSyntetic();
}
else if (input == "5")
{
//Students Per Course
StudentPerCourse.Output();
Exit.CheckToExitFolderSyntetic();
}
else if (input == "6")
{
//Trainers Per Course
TrainerPerCourse.Output();
Exit.CheckToExitFolderSyntetic();
}
else if (input == "7")
{
//Assignments Per Course
AssignmentPerCourse.Output();
Exit.CheckToExitFolderSyntetic();
}
else if (input == "8")
{
//Assignments Per Course Per Student
AssignmentPerCoursePerStudent.Output();
Exit.CheckToExitFolderSyntetic();
}
else if (input == "9")
{
//Students than belong to more than one Courses
StudentCourses.Output();
Exit.CheckToExitFolderSyntetic();
}
else if (input == "10")
{
//Exit
Exit.CheckToExitFolderSyntetic();
}
}
