Dec 232012
 
Share...Tweet about this on TwitterShare on FacebookShare on Google+Share on StumbleUponShare on LinkedInPin on PinterestShare on TumblrShare on RedditDigg this

As I have just published the Google DataTable .Net Wrapper project on CodePlex GitHub,  the following post intends to be a quick introduction on the rationale on why this project has been created and its implementation.

Codeplex

Quick Project Background

While working on the implementation of the www.linktotweet.com web site, which is one of my side projects, I had to choose a way of displaying charts in order to show the statistics. I mainly had two options, either to return an image, that would be fully generated server side, or to use a bit of a different way and use one of the existing JavaScript libraries and generate those charts directly in the client’s browser.

As I wanted to free up the server resources from generating images at runtime, and to be honest, haven’t had experience with javascript charting libraries, I’ve chosen to go with the JavaScript approach. Among a large number of JavaScript libraries I’ve chosen the “Google Chart Tools”. Google Chart Tools is one of the most extensive libraries of charts on the web. It supports both HTML5 or SVG rendered charts. One of the good points of the Google Chart Tools is that is as well compatible with the iOS and Android. In any case, all the information about the Google Charts can be directly consulted on the official project page.

Why the Google DataTable .NET Wrapper?

As in my case of a web site project, there are mainly two ways that Google Chart Tools library uses to handle the data that would be later displayed:

  1. Data, directly generated, client side, on the page, which means directly in the JavaScript.
  2. Data prepared and then retrieved, i.e: by using a Ajax call, from the server

Let’s see both examples of how this can be done by looking at the client, JavaScript side:

Generating a chart with data generated directly on the client

