Reader Level:
Articles

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

By Deepak Sharma on February 08, 2012
In this article we will bind three SQL Server tables with a TreeView in a hierarchical order
  • 1
  • 0
  • 19269
Download Files:
 

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:

TrView1.gif

Step 1:

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

TrView2.gif

TrView3.gif

TrView4.gif

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

Deepak Sharma

Deepak is a Software Developer with over 4 years of experience. His expertise is in Windows Forms, ASP.NET, WPF, SQL Server and MySql.Twitter : @idpkshrBlog : http://deepak-sharma.net

Personal Blog: http://deepak-sharma.net
COMMENT USING

Trending up