treeview动态展节点,如何判断数据库中一个表的末节点并在末节点后添加数据库中另外一个表的数据?求解

Question

这段代码已通过编译测试
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;

 

namespace beforeexpand
{
    public partial class Form1 : Form
    {
        private String LoadingKey = "57868058-716C-4D36-B48E-D840AA2C50C3";
        private TreeView treeView1;
        public Form1()
        {
            InitializeComponent();
        }

 

        private void Form1_Load(object sender, EventArgs e)
        {
            this.BuildTreeView();
        }

 

        private void BuildTreeView()
        {
            if (this.treeView1 == null)
            {
                this.treeView1 = new TreeView();
                this.treeView1.Location = new Point(0, 25);
                this.treeView1.Size = new Size(292, 248);
                this.Controls.Add(this.treeView1);
                this.treeView1.BeforeExpand += new TreeViewCancelEventHandler(treeView1_BeforeExpand);
            }

 

            this.treeView1.BeginUpdate();
            this.BuildTreeView(this.treeView1.Nodes, 0);
            this.treeView1.EndUpdate();
        }

 

        private DataTable fSampleDataSource;
        private DataTable SampleDataSource
        {
            get
            {
               
                    SqlConnection thisconnection = new SqlConnection("server=192.100.3.3;database=GADMEIERP;uid=sa;pwd=;");
                    SqlCommand cmd = thisconnection.CreateCommand();
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "SELECT a.FInterID,b.FBOMNumber,b.FUseStatus,c.FNumber,c.FName FROM ICBOMGROUP a left outer join ICBOM b on a.FInterID=b.FParentID left outer join t_ICItemCore c on b.FItemID=c.FItemID  order by a.FNumber";
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = cmd;
                    DataSet ds = new DataSet();
                    adapter.Fill(ds);
                    this.fSampleDataSource = ds.Tables[0];
                    return fSampleDataSource;
               
            }
        }

 

        private DataTable GetTableByParentId(int parentId)
        {
            /*
                -- 生成测试数据
                IF OBJECT_ID('ICBOMGROUP') IS NOT NULL
                    DROP TABLE ICBOMGROUP
                   
                CREATE TABLE ICBOMGROUP (
                    FInterID INT
                  , FParentID INT
                  , FName VARCHAR(40)
                  , FNumber VARCHAR(40) )

 

                INSERT ICBOMGROUP
                SELECT 1039,0,'产品','0'     UNION ALL
                SELECT 2296,0,'喷印机壳','1' UNION ALL
                SELECT 6497,0,'丝印彩盒','2' UNION ALL
                SELECT 7035,0,'外发加工','3' UNION ALL
                SELECT 1041,1039,'VGA电视盒','01' UNION ALL
                SELECT 1071,1041,'TV3488B系列','01.01' UNION ALL
                SELECT 1140,1071,'TV2188E BOM','01.01.01' UNION ALL
                SELECT 1144,1140,'普通订单','01.01.01.01' UNION ALL
                SELECT 1146,1144,'TV2188E 中文BOM','01.01.01.01.01' UNION ALL
                SELECT 1145,1140,'特殊订单','01.01.01.02' UNION ALL
                SELECT 1129,1039,'XGA电视盒','02' UNION ALL
                SELECT 1199,1129,'TV5811系列','02.02' UNION ALL
                SELECT 1212,1199,'TV5811 BOM','02.02.01' UNION ALL
                SELECT 2297,2296,'机壳-上盖','1.01' UNION ALL
                SELECT 2308,2297,'GM998','01.01.01' UNION ALL
                SELECT 2312,2308,'GM998上盖 灰色ABS/喷银色','1.01.01.01' UNION ALL
                SELECT 7039,7035,'支架组件','3.01' UNION ALL
                SELECT 7040,7039,'PD1048','3.01.01' UNION ALL
                SELECT 7172,7040,'PD1048支架组件1','3.01.01.01'
             */
            String connectionString = "server=192.100.3.3;database=GADMEIERP;uid=sa;pwd=;";
            String commandText = "SELECT FInterID,FParentID,FName,FNumber,[ChildCount] = (SELECT COUNT(1) FROM ICBOMGROUP a WHERE FParentID = a.FInterID)  FROM ICBOMGROUP a WHERE FParentID = @ParentId ";

 

            using (SqlDataAdapter adapter = new SqlDataAdapter(commandText, connectionString))
            {
                adapter.SelectCommand.Parameters.AddWithValue("@ParentId", parentId);
                adapter.SelectCommand.CommandTimeout = 0;

 

                DataSet ds = new DataSet();
                adapter.Fill(ds);

 

                return ds.Tables[0];
            }
        }

 

