Medhat Elmasry

Wednesday, February 9, 2022

PHP, SQLite, CSV and CanvasJS

In this article, I will import data from a CSV file into SQLite and render a chart using Canvas.JS. The purpose of this post is to familiarize the reader with importing a CSV file into a database and, subsequently, rendering a pie chart with the data.

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

The following is assumed:

You have PHP installed on your computer
You have the "extension=sqlite3" setting enabled in your php.ini.

Directory Structure

Inside of a working directory named php_chart, create the following directory structure:

The home page

Inside the root folder, add an index.html file with the following content representing a simple menu system:

<p><a href="./create" >Create school database and students table in SQLite.</a></p>

<p><a href="./import/" >Import seed data from csv file.</a></p>

<p><a href="./chart/" >Display chart with Canvas.JS</a></p>

Sample data

Inside the data folder, create a text file named seed-data.csv with data from this link.

The database file

In the root of your application, create a PHP file named include_db.php containing only one statement, representing the name of the database file:

<?php
$db = new SQLite3($_SERVER['DOCUMENT_ROOT'] . '/school.db');
?>

The above code creates a school.db SQLite database file if it does not already exist.

Creating database file and add Students table

Add an index.php file inside the create folder with the following content:

<?php include("../include_db.php"); ?>

<?php
echo "<hr /><h3>Create Student Table</h3>";
#===============================================
# Create table
#===============================================

$SQL_create_table = "CREATE TABLE IF NOT EXISTS Students (
StudentId VARCHAR(10) NOT NULL,
FirstName VARCHAR(80),
LastName VARCHAR(80),
School VARCHAR(50),
PRIMARY KEY (StudentId)
);";

echo "<p>$SQL_create_table</p>";

$db->exec($SQL_create_table);

$db->close();
?>

<hr /><a href="/" ><< BACK</a>

What does the above code do?

We first include the include_db.php file so that we have a handle to the db object representing our school.db database file.
Next, we create a Students table in the database by executing a "Create Table ..." SQL statement. The columns in the table match the items in our CSV file.
We close the connection to the database.
There is a link at the bottom that returns us to the home page.

Import CSV file into Students table

Add an index.php file inside the import folder with the following content:

<?php include('../include_db.php'); ?>

<?php

$count = $db->querySingle("SELECT count(*) from Students");

// if empty, insert sample data

