public void AddTrainer(Trainer trainer) { _trainers.Add(trainer); }
public List <TrainersPerCourse> SelectAllTrainersPerCourse() { List <TrainersPerCourse> trainersPerCourseList = new List <TrainersPerCourse>(); using (SqlConnection conn = new SqlConnection(_connectionString)) { conn.Open(); using (SqlCommand cmd = new SqlCommand("SELECT TrainersPerCourse.CourseId, Courses.Title, Courses.Stream, Courses.Type, Courses.StartDate, Courses.EndDate, " + "TrainersPerCourse.TrainerId, Trainers.FirstName, Trainers.LastName, Trainers.Subject" + " FROM Trainers INNER JOIN TrainersPerCourse ON Trainers.TrainerId = TrainersPerCourse.TrainerId" + " INNER JOIN Courses ON TrainersPerCourse.CourseId = Courses.CourseId" + " ORDER BY TrainersPerCourse.CourseId, TrainersPerCourse.TrainerId; ", conn)) { try { using (SqlDataReader reader = cmd.ExecuteReader()) { int courseId = 0; // initialize this to 0 since CourseId starts from 1 Course course = null; Trainer trainer; while (reader.Read()) { // Since rows are coming ordered by CourseId we create new course entry in trainersPerCourseList only when we find a CourseId different than the one we have already if (courseId != (int)reader["CourseId"]) { courseId = (int)reader["CourseId"]; StreamValue stream; TypeValue type; string value = (string)reader["Stream"]; if (value.Equals("C#")) { stream = StreamValue.CSharp; } else { stream = StreamValue.Java; } value = (string)reader["Type"]; if (value.Equals("Full-Time")) { type = TypeValue.Full_Time; } else { type = TypeValue.Part_Time; } course = new Course((int)reader["CourseId"], (string)reader["Title"], stream, type, (DateTime)reader["StartDate"], (DateTime)reader["EndDate"]); trainersPerCourseList.Add(new TrainersPerCourse(course)); } trainer = new Trainer((int)reader["TrainerId"], (string)reader["FirstName"], (string)reader["LastName"], (string)reader["Subject"]); // Trainers are added always in the course entry that equals current course instance since they come ordered by course if (trainersPerCourseList.Find(x => x.Course.Equals(course)) != null) { trainersPerCourseList.Find(x => x.Course.Equals(course)).AddTrainer(trainer); } else { throw new NullReferenceException("Course not found."); } } } } catch (SqlException e) { Console.WriteLine(e.Message); } catch (Exception e) { Console.WriteLine(e.Message); } } } return(trainersPerCourseList); }