        private void BuildTreeView(TreeNodeCollection nodes, int parentId)
        {
            try
            {
                DataTable dt = this.GetTableByParentId(parentId);
                foreach (DataRow dataRow in dt.Rows)
                {
                   
                        DataNode dataNode = new DataNode();

 

                        dataNode.Id = (Int32)dataRow["FInterID"];
                        dataNode.Text = dataRow["FNumber"].ToString() + '(' + dataRow["FName"].ToString() + ')';
                        dataNode.Row = dataRow;
                        dataNode.Nodes.Add(this.CreateLoadingNode()); // 添加一个特殊节点，它的存在标志它的父节点需要动态加载
                        nodes.Add(dataNode);
                        // this.BuildTreeView(node.Nodes, fPath); // 取消递归，在 BeforeExpand 事件中添加子级节点

                }

 

            }
            catch(Exception e)
            {
                MessageBox.Show(e.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
               
           
           
        }
          
       

 

        public TreeNode CreateLoadingNode()
        {
            // 创建一个特殊节点，它的存在标志它的父节点需要动态加载
            DataNode dataNode = new DataNode();
            dataNode.Type = this.LoadingKey;
            return dataNode;
        }

 

        private void treeView1_BeforeExpand(object sender, TreeViewCancelEventArgs e)
        {
            DataNode dataNode = (DataNode)e.Node;

 

            if (dataNode.Nodes.Count == 1 &&
                ((DataNode)dataNode.Nodes[0]).Type == this.LoadingKey)
            {
                // 若特殊节点存在，移除它，并为它的父节点创建子节点
                dataNode.Nodes[0].Remove();
                this.BuildTreeView(dataNode.Nodes, dataNode.Id);
            }
        }
    }

 

    public class DataNode : TreeNode
    {
        public Int32 ID { get; set; }
        public Int32 Id { get; set; }
        public String Type { get; set; }
        public DataRow Row { get; set; }

 

        public DataNode() : base() { }
    }

 

   
}

生成的结果是（以FNumber为0,FName为产品为例，一个树节点展开的效果):
0(产品)
 --VGA电视盒
   --TV3488B系列
 --XGA电视盒
   --TV5811系列
     --TV5811BOM
其中(TV3488B系列的FInterID为1071,TV5811BOM的FInterID为1212)，在FParentID中已无1071,1212记录，判断该二个节点为末节点,听老师指点：用ChildCount为0判断末节点,SQL语句如下:
SELECT FInterID,FParentID,FName,FNumber,[ChildCount] = (SELECT COUNT(1) FROM ICBOMGROUP a WHERE FParentID = a.FInterID)  FROM ICBOMGROUP a WHERE FParentID = @ParentId

ICBOMGROUP表与下句的ICBOM表的关联项是ICBOMGROUP.FInterID=ICBOM.FParentID

当ChildCount为0,则该结点为末节点，此时需要在该节点下加载另外一个数据库表（ICBOM表）的相关数据，ICBOM表结果如下:
FInterID   FParentID    FBOMNumber
 3000          1071         BOM00071
 3001          1071         BOM00072
SQL插入语句已写好：
if object_id('ICBOM') is not null drop table ICBOM
create table ICBOM (FInterID varchar(40),FParentID varchar(40),FBOMNumber varchar(40)
insert ICBOM
select '3000','1071','BOM00071' union all
select '3001','1071','BOM00072' 

当判断ICBOMGROUP表中的FInterID列中的数据(1071)为末节点，则加载ICBOM表中的数据，
Treenode Lastnode=new Treenode();
Lastnode.Text="BOM00071";
Lastnode.Tag=BOM00071";
Treenode Lastnode1=new Treenode();
Lastnode1.Text="BOM00072";
Lastnode1.Tag=BOM00072";

如最上文的代码,dataNode.nodes.Add(Lastnode)和dataNode.nodes.Add(Lastnode1),将ICBOM表中的二项数据添加到treeview节点中,得到最后的结果为:
0(产品)
 --VGA电视盒
   --TV3488B系列
     --BOM00071
     --BOM00072
 --XGA电视盒
   --TV5811系列
     --TV5811BOM
这只是以其中一小部分作为示例，请老师指点，最后关键一环了，treeview与datagridview的连动问题，在xxy版主的指导下，已经完成通过了，如何判断末节点，并添加最后数据，测试一天了始终未完成，请指导，急死了

Answer 1

老师，你邮箱几多啊?

Answer 2

如果可以你把数据库及代码发到我的邮箱里 fanfan1918#hotmail.com

---

知识改变命运，奋斗成就人生!

Answer 3

中间那个#号是@号嘛？

Answer 4

是的~～

---

知识改变命运，奋斗成就人生!

Answer 5

谢谢老师，关于数据库表的结构我导出来用excel表的形式传给你，代码还是和这个一样的,
如果老师修改成功了，回复偶邮箱即可，再次感谢

Answer 6

XXY版主，资料已经发到您邮箱了，请查收

Answer 7

可以在业务层把从数据访问层返回的数据合并。

---

---

The following is signature, not part of post
Please mark the post answered your question as the answer, and mark other helpful posts as helpful.
Visual C++ MVP

Answer 8

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;

namespace XTreeViewDemo
{
    public partial class Form1 : Form
    {
        private String LoadingKey = "57868058-716C-4D36-B48E-D840AA2C50C3";
        private TreeView treeView1;
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            this.BuildTreeView();
        }

        private void BuildTreeView()
        {
            if (this.treeView1 == null)
            {
                this.treeView1 = new TreeView();
                //this.treeView1.Location = new Point(0, 25);
                //this.treeView1.Size = new Size(292, 248);
                this.treeView1.Dock = DockStyle.Fill;
                this.Controls.Add(this.treeView1);
                this.treeView1.BeforeExpand += new TreeViewCancelEventHandler(treeView1_BeforeExpand);
            }

            this.treeView1.BeginUpdate();
            this.BuildGroupNode(this.treeView1.Nodes, 0);
            this.treeView1.EndUpdate();
        }

        private DataTable GetGroupDataTable(int parentId)
        {
            String connectionString = "server=XXY;database=X;Integrated Security=SSPI;";
            String commandText = @"
SELECT  A.FInterID
      , A.FParentID
      , A.FName
      , A.FNumber
      , ChildGroupCount = ISNULL(B.ChildGroupCount, 0)
      , ChildICbomCount = ISNULL(C.ChildICbomCount, 0)
FROM ICBOMGROUP A
LEFT OUTER JOIN 
(
	SELECT FParentID
		 , ChildGroupCount = SUM(1) 
		FROM ICBOMGROUP GROUP BY FParentID
) B ON A.FInterID = B.FParentID
LEFT OUTER  JOIN 
(
	SELECT FParentID
		 , ChildICbomCount = SUM(1) 
		 FROM ICBOM GROUP BY FParentID
) C ON A.FInterID = C.FParentID
WHERE A.FParentID = @ParentId
";

            using (SqlDataAdapter adapter = new SqlDataAdapter(commandText, connectionString))
            {
                adapter.SelectCommand.Parameters.AddWithValue("@ParentId", parentId);
                adapter.SelectCommand.CommandTimeout = 0;

                DataSet ds = new DataSet();
                adapter.Fill(ds);

                return ds.Tables[0];
            }
        }

        private DataTable GetICbomDataTable(int parentId)
        {
            String connectionString = "server=XXY;database=X;Integrated Security=SSPI;";
            String commandText = @"SELECT * FROM ICBOM WHERE FParentID = @ParentId";

            using (SqlDataAdapter adapter = new SqlDataAdapter(commandText, connectionString))
            {
                adapter.SelectCommand.Parameters.AddWithValue("@ParentId", parentId);
                adapter.SelectCommand.CommandTimeout = 0;

                DataSet ds = new DataSet();
                adapter.Fill(ds);

                return ds.Tables[0];
            }
        }

        private void BuildGroupNode(TreeNodeCollection nodes, int parentId)
        {
            try
            {
                DataTable dt = this.GetGroupDataTable(parentId);
                foreach (DataRow dataRow in dt.Rows)
                {
                    DataNode dataNode = new DataNode();

                    dataNode.Id = (Int32)dataRow["FInterID"];
                    dataNode.Text = dataRow["FNumber"].ToString() + '(' + dataRow["FName"].ToString() + ')';
                    dataNode.Row = dataRow;
                    if ((int)dataRow["ChildGroupCount"] > 0 ||
                        (int)dataRow["ChildICbomCount"] > 0)
                    {
                        dataNode.Nodes.Add(this.CreateLoadingNode()); // 添加一个特殊节点，它的存在标志它的父节点需要动态加载
                    }
                    nodes.Add(dataNode);
                    // this.BuildTreeView(node.Nodes, fPath); // 取消递归，在 BeforeExpand 事件中添加子级节点                                                                                          
                }

            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void BuildIcomNode(TreeNodeCollection nodes, int parentId)
        {
            DataTable dt = this.GetICbomDataTable(parentId);
            foreach (DataRow dataRow in dt.Rows)
            {
                DataNode dataNode = new DataNode();

                dataNode.Id = (Int32)dataRow["FInterID"];
                dataNode.Text = dataRow["FBOMNumber"].ToString();
                dataNode.Row = dataRow;
                nodes.Add(dataNode);
            }
        }

        public TreeNode CreateLoadingNode()
        {
            // 创建一个特殊节点，它的存在标志它的父节点需要动态加载
            DataNode dataNode = new DataNode();
            dataNode.Type = this.LoadingKey;
            return dataNode;
        }

        private void treeView1_BeforeExpand(object sender, TreeViewCancelEventArgs e)
        {
            DataNode dataNode = (DataNode)e.Node;

            if (dataNode.Nodes.Count == 1 &&
                ((DataNode)dataNode.Nodes[0]).Type == this.LoadingKey)
            {
                // 若特殊节点存在，移除它，并为它的父节点创建子节点
                dataNode.Nodes[0].Remove();

                DataRow dataRow = (DataRow)dataNode.Row;

                if ((int)dataRow["ChildGroupCount"] > 0 )
                    this.BuildGroupNode(dataNode.Nodes, dataNode.Id);
                else
                    this.BuildIcomNode(dataNode.Nodes, dataNode.Id);
            }
        }
    }

    public class DataNode : TreeNode
    {
        public Int32 Id { get; set; }
        public String Type { get; set; }
        public DataRow Row { get; set; }

        public DataNode() : base() { }
    }
}

---

知识改变命运，奋斗成就人生!

Answer 9

非常感谢老师的指点,代码测试已经通过,现在是treeview与datagridview数据连动,出现了一些问题,根据老师的提示,偶做的一个小测试，就是treeview与datagridview的连动,只要将datagridview的数据源绑定到相应的treeview的相应节点，即在datagridview中可以显示相关数据，根据您的代码，偶添加了一段代码关于AfterSelect事件的代码，但是无法连动，不知道是不是我选择的结点的值有问题，还是怎么着？
  private void treeView1_AfterSelect(object sender, TreeViewEventArgs e)
        {
            TreeNode node = e.Node;
            string id=node.Text;//根据当前选中的末节点text值，将节点的text值赋予存储过程，返回一个数据库中相应表的数据，但就是连动不了,选择末点节的值出现问题了？
            SqlConnection thisconnection = new SqlConnection("server=192.100.3.3;database=GADMEIERP;uid=sa;pwd=;");
            SqlCommand cmd = thisconnection.CreateCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "t_BOM1";
            SqlParameter parameter = new SqlParameter("@FBOMNumber", SqlDbType.VarChar, 40);
            parameter.Direction = System.Data.ParameterDirection.Input;
            parameter.Value = id;
            cmd.Parameters.Add(parameter);
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = cmd;
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            this.dataGridView1.DataSource = ds.Tables[0];
            //this.dataGridView2.DataSource = FChildBOM(id);
           
        }

无法连动数据