创建好Web API,并发布到Docker后,就需要连接数据库,做一些基本操作。
本文介绍如何 连接SQL Server 和 连接MySQL
连接SQL Server
通过NuGet添加相关的包:
Microsoft.EntityFrameworkCore.SqlServer
2、新增数据库连接配置
在 在appsettings.json 新增数据库连接
"ConnectionStrings": {
"SQLServerConnection": "Server=192.168.8.1;Database=Test;User Id=sa;Password=123456;",
}
3、在Startup中注册服务
var connStr = Configuration.GetConnectionString(“SQLServerConnection”);
services.AddDbContext(builder => builder.UseSqlServer(connStr));
4、构建实体类
public class Product
{
[Key] //[Key]特性标识表明Code为主键。
public string Code { get; set; }
public string Name { get; set; }
public string Descript { get; set; }
public int Numbers { get; set; }
}
5、新建DBContext类
public class SqlContext : DbContext
{
public SqlContext(DbContextOptions<SqlContext> builder) : base(builder)
{
}
//注意,这里必须与表名一致 如不一致,必须要OnModelCreating 方法,指定表名
public DbSet<Product> Products { get; set; }
/// <summary>
/// 指定表名
/// </summary>
/// <param name="modelBuilder"></param>
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Product>().ToTable("Product");
}
}
6、新建一个Controller
新增相关数据库操作接口
[Produces("application/json")]
[Route("api/product")]
public class ProductsController
{
private readonly SqlContext _context;
public ProductsController(SqlContext context)
{
_context = context;
}
/// <summary>
/// 获取产品列表
/// </summary>
/// <returns>产品列表</returns>
[Route("GetAll")]
[HttpGet]
public List<Product> GetAllProducts()
{
var products = _context.Products.ToList<Product>();
return products;
}
}
调试运行
调用接口 https://localhost:49165/api/product/getall,返回信息如下:
连接MySQL
通过NuGet添加相关的包:
Pomelo.EntityFrameworkCore.MySql
注:
网上很多资料都是关于引用 MySql.Data.EntityFrameworkCore 连接MySql的,但MySql.Data.EntityFrameworkCore目前好像已经弃用了
引用 MySql.Data.EntityFrameworkCore后,会报以下错误:
MySql.Data.EntityFrameworkCore.Query.Internal.MySQLSqlTranslatingExpressionVisitorFactory' from assembly 'MySql.Data.EntityFrameworkCore, Version=8.0.22.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d' does not have an implementation
2、新增MySql数据库连接配置
在 在appsettings.json 新增数据库连接
"ConnectionStrings": {
"MySQLConnection": "Server=192.168.8.1;port=3316;database=test;uid=root;pwd=123456;SslMode=None;",
}
3、在Startup中注册服务
var connMysqlStr = Configuration.GetConnectionString("MySQLConnection");
services.AddDbContextPool<MySqlContext>(builder => builder.UseMySql(connMysqlStr, new MySqlServerVersion(new Version(8, 0, 11))));
注: 这里必须指定 ServerVersion,否则无法编译通过
4、新建DBContext类
public class MySqlContext: DbContext
{
public MySqlContext(DbContextOptions<MySqlContext> builder) : base(builder)
{
}
protected override void OnConfiguring(DbContextOptionsBuilder optionBuilder)
{
base.OnConfiguring(optionBuilder);
}
//注意,这里必须与表名一致 如不一致,必须要OnModelCreating 方法,指定表名
public DbSet<Product> Products { get; set; }
/// <summary>
/// 指定表名
/// </summary>
/// <param name="modelBuilder"></param>
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Product>().ToTable("Product");
}
}
5、新建一个Controller
新增相关数据库操作接口
[Produces("application/json")]
[Route("api/product2")]
public class ProductsMySql1Controller
{
private readonly MySqlContext _context;
public ProductsMySql1Controller(MySqlContext context)
{
_context = context;
}
/// <summary>
/// 获取产品列表
/// </summary>
/// <returns>产品列表</returns>
[Route("GetAll")]
[HttpGet]
public List<Product> GetAllProducts()
{
var products = _context.Products.ToList<Product>();
return products;
}
}
调试运行
调用接口 https://localhost:49165/api/product2/getall,返回信息如下:
到这来,连接SQL Server 和 MySQL 已完成
下一章我们将介绍 Consul服务注册与发现
https://blog.csdn.net/weixin_41003771/article/details/118708034