I have created table named Employee in SqlServer 2005 EmployeeID, EMPName,ManagerId as columns. Set EmployeeID as PKEY, and ManagerID as foreign key for the same table.
Here is the sample data in the database.
Open VS 2005 and create new web project give name as EMPHierarchy
Drag and drop Treeview control and set ExpandDepth property to 0.Expnadcollapse to true. ShowLines to true.
ExpandDepth="0"
PopulateNodesFromClient="true"
ShowLines="true"
ShowExpandCollapse="true"
runat="server" OnTreeNodePopulate="TreeView1_TreeNodePopulate1" />.
Our first logic is to populate nodes in the root level when the page loads. Here is code for it.
//load for the page code to populate main nodes is called here.
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
PopulateMainLevel();
}
Populate mainlevel where manager doesnot have any reporting means managerid is null
///
/// function to populate main level nodes
///
private void PopulateMainLevel()
{
SqlConnection objConn = new SqlConnection(
"server=vamshi;Trusted_Connection=true;DATABASE=Employee");
SqlCommand cmd = new SqlCommand("select employeeid,empname,(select count(*) FROM Employee WHERE managerid=e.employeeid) childnodecount FROM Employee e where ManagerId IS NULL", objConn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
PopulateNodes(dt, TreeView1.Nodes);
}
Here is the main important logic. The PopulateNodes method iterates through all rows in the DataTable. If given node has child nodes, it sets the PopulateOnDemand property of the TreeNode object according to that.
///
/// Code to populate nodes
///
///
///
private void PopulateNodes(DataTable dt, TreeNodeCollection nodes)
{
foreach (DataRow dr in dt.Rows)
{
TreeNode tn = new TreeNode();
tn.Text = dr["empname"].ToString();
tn.Value = dr["employeeid"].ToString();
nodes.Add(tn);
int i = Convert.ToInt32(dr["childnodecount"].ToString());
// If node has child nodes, then enable on-demand populating
if (i > 0)
tn.PopulateOnDemand = true;
}
}
Now we need to populate sublevels in the treenode click. We have to place logic in TreenodePopulate.
protected void TreeView1_TreeNodePopulate1(object sender, TreeNodeEventArgs e)
{
PopulateSubLevel(Convert.ToInt32(e.Node.Value), e.Node);
}
///
/// function to populate sublevel nodes
///
///
///
private void PopulateSubLevel(int parentid, TreeNode parentNode)
{
SqlConnection objConn = new SqlConnection(
"server=vamshi;Trusted_Connection=true;DATABASE=Employee");
SqlCommand cmd = new SqlCommand("select employeeid,empname,(select count(*) FROM employee WHERE employeeid=e.employeeid) childnodecount FROM Employee e where managerID=@parentID", objConn);
cmd.Parameters.Add("@parentID", SqlDbType.Int).Value = parentid;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
PopulateNodes(dt, parentNode.ChildNodes);
}
HERE is the output.
Conclusion: Based on the hierarchy in organization Populating treeview in vs2005 is explained.
