Organizational Chart In ASP.NET MVC Using Google API

Introduction

Recently, I came across a requirement to display all the employees with basic info(name, designation) in hierarchical ways. I found Google Organizational chart is suitable to accomplish the requirement. Here we will discuss how to implement Organizational chart in ASP.NET MVC, using Google JS API.

Using Code

To implement an organizational chart requirement, we divide it into 3 modules:

Database design.
Design controller/model to retrieve data from DB.
Design view to display chart using Google chart.

Database Design

Let's design a table, which will keep information about an employee. SQL query, given below, is used to create table(tblEmployee) with the required columns ID like FirstName, LastName, Designation, Email and ReportID etc.

CREATE TABLE [dbo].[tblEmployee](
[Id] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
[Designation] [varchar](50) NULL,
[Email] [varchar](50) NULL,
[Address] [varchar](max) NULL,
[ReportID] [int] NULL,
[IsActive] [bit] NULL,
CONSTRAINT [PK_tblEmployee] 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]

Next action is to insert the employee records with the required information like ID, FirstName, ReportID, Designation etc. The snapshot, given below, shows the employee records:

Figure 1: Employee Records

Design Controller and Model

Once we complete the database part, it's time to focus on the Application side(model and controller design).

Model

Add model(Employee.cs), given below, in Model folder with properties ID, first name, last, designation, Email, report ID etc.

public class Employee
{
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string Designation { get; set; }
public string Email { get; set; }
public int ReportID { get; set; }
public bool IsActive { get; set; }
}

Controller

In a controller, add an action, which retrieves the Employee records and returns the data in JSON format. Here, I have used simple ADO.NET to get the data. You can use EntityFramework, if you want.

[HttpPost]
public JsonResult GetEmpChartData()
{
List<Employee> empChartList = new List<Employee>();
string query = "SELECT Id, FirstName, Designation, Email, ReportID";
query += " FROM tblEmployee";
// Get it from Web.config file
string connetionString = "Data Source=MyPC\\SQLEXPRESS;Initial Catalog=AllTest;Integrated Security=True;";
using (SqlConnection con = new SqlConnection(connetionString))
{
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
con.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
// Adding new Employee object to List
empChartList.Add(new Employee()
{
Id = dr.GetInt32(0),
FirstName = dr.GetString(1),
Designation = dr.GetString(2),
Email = dr.GetString(3),
ReportID = dr.IsDBNull(4) ? 0 : dr.GetInt32(4)
});
}
}
con.Close();
}
}
return Json(empChartList, JsonRequestBehavior.AllowGet);
}

Regarding View, we will discuss in the next module, because it needs JavaScript, Google chart, HTML code etc. and thus, it is better to see in the next module.

Design View to display chart

In View, there is a DIV, where the data will render as a chart in the hierarchical way.

Add DIV, given below:

<div id="empChart">
</div>

The code snippet, given below, is to send an AJAX request, which retrieves all the employee records. It loops over the records and binds to the chart object.

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.2/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"] });
$('#btnLoadChart').click(function () {
drawEmpChart();
});
function drawEmpChart() {
$.ajax({
type: "POST",
url: "Home/GetEmpChartData",
data: '{}',
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function (empData) {
var chartData = new google.visualization.DataTable();
chartData.addColumn('string', 'Name');
chartData.addColumn('string', 'Manager');
chartData.addColumn('string', 'ToolTip');
$.each(empData, function (index, row) {
var reportID = row.ReportID.toString() == "0" ? '' : row.ReportID.toString();
chartData.addRows([[{
v: row.Id.toString(),
f: row.FirstName + '<div>(<span>' + row.Designation + '</span>)</div><img height="50px" width="50px" src = "Photos/' + row.Id.toString() + '.jpg" />'
}, reportID, row.Designation]]);
});
var chart = new google.visualization.OrgChart($("#empChart")[0]);
chart.draw(chartData, { allowHtml: true });
},
failure: function (xhr, status, error) {
alert("Failure: " + xhr.responseText);
},
error: function (xhr, status, error) {
alert("Error: " + xhr.responseText);
}
});
}
</script>

Preceding code description

1. Add two JavaScript CDN references, one for jQuery and another one is for Google JS API.

2. First step is to load Google Organizational Chart API packages.

3. Send jQuery AJAX call to get the records from the database as list of Employee. Once the AJAX response is received, an object of Google Visualization DataTable is created. 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 the name as of understanding.

Name – This column stores the object of the Entity or Node in the organization chart. This object consists of two properties:

v – It stores the unique identifier of the Entity or Node. In this example, it is ID.
f – It stores the formatting details of the Entity or Node. In this example,I have displayed Employee Name on top, followed by Designation and finally the picture of the Employee.

Manager – This column denotes ID of parent node to display records in hierarchical way, i.e. ReportID(Reporting Manager ID). It is very important to find the parent of a particular Node. If left blank, the Node will be considered as a Root Node(default).

ToolTip – This column stores to show ToolTip or Title attribute to the Node. 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 draw the chart on to the specified HTML DIV element.

Figure 2 shows Organizational chart in hierarchical ways.

Figure 2: Organizational Chart

Conclusion

In this article, we discussed organization chart, using Google JS in ASP.NET MVC. You can use this chart in many scenarios, like program flow chart, employee hierarchy etc.

13 Comments

Join the conversation! Your thoughts help the community grow.

ugur ari3y ago
F: employeeName + '<div>(<span>' + designation + '</span>)</div><img src = "~/uploads/Andrew Fuller.png" />' is not run of img
Imran Ali5y ago
Can you share example related to glass cutting optimization with graph????
Imran Ali5y ago
Good work.... blessing for new developers
shanmuga rajan5y ago
Thanks for post and still it's helpful. I used this in core 3.1 MVC Razor page. But chart not getting generate when i used the same code. I have found two error in script. (1) toString() - uncaught error. So i used String(row.reportID) (2) row.ReportID - Undefined error . So, i used row.reportID ( Initial letter small case in reportID ) then it's working fine
Hasin Mahjabeen8y ago
Any way to create horizontal layout?
Ani Unni9y ago
I m using this code for my mvc application, my data is big, how can i add collapse and expant option in this model
Humayun Kabir Mamun9y ago
Thanks for this nice article...
Ramesh Palaniappan10y ago
Good one
Anu V10y ago
Nice...
Debendra Dash10y ago
Excellent manas...........
Vignesh Mani10y ago
Good one
Bhavik Patel10y ago
Nice
Syed Shanu10y ago
Simple and Good