Saturday, January 22, 2022

docker-compose with ASP.NET 6.0 & SQL Server

This article shows you how to create a docker-ized ASP.NET 6.0 web app that uses SQL Server. The end result will be a solution that you can run using "docker-compose up". I will, thereafter, show you how you can deploy your solution to Azure.

Source code: https://github.com/medhatelmasry/AspMsSQL-docker-compose

Assumptions

  • .NET 6.0 is installed on your computer. 
  • Docker is installed on your computer. 

Getting Started

We will create an ASP.NET 6.0 razor pages application that uses individual authentication with the following command:

dotnet new razor -f net6.0 --auth individual -o AspMsSQL --use-local-db

Thereafter, go into the new folder with:

cd AspMsSQL 

To run the web application and see what it looks like, enter the following command:

dotnet run

You will see a message in the terminal window that resembles the following:



The above message indicates that the Kestrel web server is running and listening on port 7258 (https) and port 5231 (http). Start your browser and enter the appropriate URL. You should see a page that looks like this:


Close your browser and stop the web server in the terminal window by hitting CTRL C.

Let us configure our web application to use SQL Server with environment variables. Open the Program.cs file in your favorite editor and comment out (or delete) the following statement at around line 8:

// var connectionString = builder.Configuration.GetConnectionString("DefaultConnection");
// builder.Services.AddDbContext<ApplicationDbContext>(options =>
//     options.UseSqlServer(connectionString));

Replace the above code with the following:

var host = builder.Configuration["DBHOST"] ?? "localhost";
var port = builder.Configuration["DBPORT"] ?? "1444";
var user = builder.Configuration["DBUSER"] ?? "sa";
var pwd = builder.Configuration["DBPASSWORD"] ?? "SqlPassword!";
var db = builder.Configuration["DBNAME"] ?? "YellowDB";

var conStr = $"Server=tcp:{host},{port};Database={db};UID={user};PWD={pwd};";

builder.Services.AddDbContext<ApplicationDbContext>(options => options.UseSqlServer(conStr));

Environment variables are used in the database connection string. These are: DBHOST, DBPORT, DBUSER, DBPASSWORD and DBNAME. If these environment variables are not found then they will take default values: localhost, 1444, sa, SqlPassword! and YellowDB respectively.

Next, add the following code, also in Program.cs, right before the last statement
app.Run():

using (var scope = app.Services.CreateScope()) {
    var services = scope.ServiceProvider;

    var context = services.GetRequiredService<ApplicationDbContext>();    
    context.Database.Migrate();
}

The above code will apply any outstanding database migrations.

There is, of course, something major that is missing I.E. we do not have a SQL Server database server yet. Let us have a Docker container for our SQL Server database server. Run the following command from any terminal window:

docker run --cap-add SYS_PTRACE -e ACCEPT_EULA=1 -e MSSQL_SA_PASSWORD=SqlPassword! -p 1444:1433 --name azsql -d mcr.microsoft.com/azure-sql-edge

If you do not already have the docker image for SQL Server, it will be downloaded. Thereafter, a container will be made from that image and will run in background mode. To prove that the container was indeed created from the image and is active, run this command:

docker ps -a

You will see output that looks like this:

CONTAINER ID   IMAGE                              COMMAND                  CREATED          STATUS          PORTS                              NAMES
fde883706160   mcr.microsoft.com/azure-sql-edge   "/opt/mssql/bin/perm…"   25 seconds ago   Up 24 seconds   1401/tcp, 0.0.0.0:1444->1433/tcp   azsql

Now, let us test our application and see whether or not it is able to talk to the containerized SQL Server database server. 

Run the web application with the following terminal command:

dotnet run

If all goes well, you will see a message that indicates that the web server is listening on a specified port. Point your browser to http://localhost:#### (where #### is the appropriate port number). The same web page will appear as before. Click on the Register link on the top right side.


I entered an Email, Password and Confirm password then clicked on the Register button. I was then presented with the “Register confirmation” page.


Click on the “Click here to confirm your account” link to make the app accept the login credentials that you had just entered. This is what you will see:


You can now login with the registered credentials:


