Implement List treenode for extjs tree

Question

I have a table that has these data

State       Cities

 TX         Houston
 TX         San Antonio
 TX         Austin
 TX         Austin
 PA         Philadelphia
 PA         Pitssburgh
 PA         Pittsburgh
 PA         
 CO         Boulder
 CO         Denver

I am trying to implement a tree that has these three states as nodes and the the respective cities as leaf.

On c# I am creating a list.

            comd.CommandText = "SELECT * FROM MyTable";

            con.Open();
            SqlDataReader reader = comd.ExecuteReader();
            while (reader.Read())
            {
                City myData = new City();
                myData.State = reader["State"].ToString().Trim();
                myData.Cities = reader["Cities"].ToString().Trim();
                giveData.Add(myData);
            }  
            int count = 1;
            List<TreeNode> myNode = new List<TreeNode>();
            foreach (City myData in giveData)
            {
                TreeNode treeNode = new TreeNode();
                treeNode.id = count++;
                treeNode.name = myData.State;
                treeNode.leaf = false;

                List<TreeNode> Level1 = new List<TreeNode>();
                if (mydata.Cities != null)
                {
                    if (mydata.Cities!= "")
                    {
                        foreach (City State in result)
                        {
                            TreeNode node1 = new TreeNode();
                            node1.id = count++;
                            node1.name = myData.Cities;
                            node1.leaf = true;

                            Level1.Add(node1);
                         }
                     }
                }
                treeNode.children = Level1;
                myNode.Add(treeNode);
            }

            return JsonConvert.SerializeObject(myNode);

What I am getting is a all the 4 TX and PA and 2 CO as tree Nodes and under TX node I get two Austin...

How do I implement my list so that I only get one TX, one PA and one CO and only 1 city if there are two cities...

Answer 1

I could not test it but that should do it:

comd.CommandText = "SELECT * FROM MyTable";

con.Open();
SqlDataReader reader = comd.ExecuteReader();
while (reader.Read())
{
    City myData = new City();
    myData.State = reader["State"].ToString().Trim();
    myData.Cities = reader["Cities"].ToString().Trim();
    giveData.Add(myData);
}  
int count = 1;
Dictionary<string, TreeNode> result = new Dictionary<string, TreeNode>();
foreach (City myData in giveData) 
{
    if (result.ContainsKey(myData.State ))
    {
        result[myData.State].children.Add(new TreeNode() {
            id = count++,
            name = myData.Cities,
            leaf = true
        });
    }
    else
    {
        result.add(giveData.State, new TreeNode() {
            id = count++,
            name = myData.State,
            leaf = false,
            children = new List<TreeNode>()
        });
    }
}

return JsonConvert.SerializeObject(result.Values);