In this article I will explain with example, how to create Organizational Chart from Database using Google Organizational Chart in ASP.Net using C# and VB.Net.
The Organizational Chart will also display Pictures (Images) in each Node of the Google Organizational Chart.
Database
I have made use of the following table EmployeesHierarchy with the schema as follows.
![Create Organizational Chart from Database using Google Organizational Chart in ASP.Net]()
I have already inserted few records in the table. The Manager of each Employee can be determined
using the ReportingManager column which stores the EmployeeId of the Manager of an Employee.
![Create Organizational Chart from Database using Google Organizational Chart in ASP.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 Google Organizational Chart from database.
The SQL Query gets records of all Employees 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 EmployeeId, Name, Designation, ReportingManager";
query += " FROM EmployeesHierarchy";
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["EmployeeId"], sdr["Name"], sdr["Designation"] , sdr["ReportingManager"]
});
}
}
con.Close();
return chartData;
}
}
}
VB.Net
<WebMethod()> _
Public Shared Function GetChartData() As List(Of Object)
Dim query As String = "SELECT EmployeeId, Name, Designation, ReportingManager"
query &= " FROM EmployeesHierarchy"
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("EmployeeId"), sdr("Name"), sdr("Designation"), sdr("ReportingManager")})
End While
End Using
con.Close()
Return chartData
End Using
End Using
End Function
Populate Google Organizational Chart from database in ASP.Net using jQuery AJAX
The concept is simple i.e. to get the Organizational 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 EmployeeId.
b) f – It stores the formatting details of the Entity or Node. In this example, I have displayed EmployeeName on top, followed by Designation and finally the picture of the Employee.
2. ParentEntity – This column stores the unique identifier of the ParentEntity i.e. ReportingManagerId. 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, by 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 employeeId = r.d[i][0].toString();
var employeeName = r.d[i][1];
var designation = r.d[i][2];
var reportingManager = r.d[i][3] != null ? r.d[i][3].toString() : '';
data.addRows([[{ v: employeeId,
f: employeeName + '<div>(<span>' + designation + '</span>)</div><img src = "Pictures/' + employeeId + '.jpg" />'
}, reportingManager, designation]]);
}
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 Organizational Chart from Database using Google Organizational Chart in ASP.Net]()
Demo
Downloads
