问个 TreeView 加载公司，部门，小组，员工的问题，太郁闷了?

Question

public void tvShow(TreeView TreeView1)
        {
            conn0.Open();
            SqlCommand cmd0 = new SqlCommand("select hr_CompanyID,hr_CompanyEngName from t_hr_Company", conn0);
            SqlDataReader dr0 = cmd0.ExecuteReader();            while (dr0.Read())
            {
                TreeNode childnode0 = new TreeNode(dr0["hr_CompanyEngName"].ToString(), dr0["hr_CompanyID"].ToString());
                childnode0.SelectAction = TreeNodeSelectAction.None;
                childnode0.NavigateUrl = "javascript:;";
                TreeView1.Nodes.Add(childnode0);                conn.Open();
                SqlCommand cmd = new SqlCommand("select hr_DepartmentID,hr_DepartmentEngName from t_hr_Department where hr_CompanyID = '" + childnode0.Value.Trim() + "'", conn);
                SqlDataReader dr = cmd.ExecuteReader();                while (dr.Read())
                {
                    TreeNode childnode = new TreeNode(dr["hr_DepartmentEngName"].ToString(), dr["hr_DepartmentID"].ToString());
                    childnode.SelectAction = TreeNodeSelectAction.None;
                    childnode0.ChildNodes.Add(childnode);                    conn2.Open();
                    SqlCommand cmd2 = new SqlCommand("select hr_TeamID,hr_TeamName from t_hr_Team where hr_DepartmentID = '" + childnode.Value.Trim() + "'", conn2);
                    SqlDataReader dr2 = cmd2.ExecuteReader();
                    while (dr2.Read())
                    {
                        TreeNode childnode2 = new TreeNode(dr2["hr_TeamName"].ToString(), dr2["hr_TeamID"].ToString());
                        childnode2.SelectAction = TreeNodeSelectAction.None;
                        childnode.ChildNodes.Add(childnode2);                        conn3.Open();
                        SqlCommand cmd3 = new SqlCommand("select b.hr_EmployeeID,b.hr_EngName from t_hr_PromotionHistory a,t_hr_Employee b where a.hr_EmployeeID=b.hr_EmployeeID and a.hr_TeamID = '" + childnode2.Value.Trim() + "'", conn3);
                        SqlDataReader dr3 = cmd3.ExecuteReader();
                        while (dr3.Read())
                        {
                            TreeNode childnode3 = new TreeNode(dr3["hr_EngName"].ToString(), dr3["hr_EmployeeID"].ToString());
                            childnode3.ToolTip = dr3["hr_EmployeeID"].ToString();
                            childnode3.SelectAction = TreeNodeSelectAction.Expand;
                            childnode2.ChildNodes.Add(childnode3);
                        }
                        dr3.Close();
                        conn3.Close();
                    }
                    dr2.Close();
                    conn2.Close();
                }
                dr.Close();
                conn.Close();
            }
            dr0.Close();
            conn0.Close();
        }
这样太慢了，300来个人快死掉了，怎么办啊？

Answer 1

第一：禁止VIEWSTATE
第二：数据库的多次打开操作用连表查询的方式操作，减少到一次查询
第三：用cache代替到数据库查询数据这3条足够你提高速度了

Answer 2

1.建议在设计上优化，树只加载到部门，组别想其它办法。
2.也可如1L所说，第一次只加载公司，点+号才去加载对应的部门，点部门再加载组别。
3.3个嵌套循环查询，至少可简化为2个，用存储过程实现，返回两个表，或者说两个List<>对象，部门表对象，每个部门的小组用字符串拼接起来，这样就只用循环访问数据库（部门个数*公司个数）次，你现在的查询要做（部门个数*公司个数*小组个数）次查询的。

Answer 3

            conn.Open();            string selectCompany = "select CompanyID,CompanyName from Company";
            string selectDepartment = "select CompanyID,DepartmentID,DepartmentName from Department";
            string selectTeam = "select DepartmentID,TeamID,TeamName from Team";
            string selectEmployee = "select TeamID,b.EmployeeID,b.EngName from EmployeeStatus a,Employee b where a.EmployeeID=b.EmployeeID";            DataTable dtCompany = new DataTable();
            DataTable dtDepartment = new DataTable();
            DataTable dtTeam = new DataTable();
            DataTable dtEmployee = new DataTable();            SqlDataAdapter adapterCompany = new SqlDataAdapter(selectCompany, conn);
            adapterCompany.Fill(dtCompany);            SqlDataAdapter adapterDepartment = new SqlDataAdapter(selectDepartment, conn);
            adapterDepartment.Fill(dtDepartment);            SqlDataAdapter adapterTeam = new SqlDataAdapter(selectTeam, conn);
            adapterTeam.Fill(dtTeam);            SqlDataAdapter adapterEmployee = new SqlDataAdapter(selectEmployee, conn);
            adapterEmployee.Fill(dtEmployee);            foreach (DataRow rowCompany in dtCompany.Rows)
            {
                TreeNode childnode0 = new TreeNode(rowCompany["CompanyEngName"].ToString(), rowCompany["CompanyID"].ToString());
                childnode0.SelectAction = TreeNodeSelectAction.None;
                childnode0.NavigateUrl = "javascript:;";
                TreeView1.Nodes.Add(childnode0);                DataRow[] rowsDepartment = dtDepartment.Select("CompanyID='" + childnode0.Value.Trim() + "'");                foreach (DataRow rowDepartment in rowsDepartment)
                {
                    TreeNode childnode = new TreeNode(rowDepartment["DepartmentName"].ToString(), rowDepartment["DepartmentID"].ToString());
                    childnode.SelectAction = TreeNodeSelectAction.None;
                    childnode.CollapseAll();
                    childnode0.ChildNodes.Add(childnode);                    DataRow[] rowsTeam = dtTeam.Select("DepartmentID='" + childnode.Value.Trim() + "'");                    foreach (DataRow rowTeam in rowsTeam)
                    {
                        TreeNode childnode2 = new TreeNode(rowTeam["TeamName"].ToString(), rowTeam["TeamID"].ToString());
                        childnode2.SelectAction = TreeNodeSelectAction.None;
                        childnode2.CollapseAll();
                        childnode.ChildNodes.Add(childnode2);                        DataRow[] rowsEmployee = dtEmployee.Select("TeamID='" + childnode2.Value.Trim() + "'");                        foreach (DataRow rowEmployee in rowsEmployee)
                        {
                            TreeNode childnode3 = new TreeNode(rowEmployee["EngName"].ToString(), rowEmployee["EmployeeID"].ToString());
                            childnode3.ToolTip = rowEmployee["EmployeeID"].ToString();
                            childnode3.SelectAction = TreeNodeSelectAction.Expand;
                            childnode3.CollapseAll();
                            childnode2.ChildNodes.Add(childnode3);
                        }
                    }
                }
            }
            conn.Close();这样改进了，加载一直都很快，就是点+展开或收起时很慢，不知道怎么搞的？

Answer 4

treeview，点开只是执行js隐藏显示（如果你没有修改其他的话），慢的原因，只能是客户端的

Answer 5

告诉你7年前大多数负责任的作者写的asp.net教科书讲treeview控件都应该介绍到的知识：http://msdn.microsoft.com/zh-cn/library/system.web.ui.webcontrols.treeview.treenodepopulate(v=vs.100).aspx