Deploy multi-container docker-compose solution on Azure

In this tutorial I will show you how to deploy a multi-container solution to Azure. The example I will use is an ASP.NET 6.0 Razor web app that works with a SQL Server database. The web app and database server run in separate containers. We will setup the solution on the Azure portal.

Assumptions

• .NET 6.0 is installed on your computer
• Docker Desktop is installed on your computer
• You have an Azure subscription
• Git is installed on your computer
• You have a docker hub account

Getting started

In a previous example, I discuss docker-compose with ASP.NET 6.0 & SQL Server.

Clone the ASP.NET 6.0 sample startup application by running the following command from a working directory on your computer:

git clone https://github.com/medhatelmasry/AspMsSQL-docker-compose

Change into the newly cloned directory with:

cd AspMsSQL-docker-compose

The application is a simple ASP.NET 6.0 Razor application that should be very familiar to any .NET developer. 

To understand how you can run this solution, you must inspect the following two files:

Dockerfile

FROM mcr.microsoft.com/dotnet/aspnet:6.0

COPY dist /app

WORKDIR /app

EXPOSE 80/tcp

ENTRYPOINT ["dotnet", "AspMsSQL.dll"]

Docker file is used to build the web app. In line 2 above, the dist folder is copied into the image. Therefore, we must create a dist folder with the following command:

dotnet publish -o dist

docker-compose.yml

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:

The db service above starts a SQL Server container from mcr.microsoft.com/azure-sql-edge.

The webapp service builds an image from Dockerfile and runs it. The web app can be accessed on the host computer with http://localhost:8888.

Running the solution on your computer

To run the application on your computer, type the following command from within the root folder of the web app (I.E. inside the AspMsSQL-docker-compose folder):

docker-compose up

Once the script in the terminal windows settles down, point your browser to http://localhost:8888. You will see the following landing page:

Register and login to ensure that the app functions properly with the database.

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

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

docker volume rm aspmssql-docker-compose_sqlbackup

docker volume rm aspmssql-docker-compose_sqldata

docker volume rm aspmssql-docker-compose_sqllog

docker volume rm aspmssql-docker-compose_sqlsystem

Prepare solution for Azure deployment

We need to make one minor tweak so that our application can run on Azure. The tweak is to build the web app image and deploy it to docker hub.

I am hereby using snoopy a an example docker-hub username. Be sure to replace every instance of snoopy with your docker-hub user name.

The command to build a Docker image named asp-mssql version 1.0.0 is:

docker build --tag snoopy/asp-mssql:1.0.0 .

Note: Make sure you run the above command in the same folder as Dockerfile.

You ensure that you created an image named asp-mssql, type the following command:

docker images

You will see the image that you created among the list of docker images on your computer.

We can now push our image to docker hub. First we need to login into docker-hub with the following command:

docker login --username=snoopy

You will be prompted for your password. If all goes well. you will see the following output:

Login Succeeded

Logging in with your password grants your terminal complete access to your account.

For better security, log in with a limited-privilege personal access token. Learn more at https://docs.docker.com/go/access-tokens/

We now need to push our image to docker-hub with:

docker push snoopy/asp-mssql:1.0.0

The output will be similar to this:

The push refers to repository [docker.io/snoopy/asp-mssql]

5f70bf18a086: Mounted from snoopy/toon

3b9aa4fcf4e8: Pushed

a41af57309b5: Mounted from snoopy/toon

63fa163dde0c: Mounted from snoopy/toon

0f53df05d8e3: Mounted from snoopy/toon

bc1e58de0815: Mounted from snoopy/toon

2edcec3590a4: Mounted from snoopy/toon

If you login to https://hub.docker.com, you will find that the image is sitting in your repository.

Let's modify our docker-compose.yml file so that it used this image on docker-hub instead of building it locally. Open docker-compose.yml in an editor and replace lines 22-24 with:

image: snoopy/asp-mssql:1.0.0

Needless to say that instead of snoopy, you should use your docker-hub username.

The final docker-compose.yml will look like this:

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:
    image: snoopy/asp-mssql:1.0.0
    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:

Deploying solution to Azure

Login into Azure by going to the portal at https://portal.azure.com. Click on "App Services" on the left-side hamburger menu:

Click on Create:

Add a new resource group:

Here are the remaining settings that I chose:

The most important setting you need to have for Publish is "Docker Container".

Click on the "Next : Docker >" button. On the next screen choose:

Options	Docker Compose (Preview)
Image Source	Docker Hub
Access Type	Public
Configuration File	Navigate to the docker-compose.yml file and load it. It will load in the text-area below.

This is what it should look like:

Click on "Review + create" button.

Review the configuration then click on Create.

You will see a blue "Go to resource" button once the deployment is completed.

Click on "Go to resource". This takes you to the control page for your web app. 

Click on the URL on the top right-side to see your solution running in the browser. Be patient because the solution takes some time to load. In my case, the app displayed like this:

At the time of writing this article, the Docker Compose capability in Azure App services is in preview mode. It seems to work quite well and, I am confident, it will be production ready soon.

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, type:

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.

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:

Creating a Database with Code First in EF Core

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

Create a folder named Data. Inside the Data folder, create a Student.cs class file and add to it the following Student 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 view, will contain the following minimal code:

@page

@model ExcelModel

Excel.cshtml.cs is the code-behind file for the view 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 fixed name and 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 terminal window command:

dotnet watch

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

When you click on "Export to Excel", a file with extension .xlsx is 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 that it satisfies your expectations.