How to bind multiple SQL Server tables with a TreeView in a hierarchical order

By | February 8, 2012

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. Final output will be like following figure:

Step 1:

First of all create three tables, Country, State, and City with 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 connection string in the ‘Web.config’ file

[sourcecode language=”xml”]
<connectionStrings>
<add name="ConString" connectionString="Data source=yourservername; User Id=youruserid; Password=yourpassword; Initial Catalog=yourdatabasename;"/>
</connectionStrings>
[/sourcecode]

Step 4:

Write following code in code behind file, ‘Default.aspx.cs’:

[sourcecode language=”csharp”]
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);
}
}
[/sourcecode]

Here, first SqlClient library is imported, and then required variables are declared. Then in 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 City node in the TreeView.

Thanks!!!

Leave a Reply

Your email address will not be published. Required fields are marked *