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 asp.net 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
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 the 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, '') + ']
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="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 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
{
[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();
}
}
}