Bind TreeView Control with SQL Server Database to Display Hierarchical Data in Asp.Net C# VB.Net

In most of the cases developers need to display Hierarchical data or group breakdown data using TreeView control in Asp.Net. Most of the ways you can follow to display such type of group data or relational data or hierarchical data such as gridview, repeater control but if something related to navigational issues then I think TreeView control is the perfect control to display Hierarchical or group or relational data. Here in this article I am showing “How to Bind TreeView Control With DataBase“. To do this example please create the below 3 tables with sample data in your database first to run my example code:

SQL Server Database Tables:

CREATE TABLE [dbo].[Department](
	[ID] [int] NOT NULL,
	[Name] [varchar](max) NULL,
 CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Subject](
	[ID] [int] NOT NULL,
	[Name] [varchar](max) NULL,
	[DeptID] [int] NULL,
 CONSTRAINT [PK_Subject] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Teacher](
	[ID] [int] NOT NULL,
	[Name] [varchar](max) NULL,
	[SubID] [int] NULL,
 CONSTRAINT [PK_Teacher] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Now Insert Some Data to Test the Example:

INSERT INTO Department Values(1,'Computer Science')
INSERT INTO Department Values(2,'Electrical & Electronics')
INSERT INTO Department Values(3,'Civil Engg.')
INSERT INTO Department Values(4,'Mechanical Engg.')

INSERT INTO Subject Values(1,'Turbo C',1)
INSERT INTO Subject Values(2,'Algorithm',1)
INSERT INTO Subject Values(3,'Concrete Matematics',1)
INSERT INTO Subject Values(4,'Electrical Device',2)
INSERT INTO Subject Values(5,'Math part 1',2)
INSERT INTO Subject Values(6,'Construction',3)
INSERT INTO Subject Values(7,'Design Concept',3)
INSERT INTO Subject Values(8,'Maya',3)
INSERT INTO Subject Values(9,'Robotics',4)
INSERT INTO Subject Values(10,'Motor Engg.',4)

INSERT INTO Teacher Values(1,'Shawpnendu Bikash',1)
INSERT INTO Teacher Values(2,'Purnendu Bikash',2)
INSERT INTO Teacher Values(3,'Salauddin Ahmed',3)
INSERT INTO Teacher Values(4,'MS Tutul',4)
INSERT INTO Teacher Values(5,'AKM Akram',5)
INSERT INTO Teacher Values(6,'M Rahamatullah',6)
INSERT INTO Teacher Values(7,'Gazi Rabbani',7)
INSERT INTO Teacher Values(8,'M Zilani',8)
INSERT INTO Teacher Values(9,'Noor Ahmed',9)
INSERT INTO Teacher Values(10,'Belal Hossain',10)

Now Modify Web.Config File to Connect to Database:

<configuration>
  <connectionStrings>
    <add name="DBConnection" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=TESTDB;Trusted_Connection=yes;" providerName="System.Data.SqlClient"/>
    <!--<add name="BONConnection" connectionString="Data Source=XXX.com;Initial Catalog=DBNAME;User Id=UserName;Password=YourPassword;" providerName="System.Data.SqlClient" />-->
  </connectionStrings>
...................
...................

Note: If you use windows authentication then use the first key. Otherwise use second key line. Update server name, Database name, User name, Password as per your settings.

Now add a TreeView Control:

<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
    <asp:Label runat="server" ID="lbl" Font-Bold="true">TreeView to display hierarchical data</asp:Label>

    <hr />

    <asp:TreeView ID="TreeView1" runat="server" ShowLines="true">
    </asp:TreeView>
</asp:Content>

Now Bind the TreeView Control With Database under Page_Load Event:
C# Code:

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            string ConnString = WebConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
            using (SqlConnection Conn = new SqlConnection(ConnString))
            {
                string SSQLDepartment = "Select * from Department";
                string SSQLSubject = "Select * from Subject";
                string SSQLTeacher = "Select * from Teacher";
                string SFullSQL = SSQLDepartment + ";" + SSQLSubject + ";" + SSQLTeacher;

                DataSet dsFullData = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(SFullSQL, Conn);
                da.Fill(dsFullData);
                dsFullData.Tables[0].TableName = "Department";
                dsFullData.Tables[1].TableName = "Subject";
                dsFullData.Tables[2].TableName = "Teacher";

                DataRelation Department_Subject = new DataRelation("DeptSub", dsFullData.Tables["Department"].Columns["ID"], dsFullData.Tables["Subject"].Columns["DeptID"]);
                dsFullData.Relations.Add(Department_Subject);

                DataRelation Subject_Teacher = new DataRelation("SubTech", dsFullData.Tables["Subject"].Columns["ID"], dsFullData.Tables["Teacher"].Columns["SubID"]);
                dsFullData.Relations.Add(Subject_Teacher);

                foreach (DataRow oDepartment in dsFullData.Tables["Department"].Rows)
                {
                    TreeNode NodeDepartment = new TreeNode();
                    NodeDepartment.Text = oDepartment["Name"].ToString();
                    NodeDepartment.Value = oDepartment["ID"].ToString();
                    TreeView1.Nodes.Add(NodeDepartment);

                    foreach (DataRow oSubject in oDepartment.GetChildRows("DeptSub"))
                    {
                        TreeNode NodeSubject = new TreeNode();
                        NodeSubject.Text = oSubject["Name"].ToString();
                        NodeSubject.Value = oSubject["ID"].ToString();
                        NodeDepartment.ChildNodes.Add(NodeSubject);

                        foreach (DataRow oTeacher in oSubject.GetChildRows("SubTech"))
                        {
                            TreeNode NodeTeacher = new TreeNode();
                            NodeTeacher.Text = oTeacher["Name"].ToString();
                            NodeTeacher.Value = oTeacher["ID"].ToString();
                            NodeSubject.ChildNodes.Add(NodeTeacher);
                        }
                    }
                }
            }
        }
    }

