一、新建数据表及项目
1、新建测试用表
CREATE TABLE`student` (
`id`int(11) NOT NULLAUTO_INCREMENT,
`name`varchar(255) DEFAULT NULL,
`sex`bit(1) DEFAULT NULL,
`age`int(11) DEFAULT NULL,PRIMARY KEY(`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
2、新建项目,添加EF Core 和 MySql驱动依赖项
新建.net core 2.1 webapi 网站程序,NuGet 添加依赖项 Microsoft.EntityFrameworkCore.Tools(2.1.14) 和 MySql.Data.EntityFrameworkCore(8.0.18) 包
3、添加实体类Student和数据库上下文
添加实体类:新建 Entities 目录,在目录下新建 Student 实体类,在类上加 [Table("student")] 表名、属性上加[Column("id")] 字段名等与表对应,代码如下:
usingSystem;usingSystem.Collections.Generic;usingSystem.ComponentModel.DataAnnotations.Schema;usingSystem.Linq;usingSystem.Threading.Tasks;namespaceTest.Entities
{
[Table("student")]public classStudent
{
[Column("id")]public int ID { get; set; }
[Column("name")]public string Name { get; set; }
[Column("sex")]public int Sex { get; set; }
[Column("age")]public int Age { get; set; }
}
}
添加数据库操作目录 DataAccess,进入目录,添加数据库上下文目录 Base,并在 Base 目录下新建 AlanContext 上下文类,继承 DbContext 类,通过构造函数注入数据库连接,添加 DbSet 实体属性,代码如下:
usingMicrosoft.EntityFrameworkCore;usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingSystem.Threading.Tasks;usingTest.Entities;namespaceTest.DataAccess.Base
{public classAlanContext : DbContext
{public AlanContext(DbContextOptionsoptions)
:base(options)
{ }public DbSet Student { get; set; }
}
}
4、增、删、改、查等数据库操作
在 DataAccess 目录下新建 Interface 目录,用于保存数据库操作的接口,在该目录下新建 IAlanDao 接口,在接口里增加 CreateStudent(),GetStudents(),GetStudentByID(),UpdateStudent(),UpdateNameByID(),DeleteStudentByID() 数据库 添、删、改、查接口,代码如下:
usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingSystem.Threading.Tasks;usingTest.Entities;namespaceTest.DataAccess.Interface
{public interfaceIAlanDao
{//插入数据
boolCreateStudent(Student student);//取全部记录
IEnumerableGetStudents();//取某id记录
Student GetStudentByID(intid);//根据id更新整条记录
boolUpdateStudent(Student student);//根据id更新名称
bool UpdateNameByID(int id, stringname);//根据id删掉记录
bool DeleteStudentByID(intid);
}
}
在 DataAccess 目录下新建 Implement 目录,用于保存数据库操作接口的实现,在该目录下新建 AlanDao 类,继承 IAlanDao 接口,实现接口里的数据库操作方法,在构造函数注入 AlanContext 数据库上下文,代码如下:
usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingSystem.Threading.Tasks;usingTest.DataAccess.Base;usingTest.DataAccess.Interface;usingTest.Entities;namespaceTest.DataAccess.Implement
{public classAlanDao : IAlanDao
{publicAlanContext Context;publicAlanDao(AlanContext context)
{
Context=context;
}//插入数据
public boolCreateStudent(Student student)
{
Context.Student.Add(student);return Context.SaveChanges() > 0;
}//取某id记录
public Student GetStudentByID(intid)
{return Context.Student.SingleOrDefault(s => s.ID ==id);
}//取全部记录
public IEnumerableGetStudents()
{returnContext.Student.ToList();
}//根据id更新名称
public bool UpdateNameByID(int id, stringname)
{var state = false;var student = Context.Student.SingleOrDefault(s => s.ID ==id);if (student != null)
{
student.Name=name;
state= Context.SaveChanges() > 0;
}returnstate;
}//根据id更新整条记录
public boolUpdateStudent(Student student)
{
Context.Student.Update(student);return Context.SaveChanges() > 0;
}//根据id删掉记录
public bool DeleteStudentByID(intid)
{var student = Context.Student.SingleOrDefault(s => s.ID ==id);
Context.Student.Remove(student);return Context.SaveChanges() > 0;
}
}
}
5、添加 StudentController 控制器,调用数据库操作方法
在 Controllers 目录下,添加 StudentController 控制器,在构造函数注入 AlanDao 数据库操作,在控制器里 创建 Create(),Gets(),Get(),Update(),UpdateName(),Delete()等方法,调用 AlanDao 数据库操作方法,代码如下:
usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingSystem.Threading.Tasks;usingMicrosoft.AspNetCore.Http;usingMicrosoft.AspNetCore.Mvc;usingTest.DataAccess.Interface;usingTest.Entities;namespaceTest.Controllers
{public classStudentController : ControllerBase
{privateIAlanDao AlanDao;publicStudentController(IAlanDao alanDao)
{
AlanDao=alanDao;
}//插入数据
public ActionResult Create(string name, byte sex, intage)
{if (string.IsNullOrEmpty(name.Trim()))
{return "姓名不能为空";
}if (sex < 0 || sex > 2)
{return "性别数据有误";
}if (age <= 0)
{return "年龄数据有误";
}var student = newStudent()
{
Name=name,
Sex=sex,
Age=age
};var result =AlanDao.CreateStudent(student);if(result)
{return "学生插入成功";
}else{return "学生插入失败";
}
}//取全部记录
public ActionResultGets()
{var names = "没有数据";var students =AlanDao.GetStudents();if (students != null)
{
names= "";foreach (var s instudents)
{
names+= $"{s.Name} \r\n";
}
}returnnames;
}//取某id记录
public ActionResult Get(intid)
{var name = "没有数据";var student =AlanDao.GetStudentByID(id);if (student != null)
{
name=student.Name;
}returnname;
}//根据id更新整条记录
public ActionResult Update(int id, string name, byte sex, intage)
{if (id <= 0)
{return "id 不能小于0";
}if (string.IsNullOrEmpty(name.Trim()))
{return "姓名不能为空";
}if (sex < 0 || sex > 2)
{return "性别数据有误";
}if (age <= 0)
{return "年龄数据有误";
}var student = newStudent()
{
ID=id,
Name=name,
Sex=sex,
Age=age
};var result =AlanDao.UpdateStudent(student);if(result)
{return "学生更新成功";
}else{return "学生更新失败";
}
}//根据id更新名称
public ActionResult UpdateName(int id, stringname)
{if (id <= 0)
{return "id 不能小于0";
}if (string.IsNullOrEmpty(name.Trim()))
{return "姓名不能为空";
}var result =AlanDao.UpdateNameByID(id, name);if(result)
{return "学生更新成功";
}else{return "学生更新失败";
}
}//根据id删掉记录
public ActionResult Delete(intid)
{if (id <= 0)
{return "id 不能小于0!";
}var result =AlanDao.DeleteStudentByID(id);if(result)
{return "学生删除成功";
}else{return "学生删除失败";
}
}
}
}
6、配置数据库连接字符串,注册数据库连接到容器,注册数据库操作类到容器
在 appsettings.json 中配置数据库连接串
{"ConnectionStrings": {"DefaultConnection": "server=localhost;port=3306;database=alan;uid=root;pwd=123456;",
}
}
修改 Startup.cs 类的 ConfigureServices() 方法,注册数据库连接及数据库操作类
var conn = Configuration.GetConnectionString("DefaultConnection");
services.AddDbContext(options =>options.UseMySQL(conn));
services.AddScoped();
二、运行测试
运行项目
在浏览器中分别执行