ASP.NET C#: Create simple organization chart with MSSQL database and Google org chart
Overview
This tutorial explains using Google Org chart how we create a simple organization chart in ASP.NET web application (i.e. webform .aspx page) using Google Visualization Organizational.
While working on a project there was a similar requirement where we had to show our company's organizational structure with live data from MS SQL Server.
Display a corporate org chart where the chart gets generated based on hierarchical relationship from employee to employee corresponding to manager-id (Head).
After Googling a little bit on it and we found Google Visualization Organizational Chart. Using Google Visualization API (Google org chart) we created a simple Org Chart.
What is the 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:
http://codepedia.info/wp-content/uploads/2015/09/Google-org-chart-asp-net-example.gif
Steps to create a Google Org Chart in ASP.NET C#.
- Database: Having employee, manager relationship.
- Initialize Google org chart.
- C#: Create Webmethod which returns JSON result.
- Populate Google org chart with the database via jQuery Ajax call
Step 1. Create a database having table employee, manager relationship
Create a sample employee table which contains the following columns: id_emp, employee_name, gender, manager_id, designation.
Here each employee has a manager_id to whom he/she is to report. Check Fig 1 for employee table schema and Fig 2 containing data.
Fig 1
http://codepedia.info/wp-content/uploads/2015/08/Employee_table.png
Fig 2
**
**
http://codepedia.info/wp-content/uploads/2015/08/Employee_table_with_data.jpg
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 the one shown below:
.http://codepedia.info/wp-content/uploads/2015/08/Employee_table_Emp_mgr_Data.jpg
Step 2. Initialize Google org chart
Google API loader allows you to easily import 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``>
Next, we will load the Google API with google.load(moduleName, moduleVersion, optionalSettings), where:
- moduleName: calls the specific API module you wish to use on your page.
- moduleVesion:: is the version number of the module you wish to load.
- optionalSettings: specifies all optional configuration options for the API you are loading as a JavaScript object literal.
In our case we want to create an org chart so our moduleName would be visualization and in optionalSettings we will set orgchart as packages. Code looks like as written below:
google.load("visualization", "1", { packages: ["orgchart"] });
Step 3. Create Webmethod which returns JSON result
We will assume 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 a better understanding.
First we create a class object named as Google_org_data containing properties as 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 to get back to some client side coding (jQuery).
Step 4. 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 to populate the 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();
});