How to create a category tree in C #

0

Database

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

Objective

TheParentIDisthecolumnwheretheparentofthecodeisstored,thatis,eachparentthatexistswillhavetohaveanexpansiontoshowthechildren.Here'sanexampleofwhatIwanttogetinthefollowingimage:

Observations

  • What's in the ParentID of child is CPCID of parent.
  • At the root there are 9 parents
  • The database has 32k + lines
asked by anonymous 09.06.2017 / 17:28

2 answers

0

I do not know if I understood your question directly, but see if the following code will help you with something:

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);
        }
    }
}
    
10.06.2017 / 21:44
0

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

The Category class

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's 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();
    }

The Recursive Method for Filling 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);
            }
        }
    }

The Recursive Method for Writing the Category Node Output

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

            if (cf.CategoriasFilho.Count > 0)
            {
                EscreverCategoriasFilho(cf);
            }
        }
    }
    
12.06.2017 / 16:31