Use MySQL Connector/NET for Entity Framework driver with ASP.NET Core 5.0 Web App

Although the current version of ASP.NET is 6.0, I discovered that the official MySQL EF Core driver for .NET 6.0, at this time, does not work very well. Therefore, I decided to write this article for ASP.NET 5.0 Core. The NuGet package is MySql.EntityFrameworkCore Nuget located at https://www.nuget.org/packages/MySql.EntityFrameworkCore/. 

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

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

This article assumes the following:

1. You have .NET 5.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

Note: if you are using macOS with the M1 chip, you can use this docker image instead:

docker run -d -p 3333:3306 --name db -e MYSQL_ROOT_PASSWORD=secret -e MYSQL_ROOT_HOST=% mysql/mysql-server:latest-aarch64

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 an ASP.NET 5.0 Core MVC web app

In a working directory, run the following command to create an ASP.NET MVC application named MySqlWeb using .NET 5.0:

dotnet new mvc -f net5.0 --auth individual -o MySqlWeb

This creates a web application that uses the SQLite database for individual authentication. Instead of SQLite, we will use MySQL instead.

Change directory to the newly created folder with:

cd MySqlWeb 

Firstly, let us remove support for SQLite by removing the appropriate package with this command:

dotnet remove package Microsoft.EntityFrameworkCore.Sqlite

Next, let us add two packages that will provide us with MySQL support using the Connector/NET for Entity Framework driver:

dotnet add package Microsoft.EntityFrameworkCore.Design -v 5.0.14

dotnet add package MySql.EntityFrameworkCore -v 5.0.10

Replace the connection string value for DefaultConnection in appsettings.json to the following pertaining to our MySQL database:

server=localhost;database=library;user=root;port=3333;password=secret;SslMode=none;

Open Startup.cs in an editor and change 'options.UseSqlite' to 'options.UseMySQL' around line 31.

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

Next, let us apply these migrations to the database with:

dotnet-ef database update

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 port numbers 5000 and 5001 (SSL). Point your browser to https://localhost:5001. Click on the Register link on the top right-side.

Enter an email address, password and confirm password then click 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:

Login with the email address and password that you registered with. You will see a welcome message in the top right-side indicating that you are logged-in.

This proves that your email and password was saved and that the connection to the MySQL database works as expected.

Happy coding.

Deploy single container docker image to Azure App Services

You have a self contained web app that runs in a docker container and want to deploy it on a cloud service. I will show you how easy it is to deploy your docker image to Azure App Services. I will, as an example use a simple Node.js Express web app in this demonstration.

Source code: https://github.com/medhatelmasry/xpress-sqlite-chart-docker.git

Assumptions:

• Node.js and NPM are 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 article, I created a Node.js Express app that persists data in SQLite. I will use this sample app i this article. 

Clone the Node.js Express sample web app on GitHub by running the following command from a working directory in a terminal window on your computer:

git clone https://github.com/medhatelmasry/xpress-sqlite-chart

Change into the newly cloned directory with:

cd xpress-sqlite-chart

The application is a simple Node.js Express application that uses SQLite as a database. This will come as a surprise to most Node.js developers because MongoDB is the most commonly used database with Node.js. The reason I used SQLite in this case is because it does not need a database server and can run independently in a docker container. To experience what this web app does, run the following commands in the same folder:

npm install

npm start

This will display in the console:

Server running on port 3000

Connected to the SQLite database.

Point your browser to http://localhost:3000/api/students and you will see the following output:

You can view a pie-chart summarizing the above information at http://localhost:3000/chart.html as shown below:

This data is being dynamically read from a SQLite database file named school.db located in the root of the web app.

Building our docker image

Stop the Node.js app by hitting CTRL C on the keyboard. 

Let us build a web app image and deploy it to docker hub.

We do not need to copy folder node_modules. Also, we should not copy school.db into the image because it is automatically created when the web app starts. Therefore, add a text file named .dockerignore with the following content:

school.db

node_modules

Add another text file named Dockerfile containing the instructions for creating our Docker image:

FROM node:12.18.1

WORKDIR /app

COPY ["package.json", "package-lock.json*", "./"]

RUN npm install

COPY . .

EXPOSE 3000

CMD [ "npm", "start" ]

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 bingo version 1.0.0 is:

docker build --tag snoopy/bingo:1.0.0 .

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

To ensure that you created an image named asp-bingo, type the following command:

docker images

You will see your newly created image in a list of Docker images:

Let us run the image in a container to make sure that it works as expected:

docker run -d -p 8888:3000 --name bingo snoopy/bingo:1.0.0

Point your browser to http://localhost:8888/api/students and you should see student JSON data. This confirms to us that our image and container work. Stop and remove the container with the following command:

