.NET6 webApi的分层结构注册访问数据库，mysql和sqlite的配置

EF和本体程序需要分离开.

EF实体类类库:

  
    net6.0
    enable
    enable
  

  
    
	
	
		all
		runtime; build; native; contentfiles; analyzers; buildtransitive
	
	
		  
	  
	  
		  all
		  runtime; build; native; contentfiles; analyzers; buildtransitive
	  
	  
	  
	  
	  
	  
	  
  

实体类和配置

public record Book
{
	public long Id { get; set; }
	public string Name { get; set; }
	public double Price { get; set; }
}

global using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;


class BookConfig : IEntityTypeConfiguration
{
    public void Configure(EntityTypeBuilder builder)
    {
        builder.ToTable("Book");
        builder.HasKey(x => x.Id);
    } 
}

Dbcontext

using System.Reflection;

namespace BooksEFCore
{
    public class MyDbContext:DbContext
    {
        public MyDbContext(DbContextOptions options) : base(options)
        {

        }

        public DbSet Books { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            base.OnConfiguring(optionsBuilder);
            //测试用
            //string connStr = "server=192.168.153.101; database=Book666; uid=root; pwd=123456;";
            //optionsBuilder.UseMySql(connStr, new MySqlServerVersion(new Version(5, 7, 35)));
        }
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            //modelBuilder.ApplyConfigurationsFromAssembly(Assembly.GetEntryAssembly());
            modelBuilder.ApplyConfigurationsFromAssembly(this.GetType().Assembly); //获取当前程序集
        }
    }
    //Add-Migration MyFirstMigration -Context MyDbcontext
}

注意: 如果需要数据迁移, 需要设置EF类库为启动项, 再建一个数据迁移的工具类. 因为migration 需要依赖当前程序的运行时

using Microsoft.EntityFrameworkCore.Design;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BooksEFCore
{
    class MyDesignTimeDbContextFactory : IDesignTimeDbContextFactory
    {
        public MyDbContext CreateDbContext(string[] args)
        {
            DbContextOptionsBuilder builder = new();
            string connStr = "server=192.168.153.101; database=Book666; uid=root; pwd=123456;";
            builder.UseMySql(connStr, new MySqlServerVersion(new Version(5, 7, 35)));
            return new MyDbContext(builder.Options);
        }
    }
}

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Reflection;
using System.Text;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Sqlite;
using Microsoft.Extensions.Configuration;
using Newtonsoft.Json;

namespace DotNet6Demon
{
    public class MyDbContext:DbContext
    {
        public DbSet ActualDatas { get; set; } //API中为复数, 而不是数据库表
        public DbSet ModbusNodes { get; set; }
        public DbSet Variables { get; set; }
        public DbSet SysAdmins { get; set; }
        public DbSet ReportDatas { get; set; }

        private ConfigurationBuilder cfgBuilder = new ConfigurationBuilder();

        //private IConfiguration configuration;

        //private string connString;
        string path = Directory.GetCurrentDirectory();
        public MyDbContext()
        {
            //configuration = new ConfigurationBuilder().SetBasePath(Directory.GetCurrentDirectory()).AddJsonFile("Ipcfg.json").Build();
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            base.OnConfiguring(optionsBuilder);
            cfgBuilder.AddJsonFile("VariableNode.json", optional: true, reloadOnChange: true);
            IConfigurationRoot configRoot = cfgBuilder.Build();
            string conn = configRoot.GetSection("ConnectionStrings:SqliteConnectionString").Value;
            string connString = "Data Source=" + path + conn;
            optionsBuilder.UseSqlite(connString);
            //configuration = new ConfigurationBuilder().SetBasePath(Directory.GetCurrentDirectory()).AddJsonFile("Ipcfg.json").Build();
            //"Data Source=E:\Csharp\EF6Demon\EF6Demon\bin\Debug\net6.0-windows\Database\DbSqlite.db",
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            //ConfigurationBuilder cfg1 = new ConfigurationBuilder();
            //cfg1.AddJsonFile("VariableNode.json", optional: true, reloadOnChange: true);
            //IConfigurationRoot configRoot = cfg1.Build();
            //string varNum = configRoot.GetSection("NodeClass:ModbusNode:VarNum").Value;
            //IList iniVariableList = new List();
            //for (int i = 0; i < int.Parse(varNum); i++)
            //{
            //    Variable variable = configRoot.GetSection($"NodeClass:ModbusNode:ModbusGroup:Variable:{i}").Get();
            //    iniVariableList.Add(variable);
            //}

            var iniActualData = File.ReadAllText(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location) + @"/Database/Variable.json");
            IList iniActualList = JsonConvert.DeserializeObject(iniActualData);
            //modelBuilder.Entity().HasData(iniVariableList);