I was then rewarded with the following confirmation that the credentials were saved in the SQL Server database server:


The message on the top right side confirmed that the user was saved and that communication between my ASP.NET application and SQL Server is working as expected.

Docker-izing an ASP.NET and SQL Server App

Stop the web server by hitting CTRL+C in the terminal window.

We will generate the release version of the application by executing the following command from a terminal window in the root directory of your ASP.NET project:

dotnet publish -o dist

The above command instructs the dotnet utility to produce the release version of the application in the dist directory. This results in output similar to the following:

Microsoft (R) Build Engine version 17.0.0+c9eb9dd64 for .NET
Copyright (C) Microsoft Corporation. All rights reserved.

  Determining projects to restore...
  All projects are up-to-date for restore.
  AspMsSQL -> E:\_playground\0000\AspMsSQL\bin\Debug\net6.0\AspMsSQL.dll
  AspMsSQL -> E:\_playground\0000\AspMsSQL\dist\


The highlighted file in the above screen-capture is my main DLL file that is the entry point into the web application.

Let us run the release version of your application. To do this, change directory to the dist directory with the following terminal instruction:

cd dist

You can then run your main DLL file. In my case, this file is AspMsSQL.dll. I executed the following command:

dotnet AspMsSQL.dll

This should run the web application just as it did before.

Stop & remove the SQL Server docker container with the following command:

docker rm -f azsql

We have a good idea about what ASP.NET artifacts need to be copied into a container. We will simply copy contents of the dist directory into a Docker container that has the dotnet core runtime.

Return to the root directory of your project by typing the following in a terminal window:

cd ..

We need to create a docker image that will contain the dotnet core 6.0 runtime. A suitable image for this purpose is: mcr.microsoft.com/dotnet/aspnet:6.0

Create a text file named Dockerfile and add to it the following content:

FROM mcr.microsoft.com/dotnet/aspnet:6.0
COPY dist /app
WORKDIR /app
EXPOSE 80/tcp
ENTRYPOINT ["dotnet", "AspMsSQL.dll"]

Above are instructions to create a Docker image that will contain our ASP.NET 6.0 application. I describe each line below:

FROM mcr.microsoft.com/dotnet/aspnet:6.0 Base image mcr.microsoft.com/dotnet/aspnet:6.0 will be used
COPY dist /app Contents of the dist directory on the host computer will be copied to directory /app in the container
WORKDIR /app The working directory in the container is /app
EXPOSE 80/tcp Port 80 will be exposed in the container
ENTRYPOINT ["dotnet", "AspMsSQL.dll"] The main ASP.NET web application will be launched by executing "dotnet AspMsSQL.dll"

We will next compose a docker yml file that orchestrates the entire system which involves two containers: a SQL Server database server container and a container that holds our application. In the root folder of your application, create a text file named docker-compose.yml and add to it the following content:

version: '3.8'

services:
  db:
    image: mcr.microsoft.com/azure-sql-edge
    
    volumes:
      - sqlsystem:/var/opt/mssql/
      - sqldata:/var/opt/sqlserver/data
      - sqllog:/var/opt/sqlserver/log
      - sqlbackup:/var/opt/sqlserver/backup

    ports:
      - "1433:1433"
    restart: always
    
    environment:
      ACCEPT_EULA: Y
      MSSQL_SA_PASSWORD: SqlPassword!

  webapp:
    build:
      context: .
      dockerfile: Dockerfile
    depends_on:
      - db
    ports:
      - "8888:80"
    restart: always
    environment:
      - DBHOST=db
      - DBPORT=1433
      - DBUSER=sa
      - DBPASSWORD=SqlPassword!
      - DBNAME=YellowDB
      - ASPNETCORE_ENVIRONMENT=Development

volumes:
  sqlsystem:
  sqldata:
  sqllog:
  sqlbackup:

What does the above do?

We will be having two containers. Each container is considered a service. The first service is named db and will host SQL Server. The second service is named webapp and will host our ASP.NET web app.

The most current version of docker-compose is version 3.8. This is the first line in our docker-compose file.

1) The SQL Server Container

