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:

1. Database design.
2. Design controller/model to retrieve data from DB.
3. 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.