网站的版面设计,用discuz可以做视频网站吗,为企业打造赚钱系统,电子商务网站建设实验总结下面是一个完整的 .NET Core 后端项目示例#xff0c;使用 Dapper 作为轻量级 ORM 访问 Oracle 数据库#xff0c;并实现高性能架构。我们将实现学生表、课程表、成绩表和班级表的基本增删改查功能#xff0c;以及查询某个班级学生成绩的功能#xff0c;并使用自定义缓存来…下面是一个完整的 .NET Core 后端项目示例使用 Dapper 作为轻量级 ORM 访问 Oracle 数据库并实现高性能架构。我们将实现学生表、课程表、成绩表和班级表的基本增删改查功能以及查询某个班级学生成绩的功能并使用自定义缓存来优化查询性能。
项目结构
MyApp/
│── Controllers/ # 控制器层处理HTTP请求
│ └── StudentController.cs
│── Models/ # 模型层定义实体类
│ ├── Student.cs
│ ├── Course.cs
│ ├── Grade.cs
│ └── Class.cs
│── DTOs/ # 数据传输对象用于API响应
│ └── StudentGradeDTO.cs
│── Services/ # 服务层业务逻辑处理
│ └── StudentService.cs
│── Repositories/ # 仓库层数据访问
│ └── StudentRepository.cs
│── Cache/ # 缓存层
│ └── InMemoryCache.cs
│── Startup.cs # 应用启动配置
│── appsettings.json # 应用配置文件
└── Program.cs # 应用入口实体模型
首先定义实体模型这些模型代表数据库中的表。
Models/Student.cs
public class Student
{public int Id { get; set; }public int ClassId { get; set; }public string Name { get; set; }
}Models/Course.cs
public class Course
{public int Id { get; set; }public string Name { get; set; }
}Models/Grade.cs
public class Grade
{public int Id { get; set; }public int StudentId { get; set; }public int CourseId { get; set; }public decimal Score { get; set; }
}Models/Class.cs
public class Class
{public int Id { get; set; }public string Name { get; set; }
}数据传输对象
为了优化网络传输我们通常不会直接返回实体模型而是使用DTO。
DTOs/StudentGradeDTO.cs
public class StudentGradeDTO
{public int StudentId { get; set; }public string StudentName { get; set; }public string CourseName { get; set; }public decimal Score { get; set; }
}缓存层
我们使用一个简单的字典来实现内存缓存。
Cache/InMemoryCache.cs
using System;
using System.Collections.Generic;
using System.Threading.Tasks;public class InMemoryCacheTKey, TValue
{private readonly DictionaryTKey, CacheEntryTValue _cache new DictionaryTKey, CacheEntryTValue();public async TaskTValue GetOrAddAsync(TKey key, FuncTKey, TaskTValue valueFactory, TimeSpan? expiration null){if (_cache.TryGetValue(key, out var cacheEntry)){if (cacheEntry.Expiration DateTime.UtcNow){return cacheEntry.Value;}else{_cache.Remove(key);}}var value await valueFactory(key);_cache[key] new CacheEntryTValue { Value value, Expiration DateTime.UtcNow (expiration ?? TimeSpan.FromMinutes(5)) };return value;}private class CacheEntryT{public T Value { get; set; }public DateTime Expiration { get; set; }}
}仓库层
仓库层负责与数据库交互执行具体的SQL命令。
Repositories/StudentRepository.cs
using Dapper;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using Oracle.ManagedDataAccess.Client;public class StudentRepository
{private readonly string _connectionString;public StudentRepository(string connectionString){_connectionString connectionString;}public ListStudent GetAllStudents(){using (var connection new OracleConnection(_connectionString)){return connection.QueryStudent(SELECT * FROM Students).ToList();}}public ListStudent GetPagedStudents(int page, int pageSize){using (var connection new OracleConnection(_connectionString)){int offset (page - 1) * pageSize;var sql $SELECT * FROM Students ORDER BY Id OFFSET :offset ROWS FETCH NEXT :pageSize ROWS ONLY;return connection.QueryStudent(sql, new { offset, pageSize }).ToList();}}public Student GetStudentById(int id){using (var connection new OracleConnection(_connectionString)){return connection.QueryFirstOrDefaultStudent(SELECT * FROM Students WHERE Id :id, new { id });}}public void AddStudent(Student student){using (var connection new OracleConnection(_connectionString)){connection.Execute(INSERT INTO Students (ClassId, Name) VALUES (:classId, :name), new { student.ClassId, student.Name });}}public void UpdateStudent(Student student){using (var connection new OracleConnection(_connectionString)){connection.Execute(UPDATE Students SET ClassId :classId, Name :name WHERE Id :id, new { student.ClassId, student.Name, student.Id });}}public void DeleteStudent(int id){using (var connection new OracleConnection(_connectionString)){connection.Execute(DELETE FROM Students WHERE Id :id, new { id });}}public ListStudentGradeDTO GetStudentGradesByClassId(int classId){using (var connection new OracleConnection(_connectionString)){var sql SELECT s.Id AS StudentId, s.Name AS StudentName, c.Name AS CourseName, g.ScoreFROM Students sJOIN Grades g ON s.Id g.StudentIdJOIN Courses c ON g.CourseId c.IdWHERE s.ClassId :classId;return connection.QueryStudentGradeDTO(sql, new { classId }).ToList();}}
}服务层
服务层处理业务逻辑调用仓库层的方法来完成具体的功能并集成缓存逻辑。
Services/StudentService.cs
using System.Collections.Generic;
using System.Threading.Tasks;public class StudentService
{private readonly StudentRepository _repository;private readonly InMemoryCacheint, Student _studentCache;private readonly InMemoryCache(int Page, int PageSize), ListStudent _pagedStudentCache;private readonly InMemoryCacheint, ListStudentGradeDTO _studentGradesCache;public StudentService(StudentRepository repository){_repository repository;_studentCache new InMemoryCacheint, Student();_pagedStudentCache new InMemoryCache(int Page, int PageSize), ListStudent();_studentGradesCache new InMemoryCacheint, ListStudentGradeDTO();}public async TaskListStudent GetAllStudentsAsync(){return await Task.FromResult(_repository.GetAllStudents());}public async TaskListStudent GetPagedStudentsAsync(int page, int pageSize){return await _pagedStudentCache.GetOrAddAsync((page, pageSize), async key await Task.FromResult(_repository.GetPagedStudents(key.Page, key.PageSize)));}public async TaskStudent GetStudentByIdAsync(int id){return await _studentCache.GetOrAddAsync(id, async key await Task.FromResult(_repository.GetStudentById(key)));}public void AddStudent(Student student){_repository.AddStudent(student);}public void UpdateStudent(Student student){_repository.UpdateStudent(student);}public void DeleteStudent(int id){_repository.DeleteStudent(id);}public async TaskListStudentGradeDTO GetStudentGradesByClassIdAsync(int classId){return await _studentGradesCache.GetOrAddAsync(classId, async key await Task.FromResult(_repository.GetStudentGradesByClassId(key)));}
}控制层
控制层接收客户端请求并调用服务层提供的方法来处理请求。
Controllers/StudentController.cs
using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
using System.Threading.Tasks;[ApiController]
[Route(api/[controller])]
public class StudentController : ControllerBase
{private readonly StudentService _service;public StudentController(StudentService service){_service service;}[HttpGet]public async TaskActionResultListStudent GetAllStudentsAsync(){return Ok(await _service.GetAllStudentsAsync());}[HttpGet(paged)]public async TaskActionResultListStudent GetPagedStudentsAsync(int page 1, int pageSize 10){return Ok(await _service.GetPagedStudentsAsync(page, pageSize));}[HttpGet({id})]public async TaskActionResultStudent GetStudentByIdAsync(int id){var student await _service.GetStudentByIdAsync(id);if (student null){return NotFound();}return Ok(student);}[HttpPost]public async TaskActionResultStudent AddStudentAsync([FromBody] Student student){_service.AddStudent(student);return CreatedAtAction(nameof(GetStudentByIdAsync), new { id student.Id }, student);}[HttpPut({id})]public async TaskIActionResult UpdateStudentAsync(int id, [FromBody] Student student){if (id ! student.Id){return BadRequest();}_service.UpdateStudent(student);return NoContent();}[HttpDelete({id})]public async TaskIActionResult DeleteStudentAsync(int id){_service.DeleteStudent(id);return NoContent();}[HttpGet(class/{classId}/grades)]public async TaskActionResultListStudentGradeDTO GetStudentGradesByClassIdAsync(int classId){return Ok(await _service.GetStudentGradesByClassIdAsync(classId));}
}配置依赖注入
在Startup.cs中配置依赖注入以便可以在控制器和服务之间共享仓库实例。
Startup.cs
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using MyApp.Repositories;
using MyApp.Services;public class Startup
{public Startup(IConfiguration configuration){Configuration configuration;}public IConfiguration Configuration { get; }public void ConfigureServices(IServiceCollection services){services.AddControllers();services.AddScopedStudentRepository(provider new StudentRepository(Configuration.GetConnectionString(DefaultConnection)));services.AddScopedStudentService();}public void Configure(IApplicationBuilder app, IWebHostEnvironment env){if (env.IsDevelopment()){app.UseDeveloperExceptionPage();}app.UseRouting();app.UseAuthorization();app.UseEndpoints(endpoints {endpoints.MapControllers();});}
}配置文件
在appsettings.json中配置数据库连接字符串。
appsettings.json
{ConnectionStrings: {DefaultConnection: User Idyour_username;Passwordyour_password;Data Sourceyour_data_source;},Logging: {LogLevel: {Default: Information,Microsoft: Warning,Microsoft.Hosting.Lifetime: Information}},AllowedHosts: *
}应用入口
Program.cs
using Microsoft.AspNetCore.Hosting;
using Microsoft.Extensions.Hosting;public class Program
{public static void Main(string[] args){CreateHostBuilder(args).Build().Run();}public static IHostBuilder CreateHostBuilder(string[] args) Host.CreateDefaultBuilder(args).ConfigureWebHostDefaults(webBuilder {webBuilder.UseStartupStartup();});
}总结
通过上述代码我们实现了一个高性能的 .NET Core 后端项目使用 Dapper 访问 Oracle 数据库并实现了学生表、课程表、成绩表和班级表的基本增删改查功能以及查询某个班级学生成绩的功能。查询功能使用了自定义缓存来优化性能。希望这些代码对你有所帮助