Image mcr.microsoft.com/dotnet/aspnet:6.0 will be used for the SQL Server container.

Volumes named sqlsystem, sqldata, sqllog and sqlbackup are declared that will host SQL Server data outside of the container. This ensures that even if the SQL Server container is decommissioned, data will not be lost.

restart: always is so that if the container stops, it will be automatically restarted.

The root password will be SqlPassword! when SQL Server is configured. This is set by the MSSQL_SA_PASSWORD environment variable.

2) The ASP.NET 6.0 Web Application Container

The container will be built using the instructions in the Dockerfile file and the context used is the current directory.

depends_on indicates that the web app relies on the SQL Server container (db) to properly function.
Port 80 in the webapp container is mapped to port 8888 on the host computer.

Just like in the db container, restart: always is so that if the container stops, it will be automatically restarted.

The environment variables needed by the web app are:

- DBHOST points to the SQL Server service
- DBPORT uses the port number that SQL Server is listening on
- DBUSER is sa
- DBPASSWORD is the SA password SqlPassword!
- DBNAME is the database name, set to YellowDB
- ASPNETCORE_ENVIRONMENT set to Development more. In reality, you should change this to Production one you determine that your web app container works as expected

Running the docker-compose.yml file

To find out if this all works, go to a terminal window and run the following command:

docker-compose up

Point your browser to http://localhost:8888/ and you should see the main web page. To ensure that the database works properly, register a user by clicking on the Register link in the top right corner, confirm the email address, then login.

In my case, I received confirmation that a user was indeed registered:

As you can see in the top-right corner, the user with email a@a.a has been successfully registered.

Cleanup

Let's shutdown and cleanup resources on our computer.

Inside of the terminal window that is running docker-compose, hit Ctrl C on your keyboard to stop the services. Thereafter, enter the following terminal command:

docker-compose down

To remove the webapp docker image, type:

docker rmi -f aspmssql_webapp

To remove all the volumes that were created on your computer, typw:

docker volume rm aspmssql_sqlbackup
docker volume rm aspmssql_sqldata
docker volume rm aspmssql_sqllog
docker volume rm aspmssql_sqlsystem

Conclusion

The same concepts covered in this tutorial can help you create multiple containers that involve two or more services. For example, you may wish to create three containers comprising:
  1. a database
  2. a backend WebAPI application
  3. a frontend web app developed in React or Blazor
It is my hope that this opens up for you a new world in containerizing your applications.


Saturday, January 15, 2022

Creating a MySQL Database with EF Core in .NET 6.0

In this tutorial I will show how to create a simple .NET 6 console application that interacts with data in MySQL version 8.0.0 using Entity Framework. I will be using the official Connector/NET for Entity Framework driver. The NuGet package is MySql.EntityFrameworkCore Nuget located at https://www.nuget.org/packages/MySql.EntityFrameworkCore/

Instead of installing and running a MySQL instance on my computer, I will run MySQL in a docker container for simplicity.

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

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

This article assumes the following:

  1. You have .NET 6.0 installed on your computer. 
  2. You have Docker installed on your computer. 

Setting up the MySQL 8.0.0 container

To download the MySQL version 8.0.0 image from Docker Hub and run it on your local computer, type the following command from within a terminal window:

docker run -p 3333:3306 --name db -e MYSQL_ROOT_PASSWORD=secret -d mysql:8.0.0

This starts a container named 'db' that listens on port 3333 on your local computer. The root password is 'secret'.

To ensure that the MySQL container is running, type the following from within a terminal window:

docker ps

You will see a message similar to the following:

CONTAINER ID   IMAGE         COMMAND                  CREATED        STATUS        PORTS                    NAMES

53e55f4991df   mysql:8.0.0   "docker-entrypoint.s…"   45 hours ago   Up 45 hours   0.0.0.0:3333->3306/tcp   db

Creating a console app

In a working directory, run the following command to create a console application named MySqlOnFire using .NET 6.0:

dotnet new console -f net6.0 -o MySqlOnFire 

Change directory to the newly created folder with:

cd MySqlOnFire

