Category: DataSet

Google offers a lot of really cool functionality through its Google JSAPI. Features range from APIs to render your data in a number of graph or custom table formats. You can preview some of the available options at the Google Code Playground.

In this article I will investigate one of the charting options available through Google JSAPI called Org Charts. As the name says, it can be used to represent top down relational data in an inverted tree-like structure. Example uses could include using it for rendering an org chart or even a family tree.

The Basics

You may be asking yourself how Google JSAPI works. Bottom line is that it’s actually quite straightforward…

You call the Google JSAPI from your JavaScript using an array-like format to send your data. Once you have done so, Google will respond with a nicely rendered result that you can set to be output to a <div area in your JavaScript.

That’s all great for hard-coded test scenarios, but how do you get actual data from your database over to Google JSAPI to be rendered?

Integrating JSAPI with C#

You can use a server-side language such as C# to both call your database and put together your JavaScript Google JSAPI call. I am providing a functional example in the code below (which you can see running in the video clip at the start of this article).

For starters, you need to include a reference to the Google JSAPI library in the header section of your HTML page:

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

Then, right below your call to the Google JSAPI library, you need to load the Org Chart package as follows. Note also that this call is pointing the API call to output its results to your <div tag, which in this case is set with the ID of visualization.

<script type=&quot;text/javascript">
 google.load('visualization', '1', { packages: ['orgchart'] });
</scrip>

Populating the JavaScript with Data from your Database

Next comes the most important part of this exercise. Instead of hard-coding the JavaScript with values, you will use C# to get the data that you want, and then you will build your JavaScript dynamically. I am using C# as an example, but really you can use any language that has the capability to query a database including AJAX calls.

To put together your background code, you can put together a custom function that fires on page load, or optionally one that is triggered on the press of a button. In this case, the code is started by clicking a button.

In the example below, you can see that in the C# code I have coded a database call with an imaginary connection string. In your code you will need to update this with real database connection information in order for the example to work properly. Once the database connection is established, you will want to put together your own query to the information that you want to map. For simplicity, I have hardcoded the SQL to return data in the format that you would want returned.

When you get the results back from the database, you need to loop through each row and put together an equivalent row of the JavaScript array object that will get passed to Google JSAPI.

From Google’s documentation, the basic format to use when passing each unit of data to Google JSAPI is:

['Text to Output', 'Identity Key', 'Tooltip'],

so for example:

['Bob', '123Jim', 'Bob Sponge'],

Which in this case means that Bob will appear in a box below Jim with a line connecting both.

From my example below, you will see that I use a slightly more customized way of presenting this data using the example format:

[{v: '123Jim', f: 'Jim<br/><font color="red"><i>Vice President<i></font>'}, '123Mike',    null],

The v: section codes for the identity key upon which the matches are made, while the f: section codes for the descriptive text. Subsequently the mapping codes can be added. So in the above line, 123Jim is mapped as a sub-item of 123Mike.

Linking your Org Chart

You may also want to add hyperlinks to the code in your org charts. In my example code I have included hyperlinks that pass an employee Id to an imaginary page called myPageName. The trick here is to encode single quotes, quotes, and special characters in the proper JavaScript notation:

strOutList += " [{v: '" + strPositionId + "', f: '<a href src=\"myPageName?EmployeeName=" + strEmpName + "\" />" + strEmpName + "</a><br/><font color=red><i>" + strPositionTitle + "<i></font> '}, " + strReportToPosNum + ", '" + strPositionTitle + "'] ";

The Full Example Code

