This project has moved. For the latest updates, please go here.

Introduction

Currently through "Chart Tools", Google is offering a possibility to generate Javascript based charts directly in the browser. *

In general this could be achieved in two different ways:
  1. Directly coding the javascript in order to display the chart on the page. This means everything gets executed on the client's browser.
  1. Use the javascript, but download the data by using Ajax. This involves the server which would be responsible for returning the right data in order to generate the chart.

The Google DataTable .NET Wrapper library's aim is to address the second scenario, where the server side code is involved.

As I couldn't find an easy way to generate the needed Json formatted output directly in Microsoft.NET, this library has been created in order to accomplish this.

Getting Started

Lets see the scenario for which this library becomes really useful:

1. You are using .NET

2. You want to use the Google Chart Tools in order to display charts on your web page.

3. You want to generate the needed data directly on the server, and enable the browser to execute perhaps an Ajax call, and return all the necessary data

4. You want to be sure to have the type safety and not generate the Json as required by google by yourself

Lets see a basic example

Client Side

First of all, the web page should contain a reference to google visualization api's,

<!--Load the AJAX API-->
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<!--Get the latest version of jquery -->
<script src="http://code.jquery.com/jquery-latest.min.js" type="text/javascript"></script>

<script type="text/javascript">
    // 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 });
    }
    </script>

and all this will be visualized as part of the "chart_div"
<div id="chart_div" style="width: 900px; height: 500px;"></div>

Server Side

Here is where we create a Json string to be returned to the browser.

1. Reference the Google.DataTable.Net.Wrapper.dll in your project, by simply using "Add reference" wizard, and you are ready to go.

Here is a possible implementation:

public string GetStatisticsForChart(string messageCode)
{
    //some repository that returns data....
    var data = _statisticsRepository.GetPerMessage(messageCode);

    //It simply returns a list of objects with Year and Count properties.
    var query = (from t in data
                    group t by new {t.TimeStamp.Year}
                    into grp
                    select new
                        {
                            grp.Key.Year,
                            Count = grp.Count()
                        }).ToList();

    //let's instantiate the DataTable.
    var dt = new Google.DataTable.Net.Wrapper.DataTable();
    dt.AddColumn(new Column(ColumnType.String, "Year", "Year"));
    dt.AddColumn(new Column(ColumnType.Number, "Count", "Count"));

    foreach (var item in query)
    {
        Row r = dt.NewRow();
        r.AddCellRange(new Cell[]
            {
                new Cell(item.Year),
                new Cell(item.Count)
            });
        dt.AddRow(r);
    }
           
    //Let's create a Json string as expected by the Google Charts API.
    return dt.GetJson();
}

The output

{
	"cols": [
				{"type": "string", "id": "Year",  "label": "Year"}, 
				{"type": "number", "id": "Count", "label": "Count"}
			], 
	"rows": [
				{"c": [{"v": "2011"}, {"v": "1860"}]}, 
				{"c": [{"v": "2012"}, {"v": "2000"}]}
			]
}

Convert a System.Data.DataTable to Json

It is possible to convert an ordinary .NET DataTable into a Google DataTable. The code below shows a very easy conversion of a table with three columns

using (var sysDt = new System.Data.DataTable())
{
    sysDt.Columns.Add("firstcolumn", typeof(string));
    sysDt.Columns.Add("secondcolumn", typeof(int));
    sysDt.Columns.Add("thirdcolumn", typeof(decimal));
    sysDt.Locale = CultureInfo.InvariantCulture;
 
    var row1 = sysDt.NewRow();
    row1[0] = "Ciao";
    row1[1] = 10;
    row1[2] = 2.2;
    sysDt.Rows.Add(row1);
 
    var dataTable = sysDt.ToGoogleDataTable();    
     
    var json = dataTable.GetJson();
}

Convert a List into Google DataTable

This simple code shows how to use the extension method "ToGoogleDataTable() in order to conver the IEnumerable<T> object into a DataTable.

var list = new[]
                {
                    new {Name = "Dogs", Count = 5},
                    new {Name = "Cats", Count = 2}
                };
 
var json = list.ToGoogleDataTable()
               .NewColumn(new Column(ColumnType.String, "Name"), x => x.Name)
               .NewColumn(new Column(ColumnType.Number, "Count"), x => x.Count)
               .Build()
               .GetJson();

For multiple series chart please check the following post: * Chart with two series

Further information

For further information, please check the following blog-posts:

References

In order to check how the "Chart Tools" works please visit directly the official google web page
For more information and common issues check StackOverflow
https://stackoverflow.com/search?q=Google.DataTable.Net.Wrapper

Last edited Dec 2, 2016 at 10:31 AM by zoranmax, version 18

Comments

Mlockwood Mar 1, 2015 at 10:59 PM 
I just have one question. I am trying to set a role of style for a column but it never comes through correctly. How should this be done?

Thanks,
Mike

lazfsh Oct 29, 2014 at 3:40 PM 
Could you expand your Server-Side example to include how you would cast one column of your System.Data.DataTable as the formated part of the column? I am starting from a stored procedure that returns a DataSet, then converts each table in the set as a json object.
See this GIST for what I am trying to do: https://gist.github.com/lazfsh/e1221bc12e62f6d55190#file-sql-fiddle-for-google-vis-datatable-wrapper

zoranmax Jun 13, 2014 at 6:34 PM 
Hi Gangwar,
no, not really. but I think this can be implemented quite quickly. However, you might loose some of the information that the Google.DataTable might have, labels, formatting, etc.
What is the scenario you want to use this feature?

Gangwar Jun 12, 2014 at 1:08 PM 
is there way to send google data table to my controller and convert it into System.Data.DataTable type as like we convert System.Data.DataTable to Google DataTable

zoranmax Apr 15, 2014 at 8:56 AM 
Hi,
I've done the change regarding the "type" vs "columnType" issue.
Check this: https://googledatatablelib.codeplex.com/SourceControl/changeset/26273

Regards,
Zoran

zoranmax Nov 22, 2013 at 2:26 PM 
Hi stevieshannon
Can you please open an incident and just cut and paste your code in the Issues section.

I have to have more info to understand what is the real issue here.

Cheers,
Zoran

stevieshannon Oct 30, 2013 at 5:23 PM 
Just spotted a bug and I'm not sure how to send a pull request your way so I'll just let you know what's going on!

The JSON should be showing the ColumnType as "type" (as is documented above), but if you look at the actual JSON or source code, you'll see it's actually shown as "columnType" which breaks things - changing this to "type" in Cell.cs and Column.cs seems to fix the problem.

Thanks for the great wrapper!

zoranmax Sep 9, 2013 at 9:11 PM 
Hi babairo,
Are you using the latest version of the code?
You should reference the Google.DataTable.Net.Wrapper.Extension namespace in the class where you use this method.

using Google.DataTable.Net.Wrapper.Extension;
....


Please check the Unit test code here
https://googledatatablelib.codeplex.com/SourceControl/latest#trunk/src/Google.DataTable.Net.Wrapper.Tests/Extension/SystemDataTableExtensionTest.cs

Cheers,
Zoran

babairo Aug 24, 2013 at 3:38 PM 
When using your code Convert a System.Data.DataTable to Json I get the following error:

'System.Data.DataTable' does not contain a definition for 'ToGoogleDataTable' and no extension method 'ToGoogleDataTable' accepting a first argument of type 'System.Data.DataTable' could be found (are you missing a using directive or an assembly reference?)

Am I missing something?