ASP.NET MVC 5 - Google Charts API Integration

Today, I shall be demonstrating the integration of Google Charts API with ASP.NET MVC5.

Understanding data is never a simple process; it involves a lot of pre and post-processing in order to make sense out of the data that has been collected. After understanding the data and extracting meaningful information, the next step is the presentation of the data, i.e., how to present the data to make analytical sense out of it which ultimately leads to better decision making. Graphs are one form of representing the data which helps in making meaningful reports for analytical purposes that eventually helps stakeholders to make a better decision.

Today, I shall be demonstrating the integration of Google Charts API with ASP.NET MVC5. Google Charts API is simple to use and provides a variety of options for customization of graphical chart reports for better analytics.



Prerequisites

Following are some prerequisites before you proceed further in this tutorial.

  1. Knowledge of Google Charts API.
  2. Knowledge of ASP.NET MVC5.
  3. Knowledge of HTML.
  4. Knowledge of Javascript.
  5. Knowledge of AJAX.
  6. Knowledge of CSS.
  7. Knowledge of Bootstrap.
  8. Knowledge of C# programming.
  9. Knowledge of C# LINQ.
  10. Knowledge of jQuery.

You can download the complete source code for this tutorial or you can follow the step by step discussion below. The sample code is developed in Microsoft Visual Studio 2015 Enterprise. I am using SalesOrderDetail table extracted from Adventure Works Sample Database.

Let's begin now.

Step 1

Create a new MVC5 web application project and name it as "Graphs".

Step 2

Open "Views\Shared\_Layout.cshtml" file and replace the relevant part of code with the following code in it.

  1. <!DOCTYPE html>  
  2. <html>  
  3. <head>  
  4.     <meta charset="utf-8" />  
  5.     <meta name="viewport" content="width=device-width, initial-scale=1.0">  
  6.     <title>@ViewBag.Title</title>  
  7.     @Styles.Render("~/Content/css")  
  8.     @Scripts.Render("~/bundles/modernizr")  
  9.   
  10.     <!-- Font Awesome -->  
  11.     <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/font-awesome/4.4.0/css/font-awesome.min.css" />  
  12.   
  13.     @* Custom *@  
  14.     @Styles.Render("~/Content/css/custom-style")  
  15. </head>  
  16. <body>  
  17.     <div class="navbar navbar-inverse navbar-fixed-top">  
  18.         <div class="container">  
  19.             <div class="navbar-header">  
  20.                 <button type="button" class="navbar-toggle" data-toggle="collapse" data-target=".navbar-collapse">  
  21.                     <span class="icon-bar"></span>  
  22.                     <span class="icon-bar"></span>  
  23.                     <span class="icon-bar"></span>  
  24.                 </button>  
  25.             </div>  
  26.         </div>  
  27.     </div>  
  28.     <div class="container body-content">  
  29.         @RenderBody()  
  30.         <hr />  
  31.         <footer>  
  32.             <center>  
  33.                 <p><strong>Copyright © @DateTime.Now.Year - <a href="http://www.asmak9.com/">Asma's Blog</a>.</strong> All rights reserved.</p>  
  34.             </center>  
  35.         </footer>  
  36.     </div>  
  37.   
  38.     @Scripts.Render("~/bundles/jquery")  
  39.     @Scripts.Render("~/bundles/bootstrap")  
  40.   
  41.     <!-- Graphs -->  
  42.     <script type="text/javascript" src="https://www.google.com/jsapi"></script>  
  43.     @Scripts.Render("~/bundles/Script-custom-graphs")  
  44.   
  45.     @RenderSection("scripts", required: false)  
  46. </body>  
  47. </html> 
In the above code, I have simply created the basic layout structure of this web project and I have also added reference to the Google Charts API.

Step 3

