Create A treenode with List

Question

I have a table in this structure

ListA   labelName
  1      Colorado
  1      Wyoming
  1      Illinois
  2      New York
  2      Ohio

I am trying to create a tree where if LISTA = 1, it goes under one node called "header one" and Colorado, Wyoming, Illinois as its Leaf and samething with value "2"... by doing this I am getting 3 "Header one" nodes instead of having all those three nodes under one...

SqlCommand cmd = con.CreateCommand();

        comd.CommandText = "SELECT * FROM myTable";
        con.Open();
        SqlDataReader reader = comd.ExecuteReader();
        while (reader.Read())
        {
            City MyData = new City();

            MyData.ListA = reader["ListA"].ToString().Trim();
            MyData.labelName = reader["labelName"].ToString().Trim();
            giveData.Add(MyData);
        }

        int count = 1;

        List<TreeNode> myNode = new List<TreeNode>();
        foreach (City MyData in giveData)
        {
            // 1st foreach
                if (MyData.ListA != "1")
                {

                    TreeNode treeNode = new TreeNode();
                    treeNode.id = count++;
                    treeNode.name = "Header One";
                    treeNode.leaf = false;

                    List<TreeNode> Level1 = new List<TreeNode>();
                    foreach (City labelName  in giveData)
                    {
                        if (labelName.ListA == "1")
                        {// 2nd foreach
                            TreeNode node1 = new TreeNode();
                            node1.id = count++;
                            node1.name = labelName.labelName;
                            node1.leaf = true;

                            Level1.Add(node1);
                        }
                    }

                    treeNode.children = Level1;
                    myNode.Add(treeNode);
            }
            else if (MyData.ListA != "2")
                {

                    TreeNode treeNode = new TreeNode();
                    treeNode.id = count++;
                    treeNode.name = "Header Two";
                    treeNode.leaf = false;

                    List<TreeNode> Level1 = new List<TreeNode>();
                    foreach (City labelName  in giveData)
                    {
                        if (labelName.ListA == "2")
                        {// 2nd foreach
                            TreeNode node1 = new TreeNode();
                            node1.id = count++;
                            node1.name = labelName.labelName;
                            node1.leaf = true;

                            Level1.Add(node1);
                        }
                    }

                    treeNode.children = Level1;
                    myNode.Add(treeNode);
            }
        }
        return JsonConvert.SerializeObject(myNode);

What would be the best way to handle this

Answer 1

What you need to do is group the data on ListA.

var groups = giveData.GroupBy(state => state.ListA);

foreach(var group in groups)
{
    //add header to treeview
    string header = group.Key;

    foreach(var state in group)
    {
        //add this state as a child of the group you just added
    }
}

I'd also suggest creating a lookup to help you map the ListA number to it's textual representation:

var headerLookup = new Dictionary<string, string>()
{
    {"1", "Header One"},
    {"2", "Header Two"},
    {"3", "Header Three"}
};

This will allow you to do the following:

string headerText = headerLookup[group.Key];