EN VI

Connect ASP.NET API to MySQL database?

How to Connect ASP.NET API to MySQL database

How can I connect to a MySQL database in an ASP.NET API (.Net 8) controller. Both are running in a seperate docker-container.

Here is my docker-compose.yml:

version: '3.4'

services:
  database:
    container_name: DB
    image: mysql:5.7
    environment:
      - MYSQL_ROOT_PASSWORD=1234
      - MYSQL_DATABASE=db
      - MYSQL_USER=user
      - MYSQL_PASSWORD=secretpassword
      - MYSQL_ALLOW_EMPTY_PASSWORD=1
    volumes:
      - dbdata:/var/lib/mysql
      - ./MySqlScripts/init.sql:/docker-entrypoint-initdb.d/1.sql:ro
    restart: on-failure
    healthcheck:
      test: ["CMD", "mysqladmin", "ping", "-h", "localhost"]
      timeout: 20s
      retries: 10
    ports:
      - 3306:3306
    command: --default-authentication-plugin=mysql_native_password

  api:
    container_name: API
    build: 
      context: .
      dockerfile: API/Dockerfile
    ports:
      - 5092:8080
    depends_on:
      - database

volumes:
  dbdata:

I want to use MySql.Data (NuGet-Package).

I tried to get a connection with this configuration of the docker-compose file but it always resulted in me getting "No MySQL host available".

Solution:

First you must add a Controller to you API or create an endpoint like this:

// here is only the endpoint
[HttpGet]
[Route("dbcheck")]
public IActionResult DbConnect() {
    return Ok();
}

Then add a MySqlConnection from the NuGet

string connectionString = "";
MySqlConnection connection = new MySqlConnection(connectionString)

As you can see, there is a string (connectionString) you must define. For the content of this string I added the following Interface to read the settings in appsettings.json. (see below, I show it later)

private readonly IConfiguration Configuration;

public NameOfTheController(IConfiguration configuration) {
    Configuration = configuration;
}

Now it is possible to use it like this and you can also see the first SQL-Command:

string connectionString = Configuration["ConnectionStrings:essenskasse"] ?? "";
MySqlConnection connection = new MySqlConnection(connectionString)

MySqlCommand command = new MySqlCommand("SELECT * FROM Test", con);

After this the connection can be established. To execute the command, please use a MySqlDataReader and fill the result of the reader in a DataTable:

connection.Open();
MySqlDataReader reader = command.ExecuteReader();
DataTable dataTable = new DataTable();
dataTable.Load(reader);
reader.Close();
connection.Close();

Don`t forget the connection.Close() at the end!

Here is a complete Demo-Controller:

using Microsoft.AspNetCore.Mvc;
using MySql.Data.MySqlClient;
using System.Data;

namespace API.Controllers {

    [Route("api/[controller]")]
    [ApiController]
    public class DemoController : ControllerBase {

        private readonly IConfiguration Configuration;

        public DemoController(IConfiguration configuration) {
            Configuration = configuration;
        }

        [HttpGet, Route("dbcheck")]
        public IActionResult DbConnect() {
            try {
                MySqlConnection connection = new MySqlConnection(Configuration["ConnectionStrings:mysql"]);

                MySqlCommand command = new MySqlCommand("SELECT * FROM Test", connection);

                connection.Open();
                MySqlDataReader reader = command.ExecuteReader();
                DataTable dataTable = new DataTable();
                dataTable.Load(reader);
                reader.Close();
                connection.Close();

                return Ok();
            }
            catch (Exception ex) {
                return BadRequest(ex.ToString());
            }
        }
    }
}

And here is the appsettings.json for the API:

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "mysql": "Server=database;Port=3306;Uid=user;Pwd=secretpassword;Database=db;SslMode=None;"
  }
}

SslMode=None is used because: https://stackoverflow.com/a/78042591

You also can not connect to localhost:3306 in a docker-context. Instead you should use the service name, yours was database, so I insert it.