if ($count == 0) {

$row = 1;

if (($handle = fopen("../data/seed-data.csv", "r")) !== FALSE) {

$data = fgetcsv($handle, 1000, ",", "\"", "\\");

while (($data = fgetcsv($handle, 1000, ",", "\"", "\\")) !== FALSE) {

$num = count($data);

echo "<p> $num fields in line $row: <br /></p>\n";

$row++;

$id = SQLite3::escapeString($data[0]);

$firstName = SQLite3::escapeString($data[1]);

$lastName = SQLite3::escapeString($data[2]);

$school = SQLite3::escapeString($data[3]);

$SQLinsert = "INSERT INTO Students (StudentId, FirstName, LastName, School)";

$SQLinsert .= " VALUES ";

$SQLinsert .= " ('$id', '$firstName', '$lastName', '$school')";

$db->exec($SQLinsert);

$changes = $db->changes();

echo "<p>The INSERT statement added $changes rows</p>";

}

$db->close();

What does the above code do?

We check whether or not there is any data in the Students table.
We load data from the CSV file only if the Students table is empty
The PHP fgetcsv() function is used to load CSV data into an array named $data
Every row of data in the CSV file is inserted into the Students table in the database
We close the connection to the database.

List imported students data

Add an index.php file inside the list folder with the following content:

<?php include("../include_db.php"); ?>

<table border="1">
<?php

echo "<hr /><h3>List of students</h3>";

$res = $db->query('SELECT * FROM Students');

while ($row = $res->fetchArray()) {
echo "<tr>\n";
echo "<td>{$row['StudentId']}</td>";
echo "<td>{$row['FirstName']}</td>";
echo "<td>{$row['LastName']}</td>";
echo "<td>{$row['School']}</td>";
echo "<tr>\n";
}

?>
</table>
<hr /><a href="/" ><< BACK</a>

What does the above code do?

The above code simply lists the contents of the Students in an html table.

Render a pie chart of "student count by school"

Add an index.php file inside the chart folder with the following content:

<?php include('../include_db.php'); ?>

<?php
$dataPoints = [];

$sql = "SELECT School as school, COUNT(*) as count";
$sql .= " FROM Students GROUP BY School";
$res = $db->query($sql);

while ($row = $res->fetchArray()) {
$arrayItem = array("label" => $row['school'], "y" => $row['count']);
array_push($dataPoints, $arrayItem);
}

$db->close();
?>

<script>
window.onload = function() {
var chart = new CanvasJS.Chart("chartContainer", {
animationEnabled: true,
title: {text: "Students by school"},
data: [{
type: "pie",
yValueFormatString: "#,##0.00\"\"",
indexLabel: "{label} ({y})",
dataPoints: <?php echo json_encode($dataPoints, JSON_NUMERIC_CHECK); ?>
}]
});
chart.render();
}
</script>
<div id="chartContainer" style="height: 370px; width: 100%;"></div>
<script src="https://canvasjs.com/assets/script/canvasjs.min.js"></script>

<hr /><a href="/" ><< BACK</a>

What does the above code do?

A "SELECT ... GROUP BY ..." SQL statement is executed that generates a result-set containing count of students by school.
A two-dimensional array is created with key "label" containing school and key "y" containing count
The bottom part of the above code used the CanvasJS JavaScript library
The type property is set to the type of chart you wish to generate. In this case it is pie.
The dataPoints property contains our data from the $dataPoints two-dimensional array converted into JSON objects

Testing our app

We should be good to go. Let us test our very basic PHP application. We first need to start our server. This is accomplished by running the following command in a terminal window in the root of our application:

php -S localhost:8888

The above starts the PHP development server and listens on port 8888.

Point your browser to http://localhost:8888. You should see the following:

Click on the first "Create school database and students table in SQLite." link to create the database and Students table. This displays the following output with the INSERT statement.

If you look into the root folder of your app, you will see that a school.db file was created.

Click on the "BACK" link to return to the home page.

Next click on the "Import seed data from csv file." link to import CSV data into the Students database table. This displays a series of INSERT statements as shown below:

Return to the home page and click on the third "List data" link. You will see that data was imported into the database:

Return to the home page and click on the last "Display chart with Canvas.JS" link to see our pie chart.

I trust that this simple tutorial will help you visualize data with PHP.

Saturday, February 5, 2022

ASP.NET 6.0 Web API and Mongo DB

In this tutorial I will show you how to develop an ASP.NET 6.0 API application that interacts with MongoDB. In order to proceed you will need the following pre-requisites:

Docker - used to host MongoDB. This is my preferred approach because it is quick and does not take too many resources on the host computer.
.NET 6.0
Visual Studio Code

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

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

The Database

Run the following command in a terminal window to start a MongoDB container named mdb:

docker run -p 27777:27017 --name mgo -d mongo:4.1.6

You can verify that the container is running by typing the following command in a terminal window:

docker ps -a

Let us start a bash session inside the container so that we can create a database and add some sample data to it. Enter the following command to start an interactive bash session inside the container:

docker exec -it mgo bash

We can then use the mongo command line interface (CLI) to create a database and then add some sample data to a collection of students. Type the following command:

mongo

This takes you into a mongo session command prompt. Enter the following command to create a database named school-db:

use school-db

Next, let's create a collection named students and add to it six sample students:

db.Students.insertMany(
[
{'FirstName':'Sue','LastName':'Fox','School':'Business'},
{'FirstName':'Tom','LastName':'Max','School':'Mining'},
{'FirstName':'Ann','LastName':'Lee','School':'Nursing'},
{'FirstName':'Joe','LastName':'Roy','School':'Tourism'},
{'FirstName':'Jan','LastName':'Ash','School':'Communications'},
{'FirstName':'Eva','LastName':'Day','School':'Medicine'},
]);

You can retrieve the list of students with the following command:

db.Students.find({}).pretty();

To exit the mongo command prompt by typing:

exit

You can also exit the bash session and return to the host operating system by typing:

exit

Creating an ASP.NET 6.0 Web API application

Create an ASP.NET 6.0 Web API application. This is accomplished by entering this command:

dotnet new webapi -f net6.0 -o AspMongoApi

Let us see what our application looks like. Run the application by executing:

cd AspMongoApi
dotnet run

The above command builds and runs the application in a web server. Point your browser to https://localhost:????/weatherforecast (Where ???? is your port number). This is what you should see:

Stop the web server by hitting CTRL+C on the keyboard.

Building the Students API

Find the latest version of MongoDB driver from https://www.nuget.org/packages/MongoDB.Driver/. At the time of writing, the latest version was 2.14.1. Add the MongoDB driver Nuget package with the following command:

dotnet add package MongoDB.Driver --version 2.14.1

Now that we have the driver, we can proceed with coding our API. I will use Visual Studio Code because it is operating system agnostic. To load your application workspace into VS Code, simply type in the following command from the root folder of your application:

code .

We need a Student class to represent the schema for student documents in the Students collection in the MongoDB school-db database. Create a Models folder. Inside the Models folder, add a file named Student.cs with the following code:

using MongoDB.Bson;
using MongoDB.Bson.Serialization.Attributes;

namespace AspMongoApi.Models;
public class Student {
[BsonId]
[BsonRepresentation(BsonType.ObjectId)]
public string? Id { get; set; }
public string? FirstName { get; set; }
public string? LastName { get; set; }

[BsonElement("School")]
public string? Department { get; set; }
}

In the preceding class, the Id property:

Is required for mapping the Common Language Runtime (CLR) object to the MongoDB collection.
Is annotated with [BsonId] to designate this property as the document's primary key.
Is annotated with [BsonRepresentation(BsonType.ObjectId)] to allow passing the parameter as type string instead of an ObjectId structure. Mongo handles the conversion from string to ObjectId.

The Department property is annotated with the [BsonElement] attribute. The attribute's value of School represents the property name in the MongoDB collection.

Add the following to appsettings.json:

"StudentDbSettings": {
"CollectionName": "Students",
"ConnectionString": "mongodb://localhost:27777",
"DatabaseName": "school-db"
},

The entire contents of appsettings.json will look like this:

{
"StudentDbSettings": {
"CollectionName": "Students",
"ConnectionString": "mongodb://localhost:27777",
"DatabaseName": "school-db"
},
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft.AspNetCore": "Warning"
}
},
"AllowedHosts": "*"
}

