TreeView DataLoad From Database Table

• Download Test - 165.12 KB

Introduction

It loads the treeview control of ASP.NET from the database table.

Background

Well, I encountered this problem during my work when I needed to load the treeview control from the database table. The ASP.NET control treeview doesn't provide an implementation of how to load data from database. So I thought of writing code that is generic and can load treeview from the database table.

Using the Code

To bind a database table to a treeview menu, a class "DBTreeLoad" is developed which takes the column information of the database, table information and hierarchy information. Following are the steps as to how to run the code.

1. Initialize the "DBTreeLoad" class instance.
2. Use the function SetNodeInfo. This function is the core input function.

//
// SetNodeInfo Function
//
    DBTreeLoad process = new DBTreeLoad(TableName);
    process.SetNodeInfo (NodeDepth, DisplayName, DBColumnName, 
			bool DisplayNode, ParentColumn)

...

Function SetNodeInfo Parameter Information

• name="nodeDepth": Depth of the Node i.e. at which level the node will appear
• name="displayName": Display Name of the node at this level
• name="columnName": Name of the database column from where it takes value
• name="displayNode": Whether to display a separate node of DisplayName in the hierarchy
• name="parentColumn": Database Column Name from where I gets the parent node value

For the hierarchy building, understand this example:

Database Table Columns are like this:

• CompanyName
• CompanyRate
• CompanyContracts
• CompanyServices
• CompanyEmail
• ServiceCountries

and we have to build a hierarchy such as:

CompanyName
|_CompanyRate
|_CompanyContracts
|_CompanyServices
||_ServiceCountries

The Function SetNodeInfo arguments will be:

For Level 0

SetNodeInfo (0, Company, CompanyName, true, GetRootCol()); // the getRootCol is 
				// a function that handles the parent or root menu. 

For Level 1

SetNodeInfo (1, Rate, CompanyRate, true, CompanyName); 
SetNodeInfo (1, Contracts, CompanyContracts, true, CompanyName);

For Level 2

SetNodeInfo (2, Services, ServiceCountries, true, CompanyServices);

Once Node info is set, use the main or core function that loads the data.

//
// LoadFromDataTable Function
//
    process.LoadFromDataTable(tvTest);// tvTest is the treeview Menu Object

...

//
    // LoadFromDataTable Function
    //
        public void LoadFromDataTable(TreeView tv)
        {
            string selectstatement = "select '" + GetRootValue() +
            	"' as " + GetRootName() + ", * from " + TableName;
            string whereclause = "";
            string dataPath = "";

            TreeNode parent = new TreeNode();
            mtbl = new DataAccess().ExecuteSelectStatement(selectstatement, whereclause);
            parent.Text = mtbl.Rows[0][0].ToString(); // assures that it is parent.
            tv.Nodes.Add(parent);

            for (int i = 0; i < mtbl.Rows.Count; i++)
            {
                for (int j = 0; j < Nodes.Count; j++)
                {
                    dataPath = GetValuePath(i, j);
                    dataPath = dataPath + "/" + mtbl.Rows[i]
					[Nodes[j].ColumnName].ToString();

                    TreeNode n_item = tv.FindNode(dataPath.Remove(0, 1));
                    if (n_item == null) 	// Not Exists
                    {
                        string p_datapath = dataPath.Substring(0, 
					dataPath.LastIndexOf("/"));
                        TreeNode p_item = tv.FindNode(p_datapath.Remove(0, 1));
                        if (p_item == null) 	// Create One
                        {
                            p_item = new TreeNode(p_datapath.Substring
					(p_datapath.LastIndexOf("/") + 1));
                            p_datapath = p_datapath.Substring(0,
                            p_datapath.LastIndexOf("/")); // parent valuepath
                            (tv.FindNode(p_datapath.Remove(0, 1))).ChildNodes.Add
                            (p_item); 	// finding the parent from valuepath to add 
					// newly created node
                        }
                        if (!mtbl.Rows[i][Nodes[j].ColumnName].ToString().Equals(""))
                        {
                            n_item = new TreeNode();
                            n_item.Text = mtbl.Rows[i][Nodes[j].ColumnName].ToString();
                            p_item.ChildNodes.Add(n_item);
                        }
                    }
                }
            }
        }

    ...

Points of Interest

• Optimization
• Check the behaviour of code when DisplayNode is set to false

Enhancements

1. We can make a usercontrol or a customcontrol that takes database table column information and loads the data.
2. Sorting of nodes is not implemented yet, so we have to make a list of nodes in order.