Visualize Data Using Google Charts and ASP.NET MVC

Visualizing data using charts is simpler than ever thanks to libraries like Google Charts.  I’m going to show how to construct a Google Chart from a collection of .NET objects in C#. Essentially all you need to do is take the collection of .NET objects, translate it into a class that matches the format expected by Google for chart data, and then Json.Encode that object in your view as part of a script to generate the chart.

The example takes this data…

new MarketSales[]
{
    new MarketSales() { Market = "Cincinnati", Year = 2013, TotalSales = 723898 },
    new MarketSales() { Market = "Cincinnati", Year = 2014, TotalSales = 898132 },
    new MarketSales() { Market = "Cincinnati", Year = 2015, TotalSales = 941823 },
    
    new MarketSales() { Market = "Columbus", Year = 2013, TotalSales = 509132 },
    new MarketSales() { Market = "Columbus", Year = 2014, TotalSales = 570913 },
    new MarketSales() { Market = "Columbus", Year = 2015, TotalSales = 460923 },
    
    new MarketSales() { Market = "Cleveland", Year = 2013, TotalSales = 753939 },
    new MarketSales() { Market = "Cleveland", Year = 2014, TotalSales = 830923 },
    new MarketSales() { Market = "Cleveland", Year = 2015, TotalSales = 910302 },
    
    new MarketSales() { Market = "Dayton", Year = 2013, TotalSales = 109012 },
    new MarketSales() { Market = "Dayton", Year = 2014, TotalSales = 400302 },
    new MarketSales() { Market = "Dayton", Year = 2015, TotalSales = 492901 }
}

… and uses it to create this chart:

Google Chart

I’ve published the source code for this example on Github. If you are creating this from scratch, the best way is to create a new ASP.NET Web Application project using the empty template and checking the box to add folders and core references for MVC. This will bring in the necessary dependencies without adding a whole bunch of starter code that is not needed for the example and give you a relatively clean starting point.

Here is the code for the example:

Controllers\HomeController.cs
The controller will retrieve the data (imagine that it is coming from a database, but really for this example it is just constructing an array of MarketSales objects) and then translates it into an object of type GoogleVisualizationDataTable, which serves the purpose of matching the format required by Google in order to create a DataTable for the chart to use.

using GoogleChartsExample.Models;
using System.Collections.Generic;
using System.Linq;
using System.Web.Mvc;

namespace GoogleChartsExample.Controllers
{
    public class HomeController : Controller
    {
        public ActionResult Index()
        {
            // Get the chart data from the database.  At this point it is just an array of MarketSales objects.
            var data = GetMarketSalesFromDatabase();

            return View(new SalesChartModel()
            {
                Title = "Total Sales By Market and Year",
                Subtitle = "Cincinnati, Cleveland, Columbus, and Dayton",
                DataTable = ConstructDataTable(data)
            });
        }

        private GoogleVisualizationDataTable ConstructDataTable(MarketSales[] data)
        {
            var dataTable = new GoogleVisualizationDataTable();

            // Get distinct markets from the data
            var markets = data.Select(x => x.Market).Distinct().OrderBy(x => x);

            // Get distinct years from the data
            var years = data.Select(x => x.Year).Distinct().OrderBy(x => x);

            // Specify the columns for the DataTable.
            // In this example, it is Market and then a column for each year.
            dataTable.AddColumn("Market", "string");
            foreach (var year in years)
            {
                dataTable.AddColumn(year.ToString(), "number");
            }

            // Specify the rows for the DataTable.
            // Each Market will be its own row, containing the total sales for each year.
            foreach (var market in markets)
            {
                var values = new List<object>(new[] { market });
                foreach (var year in years)
                {
                    var totalSales = data
                        .Where(x => x.Market == market && x.Year == year)
                        .Select(x => x.TotalSales)
                        .SingleOrDefault();
                    values.Add(totalSales);
                }
                dataTable.AddRow(values);
            }

            return dataTable;
        }

