How to Bind Multiple SQL Server Tables With a TreeView in a Hierarchical Order

Deepak Sharma

C#SQL Server TreeView TreeView Controlsql server articlesasp.net articleSQL Server tables

8 Comments

Join the conversation! Your thoughts help the community grow.

suray siva12y ago
thanks for given code........HAPPY CODING.......
Goutam Singh13y ago
Nice Work but the Application is not having .cs File
Bill Strachan14y ago
In my opinion your approach is not good or scalable and it doesn't "bind" the tables to the treeview, what it does do is iterate over each row of each table and create a node using nested loops. Think of the number of nested queries you're executing against the database to achieve a 3 level hierarchy!! What if you wanted a 4th level or if one of the levels had many rows!! I would suggest a better approach would be to create a stored procedure in SQL Server (assuming that is the data source) and return the resultset as XML. Then you can simply specify the XML as the data source for the treeview. One trip to the database and the rendering would be much faster also. Simples!!
Deepak Sharma14y ago
Thanks a lot
Maria Johnson14y ago
Hi Dhananjay. Its a very useful article for those who wants to learn how to Bind Multiple SQL Server Tables With a TreeView in a Hierarchical Order
Abhi Kumar14y ago
Very useful article. Thanks for sharing.
Nitin Singh14y ago
That is a great effort and thanks for sharing.
Amit Maheshwari14y ago
It's a great effort to accomplish such task keep it up and thanks for sharing................

In this article we will bind three SQL Server tables with a TreeView in a hierarchical order.

Objective

In this article we will populate a TreeView with three tables - Country, State, and City in a hierarchical order. Country is at the top level which contains its states which further contains cities. The final output will be like in the following figure:

Step 1:

First of all create three tables, Country, State, and City with the following specification and some data like above.

Step 2:

Create a new ASP.NET Web Application. Drag a TreeView in 'Default.aspx' from the Toolbox.

Step 3:

Add a connection string in the 'Web.config' file:

<connectionStrings>
<add name="ConString" connectionString="Data source=yourservername; User Id=youruserid; Password=yourpassword; Initial Catalog=yourdatabasename;"/>
</connectionStrings>

Step 4:

Write the following code in the code behind file, 'Default.aspx.cs':

using System.Data.SqlClient;

SqlConnection con;
SqlCommand cmd;
SqlDataAdapter sda;
DataTable dt, dt2, dt3;
TreeNode CountryNode, StateNode, CityNode;
string ConString, CmdString, CountryCode, StateCode, CityCode, CountryName, StateName, CityName;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
ConString = WebConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
LoadData();
}
}
private void LoadData()
{
con = new SqlConnection(ConString);
CmdString = "SELECT CountryCode, CountryName FROM Country";
cmd = new SqlCommand(CmdString, con);
sda = new SqlDataAdapter(cmd);
dt = new DataTable();

// CountryName and CountryCode is retrieved in the DataTable 'dt'
sda.Fill(dt);

// Loops through all the Country rows of 'dt'
for (int i = 0; i < dt.Rows.Count; i++)
{
// Current CountryName and CountryCode is stored
CountryCode=dt.Rows[i]["CountryCode"].ToString();
CountryName = dt.Rows[i]["CountryName"].ToString();

// A TreeNode is created with current CountryName as text and CountryCode as value
CountryNode = new TreeNode(CountryName, CountryCode);

CmdString = "SELECT StateCode, StateName FROM State WHERE CountryCode=@CountryCode AND StateCode!=5";
cmd = new SqlCommand(CmdString, con);
cmd.Parameters.AddWithValue("@CountryCode", CountryCode);
sda = new SqlDataAdapter(cmd);
dt2 = new DataTable();

// StateName and StateCode of current CountryCode is retrieved in the DataTable 'dt2'
sda.Fill(dt2);

// Loops through all the State rows of 'dt2'
for (int j = 0; j < dt2.Rows.Count; j++)
{
// Current StateName and StateCode is stored
StateCode=dt2.Rows[j]["StateCode"].ToString();
StateName = dt2.Rows[j]["StateName"].ToString();

// A TreeNode is created with current StateName as text and StateCode as value
StateNode = new TreeNode(StateName, StateCode);

// Current StateNode is added as child node of CountryNode
CountryNode.ChildNodes.Add(StateNode);

CmdString = "SELECT CityCode, CityName FROM City WHERE StateCode=@StateCode";
cmd = new SqlCommand(CmdString, con);
cmd.Parameters.AddWithValue("@StateCode", StateCode);
sda = new SqlDataAdapter(cmd);
dt3 = new DataTable();

// CityName and CityCode of current StateCode is retrieved in the DataTable 'dt3'
sda.Fill(dt3);

// Loops through all the City rows of 'dt3'
for (int k = 0; k < dt3.Rows.Count; k++)
{
// Current CityName and CityCode is stored
CityCode = dt3.Rows[k]["CityCode"].ToString();
CityName = dt3.Rows[k]["CityName"].ToString();

// A TreeNode is created with current CityName as text and CityCode as value
CityNode = new TreeNode(CityName, CityCode);

// Current CityNode is added as child node of StateNode
StateNode.ChildNodes.Add(CityNode);
}
}
// Root node 'CountryNode' is added to the TreeView
TreeView1.Nodes.Add(CountryNode);
}
}

Here, first the SqlClient library is imported, and then the required variables are declared. Then in the Page_Load event a function LoadData is called. This LoadData function loads the tables in TreeView.

Similarly, we can add any other child node inside the City node in the TreeView.

Thanks!!!