Google org chart + Asp.net C# : Create simple organization chart with database [ MS SQL Server]

/ / 18 Comments

Google Organization Chart: This tutorial explains using Google Org Chart how we create a simple organization chart in Asp.net web application (webform.aspx page) .i.e using Google Visualization Organizational.

While working on my one project, I had a similar requirement, where I have to show our Company organizational structure with live data from database MS SQL Server.

Display corporate org chart where the chart gets generated based on hierarchical relationship from employee to employee corresponding to manager-id (Head).

I googled a little bit on it, and then I find Google Visualization Organizational Chart, i.e., by using Google Visualization API (google org chart) we create a simple Org Chart.

What is Org Chart?

Org charts are diagrams of a hierarchy of nodes, commonly used to portray superior/subordinate relationships in an organization.

A family tree is a type of org chart. Let's now head to the tutorial step by step.

Example:  


Step to create Google Org Chart in Asp.net C#.

  1. Database: Having employee, manager relationship.
  2. Initialize Google org chart.
  3. C#: Create Webmethod, which returns JSON result.
  4. Populate google org chart with database via jQuery ajax call

# Create database having table employee, manager relationship

I have created a sample employee table which contains following columns id_emp, employee_name, gender, manager_id, designation. Here each employee has manager_id to whom he/she is to reporting check Fig 1 for employee table schema and Fig 2 containing data.

Fig 1.
 

Fig 2.


Before writing any code, first we need to create a select query which shows empName, empId, managerName, managerID, and designation. For better SQL understanding check SQL self-join, inner-join, outer-join article by Pinal Dave ( sqlauthority.com ) Our Select query looks like as shown below.



We are done with our database part, let's head to the coding part :)


# Initialize Google org chart:

Google API loader allows you to import easily one or more APIs and specify additional settings (such as language, location, API version, etc.) applicable to your needs. For using Google organization chart first, we need to import Google JSAPI library in our Webpage (.aspx file).

//*
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
//*

Then next will load the Google API with Google.load(moduleName, moduleVersion, optionalSettings), where

  • moduleName: calls the particular API module you wish to use on your page.
  • moduleVesion:: is the version number of the module you want to load.
  • optionalSettings:  specifies all optional configuration options for the API you are loading as a JavaScript object literal.

Here in our case we want to create org chart so our moduleName would be visualization and in optionalSettings will set orgchart as packages, code looks like as written below

//*
google.load("visualization", "1", { packages: ["orgchart"] });
//*

# Create Webmethod which returns JSON result.

Am assuming you are aware of asp.net Webservice Webmethod (.asmx file ) or you can check how to make a simple jQuery ajax call which returns JSON data in asp.net for better understanding.

Here first, we create a class object named as Google_org_data containing properties as the employee, manager, and their ids as shown in below code.

//*
public sealed class Google_org_data
{
    public string Employee { get; set; }
    public string Manager { get; set; }
    public string mgrID { get; set; }
    public string designation { get; set; }
    public string empID { get; set; }
 }
//*

Now we write a Webmethod which returns Google_org_data object

//*
[WebMethod]
public List<Google_org_data> getOrgData()
{
	List<Google_org_data> g = new List<Google_org_data>();
	DataTable myData = getDataTable();

	g.Add(new Google_org_data {
				Employee = "Rocky Balboa",
				Manager = "",
				mgrID = "",
				empID = "13",
				designation = "CEO"
			});

	foreach (DataRow row in myData.Rows)
	{
		string empName = row["EmpName"].ToString();
		var mgrName = row["MgrName"].ToString();
		var mgrID = row["mgrID"].ToString();
		var empID = row["empID"].ToString();
		var designation = row["designation"].ToString();

		g.Add(new Google_org_data{
					Employee = empName,
					Manager = mgrName,
					mgrID = mgrID,
					empID = empID,
					designation = designation
				});
	}
	return g;
}

public DataTable getDataTable()
{

	DataTable dt = new DataTable();
	string query = " select a.employee_name as EmpName,a.id_emp as empID,a.designation,b.employee_name as MgrName,b.id_emp as mgrID";
	query += " from tb_employee a inner join tb_employee b on a.manager_id=b.id_emp";
	SqlDataAdapter dap = new SqlDataAdapter(query, con);
	DataSet ds = new DataSet();
	dap.Fill(ds);
	return ds.Tables[0];
}
//*

We are done with server side code, now get back to some client side coding ( jQuery ).


