I was recently tasked with creating a navigation control which displayed a list of cities and nested communities for those cities. As the listings could change at a moments notice and I might not always be available to modify code I decided to use a TreeView control and populate it from a SQL Server table.
While my end-product looks and acts differently than this example, I will post the mere basics for building a working product.
First a look at the data. I am pulling information about communities located with a city so the relationship is many communities per city.
Example of the data follows. The communityID is our primary key and will be used as the value of the selected node.
communityID communityCity communityName
----------- -------------------------------------------------- --------------------------------------------------
1 Cypress Coles Crossing
2 Cypress Bridgeland
3 Cypress Stillwater Village
4 Memorial Terraces on Memorial
5 Richmond Lakes of Bella Terra
Let's drop a TreeView control into our page. Mine is called tvNav. I added some images to indicate the state (Collapsed or Expanded) which you see in the markup.
<body>
<form id="form1" runat="server">
<div>
<asp:TreeView ID="tvNav" runat="server"
CollapseImageUrl="~/Testimonials/images/expanded.gif"
ExpandImageUrl="~/Testimonials/images/collapsed.gif">
</asp:TreeView>
</div>
</form>
</body>
Now on the back-end we build our functions to pull the data and populate the TreeView.
The method I chose to use (for no other reason than I felt like it) was to build a DataSet of information pulled from the table. I then iterate through this dataset for data comparison and extraction.
The first function builds a DataSet and returns it to the function that calls it.
private DataSet BuildDS()
{
string strSQL = "SELECT communityID,communityName,communityCity FROM MYTABLE WHERE communityStatus = 'A' ORDER BY communityCity, communityName";
SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["MYCONNECTIONSTRING"].ToString());
conn.Open();
SqlDataAdapter sda = new SqlDataAdapter(strSQL, conn);
DataSet ds = new DataSet();
sda.Fill(ds);
conn.Close();
return ds;
}
The workhorse:
private void BuildTree()
{
DataSet ds = BuildDS();
string cCity = "";
if (ds != null)
{TreeNode parentnode = null;
TreeNode childnode = null;
foreach (DataRow row in ds.Tables[0].Rows)
{
if (cCity.CompareTo(row["communityCity"].ToString()) < 0)
{
parentnode = new TreeNode(row["communityCity"].ToString());
// we want parent nodes to simply expand so no postback (select) is needed
parentnode.SelectAction = TreeNodeSelectAction.Expand;
if (parentnode != null)
{
tvNav.Nodes.Add(parentnode);
}
}
cCity = row["communityCity"].ToString();
}
foreach (TreeNode tn in tvNav.Nodes)
{
foreach (DataRow row in ds.Tables[0].Rows)
{
if (tn.Text.CompareTo(row["communityCity"]) == 0)
{
childnode = new TreeNode();
childnode.Text = row["communityName"].ToString();
childnode.Value = row["communityID"].ToString();
tn.ChildNodes.Add(childnode);
}
}
tn.Collapse();
}
}
}
First thing we do is to add the Parent nodes to the Treeiew. As the CITY is the parent in the data's relationship schema we'll use that as the value for our Parent nodes in the control. To add those nodes I iterate through each row in the dataset and compare the value of the stored City with the value of the cCity string element. In the first run through of the loop we have "null" as the value of cCity since nothing's been assigned as the value. After we process our actions for each iteration though we assign the current value of the City field in the dataset to the cCity element.
After our parents are populated we make another loop through our dataset to build child nodes to add to the parents. To accomplish this we use 2 loops. The first loop moves through the parent nodes. The nested loop inside compares the value of the parent node text with the value of the "communityCity" data-element. If they are the same, we know to add a child node. If not we simply move on to the next.
Note: If your results-set (either in your SQL statement or via filtering your Dataset) are not in the correct order, this method won't work. if you look at the SQL query statement I've used, you see it's ordered by communityCity and then by communityName.
Finally we call our BuildTree() function so our control is built. I do this on the initial creation of the page so that subsequent postbacks don't create additional nodes.
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
BuildTree(); }
}
