Connecting C# to MySQL and MariaDB in .NET Blazor Using EF Core

Why dotConnect for MySQL?

dotConnect for MySQL is the ideal data provider for all MySQL and MariaDB-related operations. It offers features like on-the-fly connection and flexible configuration, as well as easy integration into Visual Studio and enhanced ORM support.

Prerequisites

To get things going, you will need the following:

• Visual Studio 2022: This is our IDE of choice. If you don't have it on your machine, you can visit the official website to download and install the free Community Edition.
• Blazor is a feature of ASP.NET for building interactive web UIs using C# instead of JavaScript. It's real .NET running in the browser on WebAssembly.
• dotConnect for MySQL: A high-performance ADO.NET data provider for MySQL with enhanced ORM support and database connectivity features.
• MySQL test database: A sample database provided by MySQL for learning and testing.

Download and activate dotConnect for MySQL

Visit the dotConnect for MySQL download page. You can either get it for a free trial or purchase a full version at once. The free trial gives you 30 days to explore all features, but it cannot be used for deploying commercial projects and does not require a license key for activation.

If you're ready to purchase the full version, select Buy Now to add it to your cart.

Download and install the trial version

For the trial version, simply download and install dotConnect for MySQL on your machine. No license key is required for the trial, and you can start exploring the product immediately.

Purchase and access the full version

After purchasing the full version, go to your profile's Licenses page. You'll find a list of your licenses. Find dotConnect for MySQL and click Details. Here, you'll find your license details, including the activation key.

Download the full version

Click Download Product Versions to access the list of available versions. Select the most recent version and download the installer.

Run the downloaded file and follow the on-screen instructions to install dotConnect for MySQL on your machine. This process will install the software and activate all features associated with your license key.

Create a Blazor Web App project

Next, you need to create a Blazor project. To do that, open Visual Studio and click Create a new project.

In the Search Template field, search for Blazor Web App and click it.

Give your project a name. Ours will be called MySQLBlazor. Then, select .NET 8 as the target framework, leave all the other options as they are, and click Create.

Install packages

Next, you need to install dotConnect for MySQL for your project via the NuGet Package Manager in Visual Studio. In the taskbar, click Tools > NuGet Package Manager > Manage NuGet Packages for Solution.

The NuGet Package Manager opens. Click Browse and search for Devart.Data.MySQL.EF Core. Then select your project and click Install.

Next, open the Package Manager Console and run this command:

Install-Package Microsoft.EntityFrameworkCore.Tools

This installs the EF Core tools needed to run the application.

Run the application by pressing F5. The scaffolded web page opens.

Create a project

Next, create a new folder (in our case, it will be called Models) and add a new class called (say, Actor).

The Actor class will have the following structure:

public class Actor
{
    [Key]
    public int ActorId { get; set; }
    [Required]
    public string FirstName { get; set; }
    [Required]
    public string LastName { get; set; }
    [Required]
    public DateTime LastUpdate { get; set; }

}

Next, you need to scaffold DbContext. To do that, in the Models folder, create a new class called ActorDbContext and copy this code into it:

public class ActorDbContext : DbContext
{
    public ActorDbContext(DbContextOptions options) : base(options)
    {

    }   
    public DbSet Actors { get; set; }
}

Your project should look like this now:

Create a MySQL table

Now it's time to create a table based on the defined DbContext. But first, you will need to set a few things up.

In your Blazor project, create a class named DatabaseConfiguration and copy the following code into it:

public static class DatabaseConfiguration
{
    public static string GetConnectionString()
    {
        string databasePath = "" +
            "Server=127.0.0.1;" +
            "UserId=TestUser;" +
            "Password=TestPassword;" +
            "Port=3306;" +
            "Database=devart_test";
        string licenseKey = "your-license-key";
        return $"{databasePath};LicenseKey={licenseKey};";
    }

}

