This is part one of a three part series on working with master-detail data. In part one I will show how to present detail values as indexed values on the master using a utility I wrote called the SubAttributeAccessor. In part two I will show how the SubAttributeAccessor works. In part three I will introduce a descendant class of DataGridViewColumn called the SubPropertyColumn that will allow showing the detail values as part of the master data.
On more than one occasion I’ve been asked to present master-detail data with the detail data being presented as if they were actually additional fields on the master record.
One time there were literally hundreds of detail records, but only one or two of them would be shown at a time. The master data were names and each of them had a never-ending list of scores. The names were presented in a table and the last few scores were shown as additional columns with the date of the score as the header.
Another time, it was a list of songs where each song had the usual attributes like artist, title, album, etc. However, each song could also have an unlimited list of “user attributes”. For example, one user might want to add a tempo and a category to his songs. Another user might want to add a genre, a year and a rating to his songs.
I’m going to use a simpler version of this last example in this article. The data will be stored in two tables: a songs table and an attributes table. The songs table will have three fields: a unique code, an artist and a title. The attributes table will have four fields: a unique code, a song code, a field name and a value. The song code will directly relate each attribute to exactly one song. The field name will be used to identify the the data (e.g.: tempo or score). The value will store binary data of no particular type. This is where each of the attributes actually will be stored. There is no limit to the number of additional attributes a song can have.
This master-detail setup is also reflected in the objects used to represent this data in code. There will be a class named Song and a class named Attribute. The Song class will own an unlimited list of Attribute. In the attached solution, I used LINQ to SQL to generate the initial classes.
1: using System.Collections.Generic;
2: using System.IO;
3: using System.Runtime.Serialization.Formatters.Binary;
4: using System.Text;
5: namespace SubPropertyColumns {
6: partial class Song {
7:
8: private SubAttributeAccessor<Attribute> fProperties = null;
9:
10: /// <summary>
11: /// Used to access the SongPropertiesAccessor class which
12: /// has an indexed property to access the additional
13: /// properties.
14: ///
15: /// since the Song class already has a member called Attributes,
16: /// we'll call this Properties
17: /// </summary>
18: public SubAttributeAccessor<Attribute> Properties {
19: get {
20:
21: //the the accessor hasn't been created yet,
22: //create it passing a refrerence to this Song
23: if (fProperties == null) {
24: fProperties = new SubAttributeAccessor<Attribute>(this, "Attributes", "FieldName", "Value");
25: }
26:
27: return fProperties;
28: }
29: }
30:
31: /// <summary>
32: /// This is the default indexed property
33: /// It is just a wrapper around the Properties property
34: /// that allows us to access the functionality without having
35: /// to use the Properties name.
36: /// </summary>
37: /// <param name="index"></param>
38: /// <returns></returns>
39: public object this[string index] {
40: get {
41: return Properties[index];
42: }
43: set {
44: Properties[index] = value;
45: }
46: }
47:
48: }
49: }
LINQ to SQL generates the classes as partial classes which allows us to add a few properties of our own.
The first thing we add, on line 8, is a private field of type SubAttributeAccessor<T>. The SubAttributeAccessor does all of the work accessing the list of attributes and presenting them as an indexed property.
Next, on line 18, we add a public property to access the SubAttributeAccessor. The property checks to see if SubAttributeAccessor is created and if it isn’t it creates it. When creating the SubAttributeAccessor, on line 24, we need to add a few important parameters. First, we need to include the type parameter <Attribute>. This defines what the type of each of the attributes will be. In our case, the type of the attribute is Attribute. We also need to add a reference to the object itself and the names of several fields. We need the reference to the object itself so that the SubAttributeAccessor can have access to the list of attributes. Then, we need to tell the SubAttributeAccessor the name of the property on the object that is the list of attributes. In this case it is “Attributes”. We also need to tell the SubAttributeAccessor the name of the property on each of the attributes that will be used as the index. In this case it is “FieldName”. Finally, we need to tell the SubAttributeAccessor the name of the property on each of the attributes that will be the actual value of the attribute. In this case it is “Value”. It is important to note that the first property name is the name of a property on the master object – it is the property that returns a list of detail objects. In this case it is the Attributes property on the Song class itself. The next two property names are names of properties on the detail object. In this case, they are the FieldName and Value properties of the Attribute class. It is also important to note that we named the SubAttributeAccessor property “Properties” because the Song class already has a property named Attributes.
Now that we have a SubAttributeAccessor hooked up to our Song class, we can already write code like:
1: Song MySong = new Song();
2: MySong.Properties["Tempo"] = "Medium";
3: MySong.Properties["Rating"] = 5;
However, we can take this one step further. On line 39, we add a default indexed property that is just a wrapper around the Properties property. This allows us to drop the poorly named .Properties and just use the index directly on Song like so:
1: Song MySong = new Song();
2: MySong["Tempo"] = "Medium";
3: MySong["Rating"] = 5;
At this point we’re able to access the detail data as indexed values of the master. In the next part I’ll show how the SubAttributeAccessor works. Finally, in the third part, I’ll introduce a descendant of DataGridViewColumn that will allow us to show the indexed detail data in line with the master data as additional columns.
Source Code : SubPropertyColumns.zip (485 KB)