How can you use a Foreach loop to iterate data into treenode from data table nodes

Question

I'm trying to create treeview nodes with foreach loops. I have 2 data tables that are currently being ran through with foreach loops to grab the rows. my first foreach loop sucessfully puts the first datatable rows into the treeview nodes. The issue is getting the second datatable rows into each Parent node.

The structure of the first datatable is:

job       |suffix
J000027399   0
J000027399   1
J000027399   2
J000027399   3
J000027399   4
J000027399   5

The second table structure is:

job       |suffix|operNum
J000027399  0        10
J000027399  0        20
J000027399  0        30
J000027399  1        10
J000027399  1        20
J000027399  2        10
J000027399  3        10
J000027399  4        10
J000027399  4        20
J000027399  5        10

the third table structure is:

job       |suffix|operNum|seq|item
J000027399  0        30    1  item_1
J000027399  0        30    2  item_2
J000027399  0        30    3  item_3
J000027399  0        30    4  item_4
J000027399  1        10    1  item_1
J000027399  1        10    2  item_2
J000027399  1        10    3  item_3
J000027399  1        20    1  item_1
J000027399  1        20    2  item_2
J000027399  2        10    1  item_1
J000027399  3        10    1  item_1
J000027399  4        10    1  item_1
J000027399  4        10    2  item_2
J000027399  4        20    1  item_1
J000027399  5        10    1  item_1

I need the tree nodes to populate like:

The parent node is based on the first datatable suffix and the child nodes are based on the second datatable opernum and the thrid datatable is based on the seq under each opernum per each suffix node.

Here is my code so far

using (SqlDataAdapter jobAdapter = new SqlDataAdapter(cmd))
{            
    DataTable dtJ = new DataTable();
    jobAdapter.Fill(dtJ);

    foreach (DataRow jRow in dtJ.Rows)
    {
        tvBomView.Nodes.Add("job", jRow["Suffix"].ToString());
    }

    SqlCommand cmdStageTwo = new SqlCommand("dbo.CHS_Bom_View_Grab_JobRoute", conn);
    cmdStageTwo.CommandType = CommandType.StoredProcedure;
    cmdStageTwo.Parameters.Add("@Job", SqlDbType.NVarChar, 10).Value = txtJob.Text;

    using (SqlDataAdapter jobRouteAdapter = new SqlDataAdapter(cmdStageTwo))
    {
        DataTable dtJR = new DataTable();
        jobRouteAdapter.Fill(dtJR);

        foreach (TreeNode node in tvBomView.Nodes)
        {
            foreach (DataRow jrRow in dtJR.Rows)
            {
                if (node.Text != jrRow["Suffix"].ToString())
                {
                    break;
                }
                else if (node.Text == jrRow["Suffix"].ToString())
                {
                    tvBomView.Nodes["job"].Nodes.Add("oper", "Oper: " + 
                        jrRow["OperNum"].ToString());
                }
            }
        }
    }
}

if i take the break out it writes all the data to the first parent node only. — Rufus7Ls, Feb 21 '20 at 18:24
yes as I've debugged the code the. first foreach loop runs and the second one and runs through all the data in jrRow[] and fills the first node even if its false. — Rufus7Ls, Feb 21 '20 at 18:38
I think you'd be better off joining the two tables, and the using maybe GroupBy and then binding that to your TreeList. — Austin T French, Feb 21 '20 at 19:41

score 1 · Accepted Answer · 2020-05-21T18:45:58.097

You can load both DataTable-s and pass them along with the TreeView to a method that creates the tree:

{
//The method where you call dtJ and dtJR from the database:
ToTreeView(tvBomView, dtj, dtJR);
}

ToTreeView(..) method:

private void ToTreeView(TreeView tv, DataTable dt1, DataTable dt2)
{
    tv.BeginUpdate();
    tv.Nodes.Clear();

    //Create a temp IEnumerable of anonymous type:
    var items = dt1.Rows.Cast<DataRow>()
        .Select(x => new
        {
            Parent = x,
            Children = dt2.Rows.Cast<DataRow>()
            .Where(y => y["suffix"].ToString() == x["suffix"].ToString())
        });

    foreach(var item in items)
    {
        var parentNode = tv.Nodes.Add("job", item.Parent["suffix"].ToString());

        foreach(var child in item.Children)
        {
            parentNode.Nodes.Add("oper", $"Oper: {child["operNum"].ToString()}");
        }
    }

    tv.EndUpdate();
}

Or just:

