Published: April 19 2023

.NET 7.0 + MySQL - Connect to MySQL Database with Dapper in C# and ASP.NET Core

Tutorial built with .NET 7.0, Dapper 2.0 and MySQL

This is a quick post to show how to connect a .NET 7 API to a MySQL database with C# using Dapper and ADO.NET.

The example code is from of a .NET 7 CRUD API tutorial I posted recently, the full project and documentation is available at .NET 7.0 + Dapper + MySQL - CRUD API Tutorial in ASP.NET Core.

Dapper ORM (Object Relational Mapper)

Dapper is a micro ORM that supports executing raw SQL queries and mapping results to C# objects and collections, it's implemented as a set of extension methods on top of the ADO.NET IDbConnection interface, so ADO.NET provides the connection to the MySQL database when using Dapper.

MySQL Database

MySQL is a free and open-source full featured relational database, it's a good option for any size application in production or development. The ADO.NET provider for MySQL (MySql.Data) is used to connect to the database.


Connect to MySQL in C# with Dapper and ADO.NET

Below is the data context class from the example .NET 7 CRUD API, the CreateConnection() method connects to the MySQL database by creating a new instance of the ADO.NET MySqlConnection() class with the specified connection string.

The Init() method creates the MySQL database and tables if they don't exist, it is executed once on API startup. The Dapper extension method ExecuteAsync() is used to execute SQL queries.

namespace WebApi.Helpers;

using System.Data;
using Dapper;
using Microsoft.Extensions.Options;
using MySql.Data.MySqlClient;

public class DataContext
{
    private DbSettings _dbSettings;

    public DataContext(IOptions<DbSettings> dbSettings)
    {
        _dbSettings = dbSettings.Value;
    }

    public IDbConnection CreateConnection()
    {
        var connectionString = $"Server={_dbSettings.Server}; Database={_dbSettings.Database}; Uid={_dbSettings.UserId}; Pwd={_dbSettings.Password};";
        return new MySqlConnection(connectionString);
    }

    public async Task Init()
    {
        await _initDatabase();
        await _initTables();
    }

    private async Task _initDatabase()
    {
        // create database if it doesn't exist
        var connectionString = $"Server={_dbSettings.Server}; Uid={_dbSettings.UserId}; Pwd={_dbSettings.Password};";
        using var connection = new MySqlConnection(connectionString);
        var sql = $"CREATE DATABASE IF NOT EXISTS `{_dbSettings.Database}`;";
        await connection.ExecuteAsync(sql);
    }

    private async Task _initTables()
    {
        // create tables if they don't exist
        using var connection = CreateConnection();
        await _initUsers();

        async Task _initUsers()
        {
            var sql = """
                CREATE TABLE IF NOT EXISTS Users (
                    Id INT NOT NULL AUTO_INCREMENT,
                    Title VARCHAR(255),
                    FirstName VARCHAR(255),
                    LastName VARCHAR(255),
                    Email VARCHAR(255),
                    Role INT,
                    PasswordHash VARCHAR(255),
                    PRIMARY KEY (Id)
                );
            """;
            await connection.ExecuteAsync(sql);
        }
    }
}

 


Need Some MySQL Help?

Search fiverr for freelance MySQL developers.


Follow me for updates

On Twitter or RSS.


When I'm not coding...

Me and Tina are on a motorcycle adventure around Australia.
Come along for the ride!


Comments


Supported by