Creating Organization Hierarchy in ASP.NET using Google Chart

Google Charts is a powerful tool that can be used to create graphs and charts in ASP.NET applications.

It can be used to generate graphs and charts for various types of data, such as: financial data, weather data, geographical data, etc.

The ASP.NET Google Chart API allows you to create organization hierarchies in your ASP.NET application using JavaScript, which is the language that Google Charts uses for its API.

This article will teach you how to create organization hierarchies using the API by generating a chart with nested levels of hierarchy from scratch using JavaScript.

In this post, I will show you how to integrate the google org chart in your application.

  • First, create a new empty website in the visual studio
  • Add jquery and google visualization API reference to the default page

Let’s first create dummy data. Create the following table schema and insert some dummy data into this.

Table Schema

  [EmpID] CHAR (4)     NOT NULL,
  [Ename] VARCHAR (10) NULL,
  [MGR]   CHAR (4)     NULL,

Insert Data

INSERT INTO [dbo].[EMPLOYEES] ([EmpID], [Ename], [MGR]) VALUES (N'7369', N'Smith', N'7902')
INSERT INTO [dbo].[EMPLOYEES] ([EmpID], [Ename], [MGR]) VALUES (N'7499', N'Allen', N'7698')
INSERT INTO [dbo].[EMPLOYEES] ([EmpID], [Ename], [MGR]) VALUES (N'7521', N'Ward', N'7698')
INSERT INTO [dbo].[EMPLOYEES] ([EmpID], [Ename], [MGR]) VALUES (N'7566', N'Jones', N'7839')
INSERT INTO [dbo].[EMPLOYEES] ([EmpID], [Ename], [MGR]) VALUES (N'7654', N'Martin', N'7698')
INSERT INTO [dbo].[EMPLOYEES] ([EmpID], [Ename], [MGR]) VALUES (N'7698', N'Blake', N'7839')
INSERT INTO [dbo].[EMPLOYEES] ([EmpID], [Ename], [MGR]) VALUES (N'7782', N'Clark', N'7839')
INSERT INTO [dbo].[EMPLOYEES] ([EmpID], [Ename], [MGR]) VALUES (N'7788', N'Scott', N'7566')
INSERT INTO [dbo].[EMPLOYEES] ([EmpID], [Ename], [MGR]) VALUES (N'7839', N'King', NULL)
INSERT INTO [dbo].[EMPLOYEES] ([EmpID], [Ename], [MGR]) VALUES (N'7844', N'Turner', N'7698')
INSERT INTO [dbo].[EMPLOYEES] ([EmpID], [Ename], [MGR]) VALUES (N'7876', N'Adams', N'7788')
INSERT INTO [dbo].[EMPLOYEES] ([EmpID], [Ename], [MGR]) VALUES (N'7900', N'James', N'7698')
INSERT INTO [dbo].[EMPLOYEES] ([EmpID], [Ename], [MGR]) VALUES (N'7902', N'Ford', N'7566')
INSERT INTO [dbo].[EMPLOYEES] ([EmpID], [Ename], [MGR]) VALUES (N'7934', N'Miller', N'7782')

Create the following stored procedure that returns employee and manager relationship result into JSON string.


CREATE PROC GetEmployees
AS ;
    WITH    EmployeeManager
              AS ( SELECT   e1.empid empno ,
                            e1.ename ename ,
                            e2.eName mgr_ename
                   FROM     employees e1
                            LEFT JOIN employees e2 ON e1.mgr = e2.empid
        SELECT  '['
                + STUFF((SELECT ',{"id":' + CAST(empno AS VARCHAR(MAX))
                                + ',"name":"' + ename + '"' + ',"Manger":"'
                                + CAST(mgr_ename AS VARCHAR(MAX)) + '"' + '}'
                         FROM   EmployeeManager t1
                FOR     XML PATH('') ,
    ).value('.', 'varchar(max)'), 1, 1, '') + ']

Once the database is set up create a default.aspx page and add following code into it.

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html>
<html xmlns="">
<head runat="server">
    <script src=""></script>
    <script src="//"></script>
        google.load('visualization', '1', { packages: ['table', 'orgchart'] });
        function draw() {
                type: "POST",
                url: "Service.svc/",
                data: "{}",
                dataType: "json",
                contentType: "application/json",
                success: function (x, textStatus) {
                    var data = JSON.parse(x);

                    if ((emp_count = data.length) > 0) {
                        var l_data_table = new google.visualization.DataTable();
                        l_data_table.addColumn('string', 'Name');
                        l_data_table.addColumn('string', 'Manager');

                        for (i = 0; i < emp_count; i++) {
                            l_data_table.setCell(i, 0, data[i].name);
                            l_data_table.setCell(i, 1, data[i].Manger);

                        var chart = new google.visualization.OrgChart(document.getElementById('org_div'));
                        chart.draw(l_data_table, { allowHtml: true });


        $(document).ready(function () {

    <form id="form1" runat="server">
        <div id="org_div"></div>

Add a new ajax enabled WCF service and add following code. In this code snippet, I am calling above stored procedure and returning the result as a string.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Activation;
using System.ServiceModel.Web;
using System.Text;

[ServiceContract(Namespace = "")]
[AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
public class Service
    [WebInvoke(UriTemplate = "/", ResponseFormat = WebMessageFormat.Json)]
    public string GetEmployee()
            SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True");
            SqlCommand command = new SqlCommand("json", con);
            command.CommandType = CommandType.StoredProcedure;
            return (string)command.ExecuteScalar();
        catch (SqlException ex)
            return ex.Message.ToString();


Next Post Previous Post
No Comment
Add Comment
comment url