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