Connecting Google Visualization API to any Data Source

In my previous post I explained how to use the Google Visualization API with SharePoint to create a reporting dashboard/portal.

As a .NET Developer I tend to use Microsoft SQL Server a lot to store and manage my data. After reading through the Google Visualization documentation I realized there was no way to connect to a MS SQL Server out-of-the-box.

Then I discovered that I could implement my own data source using the "Wire Protocol v0.6". After reading the document it turns out that the Google Visualizations API can connect to a web server; pass commands, and then take the results and load them into the Visualization API.

For example, I can send the following request to a web server:

http://servername/example.aspx?parametername=value&parametername2=value2

Then based on the input values (parametername/parametername2), the server can make a decision on what data to display and then output the data as the API requires.

Using ASP.NET I wrote a proof-of-concept program for testing purposes. This proof-of-concept program simply connected to a SQL server, loaded the data, converted it to JSON, and returned it to the requester (Visualization API). There we had it, the Google API rendered the charts with no problem.

Now I wanted to ensure that I can load data from any data source. For example, most of my data is stored in a Microsoft SQL Server, but I also have some data that I need from other systems such as PLC's, ERP Systems, SharePoint Lists, Text Files, Web Services, etc.

To accomplish this I had to figure out a way to specify which data source the user was expecting data from, then allow the user to pass parameters to this system so it can get the proper dataset. To do this in the URL request, I required the user to pass a parameter called DSNAME (data source name). This DSNAME would tell the system which DLL to load and execute to get the proper data back.

For this system to be able to understand the DLL's I created a very simple Interface. This interface contains 1 method called "Execute" that will accept a Dictionary Object as a parameter and returned 1 DataTable. The Dictionary object will contain all the parameters that were passed by the requestor's URL parameters (see above's http URL). Once the DLL executes, it would have to return a DataTable. From this point the system would then convert the DataTable to JSON then return that back to the requestor of the URL.

Take the following URL as an example:

http://64.244.60.200:88/DTS/Default.aspx?dsname=MSSQL&Query=SELECT%20*%20FROM%20ViewTest&tqx=version:0.6;reqId:6

The DSNAME parameter tells the system that it wants to load the DLL associated with "MSSQL" (this is configured in a Database - will go into more details later). Then the QUERY parameter is sent which contains the actual query statement to be executed. From this point the system knows which DLL to execute and the DLL knows what data to read from where (based on the parameters we passed).

I know you might wonder where the connection string is to the DB. Since you don't want to pass sensitive data in the URL, I made this system be capable of loading default parameters (configured in a db) based on the DSNAME. For example "MSSQL" knows to automatically load a default Connection String.

Now to test all this follow the steps mentioned in my previous post; but add the following HTML code to the Content Form Web Part (sorry cant paste HTML code in here):

http://64.244.60.200:88/html.txt

This sample HTML code basically loads Google's Annotated Time Line (my favorite), then connects to my data source (that we specified above), loads the MSSQL DLL, passes the Query parameter, then the system returns the JSON code which the Google API's can use to render the charts.

Here is a quick showing what I mean.

I intend to release the source code to all the code behind this as I am sure there are many great enhancements that can be made to it.

More to follow...