.NET框架 - NETCORE + API + EF（codefirst） + MYSQL

.NET框架 - NETCORE + API + EF（codefirst） + MYSQL

  

.net core环境 .net core3.1

 

一. 创建实体类项目 

项目类型：类库

项目名：NETCORE.EF.Model

1. 引入两个 nuget 包（由于项目为 netcore3.1 版本，所以以下两个包都引入的是 3.1.0版本）

Microsoft.EntityFrameworkCore.Tools
Pomelo.EntityFrameworkCore.MySql

  

2.  创建 TB_User 实体类

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

namespace NETCORE.ID4.SSO.Model.Model
{
    public class TB_User
    {
        [Key]
        public int id { get; set; }
        [Column( TypeName ="nvarchar(100)")]
        public string name { get; set; }
        [Column(TypeName = "nvarchar(100)")]
        public string gender { get; set; }
        public DateTime birthday { get; set; }

        public bool isdelete { get; set; }
        public int age { get; set; }
    }
}

 

 

3. 创建数据库上下文类DataBase

using Microsoft.EntityFrameworkCore;
using System;

namespace NETCORE.EF.Model
{
    public class DataBase : DbContext
    {
        public DataBase(DbContextOptions<DataBase> options) : base(options)
        {
        }

        #region 数据区域

        public DbSet<TB_User> TB_User { get; set; }
         
        #endregion
    }
}

 

 

 二. 创建WebAPI项目

项目类型：netcore WebAPI

项目名：NETCORE.EF.WebAPI

1. 引入nuget 包

Microsoft.EntityFrameworkCore.Design

2.  配置appsetting.json 数据库连接

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "ConnectionStrings": {
    "MysqlConnection": "server=127.0.0.1;userid=root;password=123456;database=mydbtest"
  },
  "AllowedHosts": "*"
}

 3. 配置Startup.cs文件

        public void ConfigureServices(IServiceCollection services)
        {
            services.AddDbContextPool<DataBase>(options=> {
                options.UseMySql(Configuration.GetConnectionString("MysqlConnection"));
            });

            services.AddControllers();
        }

 

 

三. 生成数据库

1. 打开程序包管理器控制台，项目为NETCORE.EF.Model

执行命令：Add-Migration  MyFirstMigration （为迁移搭建基架）

执行命令：Update-Database （将新迁移应用到数据库）

2. 数据库操作完成，可查看

 

四. EF操作

 1. 在webapi项目中创建 TestController.cs 控制器

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using NETCORE.EF.Model;

namespace NETCORE.EF.WebAPI.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class TestController : ControllerBase
    {

        private readonly DataBase db;

        public TestController(DataBase context)
        {
            this.db = context;
        }

        [HttpGet]
        [Route("Get1")]
        public IActionResult Get1()
        {
            TB_User model = new TB_User();
            model.id = 12;
            model.name = "张三";
            model.isdelete = true;
            model.gender = "男";
            model.birthday = DateTime.Parse("1985-04-15");

            var tt = db.Set<TB_User>().Where(c => true).ToList();
            
            db.Set<TB_User>().Add(model);
            db.SaveChanges();

            return new JsonResult(new { aaa = "111", bbb = "222" });
        }
    }
}

2. 操作完成，可查看数据

 

 

 项目中 DB.Set<TB_User> 也可以写成  DB.TB_User

 

 

 

 

 

net6 中引入 sqlserver

  "ConnectionStrings": {
    "SqlServerConnection": " Data Source=localhost;Initial Catalog=BSControl;User ID=sa;Password=root;Persist Security Info=True;Enlist=true;Max Pool Size=300;Min Pool Size=0;Connection Lifetime=300;Packet Size=1000;"
  },

 

//数据库连接
builder.Services.AddDbContextPool<DataBase>(options =>
{
    options.UseSqlServer(config.GetConnectionString("SqlServerConnection"));
});

 

 

net6 中引入 mysql

// Add services to the container.
var configuration = builder.Configuration;

//数据库中查版本号：SELECT VERSION();
builder.Services.AddDbContextPool<DataBase>(options =>
{
    options.UseMySql(configuration.GetConnectionString("MysqlConnection"), new MySqlServerVersion(new Version(8, 0, 32)));
});
builder.Services.AddControllers();

 

 

 写SQL语句

var arr2 = await DBCde.lbd_app_datadictionary.FromSqlInterpolated(@$"select * from lbd_app_datadictionary").ToListAsync();

 

 

 

项目：NETCORE.EF.Model

项目：NETCORE.EF.WebAPI
附代码：https://gitee.com/wuxincaicai/NETCORE.git

引用：https://www.cnblogs.com/1439107348s/p/10057694.html

 

　　　　[Column(TypeName = "nvarchar(100)")]