How to create a category tree in C#

Question

Database

I have a database with product and service codes (for public tenders) and wanted to find a way to create a "tree" for these codes. See how I have configured my database in the following image:

Goal

The ParentID is the column where you keep the father of the code, that is, every parent that exists will have to have an expansion to show the children. See an example of what I want to get in the following image:

Remarks

• What’s left in the ParentID of the son is the CPCID of the father.
• At the root there are 9 fathers
• The database has 32k+ lines

Answer 1

I’ve been through the same situation and solved using Linq as follows:

using System.Collections.Generic;

namespace XXX
{
    public class TreeviewItem
    {
        public TreeviewItem()
        {
            Children = new List<TreeviewItem>();
        }
        public string Text { get; set; }
        public int Value { get; set; }
        public int? ValuePai { get; set; }
        public bool Checked { get; set; }
        public bool Disabled { get; set; }
        public bool Collapsed { get; set; }
        public List<TreeviewItem> Children { get; set; }
    }
}

Methods:

    public List<TreeviewItem> ObterListaAninhada(List<TreeviewItem> lstFuncoes)
    {
        var lstFuncoesAninhadas = lstFuncoes.Where(x => x.ValuePai == 0)
                                            .Select(x => new TreeviewItem
                                            {
                                                Value = x.Value,
                                                ValuePai = x.ValuePai,
                                                Text = x.Text,
                                                Checked = x.Checked,
                                                Collapsed = x.Collapsed,
                                                Disabled = x.Disabled,
                                                Children = ObterFuncoesFilhas(lstFuncoes, x.Value)
                                            }).ToList();
        return lstFuncoesAninhadas;
    }

    private List<TreeviewItem> ObterFuncoesFilhas(List<TreeviewItem> lstFunction, int id)
    {

        return lstFunction.Where(x => x.ValuePai == id)
                          .Select(x => new TreeviewItem
                          {
                              Value = x.Value,
                              ValuePai = x.ValuePai,
                              Text = x.Text,
                              Checked = x.Checked,
                              Collapsed = x.Collapsed,
                              Disabled = x.Disabled,
                              Children = ObterFuncoesFilhas(lstFunction, x.Value)
                          }).ToList();
    }

Simple example of the result in Json:

{
    "text": "Teste",
    "value": 305,
    "valuePai": 0,
    "checked": false,
    "disabled": true,
    "collapsed": true,
    "children": [
      {
        "text": "Teste.pdf",
        "value": 483,
        "valuePai": 305,
        "checked": false,
        "disabled": false,
        "collapsed": true,
        "children": []
      },
      {
        "text": "Teste.txt",
        "value": 487,
        "valuePai": 305,
        "checked": false,
        "disabled": false,
        "collapsed": true,
        "children": []
      }
    ]
}

Answer 2

I think the secret here is more in the BD query than in C# itself. In SQL SERVER (which seems to be the case) I go with a CTE. Here is an example just by writing the output hierarchically, but I would make a component a part for links, routes and other specifics that are needed.

The class Category

public class Categoria
{
    public Categoria()
    {

    }

    public int NumLevel { get; set; }

    public int CPCID { get; set; }

    public int ParentID { get; set; }

    public String Code { get; set; }

    public String Nodes { get; set; }

    public String EN { get; set; }

    public String PT { get; set; }

    public String ES { get; set; }

    public String IT { get; set; }

    public List<Categoria> CategoriasFilho { get; set; }
}

The Console Main method

    static void Main(string[] args)
    {
        //Executamos a CTE
        SqlConnection Conn = DAL.Conexao.AbreConexaoApoio();

        StringBuilder cmdText = new StringBuilder();

        cmdText.Append(";WITH ArvoreCategorias AS ( ");
        cmdText.Append("    SELECT NumLevel, CPCID, ParentID, Code, PT, EN, ES, IT, 1 as Level, CAST(Code as varchar(max)) as Nodes FROM Categorias WHERE ParentID = 0");
        cmdText.Append("    UNION ALL");
        cmdText.Append("    SELECT c.NumLevel, c.CPCID, c.ParentID, c.Code, c.PT, c.EN, c.ES, c.IT, Level + 1, Cast(ac.Nodes + '->' + c.Code as varchar(max))");
        cmdText.Append("    FROM Categorias c ");
        cmdText.Append("   INNER JOIN ArvoreCategorias ac ON c.ParentID = ac.CPCID");
        cmdText.Append(")");
        cmdText.Append("SELECT * FROM ArvoreCategorias ORDER BY Nodes");

        SqlCommand cmd = new SqlCommand();
        cmd.Connection = Conn;
        cmd.CommandText = cmdText.ToString();

        SqlDataReader dr = cmd.ExecuteReader();

        List<Categoria> ArvoreCategorias = new List<Categoria>();

        //Loope perlo retorno da CTE
        while (dr.Read())
        {
            Categoria cat = new Categoria();
            cat.NumLevel = Int32.Parse(dr["NumLevel"].ToString());
            cat.CPCID = Int32.Parse(dr["CPCID"].ToString());
            cat.ParentID = Int32.Parse(dr["ParentID"].ToString());
            cat.Code = dr["Code"].ToString();
            cat.Nodes = dr["Nodes"].ToString();
            cat.EN = dr["EN"].ToString();
            cat.ES = dr["ES"].ToString();
            cat.PT = dr["PT"].ToString();
            cat.IT = dr["IT"].ToString();

            ArvoreCategorias.Add(cat);
        }

        //Separo as Categorias Pai para que sejam preenchidos os filhos
        List<Categoria> ArvoreCategoriasPai = ArvoreCategorias.Where(c => c.ParentID == 0).ToList();

        //Carregos os Filhos das Categorias Raízes
        foreach (Categoria cat in ArvoreCategoriasPai)
        {
            CarregarCategoriasFilho(cat, ArvoreCategorias);
        }

        dr.Close();
        dr.Dispose();

        DAL.Conexao.FechaConexao(Conn);

        foreach (Categoria Cat in ArvoreCategoriasPai)
        {
            Console.WriteLine(Cat.Nodes);

            if (Cat.CategoriasFilho.Count > 0)
            {
                EscreverCategoriasFilho(Cat);
            }
        }

        Console.ReadKey();
    }

