(C# EF Core) MySQL Database Connectivity in an ASP.NET Core Razor Page Project

Things you will Need

These must be available -

1. MySQL Server must be running and accessible.
2. Your UserID, Password, Server IP must be available.
3. Verify that you can connect to the MySQL server with your UserID and Password.

Video Explanation

Please watch the following youtube video:

Add Nuget Package

WE SHALL USE THE DLL PROVIDED BY Oracle Corp. THERE ARE OTHER OPTIONS BUT DO NOT USE THEM.

Run this on package manager console -

PM > Install-Package MySql.EntityFrameworkCore -Version 5.0.8

Configure your Startup.cs File

This is the bare minimum startup.cs file that will work for our case -

// Visual Studio 2019 with .NET 5 used 
//

// namespaces 
using Microsoft.AspNetCore.Builder;

using Microsoft.AspNetCore.Hosting;

using Microsoft.EntityFrameworkCore;

using Microsoft.Extensions.DependencyInjection;

// using MyRazorApp.Pages.Models; 

using MySql.Data.MySqlClient;

namespace MyRazorApp
{

  public class Startup
  {

    public void ConfigureServices(IServiceCollection services)
    {

      // Install-Package MySql.EntityFrameworkCore -Version 5.0.8 
      MySqlConnectionStringBuilder conn =
          new MySqlConnectionStringBuilder()
          {

      // ENTER YOUR OWN USERID AND PASSWORD 

      // any suitable name can be given for your database 

      // EF Core code-first will create it for you 

      // TODO 
            Database = "mydb",
            UserID = "-your-own-",
            Password = "-your-own-",
            Server = "127.0.0.1"
          };

      services.AddDbContext<MyProjectContext>(
          opt => opt.UseMySQL(conn.ConnectionString)
      );

      services.AddRazorPages();

    }

    public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
    {

      app.UseRouting();

      app.UseEndpoints(endpoints =>
      {

        endpoints.MapRazorPages();

      });

    }

  }

}

Following is the Program.cs file - the same as visual studio creates for you -

// Program.cs as created by visual studio 
using Microsoft.AspNetCore.Hosting;

using Microsoft.Extensions.Hosting;

public class Program
{

  public static void Main(string[] args)
  {

    CreateHostBuilder(args).Build().Run();

  }

  public static IHostBuilder CreateHostBuilder(string[] args) =>
    Host.CreateDefaultBuilder(args)
        .ConfigureWebHostDefaults(webBuilder =>
        {
          webBuilder.UseStartup<Startup>();

        });

}

Model and Context Classes

Use these simple model and context classes

// model class 
public class Item
{

  [Key]
  [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
  public int ID { get; set; }

  // better to specify - TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT 
  [Column(TypeName = "TEXT")]
  public String Name { get; set; }
}


// dbcontext 
public class MyProjectContext : DbContext
{

  public MyProjectContext(DbContextOptions options) : base(options)
  {

    // IMPORTANT: you will get exception here 
    // if your MySql connection fails 
    Database.EnsureCreated();

  }


  // MUST be PLURAL 
  public DbSet<Item> Items { get; set; }
}

The Index Razor Page

Use this for the Index.cshtml file

@page

// your namespace - mine is MyRazorApp 
@model MyRazorApp.Pages.IndexModel

@addTagHelper *, Microsoft.AspNetCore.Mvc.TagHelpers

<form method="post">

  <table>

    <tr>

      <td>
        <label asp-for="DataItem.Name"></label>
      </td>

      <td>
        <input asp-for="DataItem.Name" />
      </td>

    </tr>

    <tr>
      <td></td>

      <td>
        <input type="submit" />
      </td>

    </tr>

  </table>

</form>

<span>@TempData["Message"]</span>

Use this for the Index.cshtml.cs file

// use your namespace names - mine is MyRazorApp 
using Microsoft.AspNetCore.Mvc;

using Microsoft.AspNetCore.Mvc.RazorPages;

using MyRazorApp.Pages.Models;

using System;

using System.Threading.Tasks;

namespace MyRazorApp.Pages
{

  public class IndexModel : PageModel
  {

    readonly MyProjectContext _ctx;

    public IndexModel(MyProjectContext ctx)
    {

      _ctx = ctx;

    }

    [BindProperty]
    public Item DataItem { get; set; }

    // used for displaying message upon redirect 
    [TempData]
    public String Message { get; set; }

    // on post handler 
    public async Task<IActionResult> OnPostAsync()
    {

      _ctx.Items.Add(DataItem);

      try
      {

        await _ctx.SaveChangesAsync();

        Message = "Saved OK!";

      }

      catch(Exception ex)
      {

        Message = "failed: " + ex.InnerException.Message;

      }

      return RedirectToPage();

    }

  }

}

Run the Project

Run the project to see that you can add records!

I am appending my csproj file. I created my project with Visual Studio 2019. But this opens and compiles in Visual Studio 2022 also

<Project Sdk="Microsoft.NET.Sdk.Web">

  <PropertyGroup>
    <TargetFramework>net5.0</TargetFramework>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="MySql.EntityFrameworkCore" Version="5.0.8" />
  </ItemGroup>

</Project>