In this example, the data has been generated directly in the JavaScript (the data.addRows(...) part. This is pretty much easy, but somehow cumbersome if you need to generate and return some complex dynamic data.

// Load the Visualization API and the piechart package.
google.load('visualization', '1.0', {'packages':['corechart']});

// Set a callback to run when the Google Visualization API is loaded.
google.setOnLoadCallback(drawChart);

// Callback that creates and populates a data table,
// instantiates the pie chart, passes in the data and
// draws it.
function drawChart() {

    // Create the DataTable directly in the javascript.
    var data = new google.visualization.DataTable();
    data.addColumn('string', 'Topping');
    data.addColumn('number', 'Slices');
    data.addRows([
		['Mushrooms', 3],
		['Onions', 1],
		['Olives', 1],
		['Zucchini', 1],
		['Pepperoni', 2]
	]);

    // Set chart options
    var options = {'title':'How Much Pizza I Ate Last Night',
			   'width':400,
			   'height':300};

    // Instantiate and draw our chart, passing in some options.
    var chart = 
        new google.visualization
                .PieChart(document.getElementById('chart_div'));
    
    chart.draw(data, options);
}

Using Ajax in order to get the server side generated data

In this, second, JavaScript example, the data has been generated on the server and Ajax has been used to retrieve data from a RESTful service. In order to achieve this, a reference to jQuery needs to be added to the page.

// Load the Visualization API and the piechart package.
google.load('visualization', '1', {'packages':['corechart']});

// Set a callback to run when the Google Visualization API is loaded.
google.setOnLoadCallback(drawChart);

function drawChart() {
	var jsonData = $.ajax({
		url: "/Statistics/GetStatisticsForChart?messageCode=SomeCodeHere",
		dataType: "json",
		async: false
	}).responseText;

	// Create our data table out of JSON data loaded from server.
	var data = new google.visualization.DataTable(jsonData);

	// Instantiate and draw our chart, passing in some options.
	var chart = 
new google.visualization.ColumnChart(document.getElementById('chart_div'));
	chart.draw(data, { width: 400, height: 240 });
}

for more info on this particular usage, please check the google site.

Server Side generated content

When retrieving data from the server, the data transfer format has to be expressed in JSON and in order to generate a Google Charts compatible DataTable format, a specific “schema” has to be used. Below is an example of a DataTable that Google Chart Tools understand, and expressed in JSON.

{
  "cols": 
	[
	  {"id":"", "label":"Topping", "pattern":"", "type":"string"},
	  {"id":"", "label":"Slices",  "pattern":"", "type":"number"}
	],
  "rows": 
	[
	  {"c":[{"v":"Mushrooms", "f":null}, {"v":3,"f":null}]},
	  {"c":[{"v":"Onions",    "f":null}, {"v":1,"f":null}]},
	  {"c":[{"v":"Olives",    "f":null}, {"v":1,"f":null}]},
	  {"c":[{"v":"Zucchini",  "f":null}, {"v":1,"f":null}]},
	  {"c":[{"v":"Pepperoni", "f":null}, {"v":2,"f":null}]}
	]
}

Google DataTable .Net Wrapper to the rescue

Are you willing to manually generate the above JSON code for every single case? I wasn’t and therefore I created, what I called, Google DataTable .NET Wrapper, that could simplify drastically the Json generation by following a full POCO lightweight object model.

Let’s see it in action

using Google.DataTable.Net.Wrapper;

private string GetDataTableJson()
{
    DataTable dt = new DataTable();

    //Act -----------------
    dt.AddColumn(new Column(ColumnType.Number, "Year"));
    dt.AddColumn(new Column(ColumnType.String, "Count"));

    var row1 = dt.NewRow();
    var row2 = dt.NewRow();

    row1.AddCellRange(new[] {new Cell(2012, "2012"), new Cell(150, "150")});
    row2.AddCellRange(new[] {new Cell(2013, "2013"), new Cell(100, "100")});

    dt.AddRow(row1);
    dt.AddRow(row2);

    return dt.GetJson();
}

would result in the following generated JSON

{
  "cols": 
	[
	  {"type": "number", "id": "Year"}, 
	  {"type": "string", "id": "Count"}
	], 
  "rows": 
	[
	  {"c": [{"v": "2012", "f": "2012"}, {"v": "150", "f": "150"}]}, 
	  {"c": [{"v": "2013", "f": "2013"}, {"v": "100", "f": "100"}]}
	]
}

Wrapping it all together

As you may see in the above example, the Google DataTable .Net Wrapper is a simple library that contains several classes:

  1. DataTable
  2. Column
  3. Row
  4. Cell

and that gives a great way of expressing the data in a strongly typed way, by offering the possibility to export the Json data in exactly the format that the Google Chart Library needs. This will make you avoid writing any specific code in order to transform your object data into JSON format.
Objects are lightweight and made very simple, so this should affect in any way the performance of your application.

What comes next?

There are still some unfinished things, as the “full” Google DataTable support. As the Google library supports a lot of different scenarios, especially when it comes to what Google calls “Property Map”, where additional data could be added to the Cell or the DataTable itself. For the time being this has been implemented as a single string, when in reality should be more a List of NameValue pair objects.

Project page

  1. Download the latest version of the source code
  2. Download the library

Get the library directly from NuGet:

PM> Install-Package Google.DataTable.Net.Wrapper

or get more information by following the NuGet library page: https://nuget.org/packages/Google.DataTable.Net.Wrapper

References:

    Share...Tweet about this on TwitterShare on FacebookShare on Google+Share on StumbleUponShare on LinkedInPin on PinterestShare on TumblrShare on RedditDigg this

    I'm a Software Developer and Solution Architect interested in Software Development, Object-Oriented Design and Software Architecture all this especially bound to the Microsoft.NET platform.Feel free to contact me or know more in the about section

      12 Responses to “Using the Google DataTable .Net Wrapper”

    1. wooowwwww i mean wowwww thanks Zoran 🙂 🙂 🙂 is code exactly same and it even run on myside
      its just i was using on a asp.net website and your projects is in Web application ,,,,, bit embarrasing havent learn about web application or it differs anyhow, anyways really “” you are helpful “”eyes on Google Datatable Wrapper library from now on
      trying it from 1 month in free time atlast sign of peace.

      Thanks ONCE AGAIN 😛

    2. Hiii Zoran,

      you saved me from pain,

      getting data from server (from .Net Datatabe) to googledatatable format,

      my asp code

      protected void Page_Load(object sender, EventArgs e)
      {
      GetTable();
      }
      public static String rows = “”;
      public static string GetTable()
      {
      System.Data.DataTable table = new System.Data.DataTable();
      table.Columns.Add(“Dosage”, typeof(string));
      table.Columns.Add(“No_OF”, typeof(int));

      table.Rows.Add(“Tushar”, 4);
      table.Rows.Add(“Shreyas”, 7);
      table.Rows.Add(“Pratima”, 9);

      var dt = Google.DataTable.Net.Wrapper.SystemDataTableConverter.Convert(table);

      rows = dt.GetJson();
      return rows;
      }

      ASPX code

      function drawChart() {

      var data = google.visualization.arrayToDataTable();

      var chart = new google.visualization.LineChart(document.getElementById(‘chart_div’));
      chart.draw(data, {
      height: 350,
      width: 900,
      title: ‘Chart’,
      vAxis: {
      maxValue: 165.5,
      minValue: 159,
      title: ‘Hello’
      }
      });
      }
      google.load(‘visualization’, ‘1’, {packages: [‘corechart’]});
      google.setOnLoadCallback(drawChart);

      my page source which confirms data is been retrieved on client in Google Datatable format

      function drawChart() {

      var data = google.visualization.arrayToDataTable({“cols”: [{“type”: “string” ,”id”: “Dosage” ,”label”: “Dosage” }, {“type”: “number” ,”id”: “No_OF” ,”label”: “No_OF” }], “rows” : [{“c” : [{“v”: “Tushar”}, {“v”: 4}]}, {“c” : [{“v”: “Shreyas”}, {“v”: 7}]}, {“c” : [{“v”: “Pratima”}, {“v”: 9}]}]});

      var chart = new google.visualization.LineChart(document.getElementById(‘chart_div’));
      chart.draw(data, {
      height: 350,
      width: 900,
      title: ‘Chart’,
      vAxis: {
      maxValue: 165.5,
      minValue: 159,
      title: ‘Hello’
      }
      });
      }
      google.load(‘visualization’, ‘1’, {packages: [‘corechart’]});
      google.setOnLoadCallback(drawChart);

      even then chart is not showing everything blank
      plz help. it is working in array format when put it manually , how can i convert to Array format instead js literal
      BTW chart_div is emty div tag
      Thank You

      • Hi,
        Thanks for commenting.
        Sorry for the code being messed, but i have to find a nice way of showing code in comments.

        This is the code I was using, and it should work fine.

        ASPX page: First you have to declare the script that get’s the google libraries.

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

        <!–declare the div–>
        <div id=”chart_div”> </div>

        <!– javascript –>
        <script type=”text/javascript”>
        google.load(‘visualization’, ‘1’, { ‘packages’: [‘corechart’] });
        google.setOnLoadCallback(drawChart);

        function drawChart() {

        // place the data from your code behind page.
        // for this you need to use “DataTable()” object and pass “rows” variable.
        var data = new google.visualization.DataTable(‘<%=rows%>’);

        var chart = new google.visualization.LineChart(document.getElementById(‘chart_div’));
        chart.draw(data, {
        height: 350,
        width: 900,
        title: ‘Chart’,
        vAxis: {
        maxValue: 165.5,
        minValue: 159,
        title: ‘Hello’
        }
        });
        }
        </script>

        try it out and let me know please
        Zoran

        • i tried it putting quotes and also copying in normal html file but NO LUCK
          is it possible to convert ASP Datatable to to ARRAY which is also acceptable by Google Charts with wrapper library

          google.load(‘visualization’, ‘1’, { ‘packages’: [‘corechart’] });
          google.setOnLoadCallback(drawChart);

          function drawChart() {

          // place the data from your code behind page.
          // for this you need to use “DataTable()” object and pass “rows” variable.
          var data = new google.visualization.DataTable(‘{“cols”: [{“type”: “string” ,”id”: “Dosage” ,”label”: “Dosage” }, {“type”: “number” ,”id”: “No_OF” ,”label”: “No_OF” }], “rows” : [{“c” : [{“v”: “‘Tushar'”}, {“v”: 4}]}, {“c” : [{“v”: “‘Shreyas'”}, {“v”: 7}]}, {“c” : [{“v”: “‘Pratima'”}, {“v”: 9}]}]}’);

          var chart = new google.visualization.LineChart(document.getElementById(‘chart_div’));
          chart.draw(data, {
          height: 350,
          width: 900,
          title: ‘Chart’,
          vAxis: {
          maxValue: 165.5,
          minValue: 159,
          title: ‘Hello’
          }
          });
          }

          Thanks For The Reply

          • It cannot be that to me it works and to you it doesn’t.. So it must be something different that we are doing.
            What is exactly the error message you are getting?

            • i’m not getting any error, just plain white page ,
              IS the above Google JS library is enough or do we have any other library to use??
              at first i tried to to do with ASP.net, then i tried to manually do it,,, dont know why it is not coming , got success using ARRAY though ,,,,Can you please share VS Project

              Tired of using useless ASP.net Charts:P

            • Hi Tushar,

              I’ve created a project, please check it out.
              https://www.dropbox.com/s/lds7m75saz6pf5h/Google.Visualization.DataTable.Example.zip

              I changed only the Default.aspx and Default.aspx.cs

              My Library doesn’t return the array , but that’s something that I could add in the future, as it seems to be a very nice idea indeed.

              Good luck 🙂
              Zoran

    3. Is there a complete working example. I don’t understand where to use the ” return dt.GetJson();”. Can you give me a full working project as sample code.

      • Hi Ammar,
        As I get some time I will provide a full example (with a project).
        In the meanwhile, dt.GetJson() is a function that has to be placed Server Side, depending on a project you are using, this would be a MVC Controller, Code behind, or a restful service. The idea is that JavaScript (client side) calls a method on the server that would return a string generated by the dt.GetJson().
        I hope it is just a bit more clear?
        cheers,
        Zoran

    Leave a Reply

    kammer_dayna@mailxu.com coriell.ciera