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:

0 
 10
 20
 30
   1
   2
   3
   4
1
 10
   1
   2
   3
 20
   1
   2
2
 10
   1
3
 10
   1
4
 10
   1
   2
 20
   1
5
 10
   1

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());
                }
            }
        }
    }
}

Answer 1

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#}