.net mysql 增删改查

1.​​创建1个web项目​​

2.加入Microsoft.EntityFrameworkCore依赖

3.创建数据库表

CREATE TABLE `Person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(20) DEFAULT NULL,
  `Age` int(2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

4.创建1个实体类

namespace mysql1.model
{
    public class Person
    {
        [Key]
        public int Id { get; set; }
        public string Name { get; set; }
        public int Age { get; set; }
    }
}

5.创建mysql配置类

using Microsoft.EntityFrameworkCore;
using mysql1.model;

namespace mysql1.config
{
    public class DBContextHelper : DbContext
    {
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseMySql("server=xxx;port=xxx;user=xxx;password=xxx;database=xxx", new MySqlServerVersion(new Version(5, 7, 16)));

        }

        public virtual DbSet<Person>? Person { get; set; }
    }
}

6.创建1个controller

using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using mysql1.config;
using mysql1.model;

namespace mysql1.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class MyController : ControllerBase
    {
        [HttpGet(Name = "my")]
        public Object Get()
        {
            DBContextHelper helper = new();

            /*
             * 增加
            Person person1 = new Person();
            person1.Name = "住宿费1";
            person1.Age = 55;
            helper.Add<Person>(person1);
            Person person2 = new Person();
            person2.Name = "住宿费1";
            person2.Age = 55;
            helper.Add<Person>(person2);
            helper.SaveChanges();
            //原生sql
            helper.Database.ExecuteSqlRaw("insert into Person (Name,Age) values ('呵呵',21)");
            */

            /* 
             删除
             Person person1 = new Person();
             person1.Id = 23;
             helper.Remove<Person>(person1);
            helper.SaveChanges();
            //原生sql
            helper.Database.ExecuteSqlRaw("delete from Person");
            */

            /*
            修改
            Person person1 = new Person();
            person1.Id = 24;           
            person1.Name = "哈哈";
            person1.Age = 99;
            helper.Update<Person>(person1);
            helper.SaveChanges();
            //原生sql
            helper.Database.ExecuteSqlRaw("update Person set name='拉拉' where id=29");
            */

            /*
            查询全部数据
            List<Person> people = helper.Set<Person>().ToList();
            //通过id查询
            helper.Person.Find(24);
            //通过框架查询
            from p in helper.Person
                   where p.Name.StartsWith("哈")
                   select p;
            //通过框架查询
            helper.Person.FirstOrDefault(p => p.Name == "哈哈");
            //sql
            helper.Person.FromSqlRaw("select * from Person where id=24").ToList();
            */

            return helper.Database.ExecuteSqlRaw("update Person set name='拉拉' where id=29");
        }
    }
}