Using Google Charts API with an ASP.NET Core 6.0 MVC app

Google Charts is a free JavaScript API that you can use to generate good looking charts on a web page. Although it has nothing to do with C# and .NET, we can still use it in an ASP.NET application. In this article, I will show you how to generate five types of charts to display dynamically generated data. The source of data will be the well known Northwind database running in a Docker container.

In another article, I show how to use Google charts with an ASP.NET Core Razor Pages application.  In this article, I work with the ASP.NET MVC template, instead.

Source code: https://github.com/medhatelmasry/gChartMVC

The environment I am using is:

• Windows 11
• Docker Desktop for Windows
• .NET version 6.0.100
• Visual Studio Code

Start Northwind database in a Docker container

To pull & run the Northwind database in a Docker container, run the following command in a terminal window:

docker run -d --name nw -p 1444:1433 kcornwall/sqlnorthwind

The above command does the following:

Docker image:	kcornwall/sqlnorthwind
Container Name (--name):	nw
Ports (-p):	Port 1433 in container is exposed as port 1444 on the host computer
Password:	The sa password is Passw0rd2018. This was determined from the Docker Hub page for the image.
-d:	Starts the container in detached mode

This is what I experienced after I ran the above command:

Let us make sure that the container is running. Execute this command to ensure that the container is indeed running.

docker ps

The following confirmed to me that the container is running:

Project setup

Run the following command to create an ASP.NET Core MVC application using .NET 6.0 in a folder named gChartMVC:

dotnet new mvc -f net6.0 -o gChartMVC

Change directory into the new folder and open the project inside VS Code with the following commands:

cd gChartMVC 

code .

We will need to install an Entity Framework command-line utility. If you have not done so already, install dotnet-ef with this command:

dotnet tool install –g dotnet-ef 

It does not hurt to upgrade this tool to the latest version with:

dotnet tool update -g dotnet-ef

Also, from within the root folder of your project, add some SQL-Server and Entity Framework related packages with the following terminal-window commands:

dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Tools

In appsettings.json, add this to ConnectionStrings block just before “Logging”:

"ConnectionStrings": {

    "NW": "Data Source=localhost,1444;Initial Catalog=Northwind;Persist Security Info=True;User ID=sa;Password=Passw0rd2018"

},

Next, let us reverse engineer the Products & Categories entities in the Northwind database. Execute this command from the root of your project:

dotnet-ef dbcontext scaffold "Data Source=localhost,1444;Initial Catalog=Northwind;Persist Security Info=True;User ID=sa;Password=Passw0rd2018" Microsoft.EntityFrameworkCore.SqlServer -c NorthwindContext -o NW --table Products --table Categories

This creates a NW folder in your project with entities Category & Product. It also adds the database context class NorthwindContext.

Delete the OnConfiguring() method in NorthwindContext.cs so that there is no hard-coded connection string in our C# code.

Add the following code to Program.cs right after where the variable builder is declared:

var connectionString = builder.Configuration.GetConnectionString("NW");

builder.Services.AddDbContext<NorthwindContext>(options => {

  options.UseSqlServer(connectionString);

});

Reading data

Let's take advantage of dependency injection to access the database through an instance of the NorthwindContext. Add the following instance variable declaration to the top of the HomeController class:

private readonly NorthwindContext _northwindContext;

Update the HomeController constructor so it looks like this:

public HomeController(ILogger<HomeController> logger, NorthwindContext northwindContext) {

  _logger = logger;

  _northwindContext = northwindContext;

}

We will next add a method to the HomeController that can be called from our JavaScript front-end that reads products by category. 

public async Task<JsonResult> ChartData() {

   var query = await _northwindContext.Products

     .Include(c => c.Category)

     .GroupBy(p => p.Category!.CategoryName)

     .Select(g => new

     {

         Name = g.Key,

         Count = g.Count()

     })

     .OrderByDescending(cp => cp.Count)

        .ToListAsync();

   return Json(query);

}

At this stage, let's run our web application and verify that we are indeed able to read data from the Northwind database and subsequently generate JSON data. Run your application with:

dotnet watch run

Point your browser to https://localhost:7108/home/chartdata

NOTE: you will need to adjust the port number to suit your environment.

This is what was revealed in my browser:

We have a sense of assurance that our data is ready to be displayed in a chart.

Charting the data

Replace your Views/Home/Index.cshtml with the following code:

<title>@ViewData["Title"] - Google Charts</title>  
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>  
  
<div id="column_chart_div"></div>  
<div id="line_chart_div"></div>  
<div id="pie_chart_div"></div>  
<div id="area_chart_div"></div>  
<div id="bar_chart_div"></div>  
<script type="text/javascript">  
  
   google.charts.load('current', {  
     packages: ['corechart', 'bar']  
   });  
   google.charts.setOnLoadCallback(LoadData);  
   function LoadData() {  
      $.ajax({  
         url: '/Home/ChartData',  
         dataType: "json",  
         type: "GET",  
         error: function(xhr, status, error) {  
            toastr.error(xhr.responseText);  
         },  
         success: function(data) {  
            PopulationChart(data, "column-chart");  
            PopulationChart(data, "line-chart");  
            PopulationChart(data, "pie-chart");  
            PopulationChart(data, "area-chart"); 
            PopulationChart(data, "bar-chart"); 
            return false;  
         }  
      });  
      return false;  
   }  
   function PopulationChart(data, chart_type) {  
      var dataArray = [  
         ['Category', 'Product']  
      ];  
      $.each(data, function(i, item) {  
         dataArray.push([item.name, item.count]);  
      });  
      var data = google.visualization.arrayToDataTable(dataArray);  
      var options = {  
         title: 'Product count by category',  
         chartArea: {  
             width: '80%'  
         },  
         colors: ['#b0120a', '#7b1fa2', '#ffab91', '#d95f02'],  
         hAxis: {  
             title: 'Categories',  
             minValue: 0  
         },  
         vAxis: {  
             title: 'Product Count'  
         }  
      };  
      var chart;
      switch(chart_type) {
         case "line-chart":
            chart = new google.visualization.LineChart(document.getElementById('line_chart_div'));  
            break;
         case "pie-chart":
            chart = new google.visualization.PieChart(document.getElementById('pie_chart_div'));  
            break;
         case "area-chart":
            chart = new google.visualization.AreaChart(document.getElementById('area_chart_div'));  
            break;
         case "bar-chart":
            chart = new google.visualization.BarChart(document.getElementById('bar_chart_div'));  
            break;
         default:
            chart = new google.visualization.ColumnChart(document.getElementById('column_chart_div'));  
            break;
      }
      chart.draw(data, options);  
      return false;  
   }  
</script>  

If you point your browser to the home page, you should see five charts, namely: column, line, pie, area and bar charts.

Conclusion

I trust the above article helps you consider using Google Charts with ASP.NET MVC apps to visually display data.