DatabaseConfiguration has a singular method GetConnectionString, which returns a concatenated string of the connection string of your MySQL database and the license key for your dotConnect for MySQL (you can find this on the customer portal).

The MySQL connection string contains:

• Server
• Port
• User ID
• Password
• The previously created database

Note that this approach is for tutorial purposes only. You should store your activation key in a more secure way such as Environment Secrets. Also, don't forget to replace User ID and Password with your actual credentials.

Next, create a class called, say, MyDbContextFactory and add the following code:

public class MyDbContextFactory : IDesignTimeDbContextFactory
 {
     public ActorDbContext CreateDbContext(string[] args)
     {
         var optionsBuilder = new DbContextOptionsBuilder();
         string connectionString = DatabaseConfiguration.GetConnectionString();
         optionsBuilder.UseMySql(connectionString);

         return new ActorDbContext(optionsBuilder.Options);
     }
 }

The MyDbContextFactory class is implementing the IDesignTimeDbContextFactory interface. This interface creates instances of our DbContext (ActorDbContext) at design time.

So far, your project should look like this.

Next, in Program.cs, add these two lines of code so your project knows the correct database string to reference.

string connectionString = DatabaseConfiguration.GetConnectionString();
builder.Services.AddDbContext(options =>
    options.UseMySql(connectionString));

Now that everything has been set, let's create the first migration.

Run the following code from the Package Manager Console:

Add-Migration InitialCreate -Context ActorDbContext

This command creates a Migrations folder in the project based on the current state of our ActorDbContext.

To finish this process and create the Actor table, you need to update the database. So go ahead and run the following command in the Package Manager Console as well:

Update-Database

If all goes well, you should get a response saying "Applying migration 20240907153453_InitialCreate". This means our migration has been applied, and our Actor table has been created.

Before we move on, let us create a services folder that will act as the hub for where most of the business logic will reside. Inside this services folder, create an interface called IActorService and add the following code:

public interface IActorService
 {
     Task<List<Actor>> GetActorsAsync();
     Task<Actor> GetActorByIdAsync(int actorId);
     Task AddActorAsync(Actor actor);
     Task UpdateActorAsync(Actor actor);
     Task DeleteActorAsync(int actorId);
 }

Then, still in the services folder, create a new class that will implement an interface called ActorService and copy the following code into it:

public class ActorService : IActorService
 {
     private readonly ActorDbContext _context;

     public ActorService(ActorDbContext context)
     {
         _context = context;
     }

     public async Task<List<Actor>> GetActorsAsync()
     {
         return await _context.Actors.ToListAsync();
     }

     public async Task<Actor> GetActorByIdAsync(int actorId)
     {
         var actor = await _context.Actors.FindAsync(actorId);
         if (actor == null)
         {
             throw new Exception("Actor not found");
         }
         return actor;
     }

     public async Task AddActorAsync(Actor actor)
     {
         actor.LastUpdate = DateTime.Now;
         _context.Actors.Add(actor);
         await _context.SaveChangesAsync();
     }

     public async Task UpdateActorAsync(Actor actor)
     {
         actor.LastUpdate = DateTime.Now;
         _context.Entry(actor).State = EntityState.Modified;
         await _context.SaveChangesAsync();
     }

     public async Task DeleteActorAsync(int actorId)
     {
         var actor = await _context.Actors.FindAsync(actorId);
         if (actor != null)
         {
             _context.Actors.Remove(actor);
             await _context.SaveChangesAsync();
         }
     }
 }

Then, make sure to register both the interface and its implementation by adding the below code to Program.cs:

builder.Services.AddScoped();

At this point, your project should look like this:

Insert MySQL data

Now, let's try to insert some data into our Actor table. From your project, navigate to the Components folder, then to the Pages folder, and then create a Razor component.

Call this Razor component Actor.Razor and add the following code to the Actor.Razor page:

@page "/actor"
@rendermode InteractiveServer
@inject NavigationManager Navigation

<h3>Actor</h3>