There is only one NuGet package that is needed to talk to MySQL. At the time of writing this article, the version of the MySql.EntityFrameworkCore package that supports .NET 6 is 6.0.0-preview3.1. Add the package by typing the following command from within a terminal window:

dotnet add package MySql.EntityFrameworkCore -v 6.0.0-preview3.1

Model Classes

We will be modeling the following Publisher & Book entities:

In .NET 6.0, we can create a global using file. This helps keep our code minimal. Let us take advantage of this feature. Add a file named GlobalUsings.cs and add to it the following code:

global using System.Text;
global using Microsoft.EntityFrameworkCore; 
global using System.ComponentModel.DataAnnotations.Schema;

Create a file named Publisher.cs and add to it the following class code:

public class Publisher {
    public int PublisherId { get; set; }
    public string? Name { get; set; }
    public virtual ICollection<Book>? Books { get; set; }

    public override string ToString() {
        var txt = new StringBuilder();
        txt.AppendLine($"ID: {PublisherId}");
        txt.AppendLine($"Publisher: {Name}");
        
        return txt.ToString();
    }
}

In the above code, we define the properties of the Publisher class and a ToString() method.

Create another class named Book.cs and add to it the following class code:

public class Book {
    public string? ISBN { get; set; }
    public string? Title { get; set; }
    public string? Author { get; set; }
    public string? Language { get; set; }
    public int Pages { get; set; }

    public int PublisherId { get; set; }

    [ForeignKey("PublisherId")]
    public virtual Publisher? Publisher { get; set; }

    public override string ToString() {
        var txt = new StringBuilder();
        txt.AppendLine($"ISBN: {ISBN}");
        txt.AppendLine($"Title: {Title}");
        txt.AppendLine($"Author: {Author}");
        txt.AppendLine($"Language: {Language}");
        txt.AppendLine($"Pages: {Pages}");
        txt.AppendLine($"Publisher: {Publisher!.Name}");

        return txt.ToString();
    }
}

In the above code, in addition to Book properties, PublisherId is clearly declared as the foreign key and a ToString() method is also defined.

The database context class

Entity Framework requires us to define a DbContext class that becomes the entry point into the database. Therefore, create a new file named LibraryContext.cs and add to it the following code:

public class LibraryContext : DbContext {
   public DbSet<Book>? Books { get; set; }
   public DbSet<Publisher>? Publishers { get; set; }
   protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) {
     optionsBuilder.UseMySQL("server=localhost;database=library;user=root;port=3333;password=secret");
   }
   protected override void OnModelCreating(ModelBuilder modelBuilder) {
     base.OnModelCreating(modelBuilder);
     modelBuilder.Entity<Publisher>(entity => {
         entity.HasKey(e => e.PublisherId);
         entity.Property(e => e.Name).IsRequired();
     });
     modelBuilder.Entity<Book>(entity => {
         entity.HasKey(e => e.ISBN);
         entity.Property(e => e.Title).IsRequired();
         entity.HasOne(d => d.Publisher)
         .WithMany(p => p!.Books);
     });
     modelBuilder.Entity<Publisher>().HasData(
         new Publisher {
             PublisherId = 1,
             Name = "Mariner Books"
         },
         new Publisher {
             PublisherId = 2,
             Name = "Penguin Books"
         }
     );
     modelBuilder.Entity<Book>().HasData(
         new Book {
             ISBN = "978-0544003415",
             Title = "The Lord of the Rings",
             Author = "J.R.R. Tolkien",
             Language = "English",
             Pages = 1216,
             PublisherId = 1
         },
         new Book {
             ISBN = "978-0547247762",
             Title = "The Sealed Letter",
             Author = "Emma Donoghue",
             Language = "English",
             Pages = 416,
             PublisherId = 1
         },
         new Book {
             ISBN = "978-0143107569",
             Title = "Les Miserables",
             Author = "Victor Hugo",
             Language = "English",
             Pages = 1456,
             PublisherId = 2
         },
         new Book {
             ISBN = "978-0140449174",
             Title = "Anna Karenina",
             Author = "Leo Tolstoy",
             Language = "English",
             Pages = 880,
             PublisherId = 2
         }
     );
   }
}