Add StudentDbSettings.cs to the Models folder with this code:

namespace AspMongoApi.Models;

public class StudentsDbSettings {
public string ConnectionString { get; set; } = null!;
public string DatabaseName { get; set; } = null!;
public string CollectionName { get; set; } = null!;
}

The above StudentDbSettings class is used to store the appsettings.json file's StudentDbSettings property values. The JSON and C# property names are named identically to simplify the mapping process.

Add the following code to Program.cs before "var app = builder.Build();" :

builder.Services.Configure<StudentsDbSettings>(
builder.Configuration.GetSection("StudentDbSettings"));

In the above code, the configuration instance to which the appsettings.json file's StudentDatabaseSettings section binds is registered in the Dependency Injection (DI) container.

Create a folder named Services and add to it a file named StudentService.cs with the following code:

namespace AspMongoApi.Services;

public class StudentsService {
private readonly IMongoCollection<Student> _studentsCollection;

public StudentsService(IOptions<StudentsDbSettings> studentsDatabaseSettings) {
var mongoClient = new MongoClient(
studentsDatabaseSettings.Value.ConnectionString);

var mongoDatabase = mongoClient.GetDatabase(
studentsDatabaseSettings.Value.DatabaseName);

_studentsCollection = mongoDatabase.GetCollection<Student>(
studentsDatabaseSettings.Value.CollectionName);
}

public async Task<List<Student>> GetAsync() =>
await _studentsCollection.Find(_ => true).ToListAsync();

public async Task<Student?> GetAsync(string id) =>
await _studentsCollection.Find(x => x.Id == id).FirstOrDefaultAsync();

public async Task CreateAsync(Student newStudent) =>
await _studentsCollection.InsertOneAsync(newStudent);

public async Task UpdateAsync(string id, Student updatedStudent) =>
await _studentsCollection.ReplaceOneAsync(x => x.Id == id, updatedStudent);

public async Task RemoveAsync(string id) =>
await _studentsCollection.DeleteOneAsync(x => x.Id == id);
}

In the above code, an StudentsDbSettings instance is retrieved from DI via constructor injection. Also, the above class knows how to connect to the MongoDB server and use the available driver methods to retrieve, insert, update and delete data.

To register the StudentService class with DI to support constructor injection, add the following to Program.cs before "var app = builder.Build();" :

builder.Services.AddSingleton<StudentsService>();

Add a StudentsController.cs class to the Controllers folder with the following code:

namespace AspMongoApi.Controllers;

