Repository.GetAllStudents() Method

GetAllStudents.cs

public List<Student> GetAllStudents()
{
    using (SqlConnection conn = Connection)
    {
        conn.Open();
        using (SqlCommand cmd = conn.CreateCommand())
        {
            cmd.CommandText = @"select s.id as StudentId,
                                       s.FirstName,
                                       s.LastName,
                                       s.SlackHandle,
                                       s.CohortId,
                                       c.[Name] as CohortName,
                                       e.id as ExerciseId,
                                       e.[name] as ExerciseName,
                                       e.[Language]
                                  from student s
                                       left join Cohort c on s.CohortId = c.id
                                       left join StudentExercise se on s.id = se.studentid
                                       left join Exercise e on se.exerciseid = e.id;";
            SqlDataReader reader = cmd.ExecuteReader();

            Dictionary<int, Student> students = new Dictionary<int, Student>();
            while (reader.Read())
            {
                int studentId = reader.GetInt32(reader.GetOrdinal("StudentId"));
                if (!students.ContainsKey(studentId))
                {
                    Student newStudent = new Student
                    {
                        Id = studentId,
                        FirstName = reader.GetString(reader.GetOrdinal("FirstName")),
                        LastName = reader.GetString(reader.GetOrdinal("LastName")),
                        SlackHandle = reader.GetString(reader.GetOrdinal("SlackHandle")),
                        CohortId = reader.GetInt32(reader.GetOrdinal("CohortId")),
                        Cohort = new Cohort
                        {
                            Id = reader.GetInt32(reader.GetOrdinal("CohortId")),
                            Name = reader.GetString(reader.GetOrdinal("CohortName"))
                        }
                    };

                    students.Add(studentId, newStudent);
                }

                if (! reader.IsDBNull(reader.GetOrdinal("ExerciseId")))
                {
                    Student currentStudent = students[studentId];
                    currentStudent.Exercises.Add(
                        new Exercise
                        {
                            Id = reader.GetInt32(reader.GetOrdinal("ExerciseId")),
                            Language = reader.GetString(reader.GetOrdinal("Language")),
                            Name = reader.GetString(reader.GetOrdinal("ExerciseName")),
                        }
                    );
                }
            }

            reader.Close();

            return students.Values.ToList();
        }
    }
}