Create a new "Models\HomeViewModels.cs" file and replace the code with the following.

  1. using System.Collections.Generic;  
  2. using System.ComponentModel.DataAnnotations;  
  3.   
  4. namespace Graphs.Models  
  5. {  
  6.     public class SalesOrderDetail  
  7.     {  
  8.         public int Sr { getset; }  
  9.         public string OrderTrackNumber { getset; }  
  10.         public int Quantity { getset; }  
  11.         public string ProductName { getset; }  
  12.         public string SpecialOffer { getset; }  
  13.         public double UnitPrice { getset; }  
  14.         public double UnitPriceDiscount { getset; }  
  15.     }  

In the above code, we have simply created our View Model which will map the data from text file into main memory as object.

Step 4

Now create "Controllers\HomeController.cs" file and replace the code with the following code.

  1. using Graphs.Models;  
  2. using System;  
  3. using System.Collections.Generic;  
  4. using System.IO;  
  5. using System.Linq;  
  6. using System.Reflection;  
  7. using System.Web;  
  8. using System.Web.Mvc;  
  9.   
  10. namespace Graphs.Controllers  
  11. {  
  12.     public class HomeController : Controller  
  13.     {  
  14.         #region Index method  
  15.   
  16.         /// <summary>  
  17.         /// GET: Home/Index method.  
  18.         /// </summary>  
  19.         /// <returns>Returns - index view page</returns>   
  20.         public ActionResult Index()  
  21.         {  
  22.             // Info.  
  23.             return this.View();  
  24.         }  
  25.  
  26.         #endregion  
  27.  
  28.         #region Get data method.  
  29.   
  30.         /// <summary>  
  31.         /// GET: /Home/GetData  
  32.         /// </summary>  
  33.         /// <returns>Return data</returns>  
  34.         public ActionResult GetData()  
  35.         {  
  36.             // Initialization.  
  37.             JsonResult result = new JsonResult();  
  38.   
  39.             try  
  40.             {  
  41.                 // Loading.  
  42.                 List<SalesOrderDetail> data = this.LoadData();  
  43.   
  44.                 // Setting.  
  45.                 var graphData = data.GroupBy(p => new  
  46.                                     {  
  47.                                         p.ProductName,  
  48.                                         p.Quantity,  
  49.                                         p.UnitPrice  
  50.                                     })  
  51.                                     .Select(g => new  
  52.                                     {  
  53.                                         g.Key.ProductName,  
  54.                                         g.Key.Quantity,  
  55.                                         g.Key.UnitPrice  
  56.                                     }).OrderByDescending(q => q.Quantity).ToList();  
  57.   
  58.                 // Top 10  
  59.                 graphData = graphData.Take(10).Select(p => p).ToList();  
  60.   
  61.                 // Loading drop down lists.  
  62.                 result = this.Json(graphData, JsonRequestBehavior.AllowGet);  
  63.             }  
  64.             catch (Exception ex)  
  65.             {  
  66.                 // Info  
  67.                 Console.Write(ex);  
  68.             }  
  69.   
  70.             // Return info.  
  71.             return result;  
  72.         }  
  73.  
  74.         #endregion  
  75.  
  76.         #region Helpers  
  77.  
  78.         #region Load Data  
  79.   
  80.         /// <summary>  
  81.         /// Load data method.  
  82.         /// </summary>  
  83.         /// <returns>Returns - Data</returns>  
  84.         private List<SalesOrderDetail> LoadData()  
  85.         {  
  86.             // Initialization.  
  87.             List<SalesOrderDetail> lst = new List<SalesOrderDetail>();  
  88.   
  89.             try  
  90.             {  
  91.                 // Initialization.  
  92.                 string line = string.Empty;  
  93.                 string srcFilePath = "Content/files/SalesOrderDetail.txt";  
  94.                 var rootPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().CodeBase);  
  95.                 var fullPath = Path.Combine(rootPath, srcFilePath);  
  96.                 string filePath = new Uri(fullPath).LocalPath;  
  97.                 StreamReader sr = new StreamReader(new FileStream(filePath, FileMode.Open, FileAccess.Read));  
  98.   
  99.                 // Read file.  
  100.                 while ((line = sr.ReadLine()) != null)  
  101.                 {  
  102.                     // Initialization.  
  103.                     SalesOrderDetail infoObj = new SalesOrderDetail();  
  104.                     string[] info = line.Split(',');  
  105.   
  106.                     // Setting.  
  107.                     infoObj.Sr = Convert.ToInt32(info[0].ToString());  
  108.                     infoObj.OrderTrackNumber = info[1].ToString();  
  109.                     infoObj.Quantity = Convert.ToInt32(info[2].ToString());  
  110.                     infoObj.ProductName = info[3].ToString();  
  111.                     infoObj.SpecialOffer = info[4].ToString();  
  112.                     infoObj.UnitPrice = Convert.ToDouble(info[5].ToString());  
  113.                     infoObj.UnitPriceDiscount = Convert.ToDouble(info[6].ToString());  
  114.   
  115.                     // Adding.  
  116.                     lst.Add(infoObj);  
  117.                 }  
  118.   
  119.                 // Closing.  
  120.                 sr.Dispose();  
  121.                 sr.Close();  
  122.             }  
  123.             catch (Exception ex)  
  124.             {  
  125.                 // info.  
  126.                 Console.Write(ex);  
  127.             }  
  128.   
  129.             // info.  
  130.             return lst;  
  131.         }  
  132.  
  133.         #endregion  
  134.  
  135.         #endregion  
  136.     }  

In the above code, I have created a simple index() action method along with a helper method LoadData() for data loading from text file and finally, GetData() action method which will be called by Google Charts API AJAX method in order to map the data on the chart. The GetData() action method will return top 10 rows only which are sorted by product quantity and group by product name.

Step 5

Create a new "Scripts\script-custom-graphs.js" script file and replace the code with the following one.

  1. // Load the Visualization API and the piechart package.  
  2. google.load('visualization''1.0', { 'packages': ['corechart'] });  
  3.   
  4. // Set a callback to run when the Google Visualization API is loaded.  
  5. $(document).ready(function ()  
  6. {  
  7.     $.ajax(  
  8.     {  
  9.         type: 'POST',  
  10.         dataType: 'JSON',  
  11.         url: '/Home/GetData',  
  12.         success:  
  13.             function (response)  
  14.             {  
  15.                 // Set chart options  
  16.                 var options =  
  17.                     {  
  18.                         width: 1100,  
  19.                         height: 900,  
  20.                         sliceVisibilityThreshold: 0,  
  21.                         legend: { position: "top", alignment: "end" },  
  22.                         chartArea: { left: 370, top: 50, height: "90%" },  
  23.                         hAxis:  
  24.                             {  
  25.                                 slantedText: true,  
  26.                                 slantedTextAngle: 18  
  27.                             },  
  28.                         bar: { groupWidth: "50%" },  
  29.                     };  
  30.   
  31.                 // Draw.  
  32.                 drawGraph(response, options, 'graphId');  
  33.             }  
  34.     });  
  35. });  
  36.   
  37. // Callback that creates and populates a data table,  
  38. // instantiates the pie chart, passes in the data and  
  39. // draws it.  
  40. function drawGraph(dataValues, options, elementId) {  
  41.     // Initialization.  
  42.     var data = new google.visualization.DataTable();  
  43.   
  44.     // Setting.  
  45.     data.addColumn('string''Product Name');  
  46.     data.addColumn('number''Unit Price');  
  47.     data.addColumn('number''Quantity');  
  48.   
  49.     // Processing.  
  50.     for (var i = 0; i < dataValues.length; i++)  
  51.     {  
  52.         // Setting.  
  53.         data.addRow([dataValues[i].ProductName, dataValues[i].UnitPrice, dataValues[i].Quantity]);  
  54.     }  
  55.   
  56.     // Setting label.  
  57.     var view = new google.visualization.DataView(data);  
  58.     view.setColumns([0, 1,  
  59.         {  
  60.             calc: "stringify",  
  61.             sourceColumn: 1,  
  62.             type: "string",  
  63.             role: "annotation"  
  64.         },  
  65.         2,  
  66.         {  
  67.             calc: "stringify",  
  68.             sourceColumn: 2,  
  69.             type: "string",  
  70.             role: "annotation"  
  71.         }  
  72.     ]);  
  73.   
  74.     // Instantiate and draw our chart, passing in some options.  
  75.     var chart = new google.visualization.BarChart(document.getElementById(elementId));  
  76.   
  77.     // Draw chart.  
  78.     chart.draw(view, options);  

Let's break down the code chunk by chunk. First, I have loaded the Google Charts API charts visualization package.

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

Then, I call the GetData() server side method via AJAX call and after successfully receiving the data. I simply set the default chart options then passed those options to a user-defined JavaScript method "drawGraph(...)".

  1. // Set a callback to run when the Google Visualization API is loaded.  
  2. $(document).ready(function ()  
  3. {  
  4.     $.ajax(  
  5.     {  
  6.         type: 'POST',  
  7.         dataType: 'JSON',  
  8.         url: '/Home/GetData',  
  9.         success:  
  10.             function (response)  
  11.             {  
  12.                 // Set chart options  
  13.                 var options =  
  14.                     {  
  15.                         width: 1100,  
  16.                         height: 900,  
  17.                         sliceVisibilityThreshold: 0,  
  18.                         legend: { position: "top", alignment: "end" },  
  19.                         chartArea: { left: 370, top: 50, height: "90%" },  
  20.                         hAxis:  
  21.                             {  
  22.                                 slantedText: true,  
  23.                                 slantedTextAngle: 18  
  24.                             },  
  25.                         bar: { groupWidth: "50%" },  
  26.                     };  
  27.   
  28.                 // Draw.  
  29.                 drawGraph(response, options, 'graphId');  
  30.             }  
  31.     });  
  32. }); 

Now, in the below drwGraph(...) method code, I add three new columns per row. The 0th column will be the name of the products which will be shown on the chart axis, 1st column will be the unit price of the product which wil be shown on the graph, and the 2nd column will be the quantity of the product which will also be shown on the graph for each product. After adding the column metadata for the chart, I will convert the received data from the server into DataTables data type accepted by the chart. Then, I will set the annotation option for the first and second column which will display the correspondent values on the chart columns per each product. Finally, I will draw the BarChart by calling Google charts API method i.e.

  1. // Callback that creates and populates a data table,  
  2. // instantiates the pie chart, passes in the data and  
  3. // draws it.  
  4. function drawGraph(dataValues, options, elementId) {  
  5.     // Initialization.  
  6.     var data = new google.visualization.DataTable();  
  7.   
  8.     // Setting.  
  9.     data.addColumn('string''Product Name');  
  10.     data.addColumn('number''Unit Price');  
  11.     data.addColumn('number''Quantity');  
  12.   
  13.     // Processing.  
  14.     for (var i = 0; i < dataValues.length; i++)  
  15.     {  
  16.         // Setting.  
  17.         data.addRow([dataValues[i].ProductName, dataValues[i].UnitPrice, dataValues[i].Quantity]);  
  18.     }  
  19.   
  20.     // Setting label.  
  21.     var view = new google.visualization.DataView(data);  
  22.     view.setColumns([0, 1,  
  23.         {  
  24.             calc: "stringify",  
  25.             sourceColumn: 1,  
  26.             type: "string",  
  27.             role: "annotation"  
  28.         },  
  29.         2,  
  30.         {  
  31.             calc: "stringify",  
  32.             sourceColumn: 2,  
  33.             type: "string",  
  34.             role: "annotation"  
  35.         }  
  36.     ]);  
  37.   
  38.     // Instantiate and draw our chart, passing in some options.  
  39.     var chart = new google.visualization.BarChart(document.getElementById(elementId));  
  40.   
  41.     // Draw chart.  
  42.     chart.draw(view, options);  

Step 
6

Create "Views\Home\_ViewGraphPartial.cshtml" & "Views\Home\Index.cshtml" files and replace the code with the following.

Views\Home\_ViewGraphPartial.cshtml

  1. <section>  
  2.     <div class="well bs-component">  
  3.         <div class="row">  
  4.             <div class="col-xs-12">  
  5.                 <!-- CHART -->  
  6.                 <div class="box box-primary">  
  7.                     <div class="box-header with-border">  
  8.                         <h3 class="box-title custom-heading">Product wise Graph</h3>  
  9.                     </div>  
  10.                     <div class="box-body">  
  11.                         <div class="chart">  
  12.                             <div id="graphId" style="width: 1100px; height: 900px; margin:auto;"></div>  
  13.                         </div>  
  14.                     </div><!-- /.box-body -->  
  15.                 </div><!-- /.box -->  
  16.             </div>  
  17.         </div>  
  18.     </div>  
  19. </section> 
View\Home\Index.cshtml

  1. @{  
  2.     ViewBag.Title = "ASP.NET MVC5 - Google Graph Integration";  
  3. }  
  4.   
  5. <div class="row">  
  6.     <div class="panel-heading">  
  7.         <div class="col-md-8  custom-heading3">  
  8.             <h3>  
  9.                 <i class="fa fa-pie-chart"></i>  
  10.                 <span>ASP.NET MVC5 - Google Graph Integration</span>  
  11.             </h3>  
  12.         </div>  
  13.     </div>  
  14. </div>  
  15.   
  16. <div class="row">  
  17.     <section class="col-md-12 col-md-push-0">  
  18.         @Html.Partial("_ViewGraphPartial")  
  19.     </section>  
  20. </div> 

In the above code, I have simply create the view code for the page which will display the chart. I have divided the page into two parts for better manageability. Notice that, in the "Views\Home\_ViewGraphPartial.cshtml" file, I have added width & height values for the graph div i.e.

  1. <div id="graphId" style="width: 1100px; height: 900px; margin:auto;"></div> 

Above setting is important in order to properly set the chart area on the HTML page. Same width & height is set with chart options as well in the JavaScript file.

  1. var options =  
  2.      {  
  3.          width: 1100,  
  4.          height: 900,  
  5.          sliceVisibilityThreshold: 0,  
  6.          legend: { position: "top", alignment: "end" },  
  7.          chartArea: { left: 370, top: 50, height: "90%" },  
  8.          hAxis:  
  9.              {  
  10.                  slantedText: true,  
  11.                  slantedTextAngle: 18  
  12.              },  
  13.          bar: { groupWidth: "50%" },  
  14.      }; 

Play around with rest of the properties to understand chart options better.

Step 7

Execute the project and you will be able to see the following screen.



Conclusion

In this article, we learned how to integrate Google Charts API into ASP.NET MVC 5 project. We also learned about passing the data to front view through AJAX call and saw how to represent our data as a graphical entity in order to perform analysis for better decision making.