.Net Core 3.1 MVC w/MySQL Cheat Sheet

A Programming › .NET cheat sheet in English by Jackfruit

Workflow for setting up a .Net 3.1 MVC Project with MySQL DB and EF

13,320 views 1,570 downloads 14 Apr 2022 History

PRs

Step 1 - Terminal

`dotnet tool install --global dotnet-ef`	You will only ever need to run this once as it is a global install not project specific
`dotnet new mvc --no-https -o ProjectDirectoryName`	Creates new dotnet MVC Project for development

Step 2 - Terminal (Inside Project Directory)

`dotnet add package Pomelo.EntityFrameworkCore.MySql --version 3.1.1`	Entity Framework Core provider for MySQL DB's
`dotnet add package Microsoft.EntityFrameworkCore.Design --version 3.1.5`	EF Core tools for DB creation/migration

These two commands must be run in your project folder every time you set up a new MVC project!

Step 3 - appsettings.json && Startup.cs

`"DBInfo": { "Name": "MySQLconnect", "ConnectionString": "server=localhost;userid=root;password=root;port=3306;database=SchemaName;SslMode=None" }`	Add this at the end of your appsettings.json file. Don't forget a comma before this new section!
`using Microsoft.EntityFrameworkCore;`	Add this to your using statements at the top of Startup.cs
`services.AddDbContext<MyContext>(options => options.UseMySql (Configuration["DBInfo:ConnectionString"]));`	Add this to the `ConfigureServices` method in Startup.cs
`services.AddSession();`	Add this to the `ConfigureServices` method in Startup.cs Adds Session
`app.UseSession();`	Add this to the `Configure` method in Startup.cs. Adds Session

Step 4 - Context File

using Microsoft.EntityFrameworkCore;
namespace ***ProjDirName***.Models
{ 
    public class ***DBContextClassName*** : DbContext 
    { 
        public ***DBContextClassName***(DbContextOptions options) : base(options) { }
        public DbSet<***ModelName***> ***TableName*** { get; set; }
        *Add more tables here*
    }
}

Sample code for context model file

Step 5 - Model Example

using System;
using System.ComponentModel.DataAnnotations;
namespace ***ProjDirName***.Models
{
    public class ***ModelName***
    {
        [Key]
        public int ***ModelName***Id { get; set; }
        *Set all properties here*
        public DateTime CreatedAt { get; set; } = DateTime.Now;
        public DateTime UpdatedAt { get; set; } = DateTime.Now;
        *Set Navigation Properties*
    }
}

Class model for database table

Step 6 - Context Dependency Injection

using System;
using System.Collections.Generic;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using ***ProjDirName***.Models;

public HomeController(***ContextClassName*** context)
        {
            _context = context;
        }
     
        [HttpGet("")]
        public IActionResult Index()
        {
            List<***ModelName***> *VariableName* = _context.***TableName***.ToList();
            
            return View();
        }

Sample of the code required to make DB Context available in controllers

Step 7 - Terminal - Migration

`dotnet ef migrations add MigrationName`	Creates a migration in preparation for Creating/Updating your DB Schema
`dotnet ef database update`	Updates Database schema with most recent migration

This step to be done after all previous steps are complete and, preferably, after all models and relationships are in place.

Badges

Downloaded from Unusual Locations × 1

Comments (1)

Jackfruit 19:58 17 Apr 2022

Updated step 3 to include `using` statement required to set DBContext config