Tech Junkie Blog - Real World Tutorials, Happy Coding!: ACME Bank: Step 9: Create Our Database In MySQL Using Asp.Net Core And EntityFrameworkCore

Tuesday, October 6, 2020

ACME Bank: Step 9: Create Our Database In MySQL Using Asp.Net Core And EntityFrameworkCore

In this post we are going to create our first Entity Framework migration and creating an actual database in MySQL.

In order to do that we need to add a few NuGet packages to our Asp.Net Core project including the Entity Framework Core package. But, before we do that we want to find out what what version of .NET Core we are running. Obviously I have it memorized, but for the rest of you, you can type the command dotnet --version to figure out what the version is :) It's always a good idea to try and match the package version with the .NET Core runtime you have. It's one of the more annoying thing with Angular and Asp.Net Core, it's changing constantly. So as you can see my version 3.1.xxx so I should try to install packages that are made for 3.1.x.

The first package we are going to install is the Microsoft.EntityFrameworkCore. So open the ACMEBank.API project with Visual Studio Code. Press Ctrl+Shift+P and type NuGet and select

"NuGet Package Manager: Add Package", then type Micrsoft.EntityFrameworkCore press Enter, select the package like in the screenshot

You want to select the version that matches the .NET Core runtime, once you select the version, a "restore" button will appear at the bottom of the screen, click on it to add it to the project.

Now, the do the same thing with the following packages:

Pomelo.EntityFrameworkCore.MySql
Microsoft.EntityFrameworkCore.Design

Once you add the packages, you can use in your project like the screenshot below.

Now we are all set to create our first Entity Framework Core migration package and create our database.

We want to start out with something simple so we want to first set and get the connection string for the database. In Asp.Net Core there's a configuration file called appSettings.json, we are going to a our MySQL connection string in this file so that our Asp.Net Core application can connect to our MySQL database.

Open the appSettings.json file and add the following lines after the first { character

  "ConnectionStrings": {

	"DefaultConnection":"Server=localhost; Database=acmebank; Uid=devuser; Pwd=P@ssw0rd"

},

The completed file should look like the following

  {

  "ConnectionStrings": {

	"DefaultConnection":"Server=localhost; Database=acmebank; Uid=devuser; Pwd=P@ssw0rd"

},

  "Logging": {

    "LogLevel": {

      "Default": "Information",

      "Microsoft": "Warning",

      "Microsoft.Hosting.Lifetime": "Information"

    }

  },

  "AllowedHosts": "*"

}

Now we are ready to write some code to create our database using the code first approach. First create a folder at the root of the project call Models, and create a class call Value. This class will be used by Entity Framework our columns for the Values table that we will create.

namespace ACMEBank.API.Models
{
    public class Value
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }
}

Now create a folder in the root of the project call "Entities" and create a class call EntitiesContext

using ACMEBank.API.Models;
using Microsoft.EntityFrameworkCore;

namespace ACMEBank.API.Entities
{
    public class EntitiesContext : DbContext
    {
        public EntitiesContext(DbContextOptions options) : base(options){}

        public DbSet Values {get;set;}


    }
}

Finally, we are ready to make our last code change, open the Startup.cs file and add the following line to the ConfigureServices method

services.AddDbContext(x => x.UseMySql(Configuration.GetConnectionString("DefaultConnection")));

The completed code should look like the following

        public void ConfigureServices(IServiceCollection services)

        {

            services.AddDbContext(x => x.UseMySql(Configuration.GetConnectionString("DefaultConnection")));

            services.AddControllers();

        }")));

So the code ties together to make our first migration, the Value class used for the columns in the database, the DbSet in EntitiesContext class represents the tables. Finally, the Startup code, tells Asp.Net Core that the database provider is MySQL, and it also injects the connection string into the EntitiesContext instance when it's created. We now have all the parts we need to create our acmebank database. All we need to do now is run the .NET Core Entity Framework Core tool in the command line.

Open a terminal session in Visual Studio, and go into the ACMEBank.API root folder. We are going to install the Entity Framework tools in the .NET Core CLI. Type the following command in the terminal dotnet tool install --global dotnet-ef