What does the above code do?

  • Two DbSet objects are defined: Books & Publishers
  • The connection string to our MySQL database running in a docker container is:
server=localhost;database=library;user=root;port=3333;password=secret
  • The OnModelCreating() method establishes the following rules:
    • PublisherId is the primary key for the Publisher entity
    • Name is a required column in the Publisher entity
    • ISBN is the primary key in the Book entity
    • The Book entity has a foreign key into the Publisher entity
  • The OnModelCreating() method also inserts publishers & books seed data

Finally, let us add the code to create the database, seed data, and print data. Replace your Program.cs file with:

CreateDbSeedData();
PrintPublishers();
PrintBooks();
static void CreateDbSeedData() {
    using (var context = new LibraryContext()) {
        // Creates the database if not exists
        context.Database.EnsureCreated();
    }
}

static void PrintBooks() {
    // Gets and prints all books in database
    using (var context = new LibraryContext()) {
        var books = context.Books!
          .Include(p => p.Publisher);
        Console.WriteLine(new string('=', 30));
        foreach (var book in books!) {
            Console.WriteLine(book);
        }
    }
}

static void PrintPublishers() {
    // Gets and prints all books in database
    using (var context = new LibraryContext()) {
        var data = context.Publishers;
        Console.WriteLine(new string('=', 30));
        foreach (var item in data!) {
            Console.WriteLine(item);
        }
    }
}

What does the above code do:

  • The CreateDbSeedData() method creates the database and seeds sample data if it does not already exist.
  • The two print methods (PrintPublishers()PrintBooks()) are self-explanatory.
  • The following methods are called at the top of Program.cs:

CreateDbSeedData();
PrintPublishers();
PrintBooks();

The moment of truth is here. Let us run the application with:

dotnet run

The output should look like this:

==============================
ID: 1
Publisher: Mariner Books

ID: 2
Publisher: Penguin Books

==============================
ISBN: 978-0140449174
Title: Anna Karenina
Author: Leo Tolstoy
Language: English
Pages: 880
Publisher: Penguin Books

ISBN: 978-0143107569
Title: Les Miserables
Author: Victor Hugo
Language: English
Pages: 1456
Publisher: Penguin Books

ISBN: 978-0544003415
Title: The Lord of the Rings
Author: J.R.R. Tolkien
Language: English
Pages: 1216
Publisher: Mariner Books

ISBN: 978-0547247762
Title: The Sealed Letter
Author: Emma Donoghue
Language: English
Pages: 416
Publisher: Mariner Books

 

Peeking inside the MySQL database


You can peek into the MySQL database inside the container. To get into a bash session inside the container, enter the following command:

docker exec -it db bash

This takes you into a a bash session inside the container that looks like this:

root@53e55f4991df:/#

To get into the MySQL command interface, enter the following:

mysql -uroot -psecret

You can check existing databases with:

show databases;

The output will resemble this:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| library            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

To look into the contents of our library database, run the following MySQL commands:

use library;
show tables;
select * from Publishers;
select * from Books;

To exit from the container interactive session and return to the host operating system, type exit twice.

Cleanup the running MySQL container

You can do the following to stop & remove the MySQL container:

docker rm -f db

Conclusion

This gives you the option to use yet another relational database with your .NET apps. Of course, you can use MySQL with ASP.NET, Blazor, gRPC, desktop or any other types of .NET applications.

References:

Saturday, January 8, 2022

Export data in Excel format from an ASP.NET 6.0 Razor Pages Web App

Overview

In this tutorial I will show you how you can use the ClosedXML nuget package to export data in Excel format using an ASP.NET 6.0 Razor Pages web app. The data we will is is hard-coded student data. However, the approach works for data from any data source.

Source code: https://github.com/medhatelmasry/ExcelStar.git

Companion Video: https://youtu.be/8olaWPDKXyU

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

  • .NET version 6.0.100
  • Visual Studio Code 

Project setup

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

dotnet new razor -f net6.0 -o ExcelStar

Change directory into the new folder:

cd  ExcelStar

Install the ClosedXML nuget package with:

