.net core .net6 webapi 连接mysql 8

1.表结构：

CREATE TABLE `table2` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `myname` varchar(255) NOT NULL,
  `create_time` DATETIME  NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

2。新建一个名为“WebApiMySql”的 webapi项目。

3。nuget 下载“Pomelo.EntityFrameworkCore.MySql”包。

4。新建实体类，注意：类名和表名保持一致：

using System.ComponentModel.DataAnnotations.Schema;
using System.ComponentModel.DataAnnotations;

namespace WebApiMySql.Models
{
    public class table2
    {
        [Key] //主键 
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]  //设置自增
        public long id { get; set; }
        public string myname { get; set; }

        public DateTime create_time { get; set; }
    }
}

5。新建DbContext,注意：DbSet里的属性名和表名保持一致:

using Microsoft.EntityFrameworkCore;
using WebApiMySql.Models;

namespace WebApiMySql.Ctx
{
    public class w2DbContext: DbContext
    {
        //构造函数
        public w2DbContext(DbContextOptions<w2DbContext> option) : base(option)
        {
        }

        //实体类名“table2”，本字段属性名“table2”
        public DbSet<table2> table2 { get; set; }
        
    }
}

6.修改appsettings.json，增加连接字符串：

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "w2DbContext": "Server=128.0.0.33;Database=w2;uid=jojo;pwd=Yameking2022#;"
  }
}

 

7.修改 Program.cs, 在“var app = builder.Build();” 这一行的上面增加以下内容：

builder.Services.AddDbContext<w2DbContext>(option => {
    string connStr = builder.Configuration.GetConnectionString("w2DbContext");
    option.UseMySql(connStr, ServerVersion.AutoDetect(connStr), null);
});

 

 

8。新建api控制器“vvController”

using Microsoft.AspNetCore.Mvc;
using WebApiMySql.Ctx;

// For more information on enabling Web API for empty projects, visit https://go.microsoft.com/fwlink/?LinkID=397860

namespace WebApiMySql.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class vvController : ControllerBase
    {
        private readonly w2DbContext _w2db;

        public vvController(w2DbContext w2db)
        {
            _w2db=w2db;
        }
        // GET: api/<vvController>
        [HttpGet]
        public IEnumerable<string> Get()
        {
            try
            {
                var model= _w2db.table2.FirstOrDefault();
                if (model != null)
                {
                    return new string[] { "value1", model.myname };
                }
            }
            catch (Exception ex)
            {
                return new string[] { "value1", ex.Message };
            }

            return new string[] { "value1", "value2" };
        }
 
    }
}

即可使用。