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


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

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

Step 4:

Write following code in 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;
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'
    // Loops through all the Country rows of 'dt'
    for (int i = 0; i < dt.Rows.Count; i++)
        // Current CountryName and CountryCode is stored
        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'

        // Loops through all the State rows of 'dt2'
        for (int j = 0; j < dt2.Rows.Count; j++)
            // Current StateName and StateCode is stored
            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

            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'

            // 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
        // Root node 'CountryNode' is added to the TreeView

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.


Leave a Reply

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