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>

<div id="column_chart_div"></div>
<script type="text/javascript">

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

    function drawChart() {
        var jsonData = $.ajax({
            url: '/ChartData',
            dataType: "json",
            async: false
        }).responseText;
         // Create our data table out of JSON data loaded from server.
        var data = new google.visualization.DataTable(jsonData);
        var options = { title: 'Orders by city' };
        var chart = new google.visualization.ColumnChart(document.getElementById('column_chart_div'));
        chart.draw(data, options);
    }

</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

<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>

<div id="pie_chart_3d_div"></div>

<script type="text/javascript">

    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.

Electron.NET with ASP.NET MVC & EF

In this tutorial I will show you how to develop a simple cross-platform Electron application that retrieves data from the Northwind database and renders results in a chart. The solution also allows you to do the following:

• export data to a CSV file
• setup the solution as a separate desktop application

Source Code: https://github.com/medhatelmasry/ElectronEf

Companion Video: https://youtu.be/kuXFLLVQUQ8

What is Electron?

Electron is a framework that supports development of apps using web technologies such as Chromium rendering engine and Node.js runtime. The platform supports Windows, MacOS and Linux. Some very popular applications that run on Electron are Visual Studio Code, Discord, Skype, GitHub Desktop and many others. The official site for Electron is https://www.electronjs.org/.

What is Electron.NET?

Electron.NET is a wrapper around Electron that allows .NET web developers to invoke native Electron APIs using C#. To develop with Electron.NET, you need Node.js & Npm installed on your computer. In addition, you must have .NET Core 3.1 or later. The official site for Electron.NET open source project is https://github.com/electronnet/electron.net/.

Running a docker container with SQL-Server Northwind sample database

I will use a docker image that contains the SQL-Server Northwind database. Credit goes to kcornwall for creating this docker image.

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 running OK.

docker ps

The following confirms that the container is indeed running:

Setup our application

At the time of writing this article, I was using .NET version 5.0.101 on a Windows 10 computer running version 1909

Let us create an ASP.NET MVC app named ElectronEF with the following terminal window commands:

mkdir ElectronEf

cd ElectronEf

dotnet new mvc

We need three .NET tools. Run the following commands from within a terminal window to install ElectronNET.CLI , dotnet-aspnet-codegenerator and dotnet-ef.

dotnet tool install –g ElectronNET.CLI

dotnet tool install -g dotnet-aspnet-codegenerator

dotnet tool install –g dotnet-ef

Continue by adding these packages to your project:

dotnet add package Microsoft.VisualStudio.Web.CodeGeneration.Design

dotnet add package Microsoft.EntityFrameworkCore.Design

dotnet add package Microsoft.EntityFrameworkCore.SqlServer

dotnet add package ElectronNET.API

dotnet add package C1.AspNetCore.Mvc

ElectronNET.API is the Electron.NET package and C1.AspNetCore.Mvc is a package from a company named ComponentOne that provides  components that we will use (under a short trial license) for creating a visual chart.

Finally, let's open our project in VS Code. To do that, simply execute the following command from the same terminal window:

code .

Open Program.cs in the editor and add the following statements to the CreateHostBuilder() method right before webBuilder.UseStartup<Startup>() : 

webBuilder.UseElectron(args);

webBuilder.UseEnvironment("Development");

Next, open Startup.cs in the editor and add the following statement to the bottom of the Configure() method:

// Open the Electron-Window here

Task.Run (async () => {

  await Electron.WindowManager.CreateWindowAsync ();

});

That's it. Your ASP.NET application is now electron-ized. To see the fruits of your labor, type the following command in the terminal window:

electronize init

electronize start

electronize init is a one-time command that creates a manifest file named electron.manifest.json and adds it to your project. 

electronize start launches the Electron app. Note that it takes a little longer the first time and the content now appears in an application window, not a browser.

Note that you can still run your application as a web app by simply stopping the Electron app (with File >> Exit from the app's menu system) and running the web app with: dotnet run.

Interacting with the Northwind database

Let us reverse engineer the database with the following command so that it generates a DbContext class and classes representing the Category & Product database entities in a folder named NW:

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

Add the following connection string to the top of appsettings.json just before "Logging":

"ConnectionStrings": {

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

},

Open NW/NorthwindContext.cs and delete the OnConfiguring() method so that we do not have confidential connection string information embedded in source code.

Add the following to ConfigureServices() method in Startup.cs:

services.AddDbContext<NorthwindContext>(options => options.UseSqlServer(Configuration.GetConnectionString("NW")));

Rendering a chart 

Add the following instance variable to Controllers/HomeController.cs:

private readonly NorthwindContext _context;

Replace the HomeController constructor with this code:

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

    _logger = logger;

    _context = context;

}

Add the following helper method named getProductsByCategory() that returns a count of products by category from the Northwind database:

private List<object> getProductsByCategory () {

  var query = _context.Products

    .Include (c => c.Category)

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

    .Select (g => new {

        Name = g.Key,

        Count = g.Count ()

    })

    .OrderByDescending (cp => cp.Count);

  return query.ToList<object> ();

}

Add a ProductsByCategory() action method to HomeController.cs:

public IActionResult Chart() {

  ViewBag.CategoryProduct = this.getProductsByCategory ();

  return View ();

} 

To make available the char control to all views, add the following to Views/_ViewImports.cshtml:

@addTagHelper *, C1.AspNetCore.Mvc

We need a view to render the chart. Therefore, execute the following command to create /Views/Home/Chart.cshtml:

dotnet aspnet-codegenerator view Chart Empty -outDir Views/Home –udl

Replace Views/Home/Chart.cshtml with following code:

@{

  ViewData["Title"] = "Number of products by category";

}

