I'm kind a new on c#. I have a problem with to store the className to list since i need to display all the class that teacher taught. On result, it turns out just the last class teacher taught. I did use join table between teacher and classes.

Model

    public class Teacher
    {
      public int teacherId { get; set; }
      public string teacherfName { get; set; }
      public string teacherlName { get; set; }       
      public string className { get; set; }
      public int classId { get; set; }
    }

Controller

public Teacher FindTeacher(int id)
    {
        Teacher newTeacher = new Teacher();
        MySqlConnection Conn = school.AccessDatabase();

        Conn.Open();

        MySqlCommand cmd = Conn.CreateCommand();

        //SQL QUERY
        cmd.CommandText = "Select * from teachers left join classes on teachers.teacherid=classes.teacherid where teachers.teacherid = "   id;

        //Gather Result Set of Query into a variable
        MySqlDataReader ResultSet = cmd.ExecuteReader();

        while (ResultSet.Read())
        {
            int teacherId = (int)ResultSet["teacherId"];
            string teacherfName=ResultSet["teacherfname"].ToString();
            string teacherlName=ResultSet["teacherlname"].ToString();
            newTeacher.teacherId = teacherId;
            newTeacher.teacherFName = teacherFName;
            newTeacher.teacherLName = teacherLName;
            newTeacher.className = className;
            newTeacher.classId = (int)ResultSet["classid"];
        }

        return newTeacher;
    }

CodePudding user response：

Your only returning one teacher if you want all the teachers your code should be:

public IEnumerable<Teacher> FindTeacher(int id)
    {
        //Lise here
        List<Teacher> teachers = new List<Teacher>();

        //note the using
        using MySqlConnection Conn = school.AccessDatabase();

        Conn.Open();

        //note the using
        using MySqlCommand cmd = Conn.CreateCommand();

        //SQL QUERY
        cmd.CommandText = "Select * from teachers left join classes on teachers.teacherid=classes.teacherid where teachers.teacherid = "   id;

        //Gather Result Set of Query into a variable
        MySqlDataReader ResultSet = cmd.ExecuteReader();

        while (ResultSet.Read())
        {
            //new teacher in the loop
            Teacher newTeacher = new Teacher();

            int teacherId = (int)ResultSet["teacherId"];
            string teacherfName=ResultSet["teacherfname"].ToString();
            string teacherlName=ResultSet["teacherlname"].ToString();
            newTeacher.teacherId = teacherId;
            newTeacher.teacherFName = teacherFName;
            newTeacher.teacherLName = teacherLName;
            newTeacher.className = className;
            newTeacher.classId = (int)ResultSet["classid"];

           //add to the collection
           teachers.Add(newTeacher);
        }
        //return the collection
        return teachers;
    }

If also added using statements. These are important to prevent memory leaks

CodePudding user response：

Modify Teacher Class to be able to carry List of TeacherClass that correspond to one teacher:

Define New Class TeacherClass to Carry a TeacherClass Data

public class TeacherClass
    {      
      public string Name { get; set; }
      public int Id { get; set; }
    }

Modify Teacher Class To have a List Of TeacherClass

public class Teacher
    {
      public int teacherId { get; set; }
      public string teacherfName { get; set; }
      public string teacherlName { get; set; } 
      public List<TeacherClass> classes { get; set; } = new List<TeacherClass>();
    }

Then get your function to set this TeacherClass List in a loop:

public Teacher FindTeacher(int id)
    {
        Teacher newTeacher = new Teacher();
        MySqlConnection Conn = school.AccessDatabase();

        Conn.Open();

        MySqlCommand cmd = Conn.CreateCommand();

        //SQL QUERY
        cmd.CommandText = "Select * from teachers left join classes on teachers.teacherid=classes.teacherid where teachers.teacherid = "   id;

        //Gather Result Set of Query into a variable
        MySqlDataReader ResultSet = cmd.ExecuteReader();
        
        // Check if any rows retrieved
        if (reader.HasRows)
        {
            // Iterate Over Rows
            while (ResultSet.Read())
            {
               // Set Teacher Data Just Once
               if(newTeacher.teacherId == 0){
                  newTeacher.teacherId = (int)ResultSet["teacherId"];;
                  newTeacher.teacherFName = ResultSet["teacherfname"].ToString();
                  newTeacher.teacherLName = ResultSet["teacherlname"].ToString();
               }
               
               // Add new TeacherClass data for this teacher
               newTeacher.classes.Add(
                  new TeacherClass(){
                       Name = className, // className Check this variable as it is not declared
                       Id = (int)ResultSet["classid"]
                  });
           }
        }
        return newTeacher;
    }