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

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

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!!!