docker rm -f bingo

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/bingo:1.0.0

The output will be similar to this:

The push refers to repository [docker.io/snoopy/bingo]

c8b45ef9554a: Pushed 

e98b0d58d677: Pushed 

5ffdad44213c: Pushed 

db7819aa4316: Pushed 

6be3db87bf2b: Mounted from library/node 

640ef100e22c: Mounted from library/node 

f5efc9f25bb5: Mounted from library/node 

6c4912a00957: Mounted from library/node 

47b172bd2907: Mounted from library/node 

38a97d5a6ebd: Mounted from library/node 

bfdc94824303: Mounted from library/node 

33c19d48a4f3: Mounted from library/node 

db4e0d4c0410: Mounted from library/node 

1.0.0: digest: sha256:32d22b3fd5f66a8c8e11bc3566b330e8085edb809f78fa7ca04f8e27bcab3ed6 size: 3051

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

Deploying solution to Azure App Services

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 and complete the remaining settings as shown below:

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

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

Options: Single Container

Image Source: Docker Hub

Access Type: Public

Image and tag: enter the fully qualified name of your docker image

Startup Command: leave blank

Click on Review + Create 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  the link on the top right-side as shown above. The web app should show in the browser.

If you want to see something more interesting, point to page /chart.html:

I trust that you have a good appreciation of how easy it is to deploy a containerized web app to the Azure App Services.

Node.js express, SQLite, CSV and Google Charts

In this article, I will develop a Node.js Express application that imports data from a CSV file into a SQLite database and render a pie chart using Google Charts. The purpose of this post is to familiarize the reader with Node Express, importing a CSV file into SQLite and, subsequently, rendering a pie chart with the data.

SQLite is the most used database engine in the world. SQLite is built into all mobile phones and most computers. Even though it is not customary to use SQLite with MongoDB apps, it is worth exploring as this database engine is self-contained and does not need a database server.

Source code: https://github.com/medhatelmasry/xpress-sqlite-chart

The following is assumed about your environment:

• Node.js & NPM are installed on your computer
• You have installed nodemon
• You already have some familiarity with Node.js

Initializing our node express app

Let us first create a skeleton template of a simple Node.js Express application. This application will focus of APIs, so there will be no need to use any view engines. 

In a working directory, create a subdirectory named xpress-sqlite-chart for our new Node.js application with:

mkdir xpress-sqlite-chart

cd xpress-sqlite-chart

Let us initialize a new node app with:

npm init --yes

This creates a package.json that looks like this:

{

  "name": "xpress-sqlite-chart",

  "version": "1.0.0",

  "description": "",

  "main": "index.js",

  "scripts": {

    "test": "echo \"Error: no test specified\" && exit 1"

  },

  "keywords": [],

  "author": "",

  "license": "ISC"

}

Edit the package.json as follows:

1) Change the value for main: from index.js to server.js:

2) add another script >> "start": "node server.js" 

The resulting package.json will look like this, with changes highlighted:

{

  "name": "xpress-sqlite-chart",

  "version": "1.0.0",

  "description": "",

  "main": "server.js",

  "scripts": {

    "start": "node server.js",

    "test": "echo \"Error: no test specified\" && exit 1"

  },

  "keywords": [],

  "author": "",

  "license": "ISC"

}

We need to add some packages with the following npm command:

npm install express csvtojson sqlite3

The three above packages are self-explanatory for those familiar with Node.js.

Add folders config, data, database, public, and routes as shown below:

Sample data

Inside the data folder, create a text file named students.csv with the following content:

 

FirstName,LastName,School

Ann,Fay,Mining

Joe,Sun,Nursing

Ben,Ray,Mining

Zoe,Cox,Business

Sam,Ray,Mining

Dan,Ash,Medicine

Pat,Lee,Computing

Kim,Day,Nursing

Tim,Rex,Computing

Rob,Ram,Business

Jan,Fry,Mining

Jim,Tex,Nursing

Ben,Kid,Business

Mia,Chu,Medicine

Ted,Tao,Computing

Amy,Day,Business

Ian,Roy,Nursing

Liz,Kit,Nursing

Mat,Tan,Medicine

Deb,Roy,Medicine

Ana,Ray,Medicine

Lyn,Poe,Computing

Amy,Raj,Nursing

Kim,Ash,Mining

Bec,Kid,Nursing

Eva,Fry,Computing

Eli,Lap,Business

Sam,Yim,Nursing

Joe,Hui,Mining

Liz,Jin,Nursing

Ric,Kuo,Medicine

Pam,Mak,Computing

Stu,Day,Business

Tom,Gad,Medicine

Bob,Bee,Computing

