Code Snippets

C# Source Code

Welcome to Dream.In.Code
	Become a C# Expert! Join 300,308 C# Programmers for FREE! Get instant access to thousands of C# experts, tutorials, code snippets, and more! There are 2,090 people online right now. Registration is fast and FREE... Join Now! Chat LIVE With a C# Expert

Populating Tree View nodes from sql server 2000

Submitted By: venkatzeus

Actions:

Rating:

Views: 30,372

Language: C#

Last Modified: March 8, 2007

Instructions: 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.

Snippet

using System;using System.Data;using System.Configuration;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using System.Data.SqlClient; public partial class _Default : System.Web.UI.Page{ protected void Page_Load(object sender, TreeNodeEventArgs e){if (!IsPostBack == true){switch (e.Node.Depth){case 0:PopulateCategories(e.Node);break;}}}void PopulateCategories(TreeNode node){
// Query for the product categories. These are the values
// for the second-level nodes.
DataSet ResultSet = RunQuery("Select row_id, name From Categories ");
// Create the second-level nodes.
if (ResultSet.Tables.Count > 0){
// 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.
foreach (DataRow row in ResultSet.Tables[0].Rows){
// 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.
TreeNode newNode = new TreeNode();newNode.Text = row["name"].ToString();newNode.Value = row["row_id"].ToString();
// Set the PopulateOnDemand property to true so that the child nodes can be
// dynamically populated.
newNode.PopulateOnDemand = true;
// Set additional properties for the node.
newNode.SelectAction = TreeNodeSelectAction.Select;
// Add the new node to the ChildNodes collection of the parent node.
node.ChildNodes.Add(newNode); } } }void PopulateProducts(TreeNode node){
// Query for the products of the current category. These are the values
// for the third-level nodes.
DataSet ResultSet = RunQuery("Select row_id,name From Categories Where row_id=" + node.Value);
// Create the third-level nodes.
if (ResultSet.Tables.Count > 0){
// 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.
foreach (DataRow row in ResultSet.Tables[0].Rows){
// Create the new node.
TreeNode NewNode = new TreeNode();NewNode.Text = row["name"].ToString();NewNode.Value = row["row_id"].ToString();
// Set the PopulateOnDemand property to false, because these are leaf nodes and do not need to be populated.
NewNode.PopulateOnDemand = false;
// Set additional properties for the node.
NewNode.SelectAction = TreeNodeSelectAction.None;
// Add the new node to the ChildNodes collection of the parent node.
node.ChildNodes.Add(NewNode); } }}DataSet RunQuery(String QueryString){
// Declare the connection string.
String ConnectionString = "server=servername;database=database name;user id=user id;password=password";SqlConnection DBConnection = new SqlConnection(ConnectionString);SqlDataAdapter DBAdapter;DataSet ResultsDataSet = new DataSet();try{
// Run the query and create a DataSet.
DBAdapter = new SqlDataAdapter(QueryString, DBConnection);DBAdapter.Fill(ResultsDataSet);
// Close the database connection.
DBConnection.Close();}catch (Exception ex){
// Close the database connection if it is still open.
if (DBConnection.State == ConnectionState.Open){DBConnection.Close();} string str = "Unable to connect to the database.";}return ResultsDataSet;}protected void Page_Load(object sender, EventArgs e){}protected void LinksTreeView_SelectedNodeChanged(object sender, EventArgs e){TextBox1.Text = LinksTreeView.SelectedNode.Value;TextBox2.Text = LinksTreeView.SelectedNode.Text;}}

Copy & Paste

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

// for the second-level nodes.

DataSet ResultSet = RunQuery("Select row_id, name From Categories ");
// Create the second-level nodes.

if (ResultSet.Tables.Count > 0){

// 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.
foreach (DataRow row in ResultSet.Tables[0].Rows){

// 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.

TreeNode newNode = new TreeNode();newNode.Text = row["name"].ToString();newNode.Value = row["row_id"].ToString();
// Set the PopulateOnDemand property to true so that the child nodes can be

// dynamically populated.

newNode.PopulateOnDemand = true;
// Set additional properties for the node.

newNode.SelectAction = TreeNodeSelectAction.Select;

// Add the new node to the ChildNodes collection of the parent node.

node.ChildNodes.Add(newNode); } } }void PopulateProducts(TreeNode node){
// Query for the products of the current category. These are the values

// for the third-level nodes.

DataSet ResultSet = RunQuery("Select row_id,name From Categories Where row_id=" + node.Value);

// Create the third-level nodes.

if (ResultSet.Tables.Count > 0){ 
// 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.

foreach (DataRow row in ResultSet.Tables[0].Rows){ 
// Create the new node.

TreeNode NewNode = new TreeNode();NewNode.Text = row["name"].ToString();NewNode.Value = row["row_id"].ToString();

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

// Set additional properties for the node.
NewNode.SelectAction = TreeNodeSelectAction.None;
// Add the new node to the ChildNodes collection of the parent node.

node.ChildNodes.Add(NewNode); } }}DataSet RunQuery(String QueryString){

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

// Run the query and create a DataSet.

DBAdapter = new SqlDataAdapter(QueryString, DBConnection);DBAdapter.Fill(ResultsDataSet);

// Close the database connection.

DBConnection.Close();}catch (Exception ex){

// Close the database connection if it is still open.

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

Comments
pulak 2008-09-17 05:55:44
I am using two different table to add node and childnode,and i add root node with text=panchayat and value=panchayat but the node and the childnodes are not displaying.mail me pulakchetia2007@gmail.com