Creating Organization Hierarchy in ASP.NET using Google Organizational Chart

In this post I will show you how to integrate google org chart in your asp.net application.
  • First create a new empty website in visual studio
  • Add jquery and google visualization api reference to the default page
Let's first create dummy data.Create following table schema and insert some dummy data into this.
Table Schema

CREATE TABLE [dbo].[EMPLOYEES] (
    [EmpID] CHAR (4)     NOT NULL,
    [Ename] VARCHAR (10) NULL,
    [MGR]   CHAR (4)     NULL,
    PRIMARY KEY CLUSTERED ([EmpID] ASC),
    UNIQUE NONCLUSTERED ([EmpID] ASC)
);
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 following stored procedure that returns employee and manager relationship result into json string. StoredProcedure
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('') ,
                            TYPE
    ).value('.', 'varchar(max)'), 1, 1, '') + ']'

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
Once the database is setup create default.aspx page and add following code into it.
<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script src="http://www.google.com/jsapi"></script>
    <script src="//ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>
    <script>
   
    
    
    </script>
    <script>
        google.load('visualization', '1', { packages: ['table', 'orgchart'] });
        function draw() {
            $.ajax({
                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');
                        l_data_table.addRows(emp_count);

                        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 () {
            draw();

        });
    </script>
</head>
<body>
    <form id="form1" runat="server">
        <div id="org_div"></div>
    </form>
</body>
</html>
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 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
{
    [OperationContract]
    [WebInvoke(UriTemplate = "/", ResponseFormat = WebMessageFormat.Json)]
    public string GetEmployee()
    {
        try
        {
            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;
            con.Open();
            return (string)command.ExecuteScalar();
        }
        catch (SqlException ex)
        {
            return ex.Message.ToString();

        }
    }


}

3 comments:

  1. Dude No Errors and no chart also

    ReplyDelete
  2. Another possible way is to create an org chart from a software like creately snf embed it using XML.

    ReplyDelete