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

/ / 15 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 thank me? 👳 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 *

15 Comments