<button> class="btn btn-primary" @onclick="OpenAddActorModal">Add New Actor</button>

@code {
    private void OpenAddActorModal()
    {
        Navigation.NavigateTo("/add-actor");
    }
}

This creates a button that leads to the add-actor page. Now, let's create that page.

From the Pages folder under the components, add another Razor component called AddActor.Razor and copy this code into it:

@page "/add-actor"
@rendermode InteractiveServer
@using MySQLBlazor.Models
@inject MySQLBlazor.Services.IActorService ActorService
@inject NavigationManager Navigation

<h3>Add New Actor</h3>

<div class="mb-3">
    <label for="firstName" class="form-label">First Name</label>
    <input type="text" class="form-control" id="firstName" @bind="newFirstName" />
</div>
<div class="mb-3">
    <label for="lastName" class="form-label">Last Name</label>
    <input type="text" class="form-control" id="lastName" @bind="newLastName" />
</div>
<button class="btn btn-secondary" @onclick="Cancel">Cancel</button>
<button class="btn btn-primary" @onclick="InsertActor">Save</button>

@code {
    private string newFirstName;
    private string newLastName;

    private async Task InsertActor()
    {
        var newActor = new Models.Actor
            {
                FirstName = newFirstName,
                LastName = newLastName,
                LastUpdate = DateTime.UtcNow
            };
        await ActorService.AddActorAsync(newActor);

        Navigation.NavigateTo("/actor");
    }

    private void Cancel()
    {
        // Close the modal and navigate back to the actor page
        Navigation.NavigateTo("/actor");
    }
}

Now let's add the previous Actor component to the navigation bar. To do that, go to the Components folder, and there, in the Layouts folder, select NavMenu.Razor.

Next, replace the code in NavMenu with the following:

<div class="top-row ps-3 navbar navbar-dark">
    <div class="container-fluid">
        <a class="navbar-brand" href="">MySQLBlazor</a>
    </div>
</div>

<input type="checkbox" title="Navigation menu" class="navbar-toggler" />

<div class="nav-scrollable" onclick="document.querySelector('.navbar-toggler').click()">
    <nav class="flex-column">
        <div class="nav-item px-3">
            <NavLink class="nav-link" href="" Match="NavLinkMatch.All">
                <span class="bi bi-house-door-fill-nav-menu" aria-hidden="true"></span> Home
            </NavLink>
        </div>
        <div class="nav-item px-3">
            <NavLink class="nav-link" href="actor">
                <span class="bi bi-list-nested-nav-menu" aria-hidden="true"></span> Actors
            </NavLink>
        </div>

    </nav>
</div>

Now run the application and go to Actors > Add New Actor.

After adding a new actor and clicking Save, you will be redirected to the Actor page.

Read MySQL data

Let's modify the application to see the actor that has just been created. To do that, replace the code in Actor.Razor with the one below:

@page "/actor"
@rendermode InteractiveServer
@inject NavigationManager Navigation
@inject MySQLBlazor.Services.IActorService ActorService

<h3>Actor Table</h3>

@if (actors == null)
{
    <p><em>Loading...</em></p>
}
else
{
    <table class="table">
        <thead>
            <tr>
                <th>ID</th>
                <th>First Name</th>
                <th>Last Name</th>
                <th>Last Update</th>
            </tr>
        </thead>
        <tbody>
            @foreach (var actor in actors)
            {
                <tr>
                    <td>@actor.ActorId</td>
                    <td>@actor.FirstName</td>
                    <td>@actor.LastName</td>
                    <td>@actor.LastUpdate</td>
                </tr>
            }
        </tbody>
    </table>
}

<button class="btn btn-primary" @onclick="OpenAddActorModal">Add New Actor</button>

@code {
    private List<Models.Actor> actors;

    protected override async Task OnInitializedAsync()
    {
        // Load the actors when the component is initialized
        actors = await ActorService.GetActorsAsync();
    }


    private void OpenAddActorModal()
    {
        Navigation.NavigateTo("/add-actor");
    }
}