[ApiController]
[Route("api/[controller]")]
public class StudentsController : ControllerBase {
private readonly StudentsService _studentsService;

public StudentsController(StudentsService studentsService) =>
_studentsService = studentsService;

[HttpGet]
public async Task<List<Student>> Get() =>
await _studentsService.GetAsync();

[HttpGet("{id:length(24)}")]
public async Task<ActionResult<Student>> Get(string id) {
var student = await _studentsService.GetAsync(id);

if (student is null) {
return NotFound();
}

return student;
}

[HttpPost]
public async Task<IActionResult> Post(Student newStudent) {
await _studentsService.CreateAsync(newStudent);

return CreatedAtAction(nameof(Get), new { id = newStudent.Id }, newStudent);
}

[HttpPut("{id:length(24)}")]
public async Task<IActionResult> Update(string id, Student updatedStudent) {
var student = await _studentsService.GetAsync(id);

if (student is null) {
return NotFound();
}

updatedStudent.Id = student.Id;

await _studentsService.UpdateAsync(id, updatedStudent);

return NoContent();
}

[HttpDelete("{id:length(24)}")]
public async Task<IActionResult> Delete(string id) {
var student = await _studentsService.GetAsync(id);

if (student is null) {
return NotFound();
}

await _studentsService.RemoveAsync(student.Id!);

return NoContent();
}
}

The StudentsController class:

Uses the StudentService class to perform CRUD operations.
Contains action methods to support GET, POST, PUT, and DELETE HTTP requests.

Running the application

Start the application by executing the this command from a terminal windows at the root of the application:

dotnet run

You can view the data by pointing your browser to https://localhost:????/api/Students. This is the expected output:

Note that even though the last field is named School in the database, it appears as Department in our app because that is how we mapped it in the Student class.

Go ahead and test the other API endpoints for POST, PUT and DELETE using your favourite tool like Postman or curl. It should all work.

Cleanup

To stop & remove the MongoDB docker container, enter the following from any terminal window on your computer:

docker rm -f mgo

I hope you learned something new in this tutorial. Until next time, happy coding.

Reference:https://referbruv.com/blog/posts/integrating-mongodb-with-aspnet-core-(net-6)

docker-compose with MySQL pomelo driver and ASP.NET 7.0

It is customary to develop ASP.NET with either SQL Server or SQLite databases. How about if you want to use the popular MySQL database server? This article discussed one approach to making this possible by having your ASP.NET 7.0 development environment connect with MySQL running in a docker container.

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

This article assumes the following:

You have .NET 7.0 installed on your computer.
You have Docker Desktop installed on your computer.
You have the dotnet-ef tool installed.

Let's get started.

Setting up the mariadb:10.7.3 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 mariadb:10.7.3

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 like the following:

CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES

67335fb804e4 mariadb:10.7.3 "docker-entrypoint.s…" 5 seconds ago Up 3 seconds 0.0.0.0:3333->3306/tcp

Creating our ASP.NET 7.0 MVC App

The first step is to create a working directory somewhere on your computer's hard drive. I did so by creating a folder named AspMySQL with the following terminal command:

mkdir AspMySQL

Thereafter, go into the new folder with:

cd AspMySQL

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

dotnet new mvc -f net7.0 --auth individual

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:

info: Microsoft.Hosting.Lifetime[14]
Now listening on: https://localhost:7042
info: Microsoft.Hosting.Lifetime[14]
Now listening on: http://localhost:5035
info: Microsoft.Hosting.Lifetime[0]
Application started. Press Ctrl+C to shut down.
info: Microsoft.Hosting.Lifetime[0]
Hosting environment: Development
info: Microsoft.Hosting.Lifetime[0]
Content root path: /Users/medhatelmasry/AspMySQL/

The above message indicates that the Kestrel web server is running and listening on port 7042. The port number you get is probably different. Start your browser with the appropriate URL. You should see a page that looks like this:

Look into the root folder of the project, you will find a file named app.db. This is an SQLite database file. The web application that we scaffolded is configured to work with SQLite. We will change it so that it works with the popular MySQL database instead. Go ahead and delete app.db.

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

There is only one NuGet package that is needed to talk to MySQL. Add the package by typing the following command from within a terminal window:

dotnet add package Pomelo.EntityFrameworkCore.MySql -v 7.0.0

Let us configure our web application to use MySQL instead of SQLite. Open the Program.cs file in your favourite editor and comment out (or delete) the following statements found at around line 8:

~~var connectionString = builder.Configuration.GetConnectionString("DefaultConnection");~~

~~builder.Services.AddDbContext<ApplicationDbContext>(options =>~~

~~options.UseSqlite(connectionString));~~

Replace the above code with the following:

var host = builder.Configuration["DBHOST"] ?? "localhost";
var port = builder.Configuration["DBPORT"] ?? "3333";
var password = builder.Configuration["DBPASSWORD"] ?? "secret";
var db = builder.Configuration["DBNAME"] ?? "test-db";
var user = builder.Configuration["DBUSER"] ?? "root";

string connectionString = $"server={host}; userid={user}; pwd={password};"
+ $"port={port}; database={db};SslMode=none;allowpublickeyretrieval=True;";

