Medhat Elmasry: google chart

Showing posts with label google chart. Show all posts

Sunday, January 2, 2022

Google charts with DataTable .NET Wrapper and API data in ASP.NET 6.0 Razor Pages App

You have data from an API (or any other data source like a database) and wish to display the results in a chart. The library we will use for generating charts is the freely available Google Charts JavaScript-based API. The Google DataTable .NET Wrapper is used to create a lightweight representation of the google.visualization.DataTable object directly in Microsoft.NET. The wrapper allows for the creation of the appropriate JSON which is easily ingested by the Google Chart Tools JavaScript library.

I will show you how to generate six types of charts to display dynamically generated data. The source of data will be an API at https://northwind.vercel.app/api/orders that displays orders. I will work with the ASP.NET Razor Pages template (AKA Web App).

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

Companion Video: https://youtu.be/Ie43mv57-5o

The environment I am using is: https://github.com/medhatelmasry/OrdersChartRazorGoogleWrapper

.NET version 6.0.100
Visual Studio Code

The orders API

We will work with the orders API at https://northwind.vercel.app/api/orders. The data in the API is generated from the well known Northwind sample SQL Server database. If you point your browser to the above address, you will see the following:

Since some properties are not useful in this tutorial, we will ignore orderDate, requiredDate, shippedDate, postalCode and details.

Also, note that shipAddress is represented by a sub JSON address object.

Project setup

Run the following command to create an ASP.NET Core Razor Pages application using .NET 6.0 in a folder named OrdersChartRazorGoogleWrapper:

dotnet new razor -f net6.0 -o OrdersChartRazorGoogleWrapper

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

cd OrdersChartRazorGoogleWrapper

code .

Install the Google DataTable .NET Wrapper Nuget package:

dotnet add package Google.DataTable.Net.Wrapper

Address & Order model classes

Create a folder named Models. Add to the Models folder two class files, namely: Address.cs and Order.cs.

The Address class looks like this:

public class Address {
[JsonPropertyName("street")]
public string? Street { get; set; }

[JsonPropertyName("city")]
public string? City { get; set; }

[JsonPropertyName("region")]
public string? Region { get; set; }

[JsonPropertyName("country")]
public string? Country { get; set; }

}

The Order class looks like this:

public class Order {
[JsonPropertyName("id")]
public int Id { get; set; }

[JsonPropertyName("customerId")]
public string? CustomerId { get; set; }

[JsonPropertyName("employeeId")]
public int? EmployeeId { get; set; }

[JsonPropertyName("shipVia")]
public int? ShipVia { get; set; }

[JsonPropertyName("freight")]
public decimal? Freight { get; set; }

[JsonPropertyName("shipName")]
public string? ShipName { get; set; }

[JsonPropertyName("shipAddress")]
public Address ShipAddress { get; set; } = null!;
}

Reading data

In the Pages folder, add two files ChartData.cshtml and ChartData.cshtml.cs.

Content of ChartData.cshtml is:

@page
@model ChartDataModel

Content of ChartData.cshtml.cs is:

using System.Text.Json;
using Google.DataTable.Net.Wrapper;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
using OrdersChartRazorGoogleWrapper.Models;

namespace OrdersChartRazorGoogleWrapper.Pages;

public class ChartDataModel : PageModel {
private readonly ILogger<ChartDataModel> _logger;

public ChartDataModel(ILogger<ChartDataModel> logger) {
_logger = logger;
}

public async Task<IActionResult> OnGet() {
Order[] orders = await GetOrdersAsync();

var data = orders
.GroupBy(_ => _.ShipAddress.City)
.Select(g => new
{
Name = g.Key,
Count = g.Count()
})
.OrderByDescending(cp => cp.Count)
.ToList();

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

foreach (var item in data) {
Row r = dt.NewRow();
r.AddCellRange(new Cell[] {
new Cell(item.Name),
new Cell(item.Count)
});
dt.AddRow(r);
}

//Let's create a Json string as expected by the Google Charts API.
return Content(dt.GetJson());
}

private async Task<Order[]> GetOrdersAsync() {
HttpClient client = new HttpClient();
var stream = client.GetStreamAsync("https://northwind.vercel.app/api/orders");
var orders = await JsonSerializer.DeserializeAsync<Order[]>(await stream);

return orders!;
}
}

The above code in ChartData.cshtml.cs returns a JSON representation of Google.DataTable.Net.Wrapper.DataTable. It contains data from the Orders API representing the number of orders by city.

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

dotnet watch run

Point your browser to https://localhost:7205/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

Let's first generate a simple column-chart. Replace your Pages/Index.cshtml with the following code:

@page
@model IndexModel
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>

google.charts.load('current', {
packages: ['corechart', 'bar']
});
google.setOnLoadCallback(drawChart);

</script>

Point your browser to the home page, you should see a column-chart as follows:

If you want to see more types of charts, replace Pages/Index.cshtml with the following code:

@page

@model IndexModel

google.charts.load('current', {

packages: ['corechart', 'bar']

});

google.setOnLoadCallback(drawChart);

function drawChart() {

var jsonData = $.ajax({

url: '/ChartData',

dataType: "json",

async: false

}).responseText;

PopulationChart(jsonData, "column-chart");

PopulationChart(jsonData, "line-chart");

PopulationChart(jsonData, "pie-chart");

PopulationChart(jsonData, "area-chart");

PopulationChart(jsonData, "bar-chart");

PopulationChart(jsonData, "pie-chart-3d");

}

function PopulationChart(jsonData, chart_type) {

// Create our data table out of JSON data loaded from server.

var data = new google.visualization.DataTable(jsonData);

var chart;

var options = { title: 'Orders by city' };

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;

case "pie-chart-3d":

options.is3D = true;

chart = new google.visualization.PieChart(document.getElementById('pie_chart_3d_div'));

break;

default:

chart = new google.visualization.ColumnChart(document.getElementById('column_chart_div'));

break;

}

chart.draw(data, options);

return false;

}

</script>

You should see six charts on the home page, namely: column, line, pie, area, bar and pie 3D charts.

Conclusion

It is very easy and inexpensive (free) to use Google Charts to generate charts in an ASP.NET Razor application. The .NET Google DataTable wrapper (Google.DataTable.Net.Wrapper) makes it even easier.

Monday, December 20, 2021

Generate charts with Google DataTable .NET Wrapper from ASP.NET 6.0 Razor Pages App

The scenario that this article addresses is a situation whereby there is server-side generated data that needs to be displayed on a web page as a chart. The API used for generating the chart is the freely available Google Charts JavaScript-based API. The Google DataTable .NET Wrapper is used to create a lightweight representation of the google.visualization.DataTable object directly in Microsoft.NET. The wrapper allows for the creation of the appropriate JSON which is easily ingested by the Google Chart Tools JavaScript library.

I will show you how to generate six types of charts to display dynamically generated data. The source of data will be the well known Northwind database running in a Docker container. I will work with the ASP.NET Razor Pages template (AKA Web App).

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

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: