In this article I will explain with example, how to create Family Tree Hierarchy Chart from Database using Google Organizational Chart in ASP.Net using C# and VB.Net.
The Family Tree Hierarchy Chart will also display Pictures (Images) in each Node of Family Tree Hierarchy Chart.
Database
I have made use of the following table FamilyHierarchy with the schema as follows.
![Create Family Tree Hierarchy Chart from Database in ASP.Net using C# and VB.Net]()
I have already inserted few records in the table. The Parent of each Family Member can be determined using the ParentId column which stores the ParentId of the parent of a Member.
![Create Family Tree Hierarchy Chart from Database in ASP.Net using C# and VB.Net]()
Note: You can download the database table SQL by clicking the download link below.
Namespaces
You will need to import the following namespaces.
C#
using System.Data;
using System.Web.Services;
using System.Configuration;
using System.Data.SqlClient;
VB.Net
Imports System.Data
Imports System.Web.Services
Imports System.Configuration
Imports System.Data.SqlClient
WebMethod
The following WebMethod will be used for populating the Family Tree Hierarchy Chart from database.
The SQL Query gets records of all members and the results are populated into a two dimensional object array using DataReader, which is finally sent to client as a Generic List of objects.
C#
[WebMethod]
public static List<object> GetChartData()
{
string query = "SELECT MemberId, Name, ParentId";
query += " FROM FamilyHierarchy";
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(query))
{
List<object> chartData = new List<object>();
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
con.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
chartData.Add(new object[]
{
sdr["MemberId"], sdr["Name"], sdr["ParentId"]
});
}
}
con.Close();
return chartData;
}
}
}
VB.Net
<WebMethod()> _
Public Shared Function GetChartData() As List(Of Object)
Dim query As String = "SELECT MemberId, Name, ParentId"
query &= " FROM FamilyHierarchy"
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As New SqlConnection(constr)
Using cmd As New SqlCommand(query)
Dim chartData As New List(Of Object)()
cmd.CommandType = CommandType.Text
cmd.Connection = con
con.Open()
Using sdr As SqlDataReader = cmd.ExecuteReader()
While sdr.Read()
chartData.Add(New Object() {sdr("MemberId"), sdr("Name"), sdr("ParentId")})
End While
End Using
con.Close()
Return chartData
End Using
End Using
End Function
Populate Family Tree Hierarchy Chart from database in ASP.Net using jQuery AJAX
The concept is simple i.e. to get the Family Tree Hierarchy Chart data from the database with the use of WebMethod (described earlier) and jQuery AJAX and then use the data for chart population.
The very first thing is to load the Google Organizational Chart API packages and when once all the packages are loaded then the drawChart method is invoked.
Inside this method a jQuery AJAX call to the WebMethod is initiated which gets the records from the database as an array of objects.
Once the AJAX response is received, an object of Google Visualization DataTable is created. The Google Visualization DataTable must contain three columns in order to populate an Organizational chart.
Note: You can set any name of your choice to the columns. I have given name as per the significance.
1. Entity – This column stores the object of the Entity or Node in the Organization Chart. This object consists of two properties:-
a) v – It stores the unique identifier of the Entity or Node. In this example, it is MemberId.
b) f – It stores the formatting details of the Entity or Node. In this example, I have displayed MemberName on top followed by the picture of the family member.
2. ParentEntity – This column stores the unique identifier of the ParentEntity i.e. ParentId. This is very important to find the parent of a particular Node. If left blank then the Node will be considered as Root Node.
3. ToolTip – This column is used to bind ToolTip or Title attribute to the Node so that when the mouse is hovered, the default browser ToolTip is displayed. If you don’t want to display ToolTip leave it blank.
Finally a loop is executed and one by one, the records are inserted into the Google Visualization DataTable which is then used for drawing the chart on to the specified HTML DIV element.
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">
google.load("visualization", "1", { packages: ["orgchart"] });
google.setOnLoadCallback(drawChart);
function drawChart() {
$.ajax({
type: "POST",
url: "Default.aspx/GetChartData",
data: '{}',
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function (r) {
var data = new google.visualization.DataTable();
data.addColumn('string', 'Entity');
data.addColumn('string', 'ParentEntity');
data.addColumn('string', 'ToolTip');
for (var i = 0; i < r.d.length; i++) {
var memberId = r.d[i][0].toString();
var memberName = r.d[i][1];
var parentId = r.d[i][2] != null ? r.d[i][2].toString() : '';
data.addRows([[{ v: memberId,
f: memberName + '<div><img src = "Pictures/' + memberId + '.jpg" /></div>'
}, parentId, memberName]]);
}
var chart = new google.visualization.OrgChart($("#chart")[0]);
chart.draw(data, { allowHtml: true });
},
failure: function (r) {
alert(r.d);
},
error: function (r) {
alert(r.d);
}
});
}
</script>
<div id="chart">
</div>
Screenshot
![Create Family Tree Hierarchy Chart from Database in ASP.Net using C# and VB.Net]()
Demo
Downloads
