In this article I will explain with an example, how to create Organizational Chart from Database using Google Organizational Chart API in ASP.Net MVC 5 Razor.
The database records consisting of Organization Hierarchical information will be used to populate Google Organizational Chart with multiple markers in ASP.Net MVC 5 Razor.
Database
I have made use of the following table EmployeesHierarchy with the schema as follows.
![Create Organizational Chart in ASP.Net MVC using Google Organizational Chart API]()
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 in ASP.Net MVC using Google Organizational Chart API]()
Note: You can download the database table SQL by clicking the download link below.
Namespaces
You will need to import the following namespaces.
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
Controller
The Controller consists of two Action methods.
Action method for handling GET operation
Inside this Action method, simply the View is returned.
Action method for handling jQuery AJAX operation
Inside this Action method, the records are fetched from the EmployeesHierarchy Table using ADO.Net.
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.
Finally the Generic List of objects is returned to the View as JSON.
public class HomeController : Controller
{
// GET: Home
public ActionResult Index()
{
return View();
}
public JsonResult AjaxMethod()
{
List<object> chartData = new List<object>();
string query = "SELECT EmployeeId, Name, Designation, ReportingManager";
query += " FROM EmployeesHierarchy";
string constr = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(query))
{
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 Json(chartData);
}
}
View
Note: In order to execute the Google Organizational Chart application you will need to register and get an API key from Google API. For more details refer here.
The concept is simple i.e. to get the Organizational chart data from the database with the use of AJAX Action method (described earlier) and jQuery AJAX and then use the data for Google Organizational 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 AJAX Action method 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 Google Organizational 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.
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width"/>
<title>Index</title>
</head>
<body>
<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?key=API_KEY"></script>
<script type="text/javascript">
google.load("visualization", "1", { packages: ["orgchart"] });
google.setOnLoadCallback(drawChart);
function drawChart() {
$.ajax({
type: "POST",
url: "/Home/AjaxMethod",
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.length; i++) {
var employeeId = r[i][0].toString();
var employeeName = r[i][1];
var designation = r[i][2];
var reportingManager = r[i][3] != null ? r[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);
},
error: function (r) {
alert(r);
}
});
}
</script>
<div id="chart">
</div>
</body>
</html>
Screenshot
![Create Organizational Chart in ASP.Net MVC using Google Organizational Chart API]()
Downloads