        private MarketSales[] GetMarketSalesFromDatabase()
        {
            // Let's pretend this came from a database, via EF, Dapper, or something like that...
            return new MarketSales[]
            {
                new MarketSales() { Market = "Cincinnati", Year = 2013, TotalSales = 723898 },
                new MarketSales() { Market = "Cincinnati", Year = 2014, TotalSales = 898132 },
                new MarketSales() { Market = "Cincinnati", Year = 2015, TotalSales = 941823 },

                new MarketSales() { Market = "Columbus", Year = 2013, TotalSales = 509132 },
                new MarketSales() { Market = "Columbus", Year = 2014, TotalSales = 570913 },
                new MarketSales() { Market = "Columbus", Year = 2015, TotalSales = 460923 },

                new MarketSales() { Market = "Cleveland", Year = 2013, TotalSales = 753939 },
                new MarketSales() { Market = "Cleveland", Year = 2014, TotalSales = 830923 },
                new MarketSales() { Market = "Cleveland", Year = 2015, TotalSales = 910302 },

                new MarketSales() { Market = "Dayton", Year = 2013, TotalSales = 109012 },
                new MarketSales() { Market = "Dayton", Year = 2014, TotalSales = 400302 },
                new MarketSales() { Market = "Dayton", Year = 2015, TotalSales = 492901 }
            };
        }
        
    }
}

Models\GoogleVisualizationDataTable.cs
This is a model class used to get data into the format needed to create a DataTable. It has a few helper methods to simplify adding columns and rows.

using System.Collections.Generic;
using System.Linq;

namespace GoogleChartsExample.Models
{
    // This class is used to facilitate JSON serialization into the format required by Google to create a DataTable.
    // See https://developers.google.com/chart/interactive/docs/reference#DataTable
    public class GoogleVisualizationDataTable
    {
        public IList<Col> cols { get; } = new List<Col>();
        public IList<Row> rows { get; } = new List<Row>();

        public void AddColumn(string label, string type)
        {
            cols.Add(new Col() { label = label, type = type });
        }

        public void AddRow(IList<object> values)
        {
            rows.Add(new Row() { c = values.Select(x => new Row.RowValue() { v = x }) });
        }

        public class Col
        {
            public string label { get; set; }
            public string type { get; set; }
        }

        public class Row
        {
            public IEnumerable<RowValue> c { get; set; }
            public class RowValue
            {
                public object v;
            }
        }
    }
}

Models\MarketSales.cs
This is a model class used to represent the source data, as if it came from Entity Framework or other ORM.

namespace GoogleChartsExample.Models
{
    public class MarketSales
    {
        public string Market { get; set; }
        public int Year { get; set; }
        public decimal TotalSales { get; set; }
    }
}

Models\SalesChartModel.cs
This is the model we bind to the view, containing the title and subtitle of the chart and the datatable data used to construct the chart.

namespace GoogleChartsExample.Models
{
    public class SalesChartModel
    {
        public string Title { get; set; }
        public string Subtitle { get; set; }
        public GoogleVisualizationDataTable DataTable { get; set; }
    }
}

Views\Home\Index.cshtml
This view is what actually builds up the chart and this code is mostly similar to Google’s example code. The main difference is we’re using binding to take our server-side data and put it into client-side JavaScript so that it can be used by Google Charts.

@model GoogleChartsExample.Models.SalesChartModel
<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
    <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
</head>
<body>
    <div> 
        <div id="chart" style="width: 500px; height: 300px;"></div>
        <script type="text/javascript">
            google.charts.load('current', { 'packages': ['corechart', 'bar'] });

            google.charts.setOnLoadCallback(function () {
                var title = '@Model.Title';
                var subtitle = '@Model.Subtitle';
                var dataTable = new google.visualization.DataTable(
                    @Html.Raw(Json.Encode(Model.DataTable))
                );

                drawBarChart('chart', title, subtitle, dataTable);                
                //drawColumnChart('chart', title, dataTable);
            });

            function drawBarChart(elementId, title, subtitle, dataTable) {
                var options = {
                    chart: {
                        title: title,
                        subtitle: subtitle
                    }
                };
                var chart = new google.charts.Bar(document.getElementById(elementId));
                chart.draw(dataTable, options);
            }

            function drawColumnChart(elementId, title, dataTable) {
                var options = {
                    title: title
                };
                var chart = new google.visualization.ColumnChart(document.getElementById(elementId));
                chart.draw(dataTable, options);
            }
        </script>
    </div>
</body>
</html>

I hope this example gives you some ideas about how you can use Google Charts for your ASP.NET MVC applications.

2 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *