Displaying Many-to-Many Relationships
Displaying a master-details relation in a form is really not a big deal. You set up one control, which is bound to the master table, and one DataGrid, which is bound to the relation between the master table and the child table like so:
adpSoldiers.Fill(ds, "Soldiers")
ds.Relations.Add("SoldierNotes", ds.Tables("Soldiers").Columns("ID"),
ds.Tables("Notes").Columns("SoldierID"))
In this code, we have one table, Soldiers and for each soldier we can have one or more notes, which is a simple one-to-many relationship. All we have to do in order to display a master details form here is have two DataGrids, one bound to ds.Tables("Soldiers") and one bound to ds with a DataMember property of "Soldiers.SoldierNotes".
The problem arises when we have a many-to-many relationship. Let's say we have another table named Courses.” Each soldier can be enrolled into one or more courses, and each course can be taken by one or more soldiers. For that purpose, we have a third table, Registrations,” which contains only SoldierID and CourseID as columns with a PK comprised of both of these columns. Suppose we want to show, for the selected soldier in grid 1, all the courses, including course name, in which he is enrolled. Simple data binding to the relation between soldiers and registrations will not do.
adpSoldiers.Fill(ds, "Soldiers")
adpSoldiers.Fill(ds, "Courses")
adpSoldiers.Fill(ds, "Registrations")
ds.Relations.Add("SolCourses", ds.Tables("Soldiers").Columns("ID"),
ds.Tables("Registrations ").Columns("SoldierID"))
We get unreadable data in the Details grid only showing SoldierID and CourseID columns. So what's the solution? Unfortunately, as much as I looked, there is not really an elegant solution, but here's the best way I've found to overcome this. We can add calculated columns into our Registrations data table, which, for each data row in the table, displays the value of the parent Courses row's Name value, using a predefined relation.
To accomplish this, we first add this relation into our relations collection, which enables us to retrieve the parent Courses row for each Registrations row according to the CourseID value:
ds.Relations.Add("CourseReg", ds.Tables("Courses").Columns("ID"),
ds.Tables("Registrations ").Columns("CourseID"))
Next, we add the calculated columns to our Registrations data table:
ds.Tables("Registrations").Columns.Add("Course", GetType(String), "Parent(CourseReg).Name")
What I pass in to the Columns constructor is the name of the new columns, the type of displayed data in those columns, and the expression that the columns should hold. To retrieve the parent row using a specified relation, I write Parent(RelationName).ColumnName. I could also get a child aggregate function using this data column, by specifying Count(Child(RelationsName).ID). If the DataTable only has one relation defined, I don't even have to specify the name of the relation to use, like so: Sum(Child.Price). To learn more about calculated columns, here's a nice article about the subject.
Now that we have our calculated column in place, we can use the same relation in the details grid, only this time we see the name of the course as well as the other columns. The calculated columns in the DataGrid are read-only, as they should be. A new problem is encountered, though. The user still cannot add new items to the DataGrid, without knowing in advance the ID of the needed course and the soldier. Once those values are entered inside the new row in the grid, the Name column displays the name of the new course registration, but not before then. For that you either have to have a combo box that is placed over the CourseID Active cell, or other, more user-friendly external means of inserting a row to the data table.