Populating TreeView up-to N Levels in C# from Database with Auto Code Generation for New Item at Runtime

• Download source files - 42 KB

Introduction

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

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

Database Structure

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

CREATE TABLE [dbo].accounts(
       [code] [int] NOT NULL,
       [ac_name] [nvarchar](50) NOT NULL,
       [parent] [int] NOT NULL,
       [type] [nvarchar](20) NOT NULL,
       [levelno] [int] NOT NULL,
       [fixed] [nvarchar](50) NULL,
       [direct] [nvarchar](50) NULL,
       [open_bal] [decimal](18, 2) NULL,
       [dt] [datetime] NULL CONSTRAINT [DF_chart_dt]  DEFAULT (getdate()),
       [active] [int] NOT NULL CONSTRAINT [DF_chart_active]  DEFAULT ((1)),
       [cntr] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_chart] PRIMARY KEY CLUSTERED
(
       [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 use to identify each account. The program will automatically generate code for any new node.

Add Some Dummy Data to this Table

SET IDENTITY_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 AS Decimal(18, 2)), _
CAST(N'2015-02-23 21:09:27.327' AS DateTime), 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 AS Decimal(18, 2)), _
CAST(N'2015-02-23 21:09:27.327' AS DateTime), 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 AS Decimal(18, 2)), _
CAST(N'2015-02-23 21:09:27.327' AS DateTime), 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 AS Decimal(18, 2)), _
CAST(N'2015-02-26 00:00:00.000' AS DateTime), 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 AS Decimal(18, 2)), _
CAST(N'2015-02-23 21:09:27.327' AS DateTime), 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 AS Decimal(18, 2)), _
CAST(N'2015-02-23 21:09:27.327' AS DateTime), 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 AS Decimal(18, 2)), _
CAST(N'2015-02-23 21:09:27.327' AS DateTime), 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 AS Decimal(18, 2)), _
CAST(N'2015-02-23 21:09:27.327' AS DateTime), 1, 7)

GO

SET IDENTITY_INSERT [dbo].accounts OFF

GO

Populating the TreeView

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

Follow the steps given below:

• 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 functions:
  1. View the data of specific node
  2. Add node at current level
  3. Add new node under the selected node
• Double click on form, 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);
            }
        }

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;
            else if (r["fixed"].ToString().Equals("Fixed"))
                radioFixed.Checked = true;
            else
                radioVariable.Checked = true;
            if (r["direct"].ToString().Equals("NA"))
                radioNA2.Checked = true;
            else if (r["direct"].ToString().Equals("Direct"))
                radioDirect.Checked = true;
            else
                radioIndirect.Checked = true;
            txtName.Focus();  
        }

Adding New Node at this Level

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

private void 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();
        }

Adding New Node Under Selected Node

The following event handler handles the click action of context menu item “Under Select”. This event first creates 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", 
                "Account opening Failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

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.