Tuesday, 14 May 2013

Create Google Charts in Asp.Net MVC by generating JSON data on the SQL SERVER

3 comments

The purpose of this article is to share the implementation of Google Charts with data retrieved from SQL Server whereas JSON is created on the SQL Server.

Google chart tools provide a rapid way of creating charts from some data for the web developers. All charts are populated with data stored in a DataTable object. The DataTable object can be filled with data using JSON format.
There are several different ways of creating and populating a DataTable and they all have some advantages and disadvantages (more about it here).
The application I needed to add Google Charts to be Asp.Net MVC using nHibernate with MS SQL Server.
The idea is to create all required data on the SQL Server using stored procedures to gain performance benefits, minimize the efforts of creating JSON for each chart and realize the DRY principal.
Once the required SQL stored procedure is created we are going to convert the result table data into JSON and pass it into our DataTable constructor.

Let’s say we need to create the bar chart showing the number of items by location.
Our stored procedure will be like the one below, where we have to create the string of columns, populate the temp table “#chart_data” with data and call another stored procedure to convert the content of the temp table into JSON.
create procedure [dbo].[GetItemsByLocationAsJson]
as
begin
set nocount on;
/* Set your columns data here */
/* The result string should look like the one below */
/* "cols": [{"id":"","label":"Topping","type":"string"},{"id":"","label":"Slices","type":"number"}] */
declare @columns_list varchar(max) = '"cols":[' +
'{"id":"Alocation","label":"Location","type":"string"}' +
',{"id":"ItemCount","label":"Items Count","type":"number"}' +
']';

select * into #chart_data from ( 
 select 'Location 1' as Alocation, 5 as ItemCount
 union all
 select 'Location 2' as Alocation, 10 as ItemCount
 union all
 select 'Location 3' as Alocation, 15 as ItemCount
 union all
 select 'Location 4' as Alocation, 20 as ItemCount
 ) as t

exec dbo.ConvertTableToGoogleChartJson @columns_list;

drop table #chart_data;  
 
end
If we run this stored procedure we’ll get the following result:
{
  "cols":[{"id":"Alocation","label":"Location","type":"string"},{"id":"ItemCount","label":"Items Count","type":"number"}],
  "rows":[{"c":[{"v":"Location 1"},{"v":5}]} ,{"c":[{"v":"Location 2"},{"v":10}]} ,{"c":[{"v":"Location 3"},{"v":15}]} ,{"c":[{"v":"Location 4"},{"v":20}]}]
}
JSON data are generated by sorting the column names in ascending order.
Note that the order of columns and rows should be in sync, e.g. the generated JSON data in rows should related to the correct columns.
The order between columns and rows can be achieved by giving such names so they will be sorted correctly when populating the #chart_data table. In our example we name the location field as “Alocation” instead of “Location” to make it first. Thus we can ensure the data for “Alocation” and “ItemCount” will be generated in correct order.

Below is the code for the ConvertTableToGoogleChartJson stored procedure.
This generic procedure generates the appropriate JSON based on the data in the #chart_data table and pre-append the column list passed as a parameter. Most of the code based on the answer from StackOverflow (thanks to pyrospade) and use metaprogramming to boost the performance.
create procedure [dbo].[ConvertTableToGoogleChartJson] 
 @columns_list varchar(max)
as
begin
set nocount on;

    declare @columns table (
    id int identity primary key,
    name sysname,
    datatype sysname,
    is_number bit,
    is_date bit);

    insert into @columns(name, datatype, is_number, is_date)
 select columns.name, types.name,
     case when number_types.name is not NULL
    then 1 else 0
     end as is_number,
     case when date_types.name is not NULL
    then 1 else 0
     end as is_date
 from tempdb.sys.columns
 join tempdb.sys.types
  on (columns.system_type_id = types.system_type_id)
 left join (values ('int'), ('real'), ('numeric'),
       ('decimal'), ('bigint'), ('tinyint')) as number_types(name)
  on (types.name = number_types.name)
 left join (values ('date'), ('datetime'), ('datetime2'),
       ('smalldatetime'), ('time'), ('datetimeoffset')) as date_types(name)
  on (types.name = date_types.name)
 where object_id = OBJECT_ID('tempdb..#chart_data') and types.name != 'sysname';

 declare @row_list varchar(max) = STUFF((select '+'',''+''{"v":''+' + 
  case when is_number = 1 then 'COALESCE(LTRIM(' + QUOTENAME(name) + '),''null'')'
    when is_date = 1 then 'COALESCE(QUOTENAME(LTRIM(convert(varchar(max),' + QUOTENAME(name) + ', 126)),''"''),''null'')'
    else 'COALESCE(QUOTENAME(' + QUOTENAME(name) + ',''"''),''null'')'
    end + '+''}'''
  from @columns order by name  for xml path('')), 1, 5, '');

     create table #json_result (id int identity primary key, line varchar(max));

     declare @sql varchar(max) = REPLACE('insert into #json_result '  + 'select '',{"c":[''+{f}+'']}''' + 'from #chart_data', '{f}', @row_list);

     exec(@sql);

     update #json_result set line = STUFF(line, 1, 1, '') where id = 1;

     declare @row_json Varchar(max) = (select line + '' as 'data()' from #json_result for xml path(''));

     select ('{' + @columns_list + ',"rows":[' + @row_json + ']}') as Result;

     drop table #json_result;
end
The point here is that the ConvertTableToGoogleChartJson stored procedure can be reused for the other calls to speed up the development of google charts.

Here is the sample of the controller calling our stored procedure and returning the JSON result:
public ActionResult GetItemsByLocationData ()
{
    var jsonText = this.dbService.GetItemsByLocationData();
    return this.Content(jsonText, "application/json");
}
Our view can have the jQuery ajax call to pass the JSON into the DataTable constructor:
function drawPieChart() {            
    var jsonData = $.ajax({
        url: 'MyController/GetItemsByLocationData',
        dataType: 'json',
        async: false
        }).responseText;
            
    var data = new google.visualization.DataTable(jsonData);
            
    // Set chart options      
    var options = {title: 'Items by Location'};
    // Instantiate and draw our chart, passing in some options.      
    var chart = new google.visualization.PieChart(document.getElementById('pie_chart_div'));            
    chart.draw(data, options);
}

That’s it. Happy coding!

3 comments:

  1. This is really nice article.
    http://www.aspdotnet-pools.com/2014/07/how-to-create-dynamic-google-column.html

    ReplyDelete
  2. you forgot the code referenced by "var jsonText = this.dbService.GetItemsByLocationData();" and this is therefore not complete, working code. All of my attempts to go around this omission have failed.

    ReplyDelete
    Replies
    1. Hi Randy,

      It is up to developer how to implement that db service method "this.dbService.GetItemsByLocationData()".
      It's purpose is to connect a database and invoke the stored procedure "GetItemsByLocationAsJson"
      in order to retrieve required data.

      Hope that explains.

      Delete