ASP.net convert SqlDataReader to tree

Question

Given the simple data structure:

ID    |    Category_Name    |    Parent_ID

Example:

1          Cars                    0
2          Boxes                   0
3          Lamborghinis            1
4          Camper Vans             1
5          Big Boxes               2
6          Small Boxes             2
7          Cereal Boxes            2
8          Broken Lambos           3
9          Yellow Ones             3
10         Rusty                   8
11         Milkshake Stained       8
12         Chocolate Flavour       11
13         Strawberry              11
14         Indiscernible Solution  11

Along with my code:

// Fetch current site setting
using (SqlCommand cmd = new SqlCommand("SELECT ID, catName, parentID FROM tblProductCats ORDER BY parentID ASC", cn))
{
    SqlDataReader rdr = cmd.ExecuteReader();

    if (rdr.HasRows)
    {
        while (rdr.Read())
        {
            // Fetch data
            int catID = int.Parse(rdr[0].ToString());
            string catName = rdr[1].ToString();
            int catParent = int.Parse(rdr[2].ToString());
        }
    }
}

I need to convert that returned data into a tree structure so I can go through it displaying the menu in a pretty way!

I've been stuck on this for a while any help appreciated.

Answer 1

Take a look at Display Hierarchical Data with TreeView in ASP.NET.

Answer 2

It doesn't look like an optimal solution, but maybe you can find some inspiration in this CodeProject.

Answer 3

Let's say we have a class/struct Category such that

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

  private Category _Parent;
  public Category Parent {get { return _Parent; } 
   set {
     _Parent = value;
     _Parent.Children.Add(this);
   }
  }

  public List<Category> Children {get; private set; }

  public Category()
  {
    Children = new List<Category>();
  }
}

Now, if your query always order category's parent before it self then you can use below code

  var dict = new Dictionary<int, Category>();
  Category tree = null;
  while (rdr.Read())
    {
        // Fetch data
        int catID = int.Parse(rdr[0].ToString());
        string catName = rdr[1].ToString();
        int catParent = int.Parse(rdr[2].ToString());

        var category = new Category();
        category.Id = catID;
        category.Name = catName;
        dict[catID] = category;
        if (catParent > 0) {
           category.Parent = dict[catParent];
        }
        if (null == tree) 
        {
          tree = category;
        }
    }

So Tree variable should have your category tree.