最近通过WPF开发项目,为了对WPF知识点进行总结,所以利用业余时间,开发一个学生信息管理系统【Student Information Management System】。上一篇文章进行了框架搭建和模块划分,本文在前一篇基础之上,继续深入开发学生信息管理系统的数据库和WebApi接口搭建相关内容,仅供学习分享使用,如有不足之处,还请指正。 涉及知识点
本篇内容主要是数据库的操作和WebApi的创建,涉及到知识点如下所示:
数据库相关
在学生信息管理系统中,学生,班级,课程,成绩等内容和管理模块的相关内容,都离不开数据库的支持,所以数据是支撑,页面是对数据的展示。数据库结构,如下所示:
主要涉及的表如下所示: 1. 学生表Students学生表主要存储学生的基础信息,如下所示: 学生表创建语句,如下所示: USE [SIMS] GO
/****** Object: Table [dbo].[Students] Script Date: 2022/5/25 0:11:41 ******/ SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
SET ANSI_PADDING ON GO
CREATE TABLE [dbo].[Students]( [Id] [int] IDENTITY(1,1) NOT NULL, [No] [varchar](50) NULL, [Name] [varchar](50) NULL, [Age] [int] NULL, [Sex] [bit] NULL, [ClassesId] [int] NULL, [CreateUser] [int] NULL, [CreateTime] [datetime] NULL, [LastEditUser] [int] NULL, [LastEditTime] [datetime] NULL ) ON [PRIMARY]
GO
SET ANSI_PADDING OFF GO
2. 班级表Classes
班级表主要用于保存班级相关信息,如下所示: 班级表创建语句,如下所示: USE [SIMS] GO
/****** Object: Table [dbo].[Classes] Script Date: 2022/5/25 0:16:31 ******/ SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
SET ANSI_PADDING ON GO
CREATE TABLE [dbo].[Classes]( [Id] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](50) NULL, [Dept] [varchar](50) NULL, [Grade] [varchar](50) NULL, [HeadTeacher] [varchar](50) NULL, [Monitor] [int] NULL, [CreateUser] [int] NULL, [CreateTime] [datetime] NULL, [LastEditUser] [int] NULL, [LastEditTime] [datetime] NULL ) ON [PRIMARY]
GO
SET ANSI_PADDING OFF GO
3. 课程表Courses课程表主要保存课程信息,如下所示: 课程表创建语句,如下所示: USE [SIMS] GO
/****** Object: Table [dbo].[Courses] Script Date: 2022/5/25 0:20:11 ******/ SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
SET ANSI_PADDING ON GO
CREATE TABLE [dbo].[Courses]( [Id] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](50) NULL, [Teacher] [varchar](50) NULL, [CreateUser] [int] NULL, [CreateTime] [datetime] NULL, [LastEditUser] [int] NULL, [LastEditTime] [datetime] NULL ) ON [PRIMARY]
GO
SET ANSI_PADDING OFF GO
4. 成绩表Scores成绩表用于保存学生各科的成绩,如下所示: 成绩表创建语句,如下所示: USE [SIMS] GO
/****** Object: Table [dbo].[Scores] Script Date: 2022/5/25 0:23:19 ******/ SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
CREATE TABLE [dbo].[Scores]( [Id] [int] IDENTITY(1,1) NOT NULL, [StudentId] [int] NULL, [CourseId] [int] NULL, [Score] [float] NULL, [CreateUser] [int] NULL, [CreateTime] [datetime] NULL, [LastEditUser] [int] NULL, [LastEditTime] [datetime] NULL ) ON [PRIMARY]
GO
5. 其他数据表其他数据表包括用户表【Users】,角色表【Roles】,用户角色对应关系表【UserRoles】,角色菜单对应关系表【RoleMenus】菜单表【Menus】 。因源码无法折叠,其他表创建语句可到原文查看。 数据库实体类
数据库实体类是数据库表与类的映射,通过操作实体类来达到操作数据库的目的,实体类需要与EntityFramework结合起来使用。如下所示:
1. 学生类StudentEntity学生类与数据库中的学生表对应,如下所示: namespace SIMS.Entity { /// <summary> /// 学生实体 /// </summary> public class StudentEntity { /// <summary> /// 唯一标识 /// </summary> public int Id { get; set; }
/// <summary> /// 学号 /// </summary> public string No { get; set; }
/// <summary> /// 学生名称 /// </summary> public string Name { get; set; }
/// <summary> /// 年纪 /// </summary> public int Age { get; set; }
/// <summary> /// 性别 /// </summary> public Boolean Sex { get; set; }
/// <summary> /// 班级标识 /// </summary> public int? ClassesId { get; set; }
/// <summary> /// 创建时间 /// </summary> public DateTime? CreateTime { get; set; }
/// <summary> /// 当前登录的账号的ID /// </summary> public int? CreateUser { get; set; }
/// <summary> /// 最后编辑时间 /// </summary> public DateTime? LastEditTime { get; set; }
/// <summary> /// 最后修改人 /// </summary> public int? LastEditUser { get; set; } } }
2. 班级类ClassesEntity班级类与班级表对应,如下所示: namespace SIMS.Entity { /// <summary> /// 班级实体 /// </summary> public class ClassesEntity { /// <summary> /// 唯一标识 /// </summary> public int Id { get; set; }
/// <summary> /// 专业 /// </summary> public string Dept { get; set; }
/// <summary> /// 年级 /// </summary> public string Grade { get; set; }
/// <summary> /// 班级名称 /// </summary> public string Name { get; set; }
/// <summary> /// 班主任老师名称 /// </summary> public string HeadTeacher { get; set; }
/// <summary> /// 班长id,对应学生表的学生 /// </summary> public int? Monitor { get; set; }
/// <summary> /// 创建时间 /// </summary> public DateTime CreateTime { get; set; }
/// <summary> /// 当前登录的账号的ID /// </summary> public int CreateUser { get; set; }
/// <summary> /// 最后编辑时间 /// </summary> public DateTime LastEditTime { get; set; }
/// <summary> /// 最后修改人 /// </summary> public int LastEditUser { get; set; } } }
3. 课程类CourseEntity课程类与课程表对应,如下所示: namespace SIMS.Entity { /// <summary> /// 课程实体 /// </summary> public class CourseEntity { /// <summary> /// 唯一标识 /// </summary> public int Id { get; set; }
/// <summary> /// 课程名称 /// </summary> public string Name { get; set; }
/// <summary> /// 授课老师 /// </summary> public string Teacher { get; set; }
/// <summary> /// 创建时间 /// </summary> public DateTime CreateTime { get; set; }
/// <summary> /// 当前登录的账号的ID /// </summary> public int CreateUser { get; set; }
/// <summary> /// 最后编辑时间 /// </summary> public DateTime LastEditTime { get; set; }
/// <summary> /// 最后修改人 /// </summary> public int LastEditUser { get; set; } } }
4. 成绩类ScoreEntity成绩类与成绩表对应,如下所示: namespace SIMS.Entity { /// <summary> /// 成绩实体 /// </summary> public class ScoreEntity { /// <summary> /// 唯一标识 /// </summary> public int Id { get; set; }
/// <summary> ///学生id /// </summary> public int StudentId { get; set; }
/// <summary> /// 课程id /// </summary> public int CourseId { get; set; }
/// <summary> /// 成绩 /// </summary> public double Score { get; set; }
/// <summary> /// 创建时间 /// </summary> public DateTime CreateTime { get; set; }
/// <summary> /// 当前登录的账号的ID /// </summary> public int CreateUser { get; set; }
/// <summary> /// 最后编辑时间 /// </summary> public DateTime LastEditTime { get; set; }
/// <summary> /// 最后修改人 /// </summary> public int LastEditUser { get; set; } } }
5. 其他实体类除了以上四个主要的实体类,还有其他实体类,一共九个实体类,对应数据库中的九个表。其他实体源码可到原文查看。 WebApi数据接口
在本示例中,客户端和服务端交互通过webapi接口进行,避免直接操作数据库。具体步骤如下所示:
1. EntityFramework框架EntityFramework是.Net领域通用的ORM框架,有CodeFirst和DBFirst两种方式,本文采用DBFirst方式,即先设计数据库表结构,再创建实体和映射。如果要使用EntityFramework框架,首先进行安装,可通过NuGet包管理器进行安装,如下所示: 创建DataContext数据操作类,继承自DbContext,并重写OnModelCreating方法,将数据表和实体类建立联系,实现映射,如下所示: namespace SIMS.WebApi.Data { public class DataContext:DbContext { public DbSet<UserEntity> Users { get; set; }
public DbSet<MenuEntity> Menus { get; set; }
public DbSet<RoleEntity> Roles { get; set; }
public DbSet<UserRoleEntity> UserRoles { get; set; }
public DbSet<RoleMenuEntity> RoleMenus { get; set; }
/// <summary> /// 学生 /// </summary> public DbSet<StudentEntity> Students { get; set; }
/// <summary> /// 班级 /// </summary> public DbSet<ClassesEntity> Classes { get; set; }
/// <summary> /// 课程 /// </summary> public DbSet<CourseEntity> Courses { get; set; }
/// <summary> /// 成绩 /// </summary> public DbSet<ScoreEntity> Scores { get; set; }
public DataContext(DbContextOptions options) : base(options) {
}
protected override void OnModelCreating(ModelBuilder modelBuilder) { base.OnModelCreating(modelBuilder); modelBuilder.Entity<UserEntity>().ToTable("Users"); modelBuilder.Entity<MenuEntity>().ToTable("Menus"); modelBuilder.Entity<StudentEntity>().ToTable("Students"); modelBuilder.Entity<RoleEntity>().ToTable("Roles"); modelBuilder.Entity<UserRoleEntity>().ToTable("UserRoles"); modelBuilder.Entity<RoleMenuEntity>().ToTable("RoleMenus"); } } }
2. 创建服务在本示例中,为了封装数据库的底层操作,提取了四个服务接口和类,如下所示: 学生服务接口IStudentAppService,是对学生表操作的封装,如下所示: namespace SIMS.WebApi.Services.Student { public interface IStudentAppService { /// <summary> /// 查询学生列表 /// </summary> /// <param name="name"></param> /// <returns></returns> public PagedRequest<StudentEntity> GetStudents(string no,string name, int pageNum, int pageSize);
/// <summary> /// 查询某一班级的学生列表 /// </summary> /// <param name="classId"></param> /// <returns></returns> public PagedRequest<StudentEntity> GetStudentsByClasses(int classId);
/// <summary> /// 通过id查询学生信息 /// </summary> /// <param name="id"></param> /// <returns></returns> public StudentEntity GetSudent(int id);
/// <summary> /// 新增学生 /// </summary> /// <param name="student"></param> /// <returns></returns> public int AddStudent(StudentEntity student);
/// <summary> /// 修改学生 /// </summary> /// <param name="student"></param> /// <returns></returns> public int UpdateStudent(StudentEntity student);
/// <summary> /// 删除学生 /// </summary> /// <param name="id"></param> public int DeleteStudent(int id); } }
班级服务接口IClassesAppService
班级服务接口是对班级表操作的封装,如下所示: namespace SIMS.WebApi.Services.Classes { public interface IClassesAppService { public PagedRequest<ClassesEntity> GetClassess(string dept, string grade, int pageNum, int pageSize);
/// <summary> /// 通过id查询班级信息 /// </summary> /// <param name="id"></param> /// <returns></returns> public ClassesEntity GetClasses(int id);
/// <summary> /// 新增班级 /// </summary> /// <param name="classes"></param> /// <returns></returns> public int AddClasses(ClassesEntity classes);
/// <summary> /// 修改班级 /// </summary> /// <param name="classes"></param> /// <returns></returns> public int UpdateClasses(ClassesEntity classes);
/// <summary> /// 删除班级 /// </summary> /// <param name="id"></param> public int DeleteClasses(int id); } }
学生和班级服务实现类的源码可到原文查看,其他服务接口和类尚不完善,暂时先不贴代码。 3. 创建控制器接口控制器就是客户端访问的入口,与接口服务对应,一个四个接口控制器,如下所示: 学生控制器StudentController 学生控制器封装学生操作的服务,包括学生的增删改查的接口,如下所示: namespace SIMS.WebApi.Controllers { /// <summary> /// 学生控制器 /// </summary> [Route("api/[controller]/[action]")] [ApiController] public class StudentController : ControllerBase { private readonly ILogger<StudentController> logger;
private readonly IStudentAppService studentAppService;
public StudentController(ILogger<StudentController> logger, IStudentAppService studentAppService) { this.logger = logger; this.studentAppService = studentAppService; }
/// <summary> /// 学生列表,带分页 /// </summary> /// <param name="pageNum"></param> /// <param name="pageSize"></param> /// <param name="no"></param> /// <param name="name"></param> /// <returns></returns> [HttpGet] public PagedRequest<StudentEntity> GetStudents(int pageNum, int pageSize,string? no = null ,string? name=null ) { return studentAppService.GetStudents(no,name, pageNum, pageSize); }
/// <summary> /// 查询某一班级的学生列表 /// </summary> /// <param name="classId"></param> /// <returns></returns> [HttpGet] public PagedRequest<StudentEntity> GetStudentsByClasses(int classId) { return studentAppService.GetStudentsByClasses(classId); }
/// <summary> /// 获取学生信息 /// </summary> /// <param name="id"></param> /// <returns></returns> [HttpGet] public StudentEntity GetStudent(int id) { return studentAppService.GetSudent(id); }
/// <summary> /// 新增学生 /// </summary> /// <param name="student"></param> /// <returns></returns> [HttpPost] public int AddStudent(StudentEntity student) { return studentAppService.AddStudent(student); }
/// <summary> /// 修改学生 /// </summary> /// <param name="student"></param> /// <returns></returns> [HttpPut] public int UpdateStudent(StudentEntity student) { return studentAppService.UpdateStudent(student); }
/// <summary> /// 删除学生 /// </summary> /// <param name="id"></param> [HttpDelete] public int DeleteStudent(int id) { return studentAppService.DeleteStudent(id); } } }
班级控制器ClassesController
班级控制器是对班级接口服务的封装,包含对班级的增删改查,如下所示: namespace SIMS.WebApi.Controllers { /// <summary> /// 班级控制器 /// </summary> [Route("api/[controller]/[action]")] [ApiController] public class ClassesController : ControllerBase { private readonly ILogger<ClassesController> logger;
private readonly IClassesAppService classesAppService;
public ClassesController(ILogger<ClassesController> logger, IClassesAppService classesAppService) { this.logger = logger; this.classesAppService = classesAppService; }
/// <summary> /// 获取班级信息 /// </summary> /// <param name="id"></param> /// <returns></returns> [HttpGet] public ClassesEntity GetClasses(int id) { return classesAppService.GetClasses(id); }
/// <summary> /// 获取班级列表 /// </summary> /// <param name="id"></param> /// <returns></returns> [HttpGet] public PagedRequest<ClassesEntity> GetClassess(string? dept, string? grade, int pageNum, int pageSize) { return classesAppService.GetClassess(dept,grade,pageNum,pageSize); }
/// <summary> /// 新增班级 /// </summary> /// <param name="classes"></param> /// <returns></returns> [HttpPost] public int AddClasses(ClassesEntity classes) { return classesAppService.AddClasses(classes); }
/// <summary> /// 修改班级 /// </summary> /// <param name="classes"></param> /// <returns></returns> [HttpPut] public int UpdateClasses(ClassesEntity classes) { return classesAppService.UpdateClasses(classes); }
/// <summary> /// 删除班级 /// </summary> /// <param name="id"></param> [HttpDelete] public int DeleteClasses(int id) { return classesAppService.DeleteClasses(id); } } }
注意:其他控制器,暂时代码不全,暂不贴示。
通过以上示例可以看出,接口服务依赖DataContext,控制器依赖接口服务,那么如果要使程序得以运行,就要逐层创建对象,并传递对象实体。 4. 注入DataContext在Program中,通过构造器,注入DataContext,如下所示: builder.Services.AddDbContext<DataContext>(options => options.UseSqlServer(builder.Configuration.GetConnectionString("Default")));
注意:Default为appsetting.json中配置的数据库连接字符串。如下所示:
{ "Logging": { "LogLevel": { "Default": "Information", "Microsoft.AspNetCore": "Warning" } }, "ConnectionStrings": { "Default": "Server=localhost;Database=SIMS;Trusted_Connection=True;User Id=sa;Password=abc123" }, "AllowedHosts": "*" }
5. 配置Autofac框架手工创建非常不方便,需要引入Autofac进行自动注入。Autoface框架可以实现接口的自动注入,减少繁琐的人工操作,如下所示: // 以下是autofac依赖注入 builder.Host.UseServiceProviderFactory(new AutofacServiceProviderFactory()); builder.Host.ConfigureContainer<ContainerBuilder>(builder => { // 注入Service程序集 string assemblyName = Assembly.GetExecutingAssembly().GetName().Name; builder.RegisterAssemblyTypes(Assembly.Load(assemblyName)) .AsImplementedInterfaces() .InstancePerDependency(); });
Program中全部代码,可到原文查看。 6. 代码结构
WebApi代码结构如下所示: 7. 运行WebApi服务经过以上步骤的操作,Api接口已经基本搭建完成,将SIMS.WebApi作为启动项目,运行VS,如下所示: 8. 接口测试默认情况下WebApi继承了Swagger,可以进行简单的测试,以/api/Classes/GetClasses为例,输入班级ID,就可查询班级信息,如下所示: 以上就是本次的主要内容,包含数据库的基本操作和WebApi接口的创建流程,旨在抛砖引玉,一起学习,共同进步。 备注
上邪【作者】佚名 【朝代】汉
上邪,我欲与君相知,长命无绝衰。 山无陵,江水为竭。 冬雷震震,夏雨雪。 天地合,乃敢与君绝。 学习编程,从关注【老码识途】开始!!!
|