            modelBuilder.ApplyConfigurationsFromAssembly(this.GetType().Assembly);
        }
    }
}
    //VS终端下
    // 视图-其他窗口-程序包控制台
    //选择默认项目
    //add-migration initialMigration //创建数据迁移
    //add-migration initialMigration1 //创建数据迁移
    // update-database
    //Remove-migration 删除最后一次脚本
    //Script-Migration 显示迁移的sql脚本

    //DBfirst
    // Scaffold-DbContext "server=192.168.207.107; database=Demon1; uid=root; pwd=123456" Pomelo.EntityFrameworkCore.MySql

    //根据已有数据库创建数据模型。在 NuGet 的程序包管理(Package Manager)控制台中(PowerShell)执行命令：
    //Scaffold-DbContext "server=数据库服务器;uid=数据库用户名;pwd=数据库密码;database=数据库名;" Pomelo.EntityFrameworkCore.MySql -OutputDir Data -Force
    //.Net Core CLi：dotnet ef dbcontext scaffold "server=数据库服务器;uid=数据库用户名;pwd=数据库密码;database=数据库名;" Pomelo.EntityFrameworkCore.MySql -o Data -f



    //CMD 命令下 安装EF工具:
    //dotnet tool install --global dotnet-ef
    //数据迁移:
    //dotnet ef migrations add DataSeeding
    //数据更新:
    //dotnet ef database update
    

WebApi主体:

using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;

namespace EFCoreWebDemon.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class Test1Controller : ControllerBase
    {
        private readonly MyDbContext myDbContext;

        public Test1Controller(MyDbContext myDbContext)
        {
            this.myDbContext = myDbContext;
        }

        [HttpGet]
        public string Demon1()
        {
            int c = myDbContext.Books.Count();
            return $"总共有 {c}本书";
            
        }
    }
}

管理-用户机密:

{
  "ConnectionStrings": {
    "Default": "Server=.;Database=demo8;Trusted_Connection=True;",
    "MySQLConnectionString": "server=192.168.153.101; database=Book666; uid=root; pwd=123456;"
  }
}

配置路由 :

global using BooksEFCore;
using Microsoft.EntityFrameworkCore;
var builder = WebApplication.CreateBuilder(args);

// Add services to the container.

builder.Services.AddControllers();
// Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();

builder.Services.AddCors(options =>
    options.AddDefaultPolicy(builder => builder.WithOrigins("http://localhost:3000")
    .AllowAnyMethod().AllowAnyHeader().AllowCredentials()));//跨域

builder.Services.AddDbContext(opt=>
{
    string connStr = builder.Configuration.GetSection("ConnectionStrings:MySQLConnectionString").Value;
    //string connStr = "server=192.168.153.101; database=Book666; uid=root; pwd=123456;";
    opt.UseMySql(connStr, new MySqlServerVersion(new Version(5, 7, 35)));
});

var app = builder.Build();

// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI();
}

app.UseHttpsRedirection();

app.UseAuthorization();

app.MapControllers();

app.Run();

{
  "ConnectionStrings": {
    "SqliteConnectionString": "Data Source=D:\Csharp\code\STM32Demon\STM32Demon\bin\Debug\net6.0-windows\Database\DbSqlite.db",
    "MySQLConnectionString": "server=127.0.0.1; database=OneToMany; uid=root; pwd=123456;"
  },
  "ServerURL": "192.168.1.100",
  "Port": "2000",
  "VarNum": "6",
  "ByteLenth": "100",
  "Variable": [
    {
      "Id": 1,
      "Number": "1",
      "Name": "Float1",
      "Description": "40001-40002",
      "Type": "TCP",
      "VarAddress": 0,
      "Scale": "1",
      "Offset": "0",
      "Start": "0",
      "AccessProperty": "读写",
      "VarType": "Float",
      "InsertTime": "0",
      "Value": "0"
    },
    {
      "Id": 2,
      "Number": "2",
      "Name": "Float2",
      "Description": "40001-40002",
      "Type": "TCP",
      "VarAddress": 2,
      "Scale": "1",
      "Offset": "0",
      "Start": "0",
      "AccessProperty": "读写",
      "VarType": "Float",
      "InsertTime": "0",
      "Value": "0"
    },
    {
      "Id": 3,
      "Number": "3",
      "Name": "Float3",
      "Description": "40005-40006",
      "Type": "TCP",
      "VarAddress": 4,
      "Scale": "1",
      "Offset": "0",
      "Start": "0",
      "AccessProperty": "读写",
      "VarType": "Float",
      "InsertTime": "0",
      "Value": "0"
    },
    {
      "Id": 4,
      "Number": "4",
      "Name": "Float4",
      "Description": "40007-40008",
      "Type": "TCP",
      "VarAddress": 6,
      "Scale": "1",
      "Offset": "0",
      "Start": "0",
      "AccessProperty": "读写",
      "VarType": "Float",
      "InsertTime": "0",
      "Value": "0"
    },
    {
      "Id": 5,
      "Number": "5",
      "Name": "UShort1",
      "Description": "40008",
      "Type": "TCP",
      "VarAddress": 8,
      "Scale": "1",
      "Offset": "0",
      "Start": "0",
      "AccessProperty": "读写",
      "VarType": "UShort",
      "InsertTime": "0",
      "Value": "0"
    },
    {
      "Id": 6,
      "Number": "6",
      "Name": "UShort2",
      "Description": "40009",
      "Type": "TCP",
      "VarAddress": 9,
      "Scale": "1",
      "Offset": "0",
      "Start": "0",
      "AccessProperty": "读写",
      "VarType": "UShort",
      "InsertTime": "0",
      "Value": "0"
    }
  ]
}