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

These are the minimal steps for connecting to a MySQL database server with EF Core. A simple application of one textbox form has been used to demonstrate how records can be inserted into a table in a MySQL database. The tables and database are created automatically by EF Core on the basis of code first approach.
(Rev. 06-Nov-2022)

Categories | About |     |  

Parveen,

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

The current version provided by Oracle Corp. at the time of writing of this article is 5.0.x - so your project should be configured for .NET 5. The best way to ensure this is to use Visual Studio 2019 instead of 2022. ONCE THEY RELEASE VERSION 6, THEN YOU CAN USE VISUAL STUDIO 2022 BY FOLLOWING THE SAME STEPS WITHOUT ANY CHANGE. If you are learning through our course then use the attached download, which will work out of the box.

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>


This Blog Post/Article "(C# EF Core) MySQL Database Connectivity in an ASP.NET Core Razor Page Project" by Parveen is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.