private void ToTreeView(TreeView tv, DataTable dt1, DataTable dt2)
{
    tv.BeginUpdate();
    tv.Nodes.Clear();

    dt1.Rows.Cast<DataRow>()
        .ToList()
        .ForEach(x =>
        {
            var p = tv.Nodes.Add("job", x["Suffix"].ToString());
            dt2.Rows.Cast<DataRow>()
            .Where(y => x["Suffix"].ToString() == y["Suffix"].ToString())
            .ToList()
            .ForEach(y => p.Nodes.Add("oper", $"Oper: {y["operNum"].ToString()}"));
        });

    tv.EndUpdate();
}

Edit: 3 Tables Version

private void ToTreeView(TreeView tv, DataTable dt1, DataTable dt2, DataTable dt3)
{
    tv.BeginUpdate();
    tv.Nodes.Clear();

    dt1.Rows.Cast<DataRow>()
        .ToList()
        .ForEach(x =>
        {
            var p = tv.Nodes.Add("job", x["suffix"].ToString());
            dt2.Rows.Cast<DataRow>()
            .Where(y => x["suffix"].ToString() == y["suffix"].ToString())
            .ToList()
            .ForEach(y =>
            {
                var c = p.Nodes.Add("oper", $"Oper: {y["operNum"].ToString()}");
                dt3.Rows.Cast<DataRow>()
                .Where(z => z["suffix"].ToString() == y["suffix"].ToString() &&
                z["operNum"].ToString() == y["operNum"].ToString())
                .ToList()
                .ForEach(n => c.Nodes.Add("seq", $"Seq: {n["seq"].ToString()}"));
            });
        });

    tv.EndUpdate();
}

Please consider this as a quick-and-dirty workaround and consider what have been mentioned in the comments. Check out the related posts below.

Related

^{Populate TreeView from DataTable}
^{Populate WinForms TreeView from DataTable}
^{Populate TreeView from Database using C#}

This works great! Is it possible to add a third DataTable to this and have it create a child node under the opernum node? I'm trying to learn Linq so I'm just curious. — Rufus7Ls, Feb 25 '20 at 19:34
the thing is I have three tables to build a bill of materials and I'm not sure if i can join the tables into a single DataTable without having unwanted extra nodes. — Rufus7Ls, Feb 25 '20 at 20:23
Thanks! I appreciate your replies. I have added the table and updated the TreeView example to include the output of the third table. — Rufus7Ls, Feb 26 '20 at 12:32
when you run the code there is an exception message that states Specified cast is not valid — Rufus7Ls, Feb 26 '20 at 16:54
This is perfect. I know I'm asking to much but is there a way add recursion to this so it just steps through the two DataTables and fills the tree at the seq level because each seq has opernums and seqs recursively. — Rufus7Ls, Feb 26 '20 at 18:55
@Rufus7Ls No, you can't produce the same tree depending on the third table only. Still need the second table (we can skip the first one) to populate the tree as you described. That's why I suggested the links for a better approach — , Feb 27 '20 at 01:01

score 0 · Answer 2 · answered Feb 21 '20 at 19:48

Try following :

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

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();

            DataTable dt = new DataTable();
            dt.Columns.Add("job", typeof(string));
            dt.Columns.Add("suffix", typeof(int));
            dt.Columns.Add("operNum", typeof(int));

            dt.Rows.Add(new object[] { "J000027399", 0, 10});
            dt.Rows.Add(new object[] { "J000027399", 0, 20});
            dt.Rows.Add(new object[] { "J000027399", 0, 30});
            dt.Rows.Add(new object[] { "J000027399", 1, 10});
            dt.Rows.Add(new object[] { "J000027399", 1, 20});
            dt.Rows.Add(new object[] { "J000027399", 2, 10});
            dt.Rows.Add(new object[] { "J000027399", 3, 10});
            dt.Rows.Add(new object[] { "J000027399", 4, 10});
            dt.Rows.Add(new object[] { "J000027399", 4, 20});
            dt.Rows.Add(new object[] { "J000027399", 5, 10});

            var groups = dt.AsEnumerable()
                .OrderBy(x => x.Field<int>("suffix"))
                .ThenBy(x => x.Field<int>("operNum"))
                .GroupBy(x => x.Field<int>("suffix"));

            foreach (var group in groups)
            {
                TreeNode node = new TreeNode(group.Key.ToString());

                treeView1.Nodes.Add(node);
                foreach (DataRow row in group)
                {
                    node.Nodes.Add(row.Field<int>("operNum").ToString());
                }

            }
            treeView1.ExpandAll();
        }
    }
}

How can you use a Foreach loop to iterate data into treenode from data table nodes

2 Answers2