Recursive Method for Filling Out Child Objects

    private static void CarregarCategoriasFilho(Categoria CategoriaPai, List<Categoria> ArvoreCategorias)
    {
        List<Categoria> CategoriasFilho = ArvoreCategorias.Where(cf => cf.ParentID == CategoriaPai.CPCID).ToList();
        CategoriaPai.CategoriasFilho = CategoriasFilho;

        if (CategoriasFilho.Count > 0)
        {
            foreach (Categoria cf in CategoriasFilho)
            {
                CarregarCategoriasFilho(cf, ArvoreCategorias);
            }
        }
    }

Recursive Method to Write Node Output from Category

    private static void EscreverCategoriasFilho(Categoria Cat)
    {
        foreach (Categoria cf in Cat.CategoriasFilho)
        {
            Console.WriteLine(cf.Nodes);

            if (cf.CategoriasFilho.Count > 0)
            {
                EscreverCategoriasFilho(cf);
            }
        }
    }

Answer 3

I’m not sure I understand your question, but see if the following code helps you with anything:

using System;
 using System.Collections.Generic;

namespace ArvoreDeCategoria
 {
    class MainClass
    {
        public static void Main(string[] args)
        { 
            Categoria categoriaRaiz = new Categoria();

            Categoria categoria1 = new Categoria();
            categoria1.Code = "Code 1";
            categoria1.CPCID = 1;
            categoria1.NumLevel = 1;
            categoria1.ParentID = null;
            categoria1.PT = "Categoria 1";
            categoria1.EN = "Category 1 ";

            Categoria categoria2 = new Categoria();
            categoria2.Code = "Code 2";
            categoria2.CPCID = 2;
            categoria2.NumLevel = 2;
            categoria2.ParentID = null;
            categoria2.PT = "Categoria 2";
            categoria2.EN = "Category 2 ";

            Categoria subcategoria1 = new Categoria();
            subcategoria1.Code = "Code Sub 1";
            subcategoria1.CPCID = 3;
            subcategoria1.NumLevel = 1;
            subcategoria1.ParentID = 1;
            subcategoria1.PT = "Sub-Categoria 1";
            subcategoria1.EN = "Sub-Category 1 ";

            Categoria subsubcategoria1 = new Categoria();
            subsubcategoria1.Code = "Code Sub Sub 1";
            subsubcategoria1.CPCID = 4;
            subsubcategoria1.NumLevel = 1;
            subsubcategoria1.ParentID = 1;
            subsubcategoria1.PT = "Sub-Sub-Categoria 1";
            subsubcategoria1.EN = "Sub-Sub-Category 1 ";

            subcategoria1.Add(subsubcategoria1);

            categoria1.Add(subcategoria1);

            categoriaRaiz.Add(categoria1);
            categoriaRaiz.Add(categoria2);

            categoriaRaiz.Display(1);

         }
    }

    public class Categoria
    {
        public int NumLevel { get; set; }
        public int CPCID { get; set; }
        public int? ParentID { get; set; }
        public string Code { get; set; }
        public string PT { get; set; }
        public string EN { get; set; }
        public string ES { get; set; }
        public string IT { get; set; }

        private readonly ICollection<Categoria> subcategorias;

        public Categoria()
        {
            subcategorias = new List<Categoria>();
        }

        public void Add(Categoria categoria)
        {
            subcategorias.Add(categoria);
        }

        public void Display(int depth)
        {
            Console.WriteLine(new String('-', depth) + PT);

            foreach (Categoria categoria in subcategorias)
            {
                categoria.Display(depth + 2);
            }
        }

        public void Remove(Categoria categoria)
        {
            subcategorias.Remove(categoria);
        }
    }
}