代码之家 › 专栏 › 技术社区 › KnYL3R

连接ASP。NET API到MySQL数据库

docker-compose api docker asp.net mysql

KnYL3R · 技术社区 · 1 年前

如何在ASP中连接到MySQL数据库。NET API(.NET 8)控制器。两者都在一个单独的docker容器中运行。

这是我的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:

我想使用MySql。数据(NuGet包)。

我试图用docker compose文件的这种配置建立连接,但它总是导致我得到“没有可用的MySQL主机”。

1 回复 | 直到 1 年前

Kretchen001 1 年前

首先,您必须向API添加一个控制器或创建一个端点,如下所示:

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

然后从NuGet添加MySqlConnection

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

正如您所看到的,您必须定义一个字符串(connectionString)。对于这个字符串的内容,我添加了以下接口来读取appsettings.json中的设置。(见下文,我稍后展示)

private readonly IConfiguration Configuration;

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

现在可以这样使用它了,您还可以看到第一个SQL命令:

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

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

在此之后,可以建立连接。要执行该命令,请使用MySqlDataReader,并将读取器的结果填写在DataTable中:

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

别忘了 联系关 最后!

这是一个完整的演示控制器:

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());
            }
        }
    }
}

下面是API的appsettings.json:

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

SslMode=无 之所以使用,是因为: https://stackoverflow.com/a/78042591

您也不能在docker上下文中连接到localhost:3306。相反,您应该使用服务名称,您的名称是数据库,所以我插入了它。