连接MySql数据库
工程依赖
新建API工程
Nuget添加
Microsoft.EntityFrameworkCore.Tools
Pomelo.EntityFrameworkCore.MySql
自动生成model
Scaffold-DbContext "server=192.168.152.131;userid=root;pwd=ghh-Fulong12;port=3306;database=newopen5d;sslmode=none;" Pomelo.EntityFrameworkCore.MySql -OutputDir Models -Force //sqlserver
解决办法
通过 Nuget 安装Microsoft.EntityFrameworkCore.Tools注意有依赖库成功后,重启 Visual Studio 即可
如果是针对单表的更新,加一个-Tables 后面是要更新的表名
Scaffold-DbContext -Force "Server=****;User Id=root;Password=****;Database=****" -Provider "Pomelo.EntityFrameworkCore.MySql" -Tables "myTable"
执行完成之后会生成指定的是Model ，注意：表必须有主键，才会生成，如果没有主机会报 Unable to generate entity type for table “xxxx” 警告，当然实体也不会生成
出现的问题：如果有表字段为 datetime类型的，生成的时候会报错 应输入标识符，处理方法：把.()去掉。此问题如果有大神看到帮忙解答下
配置appsetting.json
"ConnectionStrings": {
"MysqlConnection": "server=192.168.152.131;userid=root;pwd=ghh-Fulong12;port=3306;database=newopen5d;sslmode=none;CharSet=utf8;"
}
Startup添加配置
public void ConfigureServices(IServiceCollection services)
{
//ef mysql 配置
services.AddDbContext<newopen5dContext>(options => options.UseMySql(Configuration.GetConnectionString("MysqlConnection")));
services.AddControllers();
}
使用方式
public class WeatherForecastController : ControllerBase
{
private readonly newopen5dContext _newopen5DContext;
public WeatherForecastController(newopen5dContext newopen5D)
{
_newopen5DContext = newopen5D;
}
}
[HttpGet]
public List<Users> GetAllUsers()
{
return _newopen5DContext.Set<Users>().ToList();
}
[HttpGet]
public List<Users> GetUser(string id)
{
return _newopen5DContext.Set<Users>().Where(b => b.Id == id).ToList();
}
[HttpPost]
public async Task<ActionResult<List<Users>>> PostUserDataAsync([FromBody] Users user)
{
_newopen5DContext.Users.Add(user);
await _newopen5DContext.SaveChangesAsync();
return CreatedAtAction(nameof(GetAllUsers), user);
}
[HttpPost]
public List<Users> addUserData([FromBody] Users user)
{
_newopen5DContext.Users.Add(user);
_newopen5DContext.SaveChanges();
return GetAllUsers();
}
测试get
测试post
查看结果
第二种方式：
配置
添加依赖
MySql.Data
新建XXContext类
using MySql.Data;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using WebApplication1.Models;
namespace WebApplication1.Contexts
{
public class UserContext
{
public string ConnectionString { get; set; }
//实例化时获得MYSQLl链接字符串
public UserContext(string connectionString)
{
this.ConnectionString = connectionString;
}
/// <summary>
/// MySqlConnection 是ADO.NET中Connection对象的Mysql版本
/// 这里是通过读取appsetting.json中的链接字符串打开一个mysql的链接
/// </summary>
/// <returns></returns>
private MySql.Data.MySqlClient.MySqlConnection GetConnection()
{
return new MySql.Data.MySqlClient.MySqlConnection(ConnectionString);
}
public List<Users> GetAllUser()
{
List<Users> list = new List<Users>();
///通过connection对象打开一个链接管道
using (MySqlConnection connection = GetConnection())
{
//打开管道
connection.Open();
//MySqlCommand是ADO.NET Command对象的mysql版本，这里是声明一个操作对象来执行SQL
MySqlCommand comand = new MySqlCommand("select * from newopen5d.users", connection);
//使用Reader对象对上面SQL执行的返回结果进行读取
using (MySqlDataReader reader = comand.ExecuteReader())
{
while (reader.Read())
{
list.Add(new Users { Id = reader.GetString("id") });
}
}
}
return list;
}
}
}
Startup配置
services.Add(new ServiceDescriptor(typeof(UserContext), new UserContext(Configuration.GetConnectionString("MysqlConnection"))));
appsetting.json设置还是一样
使用
构造器注入
[ApiController]
[Route("api/[controller]/[action]")]
public class WeatherForecastController : ControllerBase
{
//private readonly newopen5dContext _newopen5DContext;
//public WeatherForecastController(newopen5dContext newopen5D)
//{
// _newopen5DContext = newopen5D;
//}
private readonly UserContext _userContext;
public WeatherForecastController(UserContext userContext)
{
_userContext = userContext;
}
[HttpGet]
public List<Users> GetAllUsers()
{
return _userContext.GetAllUser();
}
测试
