Filling a hierarchical class structure with data

Question

I have a hierarchical class structure like this:

Category -> Template -> Instance

A category contains multiple templates, a template contains multiple instances.

If I query the data from the database via a join over all 3 tables, the data looks something like this:

CategoryID | CategoryName | CategoryType | TemplateID | TemplateName | TemplateXYZ | InstanceID | InstanceName  
1 | "CatA" | "TypeX" | 1 | "TempA" | "X" | 1 | "InstA" 
1 | "CatA" | "TypeX" | 1 | "TempA" | "X" | 2 | "InstB"
1 | "CatA" | "TypeX" | 1 | "TempA" | "X" | 3 | "InstC"
1 | "CatA" | "TypeX" | 1 | "TempB" | "Y" | 4 | "InstD"

(just an example, the real data tables have a lot more columns)

What is the best/common way in C# to fill the classes with this kind of data when cycling through it with a data reader?

From the top of my head I would do it this way:

while(data.Read())
{
  // Create new objects each time the ID changes and read the data from the first row
  if(data["CategoryID"] != lastCategoryID) {
    lastCategoryID = data["CategoryID"];
    cat = new Category(data["CategoryName"], data["CategoryType"]);
    catList.Add(cat);
  }
  if(data["TemplateID"] != lastTemplateID) {
    lastTempateID = data["TemplateID"];
    template = new Template(data["TemplateName"], data["TemplateXYZ"]));
    cat.Templates.Add(template);
  }
  template.Instances.Add(new Instance(data["InstanceID"], data["InstanceName"]);
}

Is there a better, more elegant solution to fill the hierarchical class objects? Maybe using LINQ or Dictionaries?

Note: This question is related to my other question about the best way to gather hierarchical data from a DB. I split it up because this are two separate issues.

Answer 1

What you do seems like a good way to work it. Just make sure you sort the data in your query by and ID columns you have. Sort by category then template. This will ensure you don't go back to one of those IDs and create the object again.

Also - if a template can be in multiple categories, you will have to store each template in a list somewhere to make sure you don't duplicate them over categories.

Answer 2

As you read from the data reader, populate an object with the data from each row. At this point don't worry about the duplicates:

var rawData = new List<Incoming>();
while (data.Read())
{
    rawData.Add( new Incoming(data[0], data[1],data[2],data[3],data[4],data[5],data[6],data[7]));
}

where

public class Incoming
{
    public int CategoryID { get; set; }
    public string CategoryName { get; set; }
    public int CategoryType { get; set; }
    public int TemplateID { get; set; }
    public string TemplateName { get; set; }
    public int TemplateXYZ { get; set; }
    public int InstanceID { get; set; }
    public string InstanceName { get; set; }

    public Incoming(int categoryID , string categoryName , int categoryType , int templateId,string templateName ,int templateXYZ , int instanceID , string instanceName    )
    {
        CategoryID =categoryID;
        CategoryName = categoryName; CategoryType = categoryType; TemplateID = templateId;
        TemplateName = templateName; TemplateXYZ = templateXYZ; InstanceID = instanceID; InstanceName = instanceName; 
    }
}

then you can use LINQ to get the individual levels of the hierarchy out:

var categories = rawData.GroupBy (d => d.CategoryID );