Jim,Ots,Computing

Tom,Mag,Medicine

Hal,Doe,Mining

Roy,Kim,Medicine

Vis,Cox,Nursing

Kay,Aga,Nursing

Reo,Hui,Nursing

Bob,Roe,Mining

 

Building a basic Node.js Express application

In the config folder, add a file named index.js with the following content:

module.exports = {  

    'database_name': 'school.db',

    // Setting port for server

    'port': process.env.PORT || 3000   

};

The above file serves as a configuration file that specifies the SQLite database name and the port number that will be used. As regards the port number, it takes the PORT environment variable, otherwise it defaults to 3000.

Add two files to the routes folder named index.js and students.js respectively. 

The content of routes/index.js is as follows:

const express = require("express");

const router = express.Router();

router.get("/", (req, res, next) => {

    res.json({"message":"Ok"})

});

module.exports = router;

The above will display a JSON object {"message":"Ok"} when the use points his browser to endpoint /.

The content of routes/student.js is as follows:

const express = require("express");

const router = express.Router();

router.get("/students", (req, res, next) => {

    res.json([

        {"id":1,"FirstName":"Ann","LastName":"Fay","School":"Mining"},

        {"id":2,"FirstName":"Ben","LastName":"Ray","School":"Nursing"}

    ])

});

module.exports = router;

The above code sets up an endpoint that displays a JSON array of some fixed sample data of students.

In the root folder of your application, create a file named server.js and add to it the following code:

const express = require("express")

const app = express()

const path = require('path');

const config = require('./config');

var bodyParser = require("body-parser");

app.use(bodyParser.urlencoded({ extended: false }));

app.use(bodyParser.json());

var index = require("./routes/");

var students = require("./routes/students");

app.use(express.static(path.join(__dirname, 'public')));

app.use("/", index);

app.use("/api", students);

// Start server

app.listen(config.port, () => {

    console.log(`Server running on port ${config.port}`);

});

 

What does the above code do?

• The first four lines load express, path and config objects.
• Next we load bodyParser so that we can extract json objects from the body of the response object
• objects index & students that point to /routes/index.js and /routes/students.js respectively.
• app.use(express.static(path.join(__dirname, 'public'))); sets a folder named public that will contain static files like *.css, *.js, *.html, images files, etc...
• We then set / to go to object index and route /api to go to object students
• Finally, we start our web server which listens on the port numbers specified in file config/index.js.

Start the node app by issuing the following command in a terminal window at the root of your application: 

nodemon

If you point your browser http://localhost:3000, you will see the following:

Pointing your browser to http://localhost:3000/api/students will reveal the following:

We now have a skeleton web app that we can build out towards our final product.

Importing CSV data into SQLite

We will use the csvtojson package to import CSV data that resides in the data/students.csv file into SQLite. 

Add a file named index.js to the database folder. Then, add the following code to database/index.js:

const sqlite3 = require('sqlite3').verbose()

const path = require('path');

const csv2json = require('csvtojson');

const config = require('../config');

const CSV_FILE = path.join(__dirname, "../data/students.csv")

let db = new sqlite3.Database(config.database_name, (err) => {

    if (err) {

      // Cannot open database

      console.error(err.message)

      throw err

    } else {

        console.log('Connected to the SQLite database.')

        db.run(`CREATE TABLE students (

            id INTEGER PRIMARY KEY AUTOINCREMENT,

            FirstName VARCHAR(80),

            LastName VARCHAR(80),

            School VARCHAR(50)

            )`,

        (err) => {

            if (err) {

                // Table already created

            } else {

                csv2json().fromFile(CSV_FILE)

                .then(data => {

                    var insert = 'INSERT INTO students (FirstName, LastName, School) VALUES (?,?,?)';

                    for(const item of data) {

                        db.run(insert, [item.FirstName,item.LastName,item.School]);

                    }

                }).catch(err => {

                    // log error if any

                    console.log(err);

                });

                // Table just created, creating some rows

            }

        });  

    }

});

module.exports = db

 

What does the above code do?

• Packages for sqlite3 and csv2json are loaded to use SQLite and conversion from CSV to JSON libraries
• We also load the path library so that we can use the path.join() function
• The configuration file in config/index.js is referenced enabling us to access the database name
• The location of our CSV file is defined in variable CSV_FILE
• the command 'new sqlite3.Database()' creates a database file named school.db, if it does not already exist
• Database table named students is created if it does not already exist
• Function csv2json().fromFile() loads contents of the CSV file into a variable named data. We then iterate through the data collection to execute a SQL INSERT statement for each item in the collection

Reading data from students table in school.db SQLite database

In our routes/students.js API file, let's read data from our students database table.  Add the following to the top of the routes/students.js file: 

