.Net Core3.0 WebApi 项目框架搭建 五: 轻量型ORM+异步泛型仓储
HuTiger 人气:2.Net Core3.0 WebApi 项目框架搭建:目录
SqlSugar介绍
SqlSugar是国人开发者开发的一款基于.NET的ORM框架,是可以运行在.NET 4.+ & .NET CORE的高性能、轻量级 ORM框架,众多.NET框架中最容易使用的数据库访问技术。官网http://www.codeisbug.com/Doc/8
SqlSugar的优点:
1、高性能 ,不夸张的说,去掉Sql在数据库执行的时间,SqlSugar是EF数倍性能,另外在批量操作和一对多查询上也有不错的SQL优化
2、高扩展性 ,支持自定义拉姆达函数解析、扩展数据类型、支持自定义实体特性,外部缓存等
3、稳定性和技术支持, 虽然不是官方ORM, 但在稳定性上也是有着数年用户积累,如果遇到问题可以在GITHUB提出来,会根据紧急度定期解决
4、功能全面,虽然SqlSugar小巧可功能并不逊色于EF框架
5、创新、持续更新 ,向下兼容
创建数据库
这里采用DB First的模式,先新建一个数据库,然后新建一张User表,这里比较简单,记得表字段添加注释说明。
使用代码生成器生成数据库连接文件和实体类
下载地址 :https://github.com/sunkaixuan/SoEasyPlatform
用法非常简单
1、下载项目
2、解压项目
3、点击解决方案打开项目
4、配置连接字符串名称、数据库类型和解决方案名称
5、F5运行该项目
6.生成成功
Repository 和Model层中引入SqlSugar
直接在类库中通过Nuget引入 sqlSugarCore或者通过Install-Package sqlSugarCore安装
删除原来Model层的的User.cs将代码生成器生成的实体放到model层的enity文件夹
using System; using System.Linq; using System.Text; using SqlSugar; namespace Webapi.Core.Model.Enity { ///<summary> ///用户表 ///</summary> [SugarTable("User")] public partial class User { public User() { } /// <summary> /// Desc:用户ID /// Default: /// Nullable:False /// </summary> [SugarColumn(IsPrimaryKey = true, IsIdentity = true)] public int UserId { get; set; } /// <summary> /// Desc:用户名 /// Default: /// Nullable:True /// </summary> public string UserName { get; set; } /// <summary> /// Desc:年龄 /// Default: /// Nullable:True /// </summary> public int? Age { get; set; } } }
Repository层新建suger文件夹,将生成的DBConext.cs放置进去,里面已经内置了增删改查等方法,可以删除掉这些方法,后面我们自己使用异步实现,还是很方便的,不过,如果要使用异步,就要异步到底,不然就会阻塞,变成了同步。
using Webapi.Core.Model.Entity; using SqlSugar; using System; using System.Collections.Generic; using System.Linq; using System.Linq.Expressions; public class DbContext<T> where T : class, new() { public DbContext() { Db = new SqlSugarClient(new ConnectionConfig() { ConnectionString = "server=.;uid=sa;pwd=sa123;database=CoreDB", DbType = DbType.SqlServer, InitKeyType = InitKeyType.Attribute,//从特性读取主键和自增列信息 IsAutoCloseConnection = true,//开启自动释放模式和EF原理一样我就不多解释了 }); //调式代码 用来打印SQL Db.Aop.OnLogExecuting = (sql, pars) => { Console.WriteLine(sql + "\r\n" + Db.Utilities.SerializeObject(pars.ToDictionary(it => it.ParameterName, it => it.Value))); Console.WriteLine(); }; } //注意:不能写成静态的 public SqlSugarClient Db;//用来处理事务多表查询和复杂的操作 public SimpleClient<T> CurrentDb { get { return new SimpleClient<T>(Db); } }//用来操作当前表的数据 public SimpleClient<User> UserDb { get { return new SimpleClient<User>(Db); } }//用来处理User表的常用操作 /// <summary> /// 获取所有 /// </summary> /// <returns></returns> public virtual List<T> GetList() { return CurrentDb.GetList(); } /// <summary> /// 根据表达式查询 /// </summary> /// <returns></returns> public virtual List<T> GetList(Expression<Func<T, bool>> whereExpression) { return CurrentDb.GetList(whereExpression); } /// <summary> /// 根据表达式查询分页 /// </summary> /// <returns></returns> public virtual List<T> GetPageList(Expression<Func<T, bool>> whereExpression, PageModel pageModel) { return CurrentDb.GetPageList(whereExpression, pageModel); } /// <summary> /// 根据表达式查询分页并排序 /// </summary> /// <param name="whereExpression">it</param> /// <param name="pageModel"></param> /// <param name="orderByExpression">it=>it.id或者it=>new{it.id,it.name}</param> /// <param name="orderByType">OrderByType.Desc</param> /// <returns></returns> public virtual List<T> GetPageList(Expression<Func<T, bool>> whereExpression, PageModel pageModel, Expression<Func<T, object>> orderByExpression = null, OrderByType orderByType = OrderByType.Asc) { return CurrentDb.GetPageList(whereExpression, pageModel,orderByExpression,orderByType); } /// <summary> /// 根据主键查询 /// </summary> /// <returns></returns> public virtual T GetById(dynamic id) { return CurrentDb.GetById(id); } /// <summary> /// 根据主键删除 /// </summary> /// <param name="id"></param> /// <returns></returns> public virtual bool Delete(dynamic id) { return CurrentDb.Delete(id); } /// <summary> /// 根据实体删除 /// </summary> /// <param name="id"></param> /// <returns></returns> public virtual bool Delete(T data) { return CurrentDb.Delete(data); } /// <summary> /// 根据主键删除 /// </summary> /// <param name="id"></param> /// <returns></returns> public virtual bool Delete(dynamic[] ids) { return CurrentDb.AsDeleteable().In(ids).ExecuteCommand()>0; } /// <summary> /// 根据表达式删除 /// </summary> /// <param name="id"></param> /// <returns></returns> public virtual bool Delete(Expression<Func<T, bool>> whereExpression) { return CurrentDb.Delete(whereExpression); } /// <summary> /// 根据实体更新,实体需要有主键 /// </summary> /// <param name="id"></param> /// <returns></returns> public virtual bool Update(T obj) { return CurrentDb.Update(obj); } /// <summary> ///批量更新 /// </summary> /// <param name="id"></param> /// <returns></returns> public virtual bool Update(List<T> objs) { return CurrentDb.UpdateRange(objs); } /// <summary> /// 插入 /// </summary> /// <param name="id"></param> /// <returns></returns> public virtual bool Insert(T obj) { return CurrentDb.Insert(obj); } /// <summary> /// 批量 /// </summary> /// <param name="id"></param> /// <returns></returns> public virtual bool Insert(List<T> objs) { return CurrentDb.InsertRange(objs); } //自已扩展更多方法 }
这里的ConnectionString是数据库连接字符串,我们可以写在配置文件里面,sugar新建BaseDBConfig.cs
public class BaseDBConfig { /// <summary> /// 数据库连接字符串 /// </summary> public static string ConnectionString { get; set; } }
修改DbContext的构造函数
Db = new SqlSugarClient(new ConnectionConfig() { ConnectionString = BaseDBConfig.ConnectionString, DbType = DbType.SqlServer, InitKeyType = InitKeyType.Attribute,//从特性读取主键和自增列信息 IsAutoCloseConnection = true,//开启自动释放模式和EF原理一样我就不多解释了 });
在 startup.cs 中的 ConfigureServices() 方法中添加
//数据库配置 BaseDBConfig.ConnectionString = Configuration.GetSection("AppSettings:ConnectionString").Value;
设计仓储基类接口——IBaseRepository.cs
IRepository 层中添加Base文件夹,并添加接口 IBaseRepository.cs。
using System; using System.Collections.Generic; using System.Text; using System.Threading.Tasks; namespace Webapi.Core.IRepository.Base { /// <summary> /// 基类接口,其他接口继承该接口 /// </summary> /// <typeparam name="TEntity"></typeparam> public interface IBaseRepository<TEntity> where TEntity : class { /// <summary> /// 根据ID查询 /// </summary> /// <param name="objId"></param> /// <returns></returns> Task<TEntity> QueryByID(object objId); /// <summary> /// 添加 /// </summary> /// <param name="model"></param> /// <returns></returns> Task<int> Add(TEntity model); /// <summary> /// 修改 /// </summary> /// <param name="model"></param> /// <returns></returns> Task<bool> Update(TEntity model); /// <summary> /// 删除 /// </summary> /// <param name="ids"></param> /// <returns></returns> Task<bool> DeleteByIds(object[] ids); } }
继承基接口
IRepository 层中,将其他的接口,继承Base,新建IUserRepository.cs,然后将其他所有的方法都继承该基类方法
public interface IUserRepository : IBaseRepositoryy<User> { }
对仓储基接口进行实现
Repository 层中,添加Base文件夹,并添加 BaseRepository.cs 基类
public class BaseRepository<TEntity> : DbContext<TEntity>, IBaseRepository<TEntity> where TEntity : class, new() { /// <summary> /// 写入实体数据 /// </summary> /// <param name="model"></param> /// <returns></returns> public async Task<int> Add(TEntity model) { var i = await Task.Run(() => Db.Insertable(model).ExecuteReturnBigIdentity()); //返回的i是long类型,这里你可以根据你的业务需要进行处理 return (int)i; } /// <summary> /// 根据ID删除 /// </summary> /// <param name="ids"></param> /// <returns></returns> public async Task<bool> DeleteByIds(object[] ids) { var i = await Task.Run(() => Db.Deleteable<TEntity>().In(ids).ExecuteCommand()); return i > 0; } /// <summary> /// 根据ID查询一条数据 /// </summary> /// <param name="objId"></param> /// <returns></returns> public async Task<TEntity> QueryByID(object objId) { return await Task.Run(() => Db.Queryable<TEntity>().InSingle(objId)); } /// <summary> /// 更新实体数据 /// </summary> /// <param name="model"></param> /// <returns></returns> public async Task<bool> Update(TEntity model) { //这种方式会以主键为条件 var i = await Task.Run(() => Db.Updateable(model).ExecuteCommand()); return i > 0; }
业务逻辑层基类与基接口
IService 层中添加Base文件夹,并添加接口 IBaseService.cs。
public interface IBaseServices<TEntity> where TEntity : class { /// <summary> /// 根据ID列表删除 /// </summary> /// <param name="ids"></param> /// <returns></returns> Task<bool> DeleteByIds(object[] ids); /// <summary> /// 根据ID查询 /// </summary> /// <param name="objId"></param> /// <returns></returns> Task<TEntity> QueryByID(object objId); /// <summary> /// 添加实体 /// </summary> /// <param name="model"></param> /// <returns></returns> Task<int> Add(TEntity model); /// <summary> /// 更新实体 /// </summary> /// <param name="model"></param> /// <returns></returns> Task<bool> Update(TEntity model); }
Service 层中添加Base文件夹,并添加接口 BaseService.cs。并添加 BaseService.cs 基类
public class BaseServices<TEntity> : IBaseServices<TEntity> where TEntity : class, new() { public IBaseRepository<TEntity> baseDal = new BaseRepository<TEntity>(); /// <summary> /// 写入实体 /// </summary> /// <param name="model"></param> /// <returns></returns> public async Task<int> Add(TEntity model) { return await baseDal.Add(model); } /// <summary> /// 根据ID删除 /// </summary> /// <param name="ids"></param> /// <returns></returns> public async Task<bool> DeleteByIds(object[] ids) { return await baseDal.DeleteByIds(ids); } /// <summary> /// 根据ID查询 /// </summary> /// <param name="objId"></param> /// <returns></returns> public async Task<TEntity> QueryByID(object objId) { return await baseDal.QueryByID(objId); } /// <summary> /// 更新实体 /// </summary> /// <param name="model"></param> /// <returns></returns> public async Task<bool> Update(TEntity model) { return await baseDal.Update(model); } }
IService 层新建IUserService,继承BaseService
public interface IUserService : IBaseService<User> { }
Service 层新建IUserService,继承BaseService和IUserService
public class UserService : BaseService<User>, IUserService { }
新增接口
User控制器新增接口:
/// <summary> /// 根据id获取数据 /// </summary> /// <param name="id">参数id</param> /// <returns></returns> [HttpGet("{id}", Name = "Get")] public async Task<IActionResult> GetUser(int id) { IUserService userService = new UserService(); User user = await userService.QueryByID(id); return Ok(user); } /// <summary> /// 添加数据 /// </summary> /// <param name="id">参数id</param> /// <returns></returns> [HttpGet("{id}", Name = "Get")] public async Task<IActionResult> Add(User user) { IUserService userService = new UserService(); var count = await userService.Add(user); return Ok(count); } /// <summary> /// 更新数据 /// </summary> /// <param name="id">参数id</param> /// <returns></returns> [HttpGet("{id}", Name = "Get")] public async Task<IActionResult> Update(User user) { IUserService userService = new UserService(); var sucess = await userService.Update(user); return Ok(sucess); } /// <summary> /// 删除数据 /// </summary> /// <param name="id">参数id</param> /// <returns></returns> [HttpGet("{id}", Name = "Get")] public async Task<IActionResult> Delete(object[] ids) { IUserService userService = new UserService(); var sucess = await userService.DeleteByIds(ids); return Ok(sucess); }
调试接口
F5运行项目,测试添加接口:
数据库成功添加了数据,因为Id用的是自增,所以是1
测试查询接口:
测试修改接口
如果修改不存在的ID,则删除失败
测试删除接口:
数据已删除
加载全部内容