Geek Answers Handbook

Mapping many-to-many relationships (nested ListViews?)

I appreciate that this can be a pretty simple question, but I have quite a few problems (as an advanced ASP.NET developer). I got a lot of ideas from SO and Google without any luck, and I think I'm starting to think too much about it, it seems like such a standard scenario that I think I'm missing something obvious.

I have, for example, three tables of standard examples - pay attention to the many-to-many relationships.

Students - student_id - forename - surname Courses - course_id - course_name StudentCourses - studentcourse_id - course_id - student_id

I would like to display them on my ASP.NET 3.5 web application so that it looks something like this (the last column is a button that allows them to edit student information):

 Students: # Name Courses Actions 1 Joe Bloggs Maths, English [Manage] 2 Arthur Sleep English [Manage] 3 Andy Mann Maths, German, French [Manage]

The Courses column is a list of courses in which the student is currently enrolled. It can be empty (not yet registered) or contain a list (comma-delimited or standard unordered HTML list, I am not valuable) of their currently registered courses. This will not be a mass list, as a student can only enroll in several courses at any time.

I tried a number of solutions ranging from asp: Repeater to my current favorite, ListView, which comes with 3.5. I use LINQ to SQL as my data layer (in case this is important because of the complex LINQs support for Many-to-Many relationships).

+4
source share
2 answers

Quite simply, you can add a few new properties to your Student class as follows (in C #):

 public IEnumerable<Course> Courses { get { return CourseRepository.GetCoursesByStudentId(this.Student_Id); } } //Aggregate course names into a comma-separated list public string CoursesDescription { get { if (!Courses.Any()) return "Not enrolled in any courses"; return Courses.Aggregate(string.Empty, (currentOutput, c) => (!String.IsNullOrEmpty(currentOutput)) ? string.Format("{0}, {1}", currentOutput, c.Course_Name) : c.Course_Name ); } }

Now that you need only one repeater (tied to your student collection), since the need for a sub-repeater is removed using the "CoursesDescription" property in the Student class.

As an aside, I would highly recommend calling your tables singular (i.e. you name them after what represents 1 row). In addition, there is a strong case for removing the identity column from your StudentCourses table and replacing it with the composite key in Student_Id and Course_Id.

Hope this helps.

+2
source

You can use nested repeaters. In the example below, the secondary method is called in the code behind the file, which returns a list. You can access all course information from this. I could get it to work with the method from the repository class, so I used the following code:

 protected List<tblStudentCourses> GetStudentCoursesByStudentID(int id) { return studentRepository.GetStudentCoursesByStudentID(id).ToList(); }

Here is a complete example:

 <asp:Repeater ID="parentRepeater" runat="server" DataSourceID="myDataSource"> <HeaderTemplate> Header HTML </HeaderTemplate> <ItemTemplate> <asp:Repeater ID="availabilityRepeater" runat="server" DataSource='<%#GetStudentCoursesByStudentID(Convert.ToInt32(Eval("studentCourseID"))) %>'> <ItemTemplate> <%# Eval("tblStudentCourses.courseName") %><br /> </ItemTemplate> </asp:Repeater> Student Name example: <%#Eval("studentName")%> </ItemTemplate> <FooterTemplate> Footer HTML </FooterTemplate>

It may not be the best solution, but it is a working solution, so I hope it helps you

+4
source

More articles:


All Articles