How can I connect to a MySQL database in an ASP.NET API (.Net 8) controller. Both are running in a separate 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".
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.