Click here to Skip to main content
Click here to Skip to main content

Populating Tree View nodes from sql server 2000

, 28 Nov 2006
Rate this:
Please Sign up or sign in to vote.
This article provides an insight on populating the tree view control from sql server database.

Introduction

In most websites, you can see the treeview control which is getting populated from the xml. Here i have discussed the tree view control which gets the nodes populated from the sql server database. You can also use other databases if you want. To create a Tree view, you dont usually need a parent-child relationship between two tables. Here i have used a single table and then populated the data from the database.

TreeView control can be populated with different types of sources which include SiteMapDataSource control, XML File, Collections, Containers and Database tables. The choice of the data source is closely tied with the scenario and the requirements of the application. Database should be the first choice when you certain that the data will be constantly changing. It is also the primary choice when you want to perform different operations on the data.

Prerequisites:

Install Microsoft.NET

When is the Need:

The Tree view is great control of displaying both static and dynamic data. As for websites, the data has to be shown to the user at the point when the user requests.  Providing the data's dynamically from the database would help in improving the performance of your website as well as improving the usability of the code and better understanding.

Events

The TreeView control provides several events that you can program against. This allows you to run a custom routine whenever an event occurs. The following table lists the events that are supported by the TreeView control.

Event

Description

1.    TreeNodeCheckChanged

 Occurs when the check boxes of the TreeView control change   state between posts to the server.

 

2. SelectedNodeChanged

Occurs when a node is selected in the TreeView control.

 

3. TreeNodeExpanded

Occurs when a node is expanded in the TreeView control.

4. TreeNodeCollapsed

Occurs when a node is collapsed in the TreeView control.

5. TreeNodePopulate

Occurs when a node with its PopulateOnDemand property set to true is expanded in the TreeView control.

6. TreeNodeDataBound

Occurs when a data item is bound to a node in the TreeView control.

Step By Step Implementation:

This example is done in ASP.NET using C# and sql server 2000 as the Database. The table name i have used is Categories.

1. Create a new ASP.NET website.

2. Add the tree view control from the tool box to your web page.

3.  Right click on the treeview and select its properties. Alternatively, You can select the treeview and press the f4 key.

4. Rename the treeview id to LinksTreeView.

5. Now scroll down and find the Nodes property. Now we create a default Root node. To create it, select the nodes collection property and add a root node. Change the Root node name to any name you want as the Root.

6. We will add the child nodes dynamically through the program.

7. I have put 2 textbox in the design,to capture which node has been selected and its particular row id.

8. Now double click on the design and use the following code behind. I have commented most of the lines in the code Behind, so i am not discussing much about the code.

Code Behind :

