Windows Forms VB.NET - Populate TreeView with Hierarchical data

Aloha, I'm trying to populate a treeview on a windows form app with Hierarchical data from a SQL db.

The structure from the database is:

id_def  id_parent description
1     NULL      Multidificiência
2     NULL      Síndrome
3     NULL      Outros
4     1       Surdez de Transmissão
5     2       Surdez Neurossensorial Ligeira
6     3       Surdez Neurossensorial Média

th开发者_如何转开发e records with NULL value at id_parent, are the main categories, and the ones with their id_parent, are sub categories.

Can anyone help with the code to populate the TreeView ? I managed to do it with an ASP.NET app, if it helps, here's the code:

protected void Page_Load(object sender, EventArgs e)
{
    if (!Page.IsPostBack)
        PopulateRootLevel();
}

private void PopulateRootLevel()
{
    SqlConnection objConn = new SqlConnection("Data Source=1.1.1.1;Initial Catalog=DREER_EDUCANDOS2006;User ID=sre_web;Password=xxx");
    SqlCommand objCommand = new SqlCommand("select id_deficiencia,descricao,(select count(*) FROM NecessidadesEspeciais WHERE id_deficiencia_pai=sc.id_deficiencia) childnodecount FROM NecessidadesEspeciais sc where id_deficiencia_pai IS NULL", objConn);
    SqlDataAdapter da = new SqlDataAdapter(objCommand);
    DataTable dt = new DataTable();
    da.Fill(dt);
    PopulateNodes(dt, TreeView1.Nodes);
}

private void PopulateSubLevel(int parentid, TreeNode parentNode)
{
    SqlConnection objConn = new SqlConnection("Data Source=1.1.1.1;Initial Catalog=DREER_EDUCANDOS2006;User ID=sre_web;Password=xxx");
    SqlCommand objCommand = new SqlCommand("select id_deficiencia,descricao,(select count(*) FROM NecessidadesEspeciais WHERE id_deficiencia_pai=sc.id_deficiencia) childnodecount FROM NecessidadesEspeciais sc where id_deficiencia_pai=@id_deficiencia_pai", objConn);
    objCommand.Parameters.Add("@id_deficiencia_pai", SqlDbType.Int).Value = parentid;
    SqlDataAdapter da = new SqlDataAdapter(objCommand);
    DataTable dt = new DataTable();
    da.Fill(dt);
    PopulateNodes(dt, parentNode.ChildNodes);
}


protected void TreeView1_TreeNodePopulate(object sender, TreeNodeEventArgs e)
{
    PopulateSubLevel(Int32.Parse(e.Node.Value), e.Node);
}

private void PopulateNodes(DataTable dt, TreeNodeCollection nodes)
{
    foreach (DataRow dr in dt.Rows)
    {
        TreeNode tn = new TreeNode();
        tn.Text = dr["descricao"].ToString();
        tn.Value = dr["id_deficiencia"].ToString();
        nodes.Add(tn);

        //If node has child nodes, then enable on-demand populating
        tn.PopulateOnDemand = ((int)(dr["childnodecount"]) > 0);
    }
}

---

Take a table with some hierarchical data in it:

Id  Name                                ParentNodeId
1   Top-level node 1                    -1
2   A first-level child                  1
3   Another top-level node              -1
4   Another first-level child            1
5   First-level child in another branch  3
6   A second-level child                 2

I have reworked the sample code from another SO answer, to use the above table to dynamically populate a tree view (assuming the table lives in an SQL server database). The code sample is somewhat lenghty...

using System.Windows.Forms;
using System.Threading;
using System.Collections.Generic;
using System.Data.SqlClient;
public class TreeViewSample : Form
{
    private TreeView _treeView;
    public TreeViewSample()
    {
        this._treeView = new System.Windows.Forms.TreeView();
        this._treeView.Location = new System.Drawing.Point(12, 12);
        this._treeView.Size = new System.Drawing.Size(200, 400);
        this._treeView.AfterExpand +=
            new TreeViewEventHandler(TreeView_AfterExpand);
        this.ClientSize = new System.Drawing.Size(224, 424);
        this.Controls.Add(this._treeView);
        this.Text = "TreeView Lazy Load Sample";
        PopulateChildren(null);
    }

    void TreeView_AfterExpand(object sender, TreeViewEventArgs e)
    {
        if (e.Node.Nodes.Count == 1 && e.Node.Nodes[0].Tag == "dummy")
        {
            PopulateChildren(e.Node);
        }
    }

    private void PopulateChildren(TreeNode parent)
    {
        // this node has not yet been populated, launch a thread
        // to get the data
        int? parentId = parent != null ? (parent.Tag as DataNode).Id : (int?)null;
        ThreadPool.QueueUserWorkItem(state =>
        {
            IEnumerable<DataNode> childItems = GetNodes(parentId);
            // load the data into the tree view (on the UI thread)
            _treeView.BeginInvoke((MethodInvoker)delegate
            {
                PopulateChildren(parent, childItems);
            });
        });
    }

    private void PopulateChildren(TreeNode parent, IEnumerable<DataNode> childItems)
    {
        TreeNodeCollection nodes = parent != null ? parent.Nodes : _treeView.Nodes;
        TreeNode child;
        TreeNode dummy;
        TreeNode originalDummyItem = parent != null ? parent.Nodes[0] : null;
        foreach (var item in childItems)
        {
            child = new TreeNode(item.Text);
            child.Tag = item;
            dummy = new TreeNode("Loading. Please wait...");
            dummy.Tag = "dummy";
            child.Nodes.Add(dummy);
            nodes.Add(child);
        }
        if (originalDummyItem != null)
        {
            originalDummyItem.Remove();
        }
    }

    private IEnumerable<DataNode> GetNodes(int? parentId)
    {
        List<DataNode> result = new List<DataNode>();
        using (SqlConnection conn = new SqlConnection(@"[your connection string]"))
        using (SqlCommand cmd = new SqlCommand("select * from Nodes where ParentNodeId = @parentNodeId", conn))
        {
            cmd.Parameters.Add(new SqlParameter("@parentNodeId", System.Data.SqlDbType.Int));
            cmd.Parameters["@parentNodeId"].Value = parentId != null ? parentId : -1;
            conn.Open();
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                int nodeIdCol = reader.GetOrdinal("NodeId");
                int nameCol = reader.GetOrdinal("Name");
                int parentIdCl = reader.GetOrdinal("ParentNodeId");
                while (reader.Read())
                {
                    result.Add(new DataNode
                       {
                           Id = reader.GetInt32(nodeIdCol),
                           Text = reader.GetString(nameCol),
                           ParentId = reader.IsDBNull(parentIdCl) ? (int?)null : reader.GetInt32(parentIdCl)
                       });
                }
            }
        }
        return result;
    }
}

public class DataNode
{
    public int Id { get; set; }
    public string Text { get; set; }
    public int? ParentId { get; set; }
}

The data fetching code can probably be made a bit prettier by using Linq-to-SQL, but I wanted the same to be as complete as possible, so I decided to leave that out to keep the amount of code down... (that would require the inclusion of some generated Linq-to-SQL classes).