aggregate sql data into business objects- sample code inside -

Question

Do you see a better way to read data from sqlite database into business collections?

These are 2 methods of my Repository.

I want to get all Schoolclass entites with/without referencing Pupil entities (see Left outer join)

Is there anything that I can improve ? Haven`t found any good tips about that scenario in google so I tried myself...

public ObservableCollection<Schoolclass> GetSchoolclassPupilList()
        { 
            using (var trans = DataAccess.ConnectionManager.BeginTransaction())
            using (var com = new SQLiteCommand(DataAccess.ConnectionManager))
            {
                com.CommandText = "SELECT * FROM SCHOOLCLASS LEFT OUTER JOIN PUPIL ON SCHOOLCLASS.schoolclassid = PUPIL.schoolclassid_fk ORDER BY SCHOOLCLASS.schoolclassid"; // _fk umbennen

                var schoolclassIdList = new List<int>();
                var schoolclassList = new ObservableCollection<Schoolclass>();
                int schoolclassID;

                using (var reader = com.ExecuteReader())
                {
                    Schoolclass schoolclass = null;      
                    while (reader.Read())
                    {
                        schoolclassID = Convert.ToInt32(reader["schoolclassId"]);
                        if (schoolclassIdList.Contains(schoolclassID))
                        {
                            CreatePupil(ref schoolclass, reader);
                        }
                        else
                        {
                            schoolclass = new Schoolclass();
                            CreatePupil(ref schoolclass, reader);
                            schoolclassList.Add(schoolclass);
                        }
                        schoolclassIdList.Add(schoolclassID);                       
                    }
                }
                trans.Commit();
                return schoolclassList;
            }
        }

        private void CreatePupil(ref Schoolclass schoolclass, SQLiteDataReader reader)
        {
            // Schoolclass
            schoolclass.Id = Convert.ToInt32(reader["schoolclassId"]);
            schoolclass.SchoolclassCode = reader["schoolclasscode"].ToString();            

            // if the pupil (FK is null) has no reference to a schoolclass
            if (!DBNull.Value.Equals(reader["schoolclassId_FK"]))
            {
                // Pupil
                var pupil = new Pupil();

                pupil.Id = Convert.ToInt32(reader["pupilId"]);
                pupil.FirstName = reader["firstname"].ToString();
                pupil.LastName = reader["lastname"].ToString();
                pupil.Gender = reader["gender"].ToString();
                pupil.Street = reader["street"].ToString();
                pupil.City = reader["city"].ToString();
                pupil.Postal = reader["postal"].ToString();
                pupil.Phone = reader["phone"].ToString();
                pupil.SchoolclassCodeID = Convert.ToInt32(reader["schoolclassId_FK"]);

                schoolclass.Pupils.Add(pupil);
            }           
        }