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

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

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

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;
          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 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 *