<br />

<h1>@ViewData["Title"]</h1>

<div>

  <c1-flex-chart binding-x="Name" chart-type="Bar" legend-position="None">

    <c1-items-source source-collection="@ViewBag.CategoryProduct"></c1-items-source>

    <c1-flex-chart-series binding="Count" name="Count" />

    <c1-flex-chart-axis c1-property="AxisX" position="None" />

    <c1-flex-chart-axis c1-property="AxisY" reversed="true" />

  </c1-flex-chart>

</div>

Add these styles to Views/Shared/_Layout.cshtml just before </head>:

<c1-styles />

<c1-scripts>

   <c1-basic-scripts />

</c1-scripts>

Also in _Layout.cshtml, add the following menu item  at around line 35:

<li class="nav-item">

  <a class="nav-link text-dark" asp-area="" asp-controller="Home"

    asp-action="Chart">Chart</a>

</li>

Run the application by typing the following command in the terminal window:

electronize start

You should see the following output:

Save data to file system as CSV file

Add an action method named SaveAs() to Controllers/HomeController.cs with the following code:

public async Task<IActionResult> SaveAs (string path) {

  System.IO.StringWriter writer = new System.IO.StringWriter ();

  writer.WriteLine ("Name,Count");

  var query = this.getProductsByCategory ();

  query.ForEach (item => {

    writer.Write (item.GetType ().GetProperty ("Name").GetValue (item));

    writer.Write (",");

    writer.WriteLine (item.GetType ().GetProperty ("Count").GetValue (item));

  });

  await System.IO.File.WriteAllTextAsync (path, writer.ToString ());

  return RedirectToAction ("Index");

}

Menu customization

Electron.NET provides a default application menu. Note that there are differences between macOS and other platforms. On macOS, applications have their own menu to the left of the standard File/Edit/View menus.

Add the following using statements at the top of Startup.cs:

using ElectronNET.API.Entities;

using System.Runtime.InteropServices;

Add this CreateMenu() method to Startup.cs:

private void CreateMenu () {

  bool isMac = RuntimeInformation.IsOSPlatform (OSPlatform.OSX);

  MenuItem[] menu = null;

  MenuItem[] appMenu = new MenuItem[] {

    new MenuItem { Role = MenuRole.about },

    new MenuItem { Type = MenuType.separator },

    new MenuItem { Role = MenuRole.services },

    new MenuItem { Type = MenuType.separator },

    new MenuItem { Role = MenuRole.hide },

    new MenuItem { Role = MenuRole.hideothers },

    new MenuItem { Role = MenuRole.unhide },

    new MenuItem { Type = MenuType.separator },

    new MenuItem { Role = MenuRole.quit }

  };

  MenuItem[] fileMenu = new MenuItem[] {

    new MenuItem {

      Label = "Save As...", Type = MenuType.normal, Click = async () => {

        var mainWindow = Electron.WindowManager.BrowserWindows.First ();

        var options = new SaveDialogOptions () {

          Filters = new FileFilter[] {

            new FileFilter { Name = "CSV Files", Extensions = new string[] { "csv" } }

          }

        };

        string result = await Electron.Dialog.ShowSaveDialogAsync (mainWindow, options);

        if (!string.IsNullOrEmpty (result)) {

          string url = $"http://localhost:{BridgeSettings.WebPort}/Home/SaveAs?path={result}";

          mainWindow.LoadURL (url);

        }

      }

    },

    new MenuItem { Type = MenuType.separator },

    new MenuItem { Role = isMac ? MenuRole.close : MenuRole.quit }

  };

  MenuItem[] viewMenu = new MenuItem[] {

    new MenuItem { Role = MenuRole.reload },

    new MenuItem { Role = MenuRole.forcereload },

    new MenuItem { Role = MenuRole.toggledevtools },

    new MenuItem { Type = MenuType.separator },

    new MenuItem { Role = MenuRole.resetzoom },

    new MenuItem { Role = MenuRole.zoomin },

    new MenuItem { Role = MenuRole.zoomout },

    new MenuItem { Type = MenuType.separator },

    new MenuItem { Role = MenuRole.togglefullscreen }

  };

  if (isMac) {

    menu = new MenuItem[] {

      new MenuItem { Label = "Electron", Type = MenuType.submenu, Submenu = appMenu },

      new MenuItem { Label = "File", Type = MenuType.submenu, Submenu = fileMenu },

      new MenuItem { Label = "View", Type = MenuType.submenu, Submenu = viewMenu }

    };

  } else {

    menu = new MenuItem[] {

      new MenuItem { Label = "File", Type = MenuType.submenu, Submenu = fileMenu },

      new MenuItem { Label = "View", Type = MenuType.submenu, Submenu = viewMenu }

    };

  }

  Electron.Menu.SetApplicationMenu (menu);

}

Add following statement in Configure() method of Startup.cs just before await Electron.WindowManager.CreateWindowAsync():

CreateMenu();

Test the save-as functionality by starting the Electron app with the following terminal-window command:

electronize start

Click on File >> Save As ...

Select a location and give the export file a name (like data), then click on save. The content of data.csv should look like this:

Build for specific platform:

You can produce a setup application for Windows, macOS & Linux. To generate the setup application for Windows, execute the following command from a terminal window:

electronize build /target win /PublishReadyToRun false 

The result is a setup application located in bin/Desktop that you can distribute. Be patient because it takes time to generate.

If you run the setup exe file, it will install a desktop application on your computer that you can easily uninstall.

I hope you found this article useful and hope you build great Electron.NET apps.

Reference:

    https://www.grapecity.com/blogs/building-cross-platform-desktop-apps-with-electron-dot-net