var serverVersion = new MySqlServerVersion(new Version(10,7,3));

builder.Services.AddDbContext<ApplicationDbContext>(options =>
options.UseMySql(connectionString, serverVersion));

Five environment variables are used in the database connection string. These are: DBHOST, DBPORT , DBPASSWORD, DBNAME and DBUSER. If these environment variables are not found then they will take up default values: localhost, 3333, secret, test-db and test-dbroot respectively.

Entity Framework Migrations

The migration files that were created in the /Data/Migrations folder contain commands for creating SQLite artifacts. These are not valid in our situation because we will be using MySQL and not SQLite. Therefore, delete the Migrations folder under /Data and create new migrations with the following command:

dotnet-ef migrations add M1 -o Data/Migrations

We can instruct our application to automatically process any outstanding Entity Framework migrations. This is done by adding the following statement to Program.cs right before the last app.Run() statement:

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

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

Test our app

Now, let us test our web app and see whether or not it is able to talk to the containerized MySQL 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 some random port number. Point your browser to http://localhost:???? (where ???? is your 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. The website then displays the following page that requires that you confirm the email address:

Click on the “Click here to confirm your account” link. This leads you to a confirmation page:

The message on the top right side confirms that the user was saved and that communication between the ASP.NET MVC app and MySQL is working as expected.

Dockeri-zing solution

We will generate the release version of the application by executing the following command from a terminal window in the root directory of your web app:

dotnet publish -o dist

The above command instructs the dotnet utility to produce the release version of the application in the dist directory.

If you inspect the dist directory, you will see content similar to the following:

The highlighted file in the above image is the main DLL file that is the entry point into the web application.

Let us run the release version of the web app. To do this, change 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 AspMySQL.dll. I executed the following command:

dotnet AspMySQL.dll

This displays the familiar messages from the web server that the app is ready to be accessed from a browser. Hit CTRL C to stop the web server.

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

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

Also, in a terminal window, stop and remove the MySQL container with:

docker rm -f db

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 .NET 7.0 runtime. A suitable image for this purpose is: mcr.microsoft.com/dotnet/aspnet:7.0

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

FROM mcr.microsoft.com/dotnet/aspnet:7.0
COPY dist /app
WORKDIR /app

ENV ASPNETCORE_URLS http://+:80

EXPOSE 80/tcp
ENTRYPOINT ["dotnet", "AspMySQL.dll"]

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

FROM mcr.microsoft.com/dotnet/aspnet:7.0	Base image mcr.microsoft.com/dotnet/aspnet:7.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", "AspMySQL.dll"]	The main ASP.NET web app will be launched by executing "dotnet AspMySQL.dll"

We will next compose a docker yml file that orchestrates the entire system which involves two containers: a MySQL 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'

volumes:
datafiles:

services:
db:
image: mariadb:10.7.3

volumes:
- datafiles:/var/lib/mysql
#restart: always
environment:
MYSQL_ROOT_PASSWORD: secret
MYSQL_TCP_PORT: 3306

webapp:
build:
context: .
depends_on:
- db
ports:
- "8888:80"
#restart: always
environment:
- DBHOST=db
- DBPORT=3306
- DBPASSWORD=secret
- DBNAME=bingo-db
- ASPNETCORE_ENVIRONMENT=Development

Below is an explanation of what this file does.

We will be having two containers. Each container is considered a service. The first service is named db and will host MySQL. 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.

The MySQL Container

Image mariadb:10.7.3 will be used for the MySQL container.

A volume named datafiles is declared that will host MySQL data outside of the container. This ensures that even if the MySQL 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 secret when MySQL is configured. This is set by the MYSQL_ROOT_PASSWORD environment variable.

The ASP.NET 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 MySQL container (db) to properly function.

Port 80 in the mvc container is mapped to port 8888 on the host computer.

The environment variables needed by the web app are:

DBHOST	points to the MySQL service
DBPORT	Should be set to 3306 because it is the depault port # that MySQL listens on
DBPASSWORD	this is the root password for MySQL
DBNAME	We shall call the database for our web app bingo
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 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.

NOTE: If you cannot view the home page, check that the web container is running. If it is stopped then start it with docker start …

To ensure that the database works properly, register a user by clicking on the Register link in the top right corner.

You will then receive a “Register Confirmation”:

Click on the “Click here to confirm your account” so that the app accepts the email address that was used. On the “Confirm Email” page.

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

This opens a whole new world for containerizing your ASP.NET web apps.

Cleanup

Hit CTRL C in the terminal window to stop docker-compose, then run the following command:

docker-compose down

Sunday, January 23, 2022

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:

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:

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.