# jQuery ajax call and populate Google org chart with database:

Here we have button tag and on jQuery click event it makes an ajax request and fill organizational chart. Final code looks like as written below

//*
$("#btnOrgChart").on('click', function (e) {
	
	$.ajax({
		type: "POST",
		url: "AjaxFunction/myFunction.asmx/getOrgData",
		data: '{}',
		contentType: "application/json; charset=utf-8",
		dataType: "json",
		success: OnSuccess_getOrgData,
		error: OnErrorCall_getOrgData
	});

	function OnSuccess_getOrgData(repo) {

		var data = new google.visualization.DataTable();
		data.addColumn('string', 'Name');
		data.addColumn('string', 'Manager');
		data.addColumn('string', 'ToolTip');

		var response = repo.d;
		for (var i = 0; i < response.length; i++) {
				var row = new Array();
				var empName = response[i].Employee;
				var mgrName = response[i].Manager;
				var empID = response[i].empID;
				var mgrID = response[i].mgrID;
				var designation = response[i].designation;

				data.addRows([[{
					v: empID,
					f: empName
				}, mgrID, designation]]);
		}

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

	function OnErrorCall_getOrgData() {
		console.log("Whoops something went wrong :( ");
	}
	e.preventDefault();
});
//*

Yeah, we are done here's the output

[txt_ads]

# Output:


  Reference Link:

Hope you enjoyed this tutorial. If you have any recommendations, please let us know what you think in the comment section below! See you again next time!

Thank you for reading, pls keep visiting this blog and share this in your network. Also, I would love to hear your opinions down in the comments.

PS: If you found this content valuable and want to do a favor, then Buy me a coffee

Subscribe to our newsletter

Get the latest and greatest from Codepedia delivered straight to your inbox.


Post Comment

Your email address will not be published. Required fields are marked *

18 Comments

  1. Ted 09/25/2015 18:00:33
    Very nice. Easy to follow, and very well written. I actually was getting ready to Google something similar to this, and won't need to now, because I can just modify what you've shown above to fit my needs. Thank you!
  2. Ramchand 09/30/2015 10:04:05
    Nice one.. The way you written and explained is superb. All the Best.
  3. Danish 11/20/2015 04:34:25
    Nice work keep it up,kindly start to write on MVC using angular.
  4. Satinder singh 11/20/2015 06:38:19
    Thanks Danish :) Sure soon will write article on MVC and AngularJS
  5. Satinder singh 11/20/2015 06:39:56
    Hi, Ramchand :) Thanks for the feedback, this encourage me more
  6. Satinder singh 11/20/2015 06:46:21
    Thanks, Ted For using such kind words, keep visiting, hope you like to check this post for Creating Responsive Chart in C# https://codepedia.info/2015/10/chartistjs-create-responsive-chart-in-asp-net-pie-chart-donut-chart/
  7. Monicca 02/05/2016 05:46:29
    This is a very helpful Add, for the nodes with children items how can you make the main node collapse or expand if you want to hide its children items in MVC(KendoDiagram)
  8. S.Singh 04/18/2016 11:09:26
    Thanks for the great example, the issue I have however is that I need to pass through a DepartmentID to the webmethod from a dropdownlist. Any advise?
  9. Satinder singh 04/20/2016 15:46:17
    Thanks for reading, S Singh :) Well, you can make an ajax call on drop-down list change event and send JSON data to WebMethod. For reference pls check https://codepedia.info/2016/01/insert-data-using-jquery-ajax-in-asp-net-csharp-database-ms-sql-server/
  10. Michael Bartlett 09/03/2016 04:14:30
    This is great, thank you. One question: How could we make the background colors different for each part of the org chart (node) based on some data?
  11. Furtado 11/15/2016 15:26:29
    Hello; I need to create a fully dynamic circular business organization (number of flexible parents and children) and receive dynamic data in the boxes. Can you help me with how to do this? Thanks for your attention.
  12. Seyfeddin 05/07/2017 18:51:00
    it doesnt work!!
  13. Satinder singh 05/22/2017 16:42:15
    Hi Seyfeddin, Its working fine in my project, can you tell me, what error are you getting?
  14. saichander 07/17/2017 17:42:01
    Nice article. i need to develop this in my project. do you have the code for this article to download, i could not find the link in this article to download.
  15. Meghanshu 08/01/2018 07:01:26
    Hii this code is given a following error