how to create Organizational Chart from Database

Here Mudassar Ahmed Khan has explained 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.

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.

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.

Note: You can download the database table SQL by clicking the download link below.

         Download SQL file

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