dotnet add package ClosedXML

Open the project inside VS Code with the following commands:

code .

Sample students data

Create a folder named Data. Inside the Data folder, create a Student.cs class file and add to it the following Student class code:

public class Student {
    public int? Id { get; set; }
    public string? FirstName { get; set; }
    public string? LastName { get; set; }
    public string? School { get; set; }
    public static IEnumerable<Student> GetStudents() {
        int ndx = 0;
        List<Student> students = new List<Student>() {
            new Student() { Id = ++ndx, FirstName="Max", LastName="Pao", School="Science" },
            new Student() { Id = ++ndx, FirstName="Tom", LastName="Fay", School="Mining" },
            new Student() { Id = ++ndx, FirstName="Ann", LastName="Sun", School="Nursing" },                
            new Student() { Id = ++ndx, FirstName="Joe", LastName="Fox", School="Computing" },                
            new Student() { Id = ++ndx, FirstName="Sue", LastName="Mai", School="Mining" },                
            new Student() { Id = ++ndx, FirstName="Ben", LastName="Lau", School="Business" },                
            new Student() { Id = ++ndx, FirstName="Zoe", LastName="Ray", School="Mining" },                
            new Student() { Id = ++ndx, FirstName="Sam", LastName="Ash", School="Medicine" },                
            new Student() { Id = ++ndx, FirstName="Dan", LastName="Lee", School="Computing" },                
            new Student() { Id = ++ndx, FirstName="Pat", LastName="Day", School="Science" },                
            new Student() { Id = ++ndx, FirstName="Kim", LastName="Rex", School="Computing" },                
            new Student() { Id = ++ndx, FirstName="Tim", LastName="Ram", School="Business" },                
            new Student() { Id = ++ndx, FirstName="Rob", LastName="Wei", School="Mining" },                
            new Student() { Id = ++ndx, FirstName="Jan", LastName="Tex", School="Science" },                
            new Student() { Id = ++ndx, FirstName="Jim", LastName="Kid", School="Business" },                
            new Student() { Id = ++ndx, FirstName="Ben", LastName="Chu", School="Medicine" },                
            new Student() { Id = ++ndx, FirstName="Mia", LastName="Tao", School="Computing" },                
            new Student() { Id = ++ndx, FirstName="Ted", LastName="Day", School="Business" },                
            new Student() { Id = ++ndx, FirstName="Amy", LastName="Roy", School="Science" },                
            new Student() { Id = ++ndx, FirstName="Ian", LastName="Kit", School="Nursing" },                
            new Student() { Id = ++ndx, FirstName="Liz", LastName="Tan", School="Medicine" },                
            new Student() { Id = ++ndx, FirstName="Mat", LastName="Roy", School="Tourism" },                
            new Student() { Id = ++ndx, FirstName="Deb", LastName="Luo", School="Medicine" },                
            new Student() { Id = ++ndx, FirstName="Ana", LastName="Poe", School="Computing" },                
            new Student() { Id = ++ndx, FirstName="Lyn", LastName="Raj", School="Science" },                
            new Student() { Id = ++ndx, FirstName="Amy", LastName="Ash", School="Tourism" },                
            new Student() { Id = ++ndx, FirstName="Kim", LastName="Kid", School="Mining" },                
            new Student() { Id = ++ndx, FirstName="Bec", LastName="Fry", School="Nursing" },                
            new Student() { Id = ++ndx, FirstName="Eva", LastName="Lap", School="Computing" },                
            new Student() { Id = ++ndx, FirstName="Eli", LastName="Yim", School="Business" },                
            new Student() { Id = ++ndx, FirstName="Sam", LastName="Hui", School="Science" },                
            new Student() { Id = ++ndx, FirstName="Joe", LastName="Jin", School="Mining" },                
            new Student() { Id = ++ndx, FirstName="Liz", LastName="Kuo", School="Agriculture" },                
            new Student() { Id = ++ndx, FirstName="Ric", LastName="Mak", School="Tourism" },                
            new Student() { Id = ++ndx, FirstName="Pam", LastName="Day", School="Computing" },                
            new Student() { Id = ++ndx, FirstName="Stu", LastName="Gad", School="Business" },                
            new Student() { Id = ++ndx, FirstName="Tom", LastName="Bee", School="Tourism" },                
            new Student() { Id = ++ndx, FirstName="Bob", LastName="Lam", School="Agriculture" },                
            new Student() { Id = ++ndx, FirstName="Jim", LastName="Ots", School="Medicine" },                
            new Student() { Id = ++ndx, FirstName="Tom", LastName="Mag", School="Mining" },                
            new Student() { Id = ++ndx, FirstName="Hal", LastName="Doe", School="Agriculture" },                
            new Student() { Id = ++ndx, FirstName="Roy", LastName="Kim", School="Nursing" },                
            new Student() { Id = ++ndx, FirstName="Vis", LastName="Cox", School="Science" },                
            new Student() { Id = ++ndx, FirstName="Kay", LastName="Aga", School="Tourism" },                
            new Student() { Id = ++ndx, FirstName="Reo", LastName="Hui", School="Business" },               
            new Student() { Id = ++ndx, FirstName="Bob", LastName="Roe", School="Medicine" },                          
        };
        return students;
    }
}

