Member Avatar for csolutions

I have an access database from which i have to retrieve data from access database into c# showing tree view in my application. here is what i want to do. I have this access database with the following tables.

categoryid   | Category Name | Parent Category 
===============================================
1000            Food              996
996            contemporary       13
995            Anything           13
13             Glassware          50693

This is just an example so now when the tree is displayed it should be like this

Glassware (13)
||
  -> Contemporary (996)
      ||
         -> Food  (1000)

  -> Anything   (995)

Like this there are numerous parent and child categories. I am trying to find an algorithm in c Sharp which will do this for me.

I appreciate any help which i can get

Edited by csolutions because: n/a
  • 3 Contributors
  • 4 Replies
  • 693 Views
  • 1 Year Discussion Span
  • Latest Post Latest Post by sayantanbagchi

Dani AI

Generated

described the common adjacency-list layout (id / parent-id) where rows can arrive in any order. ’s single-pass insert works only when parents always appear before children. A simple, robust pattern is a two-pass build using an in-memory dictionary: create node objects for every row first, then link each node to its parent using dictionary lookups. That guarantees correct structure regardless of input order and runs in O(n) time.

Steps in plain terms:

  1. Read the three columns (id, name, parent) into a light node object for each row and store by id in a Dictionary<int,Node>.
  2. Iterate the dictionary values and for each node attach it to map[parentId] when present; otherwise add it to a roots list (treat missing parent as an orphan/root).
  3. Convert the roots into UI TreeNodes (recursive or iterative) and add to the TreeView.
    Handle DBNull/zero parent values, detect parent==id, and optionally collect orphans or create placeholder parent nodes. For very deep trees use an iterative conversion to avoid stack overflow.

Example C# sketch (adjust column names to match the Access table):

class CatNode {
    public int Id;
    public string Name;
    public int? ParentId;
    public List<CatNode> Children = new List<CatNode>();
}

List<CatNode> BuildTree(DataTable tbl, string idCol="categoryid", string nameCol="Category Name", string parentCol="Parent Category")
{
    var map = new Dictionary<int, CatNode>();
    foreach (DataRow r in tbl.Rows)
    {
        int id = Convert.ToInt32(r[idCol]);
        string name = Convert.ToString(r[nameCol]);
        int? pid = r.IsNull(parentCol) ? (int?)null : Convert.ToInt32(r[parentCol]);
        map[id] = new CatNode { Id = id, Name = name, ParentId = pid };
    }

    var roots = new List<CatNode>();
    foreach (var node in map.Values)
    {
        if (node.ParentId.HasValue && map.TryGetValue(node.ParentId.Value, out var parent))
            parent.Children.Add(node);
        else
            roots.Add(node);
    }
    return roots;
}

TreeNode ToTreeNode(CatNode node)
{
    var tn = new TreeNode(node.Name + " (" + node.Id + ")") { Tag = node.Id };
    foreach (var c in node.Children) tn.Nodes.Add(ToTreeNode(c));
    return tn;
}

For web scenarios (as asked), serialize the root nodes to JSON and render client-side (jsTree, nested UL/LI). Show details on hover with a small AJAX call or embed the data in attributes and fetch extra info on expand.

Recommended Answers

All 4 Replies

Member Avatar for Alexpap

Hey there,
What could you do is to run a SELECT statement like this:

SELECT * FROM [TableName]

After you got the query results into a DataTable Object, you should run a For each loop like this:

For each(DataRow dr in DataTable)
{
  TreeNode trnode = new TreeNode();

  if(dr["ParentCategory"].Value == 0)
  {
   trnode.text = dr["CategoryName"].Value.ToString();
   trnode.Tag = dr["CategoryId"].value.toString();
   TreeView.nodes.Add(trnode);
  }
  else
  {
   TreeNode parent = //Find the ParentNode from the Tag Property
   trnode.text = dr["CategoryName"].Value.ToString();
   trnode.Tag = dr["CategoryId"].value.toString();
   parent.Add(trnode);
  }
}

Note that in order for this to work you have to spesify that the nodes that don't have a parent( which means that they are parent nodes) have to have a unique number (like 0 or whatever you like).

Cheers.

Member Avatar for csolutions

Thanks for your help but that didn't quite work for what i was looking for. This algorithm will work only when the DB table is sorted, but my table is not. It is not necessary that a child's parent row arrives prior to its row but a parent row can arrive after in the table.

Edited by csolutions because: n/a
Member Avatar for Alexpap

Then modify your sql statement to look like this, so the parents should come first and then the children :) :

SELECT * FROM [TableName] ORDER BY ASC


Hope i helped this time :p

Member Avatar for sayantanbagchi

HI Friends,

i am also looking for a code which will show the employees of a organization as tree. it will take input from a database server. it will be more more helpful if you can show it as a web page.

if we click or point the mouse over an employee it will show all the details of that employee.

can you please tell me that is it possible in php and java script or not?

thank you very much.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.