<%@ Page Language= C#  %>
<%@ Import Namespace= System.IO  %>
<%@ Import Namespace= System.Data  %>
<%@ Import Namespace= System.Data.SqlClient  %>
<%@ Import Namespace= System.Collections  %>
<!DOCTYPE html PUBLIC  -//W3C//DTD XHTML 1.0 Transitional//EN   http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd >
<script runat= server >
 public void Page_Load(object Sender, EventArgs E)
 {
 }
 protected void btnFind_Click(object sender, System.EventArgs e)
 {
 DataSet objDataSet = default(DataSet);
 SqlConnection conn = new SqlConnection();
 conn.ConnectionString =  Data Source=myServer;Initial Catalog=myDatabase;Persist Security Info=True;User ID=myUserAccount;Password=myPassword ;
 conn.Open();
 SqlDataAdapter objAdapter = new SqlDataAdapter();
 SqlCommand cmdCommand = new SqlCommand();
 DataView tmpDataView = default(DataView);
 string strOutJS =  <script type=text/javascript >  function drawVisualization() {   + Environment.NewLine;
 strOutJS +=   var data = google.visualization.arrayToDataTable([  ;
 string strOutList =   ['Name', 'Manager', 'Tooltip']  + Environment.NewLine;
 string strRunSql =   SELECT 1 [PositionOrder], '123Jim' [PositionId], 'Jim Jones' [EmpName], 'President' [PositionTitle], 'null' [ReportsToPosition]  ;
 strRunSql +=   UNION  ;
 strRunSql +=   SELECT 2 [PositionOrder], '222Mike' [PositionId], 'Mike Macro' [EmpName], 'CTO' [PositionTitle], '123Jim' [ReportsToPosition]  ;
 strRunSql +=   UNION  ;
 strRunSql +=   SELECT 2 [PositionOrder], '333Suzy' [PositionId], 'Suzy Seven' [EmpName], 'CFO' [PositionTitle], '123Jim' [ReportsToPosition]  ;
 strRunSql +=   UNION  ;
 strRunSql +=   SELECT 3 [PositionOrder], '445Bob' [PositionId], 'Bob Better' [EmpName], 'Programmer' [PositionTitle], '222Mike' [ReportsToPosition]  ;
 strRunSql +=   UNION  ;
 strRunSql +=   SELECT 3 [PositionOrder], '556Kim' [PositionId], 'Kim Kentucky' [EmpName], 'Programmer' [PositionTitle], '222Mike' [ReportsToPosition]  ;
 strRunSql +=   UNION  ;
 strRunSql +=   SELECT 3 [PositionOrder], '667' [PositionId], 'John Johson' [EmpName], 'Taxation' [PositionTitle], '333Suzy' [ReportsToPosition]  ;
 objAdapter.SelectCommand = cmdCommand;
 objAdapter.SelectCommand.Connection = conn;
 objAdapter.SelectCommand.CommandType = CommandType.Text;
 objAdapter.SelectCommand.CommandText = strRunSql;
 objDataSet = new DataSet( EmployeeResultset );
 objAdapter.Fill(objDataSet);
 DataTable tmpDataTable = new DataTable();
 tmpDataTable = (DataTable)objDataSet.Tables[0];
 tmpDataView = tmpDataTable.DefaultView;
 foreach (DataRowView rowView in tmpDataView)
 {
 DataRow myRow = rowView.Row;
 string strPositionId = (string)myRow[ PositionId ];
 string strEmpName = (string)myRow[ EmpName ];
 string strPositionTitle = (string)myRow[ PositionTitle ];
 string strReportToPosNum = (string)myRow[ ReportsToPosition ];
 if (strReportToPosNum.Equals( null ) == false)
 {
 strReportToPosNum =  '  + strReportToPosNum +  ' ;
 }
 strOutList +=  ,   + Environment.NewLine;
 strOutList +=   [{v: '  + strPositionId +  ', f: '<a href src=\ myPageName?EmployeeName=  + strEmpName +  \  />  + strEmpName +  </a><br/><font color=red><i>  + strPositionTitle +  <i></font> '},   + strReportToPosNum +  , '  + strPositionTitle +  ']  ;
 }
 cmdCommand.Dispose();
 objDataSet.Dispose();
 objAdapter.Dispose();
 conn.Close();
 conn.Dispose();
 strOutJS += strOutList + Environment.NewLine;
 strOutJS +=   ]);   + Environment.NewLine;
 strOutJS +=   new google.visualization.OrgChart(document.getElementById('visualization')).   + Environment.NewLine;
 strOutJS +=   draw(data, {allowHtml: true});   + Environment.NewLine;
 strOutJS +=   }   + Environment.NewLine;
 strOutJS +=   google.setOnLoadCallback(drawVisualization);   + Environment.NewLine +  </  +  script> ;
 Page.ClientScript.RegisterClientScriptBlock(this.GetType(),  BtnClickHierarchyScript , strOutJS, false);
 }
</script>
<html xmlns= http://www.w3.org/1999/xhtml >
<head runat= server >
 <title>My Example Employee Hierarchy Page</title>
 <script type= text/javascript  src= //www.google.com/jsapi ></script>
 <script type= text/javascript >
 google.load('visualization', '1', { packages: ['orgchart'] });
 </script>
</head>
<body>
 <form id= form1  runat= server >
 <div id= divSearchForm  runat= server  style= display:inline; >
 <asp:Button ID= btnFind  runat= server  Text= Find  style= font-family:verdanna;font-size:X-Small;  OnClick= btnFind_Click  />
 </div>
 <div id= visualization  style= width: 300px; height: 300px; ></div>
 </form>
</body>
</html>