Am populating a treeview from database,i have two tables one-to-many relationship,when am loading it the parent nodes are done but in the child nodes there are duplicate values,like in the first parent node there is street1 and street2 in the second node there is street1 and street2(of the first node) plus its streets 3 and 4 here is my code :
private void PopulateTreeview12()
{
treeView1.Nodes.Clear();
DataSet ds = new DataSet();
DataSet ds2 = new DataSet();
SqlCommand cmd1 = cnn.CreateCommand();
cmd1.CommandText = "select areaId, areaName from area";
SqlDataAdapter sdr1 = new SqlDataAdapter(cmd1);
cmd1.CommandType = CommandType.Text;
cmd1.Connection = cnn;
sdr1.Fill(ds);
foreach (DataRow item in ds.Tables[0].Rows)
{
TreeNode AreaNode = new TreeNode();
AreaNode.Text = item["areaName"].ToString();
string areaID = item["areaId"].ToString();
SqlCommand cmd2 = cnn.CreateCommand();
cmd2.CommandText = "SELECT streetId, streetName, areaId FROM street WHERE areaId = @areaId";
cmd2.CommandType = CommandType.Text;
cmd2.Parameters.AddWithValue("@areaId", areaID);
SqlDataAdapter sdr2 = new SqlDataAdapter(cmd2);
cmd2.Connection = cnn;
sdr2.Fill(ds2);
foreach (DataRow itemchild in ds2.Tables[0].Rows)
{
TreeNode childtreenode = new TreeNode();
childtreenode.Text = itemchild["streetName"].ToString();
AreaNode.Nodes.Add(childtreenode);
}
treeView1.Nodes.Add(AreaNode);
}
}
Am stuck here, please i need help
What I have tried:
i have tried to use for loop but same result :
for (int i = 0; i < dt.Rows.Count; i++)
{
// Current areaId and areaname is stored
string areaID = dt.Rows[i]["areaId"].ToString();
string areaName = dt.Rows[i]["areaName"].ToString();
TreeNode AreaNode = new TreeNode(areaName);
SqlCommand cmd2 = cnn.CreateCommand();
cmd2.CommandText = "SELECT streetId, streetName FROM street WHERE areaId = @areaId";
cmd2.CommandType = CommandType.Text;
cmd2.Parameters.AddWithValue("@areaId", areaID);
SqlDataAdapter sdr2 = new SqlDataAdapter(cmd2);
cmd2.Connection = cnn;
sdr2.Fill(dt2);
for (int j = 0; j < dt2.Rows.Count; j++)
{
string streetId = dt2.Rows[j]["streetId"].ToString();
string streetName = dt2.Rows[j]["streetName"].ToString();
TreeNode StreetNode = new TreeNode(streetName);
AreaNode.Nodes.Add(StreetNode);
}
treeView1.Nodes.Add(AreaNode);
}