Note: Don’t forget to add “System.Data”, “System.Data.SqlClient”, “System.Web.Configuration” namespaces.

VB.Net Code:

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        If Not Page.IsPostBack Then
            Dim ConnString As String = WebConfigurationManager.ConnectionStrings("DBConnection").ConnectionString
            Using Conn As New SqlConnection(ConnString)
                Dim SSQLDepartment As String = "Select * from Department"
                Dim SSQLSubject As String = "Select * from Subject"
                Dim SSQLTeacher As String = "Select * from Teacher"
                Dim SFullSQL As String = SSQLDepartment & ";" & SSQLSubject & ";" & SSQLTeacher

                Dim dsFullData As New DataSet()
                Dim da As New SqlDataAdapter(SFullSQL, Conn)
                da.Fill(dsFullData)
                dsFullData.Tables(0).TableName = "Department"
                dsFullData.Tables(1).TableName = "Subject"
                dsFullData.Tables(2).TableName = "Teacher"

                Dim Department_Subject As New DataRelation("DeptSub", dsFullData.Tables("Department").Columns("ID"), dsFullData.Tables("Subject").Columns("DeptID"))
                dsFullData.Relations.Add(Department_Subject)

                Dim Subject_Teacher As New DataRelation("SubTech", dsFullData.Tables("Subject").Columns("ID"), dsFullData.Tables("Teacher").Columns("SubID"))
                dsFullData.Relations.Add(Subject_Teacher)

                For Each oDepartment As DataRow In dsFullData.Tables("Department").Rows
                    Dim NodeDepartment As New TreeNode()
                    NodeDepartment.Text = oDepartment("Name").ToString()
                    NodeDepartment.Value = oDepartment("ID").ToString()
                    TreeView1.Nodes.Add(NodeDepartment)

                    For Each oSubject As DataRow In oDepartment.GetChildRows("DeptSub")
                        Dim NodeSubject As New TreeNode()
                        NodeSubject.Text = oSubject("Name").ToString()
                        NodeSubject.Value = oSubject("ID").ToString()
                        NodeDepartment.ChildNodes.Add(NodeSubject)

                        For Each oTeacher As DataRow In oSubject.GetChildRows("SubTech")
                            Dim NodeTeacher As New TreeNode()
                            NodeTeacher.Text = oTeacher("Name").ToString()
                            NodeTeacher.Value = oTeacher("ID").ToString()
                            NodeSubject.ChildNodes.Add(NodeTeacher)
                        Next
                    Next
                Next
            End Using
        End If
    End Sub

Note: Don’t forget to add “System.Data”, “System.Data.SqlClient”, “System.Web.Configuration” namespaces.

Output:
TreeView

Code Explanation: First fill all data into a DataSet in a tabular way. Then create relationship among 3 tables based on Primary key & foreign key. After that loop through from high level to the lower level hierarchy. That’s it. Primary task has been completed. Now keep experimenting to make a professional solution.

Download Code Example C#        Download Code Example VB.Net

Posted in .Net, Asp.net, C#, VB.Net
2 comments on “Bind TreeView Control with SQL Server Database to Display Hierarchical Data in Asp.Net C# VB.Net
  1. Dev says:

    very good example. Thanks

  2. Dev says:

    How can add onClick event in Child node?

Leave a Reply

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

AlphaOmega Captcha Classica  –  Enter Security Code
     
 

*