Run the application and go to Actors. Now you should see the actor displayed.

Update MySQL data

Let's assume that Seth wants to change his last name. Our application should be able to handle that.

So, from the Pages folder, create a new Razor component called Update.Razor and add the following code to the file:

@page "/update/{actorId:int}"
@rendermode InteractiveServer
@using MySQLBlazor.Models
@inject MySQLBlazor.Services.IActorService ActorService
@inject NavigationManager Navigation

<h3>Update Actor</h3>

@if (actor == null)
{
    <p><em>Loading...</em></p>
}
else
{
    <div class="mb-3">
        <label for="firstName" class="form-label">First Name</label>
        <input type="text" class="form-control" id="firstName" @bind="actor.FirstName" />
    </div>
    <div class="mb-3">
        <label for="lastName" class="form-label">Last Name</label>
        <input type="text" class="form-control" id="lastName" @bind="actor.LastName" />
    </div>
    <button class="btn btn-secondary" @onclick="Cancel">Cancel</button>
    <button class="btn btn-primary" @onclick="UpdateActor">Save</button>
}

@code {
    [Parameter]
    public int actorId { get; set; }

    private Models.Actor actor;

    protected override async Task OnInitializedAsync()
    {
        // Load the actor to be updated
        actor = await ActorService.GetActorByIdAsync(actorId);
    }

    private async Task UpdateActor()
    {
        // Update the actor using the service
        await ActorService.UpdateActorAsync(actor);

        // Redirect back to the actor list page
        Navigation.NavigateTo("/actor");
    }

    private void Cancel()
    {
        // Navigate back to the actor list page
        Navigation.NavigateTo("/actor");
    }
}

Then, in Actor.Blazor, modify the Table element in the else block with this code:

<table class="table">
    <thead>
        <tr>
            <th>ID</th>
            <th>First Name</th>
            <th>Last Name</th>
            <th>Last Update</th>
            <th>Actions</th>
        </tr>
    </thead>
    <tbody>
        @foreach (var actor in actors)
        {
            <tr>
                <td>@actor.ActorId</td>
                <td>@actor.FirstName</td>
                <td>@actor.LastName</td>
                <td>@actor.LastUpdate</td>
                <td>
                    <button class="btn btn-warning btn-sm" @onclick="() => NavigateToUpdate(actor.ActorId)">Update</button>
                </td>
            </tr>
        }
    </tbody>
</table>

Then, in the @code block, add this method:

private void NavigateToUpdate(int actorId)
{
    Navigation.NavigateTo($"/update/{actorId}");
}

This will navigate to the page you have just created with the particular Actor ID.

Run the application and go to the Actors page.

If you click Update, this will open the update page with that particular actor, where you will be able to change his name.

After changing the name, click Save, and the actor's name will be updated.

Delete MySQL data

Finally, let's try to remove an actor from our database.

For reference, there is an actor Johnny Blaze that needs to be removed. To do that, on the Actor.Razor page, add this line of code to the table under the Update button.

<button class="btn btn-danger btn-sm" @onclick="() => DeleteActor(actor.ActorId)">Delete</button>

Then add this method to the @code block:

private async Task DeleteActor(int actorId)
{

    await ActorService.DeleteActorAsync(actorId);

    actors = await ActorService.GetActorsAsync();
}

This method takes that particular Actor ID and calls the DeleteActorAsync service.

Next, run the application and proceed to Actors.

Now, simply click Delete to delete the required actor.

Video tutorial: How to Connect a .NET Blazor Application to a MySQL Database

Conclusion

Now you know how to connect a Blazor application to a MySQL database using dotConnect for MySQL. You know how to create new tables, insert data into them, read, update, and delete records. Which means you are all set to try dotConnect for MySQL in your real-life project. Simply download dotConnect for MySQL for a free 30-day trial and give it a go today!