const db = require("../database");

The above allows us to access the db database object defined in database/index.js. 

Instead of hardcoding JSON data in the "/students" GET route, we can read data from the database, Therefore, replace the the router.get("/students .... code in router/students.js with the following:

router.get("/students", (req, res, next) => {

    var sql = "select * from students"

    var params = []

    db.all(sql, params, (err, rows) => {

        if (err) {

          res.status(400).json({"error":err.message});

          return;

        }

        res.json({

            "message":"success",

            "data":rows

        })

      });

}); 

We execute the SQL statement "SELECT * FROM students" to read contents of students table and return the data as an array of JSON objects.

Testing our API

Let's test our application. When you point your browser to http://localhost:3000/api/students, you will see a list of all the students in the database: 

Also note the creation of a SQLite database file named school.db in the root of your web app.

Creating a chart of students by school

We will use the freely available Google Charts library to render a pie-chart illustrating number of students in every school. The starting point is to create a aggregation of the data. This will be done by adding the following additional /chart route to routes/students.js just under the existing /students route:

router.get("/chart", (req, res, next) => {

    sql = "SELECT School as school, COUNT(*) as count";

    sql += " FROM students GROUP BY School";

    var params = []

    db.all(sql, params, (err, rows) => {

        if (err) {

          res.status(400).json({"error":err.message});

          return;

        }

        res.json(rows)

      });

});

Point your browser to http://localhost:3000/api/chart and you will see a list of aggregate data as follows: 

We can render this aggregate data into a pie chart. In the public folder, create a simple HTML file named chart.html with the following content:

<!DOCTYPE html>

<html lang="en">

<head>

    <title>Pie chart of students by school</title>

    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>

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

</head>

<body>

    <div id="chart_div"></div>

    <script type="text/javascript">

        google.charts.load('current', {

            packages: ['corechart', 'bar']

        });

        google.charts.setOnLoadCallback(LoadData);

        function LoadData() {

            $.ajax({

                url: '/api/chart',

                dataType: "json",

                type: "GET",

                error: function (xhr, status, error) {

                    toastr.error(xhr.responseText);

                },

                success: function (data) {

                    PopulationChart(data);

                    return false;

                }

            });

            return false;

        }

        function PopulationChart(data) {

            var dataArray = [

                ['school', 'count']

            ];

            $.each(data, function (i, item) {

                dataArray.push([item.school, item.count]);

            });

            var data = google.visualization.arrayToDataTable(dataArray);

            var options = {

                title: 'Students by School',

                width: 700,

                height: 800

            };

            var divElement = document.getElementById('chart_div');

            var chart = new google.visualization.PieChart(divElement);

            chart.draw(data, options);

            return false;

        }  

    </script>

</body>

</html>

Note that we are making a ajax get call to endpoint /api/chart in the $.ajax() jQuery call. Also, note that new google.visualization.PieChart() generates a pie chart. With Google Charts, you can generate a multitude of chart types including bar, geo, column, area, etc...

When you point your browser to http://localhost:3000/chart.html you will see the following pie-chart.

No surprise, 27.9% of students are doing nursing during this age of covid.

CORS

In public/chart.html, change the URL in "url: '/api/chart', to "http://localhost:3000/api/chart" so that we can run the chart.html page outside of the current web server.

From within the file system of desktop computer, double click on file public/chart.html. The page loads in a browser but appears blank. Visit the browser developer tools console tab. You will see an error that resembles the following:

This suggests that we have a CORS issue. What is CORS?

Cross-origin resource sharing (CORS) is a browser mechanism which enables controlled access to resources located outside of a given domain. 

We need to enable CORS in our server app. This is done by adding the following code to server.js just before app.use("/", index); around line 15:

// enable CORS

app.use(function(req, res, next) {

    res.setHeader("Access-Control-Allow-Origin", "*");

    res.setHeader('Access-Control-Allow-Methods', 'GET, POST, OPTIONS, PUT, PATCH, DELETE');

    res.setHeader("Access-Control-Allow-Headers", "Origin, X-Requested-With, Content-Type, Accept");

    next();

}); 

Now try double-clicking on public/chart.html (or refreshing the page) and you should see that it is working fine.

What have we learned in this tutorial?

• initializing a node.js Express web app
• building a basic Node.js Express web app
• import a CSV file into SQLite database
• work with SQLite in a Node.js application
• creating a simple pie chart from aggregate data with Google Charts
• enabling CORS in a Node.js Express application

Thanks for coming this far in this article. In my next article I will show you how you can take this app and turn it into a Docker image that can be pushed to Docker-Hub and, subsequently, hosted on Microsoft Azure.