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.

View Account

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.

  1. CREATE TABLE [dbo].accounts(  
  2.     [code] [int] NOTNULL,  
  3.     [ac_name] [nvarchar](50)NOTNULL,  
  4.     [parent] [int] NOTNULL,  
  5.     [type] [nvarchar](20)NOTNULL,  
  6.     [levelno] [int] NOTNULL,  
  7.     [fixed] [nvarchar](50)NULL,  
  8.     [direct] [nvarchar](50)NULL,  
  9.     [open_bal] [decimal](18, 2)NULL,  
  10.     [dt] [datetime] NULLCONSTRAINT [DF_chart_dt]  DEFAULT (getdate()),  
  11.     [active] [int] NOTNULLCONSTRAINT [DF_chart_active]  DEFAULT ((1)),  
  12.     [cntr] [int] IDENTITY(1,1)NOTNULL,  
  13. CONSTRAINT [PK_chart] PRIMARYKEYCLUSTERED  
  14. (  
  15.     [code] ASC  
  16. )WITH (PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON [PRIMARY]  
  17. )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 
  1. GO  
  2. SETIDENTITY_INSERT[dbo].accounts ON  
  3. GO  
  4. 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'  
  5.     ASDateTime), 1, 1)  
  6. GO  
  7. 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'  
  8.     ASDateTime), 1, 2)  
  9. GO  
  10. 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'  
  11.     ASDateTime), 1, 3)  
  12. GO  
  13. 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'  
  14.     ASDateTime), 1, 38)  
  15. GO  
  16. 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'  
  17.     ASDateTime), 1, 4)  
  18. GO  
  19. 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'  
  20.     ASDateTime), 1, 5)  
  21. GO  
  22. 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'  
  23.     ASDateTime), 1, 6)  
  24. GO  
  25. 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'  
  26.     ASDateTime), 1, 7)  
  27. GO  
  28. SETIDENTITY_INSERT[dbo].accounts OFF  
  29. 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. 
  1. private void PopulateTreeView(int parentId, TreeNode parentNode)  
  2. {  
  3.     TreeNode childNode;  
  4.     foreach(DataRow dr in _acountsTb.Select("[parent]=" + parentId))  
  5.     {  
  6.         TreeNode t = new TreeNode();  
  7.         t.Text = dr["code"].ToString() + " - " + dr["ac_name"].ToString();  
  8.         t.Name = dr["code"].ToString();  
  9.         t.Tag = _acountsTb.Rows.IndexOf(dr);  
  10.         if (parentNode == null)  
  11.         {  
  12.             treeView1.Nodes.Add(t);  
  13.             childNode = t;  
  14.         }  
  15.         else  
  16.         {  
  17.             parentNode.Nodes.Add(t);  
  18.             childNode = t;  
  19.         }  
  20.         PopulateTreeView(Convert.ToInt32(dr["code"].ToString()), childNode);  
  21.     }  
  22. }  
4. Displaying Node Data

The following function is used to display details of a specific node.
  1. private void ShowNodeData(TreeNode nod)  
  2. {  
  3.     DataRow r = _acountsTb.Rows[int.Parse(nod.Tag.ToString())];  
  4.     txtCode.Text = r["code"].ToString();  
  5.     txtName.Text = r["ac_name"].ToString();  
  6.     dtpDate.Value = DateTime.Parse(r["dt"].ToString());  
  7.     textBox1.Text = r["open_bal"].ToString();  
  8.     if (r["type"].ToString().Equals("Parent Account"))  
  9.     {  
  10.         radioParent.Checked = true;  
  11.         textBox1.Enabled = false;  
  12.     }  
  13.     
  14.     else radioTransaction.Checked = true;  
  15.     
  16.     if (r["fixed"].ToString().Equals("NA")) radioNA1.Checked = true;  
  17.     
  18.     elseif(r["fixed"].ToString().Equals("Fixed"))  
  19.       
  20.     radioFixed.Checked = true;  
  21.     
  22.     else radioVariable.Checked = true;  
  23.     
  24.     if (r["direct"].ToString().Equals("NA")) radioNA2.Checked = true;  
  25.     
  26.     elseif(r["direct"].ToString().Equals("Direct"))  
  27.       
  28.     radioDirect.Checked = true;  
  29.     
  30.     else radioIndirect.Checked = true;  
  31.     
  32.     txtName.Focus();  
  33. }  
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.
  1. privatevoid atThisLevelToolStripMenuItem_Click(object sender, EventArgs e)  
  2. {  
  3.     _selectedNode = treeView1.SelectedNode;  
  4.     int max = 0;  
  5.     if (treeView1.Nodes.Count > 0)  
  6.     {  
  7.         _parent = int.Parse(_acountsTb.Rows[int.Parse(_selectedNode.Tag.ToString())]["parent"].ToString());  
  8.         DataRow[] nodes = _acountsTb.Select("[parent]=" + _parent);  
  9.         foreach(DataRow r in nodes)  
  10.         {  
  11.             int n = int.Parse(r["code"].ToString());  
  12.             if (n > max) max = n;  
  13.         }  
  14.     }  
  15.     max += 1;  
  16.     txtCode.Text = max.ToString();  
  17.     _newNode = true;  
  18.     _thisLevel = true;  
  19.     txtName.Focus();  
  20. }  
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.
  1. private void underSelectedToolStripMenuItem_Click(object sender, EventArgs e)  
  2. {  
  3.     _selectedNode = treeView1.SelectedNode;  
  4.     DataRow r = _acountsTb.Rows[int.Parse(treeView1.SelectedNode.Tag.ToString())];  
  5.     if (r["type"].ToString().Equals("Parent Account"))  
  6.     {  
  7.         _newNode = true;  
  8.         _thisLevel = false;  
  9.         string code = string.Empty;  
  10.         _parent = int.Parse(_acountsTb.Rows[int.Parse(_selectedNode.Tag.ToString())]["code"].ToString());  
  11.         if (_selectedNode.Nodes.Count > 0)  
  12.         {  
  13.             DataRow[] nodes = _acountsTb.Select("[parent]=" + _parent);  
  14.             int max = 0;  
  15.             foreach(DataRow ra in nodes)  
  16.             {  
  17.                 int n = int.Parse(ra["code"].ToString());  
  18.                 if (n > max) max = n;  
  19.             }  
  20.             max += 1;  
  21.             txtCode.Text = max.ToString();  
  22.             code = max.ToString();  
  23.         }  
  24.         else  
  25.         {  
  26.             if (_selectedNode.Level < 3) code = "01";  
  27.             else code = "001";  
  28.             txtCode.Text = r["code"] + code;  
  29.         }  
  30.         txtName.Focus();  
  31.     }  
  32.     else  
  33.     {  
  34.         _newNode = false;  
  35.         MessageBox.Show("New Account can't be opened under a Transaction Account""Acount opening Failed", MessageBoxButtons.OK, MessageBoxIcon.Error);  
  36.     }  
  37. }  
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:


Similar Articles