Populate And Add New Nodes To TreeView Up To N Levels From Database

TreeView control used to represent hierarchical data using expandable nodes. TreeView control is available in window forms and WPF as well.

This article will guide you “How to populate and add new nodes to TreeView up-to N Levels from Database”. You will be able to add new node at any level and expand this up-to N Levels and generate automatic code for any child.



1. Database Structure

Firstly, create database table with the given model. We will use only one table, as we have to expand it to N level. I have used SQL Server 2014. Any version of SQL server can be used.

CREATE TABLE [dbo].accounts(
    [code] [int] NOTNULL,
    [ac_name] [nvarchar](50)NOTNULL,
    [parent] [int] NOTNULL,
    [type] [nvarchar](20)NOTNULL,
    [levelno] [int] NOTNULL,
    [fixed] [nvarchar](50)NULL,
    [direct] [nvarchar](50)NULL,
    [open_bal] [decimal](18, 2)NULL,
    [dt] [datetime] NULLCONSTRAINT [DF_chart_dt]  DEFAULT (getdate()),
    [active] [int] NOTNULLCONSTRAINT [DF_chart_active]  DEFAULT ((1)),
    [cntr] [int] IDENTITY(1,1)NOTNULL,
CONSTRAINT [PK_chart] PRIMARYKEYCLUSTERED
(
    [code] ASC
)WITH (PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON [PRIMARY]
)ON [PRIMARY]

The field ‘code’ will be the primary key. It will be used to identify each account. The program will automatically generate code for any new node.

2. Add some dummy data to this table 

GO
SETIDENTITY_INSERT[dbo].accounts ON
GO
INSERT[dbo].accounts([code], [ac_name], [parent], [type], [levelno], [fixed], [direct], [open_bal], [dt], [active], [cntr]) VALUES(1, N 'Assets', 0, N 'Parent Account', 0, N 'NA', N 'NA', CAST(0.00 ASDecimal(18, 2)), CAST(N '2015-02-23 21:09:27.327'
    ASDateTime), 1, 1)
GO
INSERT[dbo].accounts([code], [ac_name], [parent], [type], [levelno], [fixed], [direct], [open_bal], [dt], [active], [cntr]) VALUES(2, N 'Liabilities', 0, N 'Parent Account', 0, N 'NA', N 'NA', CAST(0.00 ASDecimal(18, 2)), CAST(N '2015-02-23 21:09:27.327'
    ASDateTime), 1, 2)
GO
INSERT[dbo].accounts([code], [ac_name], [parent], [type], [levelno], [fixed], [direct], [open_bal], [dt], [active], [cntr]) VALUES(3, N 'Equity', 0, N 'Parent Account', 0, N 'NA', N 'NA', CAST(0.00 ASDecimal(18, 2)), CAST(N '2015-02-23 21:09:27.327'
    ASDateTime), 1, 3)
GO
INSERT[dbo].accounts([code], [ac_name], [parent], [type], [levelno], [fixed], [direct], [open_bal], [dt], [active], [cntr]) VALUES(4, N 'Revenue', 0, N 'Parent Account', 0, N 'Variable', N 'Indirect', CAST(0.00 ASDecimal(18, 2)), CAST(N '2015-02-26 00:00:00.000'
    ASDateTime), 1, 38)
GO
INSERT[dbo].accounts([code], [ac_name], [parent], [type], [levelno], [fixed], [direct], [open_bal], [dt], [active], [cntr]) VALUES(101, N 'Current Assets', 1, N 'Parent Account', 1, N 'NA', N 'NA', CAST(0.00 ASDecimal(18, 2)), CAST(N '2015-02-23 21:09:27.327'
    ASDateTime), 1, 4)
GO
INSERT[dbo].accounts([code], [ac_name], [parent], [type], [levelno], [fixed], [direct], [open_bal], [dt], [active], [cntr]) VALUES(102, N 'Fixed Assets', 1, N 'Parent Account', 1, N 'NA', N 'NA', CAST(0.00 ASDecimal(18, 2)), CAST(N '2015-02-23 21:09:27.327'
    ASDateTime), 1, 5)
GO
INSERT[dbo].accounts([code], [ac_name], [parent], [type], [levelno], [fixed], [direct], [open_bal], [dt], [active], [cntr]) VALUES(201, N 'Short Term Liabilities', 2, N 'Parent Account', 1, N 'NA', N 'NA', CAST(0.00 ASDecimal(18, 2)), CAST(N '2015-02-23 21:09:27.327'
    ASDateTime), 1, 6)
GO
INSERT[dbo].accounts([code], [ac_name], [parent], [type], [levelno], [fixed], [direct], [open_bal], [dt], [active], [cntr]) VALUES(202, N 'Long Term Liabilities', 2, N 'Parent Account', 1, N 'NA', N 'NA', CAST(0.00 ASDecimal(18, 2)), CAST(N '2015-02-23 21:09:27.327'
    ASDateTime), 1, 7)
GO
SETIDENTITY_INSERT[dbo].accounts OFF
GO

3. Populating the tree view

After creating the table and adding data to it, now we are able to populate data into TreeView.

Here are the steps:

• Create a winform project in Visual Studio.
  
  
• Drag and drop a tree view to the form.
  
  
• Add a context menu to the form. This context menu will be used to perform the following function,
  
  a. View the data of specific node
  b. Adding node at current level
  c. Adding new node under the selected node
  
  
• Double click on form and create the Form_Load event. We will populate tree at the loading time of the form.
  
  
• While form will be loading, we will fetch the data from SQL Server and save it to the datatable.
  
  
• The following function will populate the data into the TreeView.
  
  
• PopulateTreeView is a recursive function. It calls itself until no more nodes are available to show in TreeView. 

private void PopulateTreeView(int parentId, TreeNode parentNode)
{
    TreeNode childNode;
    foreach(DataRow dr in _acountsTb.Select("[parent]=" + parentId))
    {
        TreeNode t = new TreeNode();
        t.Text = dr["code"].ToString() + " - " + dr["ac_name"].ToString();
        t.Name = dr["code"].ToString();
        t.Tag = _acountsTb.Rows.IndexOf(dr);
        if (parentNode == null)
        {
            treeView1.Nodes.Add(t);
            childNode = t;
        }
        else
        {
            parentNode.Nodes.Add(t);
            childNode = t;
        }
        PopulateTreeView(Convert.ToInt32(dr["code"].ToString()), childNode);
    }
}

4. Displaying Node Data

The following function is used to display details of a specific node.

private void ShowNodeData(TreeNode nod)
{
    DataRow r = _acountsTb.Rows[int.Parse(nod.Tag.ToString())];
    txtCode.Text = r["code"].ToString();
    txtName.Text = r["ac_name"].ToString();
    dtpDate.Value = DateTime.Parse(r["dt"].ToString());
    textBox1.Text = r["open_bal"].ToString();
    if (r["type"].ToString().Equals("Parent Account"))
    {
        radioParent.Checked = true;
        textBox1.Enabled = false;
    }

    else radioTransaction.Checked = true;

    if (r["fixed"].ToString().Equals("NA")) radioNA1.Checked = true;

    elseif(r["fixed"].ToString().Equals("Fixed"))

    radioFixed.Checked = true;

    else radioVariable.Checked = true;

    if (r["direct"].ToString().Equals("NA")) radioNA2.Checked = true;

    elseif(r["direct"].ToString().Equals("Direct"))

    radioDirect.Checked = true;

    else radioIndirect.Checked = true;

    txtName.Focus();
}

5. Adding new node at this level

The following event handler handles the click action of context menu item “At this level”. This event first create code for the new item to be inserted.

privatevoid atThisLevelToolStripMenuItem_Click(object sender, EventArgs e)
{
    _selectedNode = treeView1.SelectedNode;
    int max = 0;
    if (treeView1.Nodes.Count > 0)
    {
        _parent = int.Parse(_acountsTb.Rows[int.Parse(_selectedNode.Tag.ToString())]["parent"].ToString());
        DataRow[] nodes = _acountsTb.Select("[parent]=" + _parent);
        foreach(DataRow r in nodes)
        {
            int n = int.Parse(r["code"].ToString());
            if (n > max) max = n;
        }
    }
    max += 1;
    txtCode.Text = max.ToString();
    _newNode = true;
    _thisLevel = true;
    txtName.Focus();
}

6. Adding new node under selected node

The following event handler handles the click action of context menu item “Under Select”. This event first create code for the new item to be inserted.

private void underSelectedToolStripMenuItem_Click(object sender, EventArgs e)
{
    _selectedNode = treeView1.SelectedNode;
    DataRow r = _acountsTb.Rows[int.Parse(treeView1.SelectedNode.Tag.ToString())];
    if (r["type"].ToString().Equals("Parent Account"))
    {
        _newNode = true;
        _thisLevel = false;
        string code = string.Empty;
        _parent = int.Parse(_acountsTb.Rows[int.Parse(_selectedNode.Tag.ToString())]["code"].ToString());
        if (_selectedNode.Nodes.Count > 0)
        {
            DataRow[] nodes = _acountsTb.Select("[parent]=" + _parent);
            int max = 0;
            foreach(DataRow ra in nodes)
            {
                int n = int.Parse(ra["code"].ToString());
                if (n > max) max = n;
            }
            max += 1;
            txtCode.Text = max.ToString();
            code = max.ToString();
        }
        else
        {
            if (_selectedNode.Level < 3) code = "01";
            else code = "001";
            txtCode.Text = r["code"] + code;
        }
        txtName.Focus();
    }
    else
    {
        _newNode = false;
        MessageBox.Show("New Account can't be opened under a Transaction Account", "Acount opening Failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}

7. Conclusion

The above tutorial shows you, how we can dynamically create and populate TreeView in C# up-to N Levels. We can generate code automatically at any level. 

 

Read more articles on SQL Server:

• SQL Basic Query
• Upgrading SSIS Packages In SQL Server