- A+
最近通过WPF开发项目,为了对WPF知识点进行总结,所以利用业余时间,开发一个学生信息管理系统【Student Information Management System】。上一篇文章进行了框架搭建和模块划分,本文在前一篇基础之上,继续深入开发学生信息管理系统的数据库和WebApi接口搭建相关内容,仅供学习分享使用,如有不足之处,还请指正。
涉及知识点
本篇内容主要是数据库的操作和WebApi的创建,涉及到知识点如下所示:
- 数据库知识,基于SQL Server 2012的数据表创建,以及CRUD【(Create)、检索(Retrieve)、更新(Update)和删除(Delete)】等基础操作。
- WebApi接口创建基础操作,本文会涉及到通过WebApi提供数据接口给客户端,用于封装数据持久化的操作。
数据库相关
在学生信息管理系统中,学生,班级,课程,成绩等内容和管理模块的相关内容,都离不开数据库的支持,所以数据是支撑,页面是对数据的展示。数据库结构,如下所示:
主要涉及的表如下所示:
1. 学生表Student
学生表主要存储学生的基础信息,如下所示:
学生表创建语句,如下所示:
1 USE [SIMS] 2 GO 3 4 /****** Object: Table [dbo].[Students] Script Date: 2022/5/25 0:11:41 ******/ 5 SET ANSI_NULLS ON 6 GO 7 8 SET QUOTED_IDENTIFIER ON 9 GO 10 11 SET ANSI_PADDING ON 12 GO 13 14 CREATE TABLE [dbo].[Students]( 15 [Id] [int] IDENTITY(1,1) NOT NULL, 16 [No] [varchar](50) NULL, 17 [Name] [varchar](50) NULL, 18 [Age] [int] NULL, 19 [Sex] [bit] NULL, 20 [ClassesId] [int] NULL, 21 [CreateUser] [int] NULL, 22 [CreateTime] [datetime] NULL, 23 [LastEditUser] [int] NULL, 24 [LastEditTime] [datetime] NULL 25 ) ON [PRIMARY] 26 27 GO 28 29 SET ANSI_PADDING OFF 30 GO
2. 班级表Classes
班级表主要用于保存班级相关信息,如下所示:
班级表创建语句,如下所示:
1 USE [SIMS] 2 GO 3 4 /****** Object: Table [dbo].[Classes] Script Date: 2022/5/25 0:16:31 ******/ 5 SET ANSI_NULLS ON 6 GO 7 8 SET QUOTED_IDENTIFIER ON 9 GO 10 11 SET ANSI_PADDING ON 12 GO 13 14 CREATE TABLE [dbo].[Classes]( 15 [Id] [int] IDENTITY(1,1) NOT NULL, 16 [Name] [varchar](50) NULL, 17 [Dept] [varchar](50) NULL, 18 [Grade] [varchar](50) NULL, 19 [HeadTeacher] [varchar](50) NULL, 20 [Monitor] [int] NULL, 21 [CreateUser] [int] NULL, 22 [CreateTime] [datetime] NULL, 23 [LastEditUser] [int] NULL, 24 [LastEditTime] [datetime] NULL 25 ) ON [PRIMARY] 26 27 GO 28 29 SET ANSI_PADDING OFF 30 GO
3. 课程表Courses
课程表主要保存课程信息,如下所示:
课程表创建语句,如下所示:
1 USE [SIMS] 2 GO 3 4 /****** Object: Table [dbo].[Courses] Script Date: 2022/5/25 0:20:11 ******/ 5 SET ANSI_NULLS ON 6 GO 7 8 SET QUOTED_IDENTIFIER ON 9 GO 10 11 SET ANSI_PADDING ON 12 GO 13 14 CREATE TABLE [dbo].[Courses]( 15 [Id] [int] IDENTITY(1,1) NOT NULL, 16 [Name] [varchar](50) NULL, 17 [Teacher] [varchar](50) NULL, 18 [CreateUser] [int] NULL, 19 [CreateTime] [datetime] NULL, 20 [LastEditUser] [int] NULL, 21 [LastEditTime] [datetime] NULL 22 ) ON [PRIMARY] 23 24 GO 25 26 SET ANSI_PADDING OFF 27 GO
4. 成绩表Scores
成绩表用于保存学生各科的成绩,如下所示:
成绩表创建语句,如下所示:
1 USE [SIMS] 2 GO 3 4 /****** Object: Table [dbo].[Scores] Script Date: 2022/5/25 0:23:19 ******/ 5 SET ANSI_NULLS ON 6 GO 7 8 SET QUOTED_IDENTIFIER ON 9 GO 10 11 CREATE TABLE [dbo].[Scores]( 12 [Id] [int] IDENTITY(1,1) NOT NULL, 13 [StudentId] [int] NULL, 14 [CourseId] [int] NULL, 15 [Score] [float] NULL, 16 [CreateUser] [int] NULL, 17 [CreateTime] [datetime] NULL, 18 [LastEditUser] [int] NULL, 19 [LastEditTime] [datetime] NULL 20 ) ON [PRIMARY] 21 22 GO
5. 其他数据表
其他数据表包括用户表【Users】,角色表【Roles】,用户角色对应关系表【UserRoles】,角色菜单对应关系表【RoleMenus】菜单表【Menus】 创建语句,如下所示:
1 USE [SIMS] 2 GO 3 4 /****** Object: Table [dbo].[Users] Script Date: 2022/5/25 0:29:55 ******/ 5 SET ANSI_NULLS ON 6 GO 7 8 SET QUOTED_IDENTIFIER ON 9 GO 10 11 SET ANSI_PADDING ON 12 GO 13 14 CREATE TABLE [dbo].[Users]( 15 [Id] [int] IDENTITY(1,1) NOT NULL, 16 [UserName] [varchar](50) NULL, 17 [Password] [varchar](50) NULL, 18 [NickName] [varchar](50) NULL 19 ) ON [PRIMARY] 20 21 GO 22 23 SET ANSI_PADDING OFF 24 GO 25 ------------------------------------------ 26 USE [SIMS] 27 GO 28 29 /****** Object: Table [dbo].[Roles] Script Date: 2022/5/25 0:30:21 ******/ 30 SET ANSI_NULLS ON 31 GO 32 33 SET QUOTED_IDENTIFIER ON 34 GO 35 36 SET ANSI_PADDING ON 37 GO 38 39 CREATE TABLE [dbo].[Roles]( 40 [id] [int] IDENTITY(1,1) NOT NULL, 41 [Name] [varchar](50) NULL, 42 [Description] [varchar](50) NULL 43 ) ON [PRIMARY] 44 45 GO 46 47 SET ANSI_PADDING OFF 48 GO 49 ------------------------------------------ 50 USE [SIMS] 51 GO 52 53 /****** Object: Table [dbo].[Menus] Script Date: 2022/5/25 0:31:04 ******/ 54 SET ANSI_NULLS ON 55 GO 56 57 SET QUOTED_IDENTIFIER ON 58 GO 59 60 SET ANSI_PADDING ON 61 GO 62 63 CREATE TABLE [dbo].[Menus]( 64 [id] [int] IDENTITY(1,1) NOT NULL, 65 [Name] [varchar](50) NULL, 66 [Description] [varchar](50) NULL, 67 [Url] [varchar](250) NULL, 68 [ParentId] [int] NULL, 69 [SortId] [int] NULL 70 ) ON [PRIMARY] 71 72 GO 73 74 SET ANSI_PADDING OFF 75 GO 76 77 ------------------------------------------ 78 USE [SIMS] 79 GO 80 81 /****** Object: Table [dbo].[UserRoles] Script Date: 2022/5/25 0:31:20 ******/ 82 SET ANSI_NULLS ON 83 GO 84 85 SET QUOTED_IDENTIFIER ON 86 GO 87 88 CREATE TABLE [dbo].[UserRoles]( 89 [id] [int] IDENTITY(1,1) NOT NULL, 90 [UserId] [int] NULL, 91 [RoleId] [int] NULL 92 ) ON [PRIMARY] 93 94 GO 95 ------------------------------------------ 96 USE [SIMS] 97 GO 98 99 /****** Object: Table [dbo].[RoleMenus] Script Date: 2022/5/25 0:31:35 ******/ 100 SET ANSI_NULLS ON 101 GO 102 103 SET QUOTED_IDENTIFIER ON 104 GO 105 106 CREATE TABLE [dbo].[RoleMenus]( 107 [id] [int] IDENTITY(1,1) NOT NULL, 108 [RoleId] [int] NULL, 109 [MenuId] [int] NULL 110 ) ON [PRIMARY] 111 112 GO
View Code
数据库实体类
数据库实体类是数据库表与类的映射,通过操作实体类来达到操作数据库的目的,实体类需要与EntityFramework结合起来使用。如下所示:
1. 学生类StudentEntity
学生类与学生表对应,如下所示:
1 namespace SIMS.Entity 2 { 3 /// <summary> 4 /// 学生实体 5 /// </summary> 6 public class StudentEntity 7 { 8 /// <summary> 9 /// 唯一标识 10 /// </summary> 11 public int Id { get; set; } 12 13 /// <summary> 14 /// 学号 15 /// </summary> 16 public string No { get; set; } 17 18 /// <summary> 19 /// 学生名称 20 /// </summary> 21 public string Name { get; set; } 22 23 /// <summary> 24 /// 年纪 25 /// </summary> 26 public int Age { get; set; } 27 28 /// <summary> 29 /// 性别 30 /// </summary> 31 public Boolean Sex { get; set; } 32 33 /// <summary> 34 /// 班级标识 35 /// </summary> 36 public int? ClassesId { get; set; } 37 38 /// <summary> 39 /// 创建时间 40 /// </summary> 41 public DateTime? CreateTime { get; set; } 42 43 /// <summary> 44 /// 当前登录的账号的ID 45 /// </summary> 46 public int? CreateUser { get; set; } 47 48 /// <summary> 49 /// 最后编辑时间 50 /// </summary> 51 public DateTime? LastEditTime { get; set; } 52 53 /// <summary> 54 /// 最后修改人 55 /// </summary> 56 public int? LastEditUser { get; set; } 57 } 58 }
2. 班级类ClassesEntity
班级类与班级表对应,如下所示:
1 namespace SIMS.Entity 2 { 3 /// <summary> 4 /// 班级实体 5 /// </summary> 6 public class ClassesEntity 7 { 8 /// <summary> 9 /// 唯一标识 10 /// </summary> 11 public int Id { get; set; } 12 13 /// <summary> 14 /// 专业 15 /// </summary> 16 public string Dept { get; set; } 17 18 /// <summary> 19 /// 年级 20 /// </summary> 21 public string Grade { get; set; } 22 23 /// <summary> 24 /// 班级名称 25 /// </summary> 26 public string Name { get; set; } 27 28 /// <summary> 29 /// 班主任老师名称 30 /// </summary> 31 public string HeadTeacher { get; set; } 32 33 /// <summary> 34 /// 班长id,对应学生表的学生 35 /// </summary> 36 public int? Monitor { get; set; } 37 38 /// <summary> 39 /// 创建时间 40 /// </summary> 41 public DateTime CreateTime { get; set; } 42 43 /// <summary> 44 /// 当前登录的账号的ID 45 /// </summary> 46 public int CreateUser { get; set; } 47 48 /// <summary> 49 /// 最后编辑时间 50 /// </summary> 51 public DateTime LastEditTime { get; set; } 52 53 /// <summary> 54 /// 最后修改人 55 /// </summary> 56 public int LastEditUser { get; set; } 57 } 58 }
3. 课程类CourseEntity
课程类与课程表对应,如下所示:
1 namespace SIMS.Entity 2 { 3 /// <summary> 4 /// 课程实体 5 /// </summary> 6 public class CourseEntity 7 { 8 /// <summary> 9 /// 唯一标识 10 /// </summary> 11 public int Id { get; set; } 12 13 /// <summary> 14 /// 课程名称 15 /// </summary> 16 public string Name { get; set; } 17 18 /// <summary> 19 /// 授课老师 20 /// </summary> 21 public string Teacher { get; set; } 22 23 /// <summary> 24 /// 创建时间 25 /// </summary> 26 public DateTime CreateTime { get; set; } 27 28 /// <summary> 29 /// 当前登录的账号的ID 30 /// </summary> 31 public int CreateUser { get; set; } 32 33 /// <summary> 34 /// 最后编辑时间 35 /// </summary> 36 public DateTime LastEditTime { get; set; } 37 38 /// <summary> 39 /// 最后修改人 40 /// </summary> 41 public int LastEditUser { get; set; } 42 } 43 }
4. 成绩类ScoreEntity
成绩类与成绩表对应,如下所示:
1 namespace SIMS.Entity 2 { 3 /// <summary> 4 /// 成绩实体 5 /// </summary> 6 public class ScoreEntity 7 { 8 /// <summary> 9 /// 唯一标识 10 /// </summary> 11 public int Id { get; set; } 12 13 /// <summary> 14 ///学生id 15 /// </summary> 16 public int StudentId { get; set; } 17 18 /// <summary> 19 /// 课程id 20 /// </summary> 21 public int CourseId { get; set; } 22 23 /// <summary> 24 /// 成绩 25 /// </summary> 26 public double Score { get; set; } 27 28 /// <summary> 29 /// 创建时间 30 /// </summary> 31 public DateTime CreateTime { get; set; } 32 33 /// <summary> 34 /// 当前登录的账号的ID 35 /// </summary> 36 public int CreateUser { get; set; } 37 38 /// <summary> 39 /// 最后编辑时间 40 /// </summary> 41 public DateTime LastEditTime { get; set; } 42 43 /// <summary> 44 /// 最后修改人 45 /// </summary> 46 public int LastEditUser { get; set; } 47 } 48 }
5. 其他实体类
除了以上四个主要的实体类,还有其他实体类,如下所示:
菜单实体MenuEntity,如下所示:
1 namespace SIMS.Entity 2 { 3 /// <summary> 4 /// 菜单管理 5 /// </summary> 6 public class MenuEntity 7 { 8 /// <summary> 9 /// 唯一标识 10 /// </summary> 11 public int Id { get; set; } 12 13 /// <summary> 14 /// 菜单名称 15 /// </summary> 16 public string Name { get; set; } 17 18 /// <summary> 19 /// 菜单描述 20 /// </summary> 21 public string Description { get; set; } 22 23 /// <summary> 24 /// 菜单路径 25 /// </summary> 26 public string Url { get; set; } 27 28 /// <summary> 29 /// 父ID 30 /// </summary> 31 public int? ParentId { get; set; } 32 33 /// <summary> 34 /// 排序 35 /// </summary> 36 public int? SortId { get; set; } 37 } 38 }
View Code
用户实体类UserEntity,如下所示:
1 namespace SIMS.Entity 2 { 3 public class UserEntity 4 { 5 /// <summary> 6 /// 用户唯一标识 7 /// </summary> 8 public int Id { get; set; } 9 10 /// <summary> 11 /// 登录账号 12 /// </summary> 13 public string UserName { get; set; } 14 15 /// <summary> 16 /// 密码 17 /// </summary> 18 public string Password { get; set; } 19 20 /// <summary> 21 /// 显示名称 22 /// </summary> 23 public string NickName { get; set; } 24 } 25 }
View Code
角色实体类RoleEntity,如下所示:
1 namespace SIMS.Entity 2 { 3 /// <summary> 4 /// 角色 5 /// </summary> 6 public class RoleEntity 7 { 8 /// <summary> 9 /// 唯一标识 10 /// </summary> 11 public int Id { get; set; } 12 13 /// <summary> 14 /// 角色名称 15 /// </summary> 16 public string Name { get; set; } 17 18 /// <summary> 19 /// 角色描述 20 /// </summary> 21 public string Description { get; set; } 22 } 23 }
View Code
用户角色关系实体类UserRoleEntityr,如下所示:
1 namespace SIMS.Entity 2 { 3 /// <summary> 4 /// 用户-角色模型 5 /// </summary> 6 public class UserRoleEntity 7 { 8 /// <summary> 9 /// 唯一标识 10 /// </summary> 11 public int Id { get; set; } 12 13 /// <summary> 14 /// 用户ID 15 /// </summary> 16 public int UserId { get; set; } 17 18 /// <summary> 19 /// 角色ID 20 /// </summary> 21 public int RoleId { get; set; } 22 } 23 }
View Code
角色菜单关系实体RoleMenuEntity,如下所示:
1 namespace SIMS.Entity 2 { 3 /// <summary> 4 /// 角色-菜单关联 5 /// </summary> 6 public class RoleMenuEntity 7 { 8 /// <summary> 9 /// 唯一标识 10 /// </summary> 11 public int Id { get; set; } 12 13 /// <summary> 14 /// 菜单IP 15 /// </summary> 16 public int MenuId { get; set; } 17 18 /// <summary> 19 /// 角色ID 20 /// </summary> 21 public int RoleId { get; set; } 22 } 23 }
View Code
一共九个实体类,对应数据库中的九个表。
WebApi数据接口
在本示例中,客户端和服务端交互通过webapi接口进行,避免直接操作数据库。具体步骤如下所示:
1. EntityFramework框架
EntityFramework是.Net领域通用的ORM框架,有CodeFirst和DBFirst两种方式,本文采用DBFirst方式,即先设计数据库表结构,再创建实体和映射。如果要使用EntityFramework框架,首先进行安装,可通过NuGet包管理器进行安装,如下所示:
创建DataContext数据操作类,继承自DbContext,并重写OnModelCreating方法,将数据表和实体类建立联系,实现映射,如下所示:
1 namespace SIMS.WebApi.Data 2 { 3 public class DataContext:DbContext 4 { 5 public DbSet<UserEntity> Users { get; set; } 6 7 public DbSet<MenuEntity> Menus { get; set; } 8 9 public DbSet<RoleEntity> Roles { get; set; } 10 11 public DbSet<UserRoleEntity> UserRoles { get; set; } 12 13 public DbSet<RoleMenuEntity> RoleMenus { get; set; } 14 15 /// <summary> 16 /// 学生 17 /// </summary> 18 public DbSet<StudentEntity> Students { get; set; } 19 20 /// <summary> 21 /// 班级 22 /// </summary> 23 public DbSet<ClassesEntity> Classes { get; set; } 24 25 /// <summary> 26 /// 课程 27 /// </summary> 28 public DbSet<CourseEntity> Courses { get; set; } 29 30 /// <summary> 31 /// 成绩 32 /// </summary> 33 public DbSet<ScoreEntity> Scores { get; set; } 34 35 public DataContext(DbContextOptions options) : base(options) 36 { 37 38 } 39 40 protected override void OnModelCreating(ModelBuilder modelBuilder) 41 { 42 base.OnModelCreating(modelBuilder); 43 modelBuilder.Entity<UserEntity>().ToTable("Users"); 44 modelBuilder.Entity<MenuEntity>().ToTable("Menus"); 45 modelBuilder.Entity<StudentEntity>().ToTable("Students"); 46 modelBuilder.Entity<RoleEntity>().ToTable("Roles"); 47 modelBuilder.Entity<UserRoleEntity>().ToTable("UserRoles"); 48 modelBuilder.Entity<RoleMenuEntity>().ToTable("RoleMenus"); 49 } 50 } 51 }
2. 创建服务
在本示例中,为了封装数据库的底层操作,提取了四个服务接口和类,如下所示:
学生服务接口IStudentAppService,是对学生表操作的封装,如下所示:
1 namespace SIMS.WebApi.Services.Student 2 { 3 public interface IStudentAppService 4 { 5 /// <summary> 6 /// 查询学生列表 7 /// </summary> 8 /// <param name="name"></param> 9 /// <returns></returns> 10 public PagedRequest<StudentEntity> GetStudents(string no,string name, int pageNum, int pageSize); 11 12 /// <summary> 13 /// 查询某一班级的学生列表 14 /// </summary> 15 /// <param name="classId"></param> 16 /// <returns></returns> 17 public PagedRequest<StudentEntity> GetStudentsByClasses(int classId); 18 19 /// <summary> 20 /// 通过id查询学生信息 21 /// </summary> 22 /// <param name="id"></param> 23 /// <returns></returns> 24 public StudentEntity GetSudent(int id); 25 26 /// <summary> 27 /// 新增学生 28 /// </summary> 29 /// <param name="student"></param> 30 /// <returns></returns> 31 public int AddStudent(StudentEntity student); 32 33 /// <summary> 34 /// 修改学生 35 /// </summary> 36 /// <param name="student"></param> 37 /// <returns></returns> 38 public int UpdateStudent(StudentEntity student); 39 40 /// <summary> 41 /// 删除学生 42 /// </summary> 43 /// <param name="id"></param> 44 public int DeleteStudent(int id); 45 } 46 }
学生服务实现类StudentAppService
学生服务实现类是对接口的实现,如下所示:
1 namespace SIMS.WebApi.Services.Student 2 { 3 public class StudentAppService : IStudentAppService 4 { 5 private DataContext dataContext; 6 7 public StudentAppService(DataContext dataContext) { 8 this.dataContext = dataContext; 9 } 10 11 /// <summary> 12 /// 新增学生 13 /// </summary> 14 /// <param name="student"></param> 15 /// <returns></returns> 16 public int AddStudent(StudentEntity student) 17 { 18 var entry = dataContext.Students.Add(student); 19 dataContext.SaveChanges(); 20 return 0; 21 } 22 23 /// <summary> 24 /// 删除学生 25 /// </summary> 26 /// <param name="id"></param> 27 public int DeleteStudent(int id) 28 { 29 var entity = dataContext.Students.FirstOrDefault(x => x.Id == id); 30 if (entity != null) 31 { 32 dataContext.Students.Remove(entity); 33 dataContext.SaveChanges(); 34 } 35 return 0; 36 } 37 38 /// <summary> 39 /// 查询学生列表 40 /// </summary> 41 /// <param name="name"></param> 42 /// <param name="pageNum">页数</param> 43 /// <param name="pageSize">每页大小</param> 44 /// <returns></returns> 45 public PagedRequest<StudentEntity> GetStudents(string no,string name,int pageNum,int pageSize) 46 { 47 IQueryable<StudentEntity> students = null; 48 if (!string.IsNullOrEmpty(name) && string.IsNullOrEmpty(no)) 49 { 50 students = dataContext.Students.Where(r => r.Name.Contains(name) && r.No.Contains(no)).OrderBy(r => r.Id); 51 } 52 else if (!string.IsNullOrEmpty(name)) 53 { 54 students = dataContext.Students.Where(r => r.Name.Contains(name)).OrderBy(r => r.Id); 55 } 56 else if (!string.IsNullOrEmpty(name)) { 57 students = dataContext.Students.Where(r => r.No.Contains(no)).OrderBy(r => r.Id); 58 } 59 else 60 { 61 students = dataContext.Students.Where(r => true).OrderBy(r => r.Id); 62 } 63 int count = students.Count(); 64 List<StudentEntity> items; 65 if (pageSize > 0) 66 { 67 items = students.Skip((pageNum - 1) * pageSize).Take(pageSize).ToList(); 68 } 69 else { 70 items = students.ToList(); 71 } 72 return new PagedRequest<StudentEntity>() 73 { 74 count = count, 75 items = items 76 }; 77 } 78 79 /// <summary> 80 /// 查询某一班级的学生列表 81 /// </summary> 82 /// <param name="classId"></param> 83 /// <returns></returns> 84 public PagedRequest<StudentEntity> GetStudentsByClasses(int classId) 85 { 86 IQueryable<StudentEntity> students = dataContext.Students.Where(r => r.ClassesId==classId).OrderBy(r => r.Id); 87 int count = students.Count(); 88 var items = students.ToList(); 89 return new PagedRequest<StudentEntity>() 90 { 91 count = count, 92 items = items 93 }; 94 } 95 96 /// <summary> 97 /// 通过id查询学生信息 98 /// </summary> 99 /// <param name="id"></param> 100 /// <returns></returns> 101 public StudentEntity GetSudent(int id) 102 { 103 var entity = dataContext.Students.FirstOrDefault(r => r.Id == id); 104 return entity; 105 } 106 107 /// <summary> 108 /// 修改学生 109 /// </summary> 110 /// <param name="student"></param> 111 /// <returns></returns> 112 public int UpdateStudent(StudentEntity student) 113 { 114 dataContext.Students.Update(student); 115 dataContext.SaveChanges(); 116 return 0; 117 } 118 } 119 }
View Code
班级服务接口IClassesAppService
班级服务接口是对班级表操作的封装,如下所示:
1 namespace SIMS.WebApi.Services.Classes 2 { 3 public interface IClassesAppService 4 { 5 public PagedRequest<ClassesEntity> GetClassess(string dept, string grade, int pageNum, int pageSize); 6 7 /// <summary> 8 /// 通过id查询班级信息 9 /// </summary> 10 /// <param name="id"></param> 11 /// <returns></returns> 12 public ClassesEntity GetClasses(int id); 13 14 /// <summary> 15 /// 新增班级 16 /// </summary> 17 /// <param name="classes"></param> 18 /// <returns></returns> 19 public int AddClasses(ClassesEntity classes); 20 21 /// <summary> 22 /// 修改班级 23 /// </summary> 24 /// <param name="classes"></param> 25 /// <returns></returns> 26 public int UpdateClasses(ClassesEntity classes); 27 28 /// <summary> 29 /// 删除班级 30 /// </summary> 31 /// <param name="id"></param> 32 public int DeleteClasses(int id); 33 } 34 }
班级服务实现类ClassesAppService,如下所示:
1 namespace SIMS.WebApi.Services.Classes 2 { 3 public class ClassesAppService : IClassesAppService 4 { 5 private DataContext dataContext; 6 7 public ClassesAppService(DataContext dataContext) 8 { 9 this.dataContext = dataContext; 10 } 11 12 /// <summary> 13 /// 新增班级 14 /// </summary> 15 /// <param name="classes"></param> 16 /// <returns></returns> 17 public int AddClasses(ClassesEntity classes) 18 { 19 var entry = dataContext.Classes.Add(classes); 20 dataContext.SaveChanges(); 21 return 0; 22 } 23 24 /// <summary> 25 /// 删除班级 26 /// </summary> 27 /// <param name="id"></param> 28 public int DeleteClasses(int id) 29 { 30 var entity = dataContext.Classes.FirstOrDefault(x => x.Id == id); 31 if (entity != null) 32 { 33 dataContext.Classes.Remove(entity); 34 dataContext.SaveChanges(); 35 } 36 return 0; 37 } 38 39 /// <summary> 40 /// 查询班级 41 /// </summary> 42 /// <param name="id"></param> 43 /// <returns></returns> 44 public ClassesEntity GetClasses(int id) 45 { 46 var entity = dataContext.Classes.FirstOrDefault(r => r.Id == id); 47 return entity; 48 } 49 50 public PagedRequest<ClassesEntity> GetClassess(string dept, string grade, int pageNum, int pageSize) 51 { 52 IQueryable<ClassesEntity> classes = null; 53 if (!string.IsNullOrEmpty(dept) && string.IsNullOrEmpty(grade)) 54 { 55 classes = dataContext.Classes.Where(r => r.Dept.Contains(dept) && r.Grade.Contains(grade)).OrderBy(r => r.Id); 56 } 57 else if (!string.IsNullOrEmpty(dept)) 58 { 59 classes = dataContext.Classes.Where(r => r.Dept.Contains(dept)).OrderBy(r => r.Id); 60 } 61 else if (!string.IsNullOrEmpty(grade)) 62 { 63 classes = dataContext.Classes.Where(r => r.Grade.Contains(grade)).OrderBy(r => r.Id); 64 } 65 else 66 { 67 classes = dataContext.Classes.Where(r => true).OrderBy(r => r.Id); 68 } 69 int count = classes.Count(); 70 List<ClassesEntity> items=new List<ClassesEntity>(); 71 if (pageSize < 1) 72 { 73 items = classes.ToList(); 74 } 75 else { 76 items = classes.Skip((pageNum - 1) * pageSize).Take(pageSize).ToList(); 77 } 78 79 return new PagedRequest<ClassesEntity>() 80 { 81 count = count, 82 items = items 83 }; 84 } 85 86 /// <summary> 87 /// 修改班级 88 /// </summary> 89 /// <param name="classes"></param> 90 /// <returns></returns> 91 public int UpdateClasses(ClassesEntity classes) 92 { 93 dataContext.Classes.Update(classes); 94 dataContext.SaveChanges(); 95 return 0; 96 } 97 } 98 }
View Code
其他服务接口和类尚不完善,暂时先不贴代码。
3. 创建控制器
接口控制器就是客户端访问的入口,与接口服务对应,一个四个接口控制器,如下所示:
学生控制器StudentController
学生控制器封装学生操作的服务,包括学生的增删改查的接口,如下所示:
1 namespace SIMS.WebApi.Controllers 2 { 3 /// <summary> 4 /// 学生控制器 5 /// </summary> 6 [Route("api/[controller]/[action]")] 7 [ApiController] 8 public class StudentController : ControllerBase 9 { 10 private readonly ILogger<StudentController> logger; 11 12 private readonly IStudentAppService studentAppService; 13 14 public StudentController(ILogger<StudentController> logger, IStudentAppService studentAppService) { 15 this.logger = logger; 16 this.studentAppService = studentAppService; 17 } 18 19 /// <summary> 20 /// 学生列表,带分页 21 /// </summary> 22 /// <param name="pageNum"></param> 23 /// <param name="pageSize"></param> 24 /// <param name="no"></param> 25 /// <param name="name"></param> 26 /// <returns></returns> 27 [HttpGet] 28 public PagedRequest<StudentEntity> GetStudents(int pageNum, int pageSize,string? no = null ,string? name=null ) { 29 return studentAppService.GetStudents(no,name, pageNum, pageSize); 30 } 31 32 /// <summary> 33 /// 查询某一班级的学生列表 34 /// </summary> 35 /// <param name="classId"></param> 36 /// <returns></returns> 37 [HttpGet] 38 public PagedRequest<StudentEntity> GetStudentsByClasses(int classId) { 39 return studentAppService.GetStudentsByClasses(classId); 40 } 41 42 /// <summary> 43 /// 获取学生信息 44 /// </summary> 45 /// <param name="id"></param> 46 /// <returns></returns> 47 [HttpGet] 48 public StudentEntity GetStudent(int id) { 49 return studentAppService.GetSudent(id); 50 } 51 52 /// <summary> 53 /// 新增学生 54 /// </summary> 55 /// <param name="student"></param> 56 /// <returns></returns> 57 [HttpPost] 58 public int AddStudent(StudentEntity student) 59 { 60 return studentAppService.AddStudent(student); 61 } 62 63 /// <summary> 64 /// 修改学生 65 /// </summary> 66 /// <param name="student"></param> 67 /// <returns></returns> 68 [HttpPut] 69 public int UpdateStudent(StudentEntity student) { 70 return studentAppService.UpdateStudent(student); 71 } 72 73 /// <summary> 74 /// 删除学生 75 /// </summary> 76 /// <param name="id"></param> 77 [HttpDelete] 78 public int DeleteStudent(int id) 79 { 80 return studentAppService.DeleteStudent(id); 81 } 82 } 83 }
班级控制器ClassesController
班级控制器是对班级接口服务的封装,包含对班级的增删改查,如下所示:
1 namespace SIMS.WebApi.Controllers 2 { 3 /// <summary> 4 /// 班级控制器 5 /// </summary> 6 [Route("api/[controller]/[action]")] 7 [ApiController] 8 public class ClassesController : ControllerBase 9 { 10 private readonly ILogger<ClassesController> logger; 11 12 private readonly IClassesAppService classesAppService; 13 14 public ClassesController(ILogger<ClassesController> logger, IClassesAppService classesAppService) 15 { 16 this.logger = logger; 17 this.classesAppService = classesAppService; 18 } 19 20 /// <summary> 21 /// 获取班级信息 22 /// </summary> 23 /// <param name="id"></param> 24 /// <returns></returns> 25 [HttpGet] 26 public ClassesEntity GetClasses(int id) 27 { 28 return classesAppService.GetClasses(id); 29 } 30 31 /// <summary> 32 /// 获取班级列表 33 /// </summary> 34 /// <param name="id"></param> 35 /// <returns></returns> 36 [HttpGet] 37 public PagedRequest<ClassesEntity> GetClassess(string? dept, string? grade, int pageNum, int pageSize) 38 { 39 return classesAppService.GetClassess(dept,grade,pageNum,pageSize); 40 } 41 42 /// <summary> 43 /// 新增班级 44 /// </summary> 45 /// <param name="classes"></param> 46 /// <returns></returns> 47 [HttpPost] 48 public int AddClasses(ClassesEntity classes) 49 { 50 return classesAppService.AddClasses(classes); 51 } 52 53 /// <summary> 54 /// 修改班级 55 /// </summary> 56 /// <param name="classes"></param> 57 /// <returns></returns> 58 [HttpPut] 59 public int UpdateClasses(ClassesEntity classes) 60 { 61 return classesAppService.UpdateClasses(classes); 62 } 63 64 /// <summary> 65 /// 删除班级 66 /// </summary> 67 /// <param name="id"></param> 68 [HttpDelete] 69 public int DeleteClasses(int id) 70 { 71 return classesAppService.DeleteClasses(id); 72 } 73 } 74 }
注意:其他控制器,暂是代码不全,暂不贴示。
通过以上示例可以看出,接口服务依赖DataContext,控制器依赖接口服务,那么如果要使程序得以运行,就要逐层创建对象,并传递对象实体。手工创建非常不方便,所以就需要引入Autofac进行自动注入,如下所示:
4. 注入DataContext
在Program中,通过构造器,注入DataContext,如下所示:
1 builder.Services.AddDbContext<DataContext>(options => options.UseSqlServer(builder.Configuration.GetConnectionString("Default")));
注意:Default为appsetting.json中配置的数据库连接字符串。如下所示:
1 { 2 "Logging": { 3 "LogLevel": { 4 "Default": "Information", 5 "Microsoft.AspNetCore": "Warning" 6 } 7 }, 8 "ConnectionStrings": { 9 "Default": "Server=localhost;Database=SIMS;Trusted_Connection=True;User Id=sa;Password=abc123" 10 }, 11 "AllowedHosts": "*" 12 }
5. 配置Autofac框架
Autoface框架可以实现接口的自动注入,减少繁琐的人工操作,如下所示:
1 // 以下是autofac依赖注入 2 builder.Host.UseServiceProviderFactory(new AutofacServiceProviderFactory()); 3 builder.Host.ConfigureContainer<ContainerBuilder>(builder => 4 { // 注入Service程序集 5 string assemblyName = Assembly.GetExecutingAssembly().GetName().Name; 6 builder.RegisterAssemblyTypes(Assembly.Load(assemblyName)) 7 .AsImplementedInterfaces() 8 .InstancePerDependency(); 9 });
Program中全部代码,如下所示:
1 using Autofac; 2 using Autofac.Extensions.DependencyInjection; 3 using Microsoft.EntityFrameworkCore; 4 using SIMS.WebApi; 5 using SIMS.WebApi.Data; 6 using SIMS.WebApi.Services.Student; 7 using System.Configuration; 8 using System.Reflection; 9 10 var builder = WebApplication.CreateBuilder(args); 11 12 // Add services to the container. 13 14 builder.Services.AddControllers(); 15 // Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle 16 builder.Services.AddEndpointsApiExplorer(); 17 builder.Services.AddSwaggerGen(); 18 builder.Services.AddDbContext<DataContext>(options => options.UseSqlServer(builder.Configuration.GetConnectionString("Default"))); 19 // 以下是autofac依赖注入 20 builder.Host.UseServiceProviderFactory(new AutofacServiceProviderFactory()); 21 builder.Host.ConfigureContainer<ContainerBuilder>(builder => 22 { // 注入Service程序集 23 string assemblyName = Assembly.GetExecutingAssembly().GetName().Name; 24 builder.RegisterAssemblyTypes(Assembly.Load(assemblyName)) 25 .AsImplementedInterfaces() 26 .InstancePerDependency(); 27 }); 28 var app = builder.Build(); 29 30 // Configure the HTTP request pipeline. 31 if (app.Environment.IsDevelopment()) 32 { 33 app.UseSwagger(); 34 app.UseSwaggerUI(); 35 } 36 37 app.UseHttpsRedirection(); 38 39 app.UseAuthorization(); 40 41 app.MapControllers(); 42 43 app.Run();
View Code
6. 代码结构
WebApi中代码结构如下所示:
7. 运行WebApi服务
经过以上步骤的操作,Api接口已经基本搭建完成,将SIMS.WebApi作为启动项目,运行VS,如下所示:
8. 接口测试
默认情况下WebApi继承了Swagger,可以进行简单的测试,以/api/Classes/GetClasses为例,输入班级ID,就可查询班级信息,如下所示:
以上就是本次的主要内容,包含数据库的基本操作和WebApi接口的创建流程,旨在抛转引玉,一起学习,共同进步。
备注
上邪【作者】佚名 【朝代】汉
上邪,我欲与君相知,长命无绝衰。
山无陵,江水为竭。
冬雷震震,夏雨雪。
天地合,乃敢与君绝。