The above provides us with suitable sample students data that we will later export into Excel format.

Export data to Excel format

Inside the Pages folder, add two files names Excel.cshtml & Excel.cshtml.cs

Content of Excel.cshtml, representing the viewwill contain the following minimal code:

@page
@model ExcelModel

Excel.ccshtml.cs is the code-behind file for the view file and will contain the following minimal code:

using ClosedXML.Excel;
using ExcelStar.Data;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;

namespace ExcelStar.Pages;

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

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

    public FileResult OnGet() {
        var data = Student.GetStudents();

        using (var workbook = new XLWorkbook()) {
            IXLWorksheet worksheet =
            workbook.Worksheets.Add("Students");
            worksheet.Cell(1, 1).Value = "Id";
            worksheet.Cell(1, 2).Value = "First";
            worksheet.Cell(1, 3).Value = "Last";
            worksheet.Cell(1, 4).Value = "School";

            IXLRange range = worksheet.Range(worksheet.Cell(1, 1).Address, worksheet.Cell(1, 4).Address);
            range.Style.Fill.SetBackgroundColor(XLColor.Almond);

            int index = 1;

            foreach (var item in data) {
                index++;

                worksheet.Cell(index, 1).Value = item.Id;
                worksheet.Cell(index, 2).Value = item.FirstName;
                worksheet.Cell(index, 3).Value = item.LastName;
                worksheet.Cell(index, 4).Value = item.School;

            }

            using (var stream = new MemoryStream()) {
                workbook.SaveAs(stream);
                var content = stream.ToArray();
                string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

                var strDate = DateTime.Now.ToString("yyyyMMdd");
                string filename = string.Format($"Students_{strDate}.xlsx");

                return File(content, contentType, filename);
            }
        }
    }
}

What does the above code do?

  1. Students are read into a variable named data.
  2. A worksheet is created named Students with column titles Id, First, Last & School
  3. The Students worksheet is added to the workbook.
  4. The background color of the column titles is made to be the Almond color.
  5. Thereafter, each row is filled with student data whereby Id goes into column 1, FirstName goes into column 2, LastName goes into column 3 and School goes into column 4.
  6. The workbook is saved as a stream.
  7. The stream is returned to the user as a file with a set name an an appropriate HTTP content type.

Adding Excel page to the navigation

We need to add our new page to the main navigation system. Edit Pages/Shared/_Layout.cshtml and add the following to the bottom of the <ul> . . . </ul> block:

<li class="nav-item">
<a class="nav-link text-dark" asp-area="" asp-page="/Excel">Export to Excel</a>
</li>

Testing our app

It is time to run our application with the following command:

dotnet watch run

The application opens in your default browser and looks like this:


When you click on "Export to Excel", a file with extension .xlsx gets downloaded to your default downloads folder. If you open this file in Excel, it should look like this:


Go ahead and explore other things you can do, like adding links, count, sum etc...

I hope you found this brief tutorial useful and satisfies your expectations.

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>

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