<BR>using System;<BR><BR>using System.Data;<BR><BR>using System.Configuration;<BR><BR>using System.Web;<BR><BR>using System.Web.Security;<BR><BR>using System.Web.UI;<BR><BR>using System.Web.UI.WebControls;<BR><BR>using System.Web.UI.WebControls.WebParts;<BR><BR>using System.Web.UI.HtmlControls;<BR><BR>using System.Data.SqlClient;<BR><BR> <BR><BR> <BR><BR>public partial class _Default : System.Web.UI.Page<BR><BR>{<BR><BR> <BR><BR> <BR><BR>protected void Page_Load(object sender, TreeNodeEventArgs e)<BR><BR>{<BR><BR><BR><BR>if (!IsPostBack == true)<BR><BR>{<BR><BR>switch (e.Node.Depth)<BR><BR>{<BR><BR>case 0:<BR><BR>PopulateCategories(e.Node);<BR><BR>break;<BR><BR>}<BR><BR>}<BR><BR>}<BR><BR>void PopulateCategories(TreeNode node)<BR><BR>{<BR><BR> 
// Query for the product categories. These are the values

// for the second-level nodes.
<BR>DataSet ResultSet = RunQuery("Select row_id, name From Categories ");<BR>
// Create the second-level nodes.
<BR>if (ResultSet.Tables.Count > 0)<BR><BR>{<BR><BR> <BR>

// Iterate through and create a new node for each row in the query results.

// Notice that the query results are stored in the table of the DataSet.
<BR><BR>foreach (DataRow row in ResultSet.Tables[0].Rows)<BR><BR>{

// Create the new node. Notice that the CategoryId is stored in the Value property

// of the node. This will make querying for items in a specific category easier when

// the third-level nodes are created.
<BR>TreeNode newNode = new TreeNode();<BR><BR>newNode.Text = row["name"].ToString();<BR><BR>newNode.Value = row["row_id"].ToString();<BR>
// Set the PopulateOnDemand property to true so that the child nodes can be

// dynamically populated.
<BR>newNode.PopulateOnDemand = true;
// Set additional properties for the node.
<BR>newNode.SelectAction = TreeNodeSelectAction.Select;<BR>

// Add the new node to the ChildNodes collection of the parent node.
<BR>node.ChildNodes.Add(newNode);<BR><BR> <BR><BR>}<BR><BR> <BR><BR>}<BR><BR> <BR><BR>}<BR><BR>void PopulateProducts(TreeNode node)<BR><BR>{<BR>
// Query for the products of the current category. These are the values

// for the third-level nodes.
<BR>DataSet ResultSet = RunQuery("Select row_id,name From Categories Where row_id=" + node.Value);<BR>

// Create the third-level nodes.
<BR>if (ResultSet.Tables.Count > 0)<BR><BR>{<BR><BR> <BR>
// Iterate through and create a new node for each row in the query results.

// Notice that the query results are stored in the table of the DataSet.
<BR>foreach (DataRow row in ResultSet.Tables[0].Rows)<BR><BR>{<BR><BR> <BR>
// Create the new node.
<BR>TreeNode NewNode = new TreeNode();<BR><BR>NewNode.Text = row["name"].ToString();<BR><BR>NewNode.Value = row["row_id"].ToString();<BR>

// Set the PopulateOnDemand property to false, because these are leaf nodes and do not need to be populated.
<BR><BR>NewNode.PopulateOnDemand = false;<BR><BR> <BR>

// Set additional properties for the node.
<BR><BR>NewNode.SelectAction = TreeNodeSelectAction.None;<BR>
// Add the new node to the ChildNodes collection of the parent node.
<BR>node.ChildNodes.Add(NewNode);<BR><BR> <BR><BR>}<BR><BR> <BR><BR>}<BR><BR>}<BR><BR>DataSet RunQuery(String QueryString)<BR><BR>{<BR>

// Declare the connection string.
<BR>String ConnectionString = "server=servername;database=database name;user id=user id;password=password";<BR><BR>SqlConnection DBConnection = new SqlConnection(ConnectionString);<BR><BR>SqlDataAdapter DBAdapter;<BR><BR>DataSet ResultsDataSet = new DataSet();<BR><BR>try<BR><BR>{<BR>

// Run the query and create a DataSet.
<BR>DBAdapter = new SqlDataAdapter(QueryString, DBConnection);<BR><BR>DBAdapter.Fill(ResultsDataSet);<BR>

// Close the database connection.
<BR>DBConnection.Close();<BR><BR>}<BR><BR>catch (Exception ex)<BR><BR>{<BR>

// Close the database connection if it is still open.
<BR>if (DBConnection.State == ConnectionState.Open)<BR><BR>{<BR><BR>DBConnection.Close();<BR><BR>}<BR><BR> <BR><BR>string str = "Unable to connect to the database.";<BR><BR>}<BR><BR>return ResultsDataSet;<BR><BR>}<BR><BR>protected void Page_Load(object sender, EventArgs e)<BR><BR>{<BR><BR>}<BR><BR>protected void LinksTreeView_SelectedNodeChanged(object sender, EventArgs e)<BR><BR>{<BR><BR>TextBox1.Text = LinksTreeView.SelectedNode.Value;<BR><BR>TextBox2.Text = LinksTreeView.SelectedNode.Text;<BR><BR>}<BR><BR>}<BR> 

Conclusion

As you can see, the tree view gets populated from the database and the child nodes are created from the database values. Try to keep your trees small in size, or slow performance will be encountered

Hope this helps..

 

 

 

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Venkatesh.P
Web Developer
India India
Hi..I am venkatesh, a begineer Developer.

Comments and Discussions

 
GeneralMy vote of 1 PinmemberJason Barry10-Jul-09 5:03 
GeneralThere's nothing here PinmvpDave Kreskowiak21-Mar-07 8:01 
Generalthis sample was copied as is from MSDN Pinmemberrtsistemas23-Jan-07 12:18 
GeneralRe: this sample was copied as is from MSDN PinmemberVenkatesh.P24-Jan-07 1:57 
GeneralThere may be something here... PinmemberDewey29-Nov-06 18:31 
GeneralIt may do... PinmemberJonathan [Darka]28-Nov-06 3:35 
GeneralRe: It may do... Pinmembernorm .net28-Nov-06 4:00 
GeneralRe: It may do... PinmemberJonathan [Darka]28-Nov-06 4:09 
GeneralRe: It may do... PinmemberVenkatesh.P28-Nov-06 4:12 
GeneralRe: It may do... Pinmembernorm .net28-Nov-06 4:24 
GeneralRe: It may do... PinmemberJonathan [Darka]28-Nov-06 4:24 
GeneralRe: It may do... PinmemberVenkatesh.P28-Nov-06 4:10 
GeneralRe: It may do... PinmemberJonathan [Darka]28-Nov-06 4:20 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.141022.2 | Last Updated 28 Nov 2006
Article Copyright 2006 by Venkatesh.P
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid