目录
1.开发环境
.NET6
Visual Studio 2022
SQLServer
SqlSugar SqlSugar ORM 5.X 官网 、文档、教程 - SqlSugar 5x - .NET果糖网
2.项目创建
2.1创建WebApi主项目
创建名为MyFurion.WebApi的项目
2.2 创建Start类库
创建名称为 MyFurion.Start的类库
解决方案右击——添加——新项目——类库
2.3创建Model实体类库
创建名称为 MyFurion.Model的类库
创建步骤同 2.2创建Startup类库
2.4创建Application仓储业务类库
创建名称为 MyFurion.Application的类库
创建步骤同 2.2创建Startup类库
2.5创建Unility通用方法类库
创建名称为 MyFurion.Unility的类库
创建步骤同 2.2创建Startup类库
至此需要的类库项目创建完成
3.基础功能配置
3.1 Model实体对象与数据表映射
MyFurion.Model项目中,通过Nuget添加Furion、Furion.Extras.DatabaseAccessor.SqlSugar、Furion.Extras.ObjectMapper.Mapster、SqlSugarCore,同时添加对项目MyFurion.Unility的引用
创建实体基类BaseEntity
using SqlSugar;
using System.Text.Json.Serialization;
using MyFurion.Unility.Const;
namespace MyFurion.Model
{
/// <summary>
/// 实体基类
/// </summary>
public class BaseEntity
{
/// <summary>
///
/// </summary>
public BaseEntity()
{
CreateTime = DateTime.Now;
IsDeleted = false;
Id = SnowFlakeSingle.Instance.NextId();
}
/// <summary>
/// id
/// </summary>
[SugarColumn(IsPrimaryKey =true,DefaultValue ="主键")]
public long Id { get; set; }
/// <summary>
/// 创建时间
/// </summary>
[SugarColumn(IsOnlyIgnoreUpdate = true,ColumnDescription = "创建时间")]
public DateTime CreateTime { get; set; }
/// <summary>
/// 创建人id
/// </summary>
[SugarColumn(IsOnlyIgnoreUpdate = true,IsNullable =true, ColumnDescription = "创建人id")]
[JsonIgnore]
public string? CreateUserId { get; set; }
/// <summary>
/// 创建人
/// </summary>
[SugarColumn(IsOnlyIgnoreUpdate = true, IsNullable = true, ColumnDescription = "创建人")]
[JsonIgnore]
public string? CreateUser { get; set; }
/// <summary>
/// 创建单位id
/// </summary>
[SugarColumn(IsOnlyIgnoreUpdate = true, IsNullable = true, ColumnDescription = "创建单位id")]
[JsonIgnore]
public string? CreateOrgId { get; set; }
/// <summary>
/// 修改时间
/// </summary>
[SugarColumn(IsOnlyIgnoreInsert =true,IsNullable =true, ColumnDescription = "修改时间")]
public DateTime? ModifyTime { get; set; }
/// <summary>
/// 修改人id
/// </summary>
[SugarColumn(IsOnlyIgnoreInsert = true, IsNullable = true, ColumnDescription = "修改人id")]
[JsonIgnore]
public string? ModifyUserId { get; set; }
/// <summary>
/// 修改人
/// </summary>
[SugarColumn(IsOnlyIgnoreInsert = true, IsNullable = true, ColumnDescription = "修改人")]
[JsonIgnore]
public string? ModifyUser { get; set; }
/// <summary>
/// 删除标识
/// </summary>
[SugarColumn(ColumnDescription ="删除标识")]
public bool IsDeleted { get; set; }
/// <summary>
/// 删除时间
/// </summary>
[SugarColumn(IsOnlyIgnoreInsert = true, ColumnDescription = "删除时间",IsNullable =true)]
[JsonIgnore]
public DateTime? DeleteTime { get; set; }
/// <summary>
/// 删除原因
/// </summary>
[SugarColumn(IsOnlyIgnoreInsert = true, ColumnDescription = "删除原因", IsNullable = true)]
public string? DeleteReason { get; set; }
/// <summary>
/// 删除人id
/// </summary>
[SugarColumn(IsOnlyIgnoreInsert = true, ColumnDescription = "删除人id", IsNullable = true)]
[JsonIgnore]
public string? DeleteUserId { get; set; }
/// <summary>
/// 删除人
/// </summary>
[SugarColumn(IsOnlyIgnoreInsert = true, ColumnDescription = "删除人", IsNullable = true)]
[JsonIgnore]
public string? DeleteUser { get; set; }
/// <summary>
/// 排序
/// </summary>
[SugarColumn(ColumnDescription ="排序")]
public int SortNum { get; set; }
/// <summary>
/// 备注
/// </summary>
[SugarColumn(ColumnDescription = "备注", IsNullable =true,ColumnDataType =CommonConst.DB_STRING_MAX)]
public string? Remark { get; set; }
/// <summary>
/// 多租户ID
/// </summary>
[SugarColumn(ColumnDescription = "多租户ID", DefaultValue = "0")]
[JsonIgnore]
public long TenantId { get; set; }
}
}
3.2 基类仓储及动态Api接口配置
MyFurion.Application项目中,通过Nuget添加SqlSugar.IOC,同时添加对MyFurion.Model项目的引用
新增Dtos、Repository、Controller三个文件夹,分别用于查询条件及输出信息的实体对象、业务代码、Api接口文件
在Dtos文件加下创建PageResult类(分页结果)及PageBaseInput(分页查询条件基类)
namespace MyFurion.Application.Dtos
{
/// <summary>
/// 分页数据信息
/// </summary>
/// <typeparam name="T"></typeparam>
public class PageResult<T>
{
/// <summary>
/// 页码
/// </summary>
public int PageIndex { get; set; }
/// <summary>
/// 分页大小
/// </summary>
public int PageSize { get; set; }
/// <summary>
/// 页总数
/// </summary>
public int TotalPage { get; set; }
/// <summary>
/// 记录总数
/// </summary>
public int TotalCount { get; set; }
/// <summary>
/// 记录集合
/// </summary>
public List<T> Items { get; set; } = new();
}
}
namespace MyFurion.Application.Dtos
{
/// <summary>
/// 分页查询条件基类
/// </summary>
public class PageBaseInput
{
/// <summary>
/// 页码
/// </summary>
public int PageIndex { get; set; } = 1;
/// <summary>
/// 分页大小
/// </summary>
public int PageSize { get; set; } = 20;
/// <summary>
/// 开始日期
/// </summary>
public DateTime? StartTime { get; set; }
/// <summary>
/// 结束日期
/// </summary>
public DateTime? EndTime { get; set; }
}
}
创建GlobalUsings.cs全局引用配置类
global using System.Reflection;
global using System.ComponentModel.DataAnnotations;
global using System.Linq.Expressions;
global using Microsoft.AspNetCore.Authorization;
global using Microsoft.AspNetCore.Http;
global using Microsoft.AspNetCore.Mvc;
global using Microsoft.CodeAnalysis;
global using Furion;
global using Furion.DataEncryption;
global using Furion.DataValidation;
global using Furion.DependencyInjection;
global using Furion.DynamicApiController;
global using Furion.Extensions;
global using Furion.FriendlyException;
global using Furion.Logging;
global using SqlSugar;
global using Mapster;
global using SqlSugar.IOC;
global using MyFurion.Model;
global using MyFurion.Application.Dtos;
创建仓储基类BaseRepository
namespace MyFurion.Application
{
/// <summary>
/// 仓储基类
/// </summary>
/// <typeparam name="T"></typeparam>
public class BaseRepository<T> : SimpleClient<T> where T : BaseEntity, new()
{
public ITenant itenant = null;//多租户事务
public BaseRepository(ISqlSugarClient context = null) : base(context)
{
//通过特性拿到ConfigId
var configId = typeof(T).GetCustomAttribute<TenantAttribute>()?.configId;
if (configId != null)
{
Context = DbScoped.SugarScope.GetConnectionScope(configId);//根据类传入的ConfigId自动选择
}
else
{
Context = context ?? DbScoped.SugarScope.GetConnectionScope(0);//没有默认db0
}
//Context = DbScoped.SugarScope.GetConnectionScopeWithAttr<T>();
itenant = DbScoped.SugarScope;//设置租户接口
}
#region 基础业务
/// <summary>
/// 新增
/// </summary>
/// <param name="t"></param>
/// <returns></returns>
public async Task<bool> Add(T t)
{
try
{
int rowsAffect = await Context.Insertable(t).IgnoreColumns(true).ExecuteCommandAsync();
return rowsAffect > 0;
}
catch (Exception ex)
{
Log.Error($"新增失败:{ex.Message}");
return false;
}
}
/// <summary>
/// 批量新增
/// </summary>
/// <param name="t"></param>
/// <returns></returns>
public async Task<bool> Insert(List<T> t)
{
try
{
int rowsAffect = await Context.Insertable(t).ExecuteCommandAsync();
return rowsAffect > 0;
}
catch (Exception ex)
{
Log.Error($"批量新增失败:{ex.Message}");
return false;
}
}
/// <summary>
/// 插入设置列数据
/// </summary>
/// <param name="parm"></param>
/// <param name="iClumns"></param>
/// <param name="ignoreNull"></param>
/// <returns></returns>
public async Task<bool> Insert(T parm, Expression<Func<T, object>> iClumns = null, bool ignoreNull = true)
{
try
{
int rowsAffect = await Context.Insertable(parm).InsertColumns(iClumns).IgnoreColumns(ignoreNullColumn: ignoreNull).ExecuteCommandAsync();
return rowsAffect > 0;
}
catch (Exception ex)
{
Log.Error($"插入设置列数据失败:{ex.Message}");
return false;
}
}
/// <summary>
/// 更新
/// </summary>
/// <param name="entity"></param>
/// <param name="ignoreNullColumns"></param>
/// <returns></returns>
public async Task<bool> Update(T entity, bool ignoreNullColumns = false)
{
try
{
int rowsAffect = await Context.Updateable(entity).IgnoreColumns(ignoreNullColumns).ExecuteCommandAsync();
return rowsAffect >= 0;
}
catch (Exception ex)
{
Log.Error($"更新失败:{ex.Message}");
return false;
}
}
/// <summary>
/// 根据实体类更新指定列 eg:Update(dept, it => new { it.Status });只更新Status列,条件是包含
/// </summary>
/// <param name="entity"></param>
/// <param name="expression"></param>
/// <param name="ignoreAllNull"></param>
/// <returns></returns>
public async Task<bool> Update(T entity, Expression<Func<T, object>> expression, bool ignoreAllNull = false)
{
try
{
int rowsAffect = await Context.Updateable(entity).UpdateColumns(expression).IgnoreColumns(ignoreAllNull).ExecuteCommandAsync();
return rowsAffect >= 0;
}
catch (Exception ex)
{
Log.Error($"根据实体类更新指定列失败:{ex.Message}");
return false;
}
}
/// <summary>
/// 根据实体类更新指定列 eg:Update(dept, it => new { it.Status }, f => depts.Contains(f.DeptId));只更新Status列,条件是包含
/// </summary>
/// <param name="entity"></param>
/// <param name="expression"></param>
/// <param name="where"></param>
/// <returns></returns>
public async Task<bool> Update(T entity, Expression<Func<T, object>> expression, Expression<Func<T, bool>> where)
{
try
{
int rowsAffect = await Context.Updateable(entity).UpdateColumns(expression).Where(where).ExecuteCommandAsync();
return rowsAffect >= 0;
}
catch (Exception ex)
{
Log.Error($"根据实体类更新指定列失败:{ex.Message}");
return false;
}
}
/// <summary>
/// 更新指定列 eg:Update(w => w.NoticeId == model.NoticeId, it => new SysNotice(){ UpdateTime = DateTime.Now, Title = "通知标题" });
/// </summary>
/// <param name="where"></param>
/// <param name="columns"></param>
/// <returns></returns>
public async Task<bool> Update(Expression<Func<T, bool>> where, Expression<Func<T, T>> columns)
{
try
{
int rowsAffect = await Context.Updateable<T>().SetColumns(columns).Where(where).RemoveDataCache().ExecuteCommandAsync();
return rowsAffect >= 0;
}
catch (Exception ex)
{
Log.Error($"更新指定列失败:{ex.Message}");
return false;
}
}
/// <summary>
/// 事务 eg:var result = UseTran(() =>{SysRoleRepository.UpdateSysRole(sysRole);DeptService.DeleteRoleDeptByRoleId(sysRole.ID);DeptService.InsertRoleDepts(sysRole);});
/// </summary>
/// <param name="action"></param>
/// <returns></returns>
public bool UseTran(Action action)
{
try
{
var result = Context.Ado.UseTran(() => action());
return result.IsSuccess;
}
catch (Exception ex)
{
Context.Ado.RollbackTran();
Log.Error($"事务执行失败:{ex.Message}");
return false;
}
}
/// <summary>
/// 删除
/// </summary>
/// <param name="id">主键id</param>
/// <param name="IsDelete">是否真删除</param>
/// <returns></returns>
public async Task<bool> DeleteById(long id, bool IsDelete = false)
{
int rowsAffect = 0;
try
{
if (IsDelete)
{
rowsAffect = await Context.Deleteable<T>().In(id).ExecuteCommandAsync();
}
else
{
//假删除 实体属性有isdelete或者isdeleted 请升级到5.0.4.9+,(5.0.4.3存在BUG)
rowsAffect = await Context.Deleteable<T>().In(id).IsLogic().ExecuteCommandAsync();
}
return rowsAffect > 0;
}
catch (Exception ex)
{
Log.Error($"删除失败:{ex.Message}");
return false;
}
}
/// <summary>
/// 根据查询条件删除
/// </summary>
/// <param name="where"></param>
/// <param name="IsDelete"></param>
/// <returns></returns>
public async Task<bool> DeleteByWhere(Expression<Func<T, bool>> where, bool IsDelete = false)
{
int rowsAffect = 0;
try
{
if (IsDelete)
{
rowsAffect = await Context.Deleteable<T>().Where(where).ExecuteCommandAsync();
}
else
{
//假删除 实体属性有isdelete或者isdeleted 请升级到5.0.4.9+,(5.0.4.3存在BUG)
rowsAffect = await Context.Deleteable<T>().Where(where).IsLogic().ExecuteCommandAsync();
}
return rowsAffect > 0;
}
catch (Exception ex)
{
Log.Error($"根据查询条件删除失败:{ex.Message}");
return false;
}
}
/// <summary>
/// 根据id获取数据
/// </summary>
/// <param name="id">主键值</param>
/// <returns>泛型实体</returns>
public async Task<T> GetEntityById(long id)
{
return await Context.Queryable<T>().FirstAsync(p => p.Id == id);
}
/// <summary>
/// 数据是否存在
/// </summary>
/// <param name="expression"></param>
/// <returns></returns>
public async Task<bool> IsExists(Expression<Func<T, bool>> expression)
{
return await Context.Queryable<T>().Where(expression).AnyAsync();
}
/// <summary>
/// 获取所有数据
/// </summary>
/// <returns></returns>
public async Task<List<T>> GetAll()
{
return await Context.Queryable<T>().ToListAsync();
}
/// <summary>
/// 根据查询条件获取数据
/// </summary>
/// <param name="expression"></param>
/// <returns></returns>
public async Task<List<T>> GetListByWhere(Expression<Func<T, bool>> expression)
{
return await Context.Queryable<T>().Where(expression).ToListAsync();
}
/// <summary>
/// 根据查询条件获取数据(动态表格拼接查询条件)
/// </summary>
/// <param name="conditions"></param>
/// <returns></returns>
public async Task<List<T>> GetListByWhere(List<IConditionalModel> conditions)
{
return await Context.Queryable<T>().Where(conditions).ToListAsync();
}
/// <summary>
/// 根据查询条件获取数据
/// </summary>
/// <param name="expression"></param>
/// <param name="orderFiled">排序字段</param>
/// <param name="orderEnum">排序方式</param>
/// <returns></returns>
public async Task<List<T>> GetList(Expression<Func<T, bool>> expression, Expression<Func<T, object>> orderFiled, OrderByType orderEnum = OrderByType.Desc)
{
return await Context.Queryable<T>().Where(expression).OrderByIF(orderEnum == OrderByType.Asc, orderFiled, OrderByType.Asc).OrderByIF(orderEnum == OrderByType.Desc, orderFiled, OrderByType.Desc).ToListAsync();
}
/// <summary>
/// 获取分页数据
/// </summary>
/// <param name="expression"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public PageResult<T> GetPageList(Expression<Func<T, bool>> expression, int pageIndex, int pageSize)
{
int totalCount = 0;
var result = Context.Queryable<T>().Where(expression).ToPageList(pageIndex, pageSize, ref totalCount);
var pageResult = new PageResult<T>();
pageResult.Items = result;
pageResult.TotalCount = totalCount;
pageResult.TotalPage = (int)Math.Ceiling(totalCount / (double)pageSize);
return pageResult;
}
/// <summary>
/// 获取分页数据
/// </summary>
/// <param name="expression"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public async Task<PageResult<T>> GetPageListAsync(Expression<Func<T, bool>> expression, int pageIndex, int pageSize)
{
RefAsync<int> totalCount = 0;
var result = await Context.Queryable<T>().Where(expression).ToPageListAsync(pageIndex, pageSize, totalCount);
var pageResult = new PageResult<T>();
pageResult.Items = result;
pageResult.TotalCount = totalCount;
pageResult.TotalPage = (int)Math.Ceiling(totalCount / (double)pageSize);
return pageResult;
}
/// <summary>
/// 获取分页数据
/// </summary>
/// <param name="expression"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="orderFiled"></param>
/// <param name="orderEnum"></param>
/// <returns></returns>
public PageResult<T> GetPageList(Expression<Func<T, bool>> expression, int pageIndex, int pageSize, Expression<Func<T, object>> orderFiled, OrderByType orderEnum = OrderByType.Desc)
{
int totalCount = 0;
var result = Context.Queryable<T>().Where(expression).OrderByIF(orderEnum == OrderByType.Asc, orderFiled, OrderByType.Asc).OrderByIF(orderEnum == OrderByType.Desc, orderFiled, OrderByType.Desc)
.ToPageList(pageIndex, pageSize, ref totalCount);
var pageResult = new PageResult<T>();
pageResult.Items = result;
pageResult.TotalCount = totalCount;
pageResult.TotalPage = (int)Math.Ceiling(totalCount / (double)pageSize);
return pageResult;
}
/// <summary>
/// 获取分页数据
/// </summary>
/// <param name="expression"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="orderFiled"></param>
/// <param name="orderEnum"></param>
/// <returns></returns>
public async Task<PageResult<T>> GetPageListAsync(Expression<Func<T, bool>> expression, int pageIndex, int pageSize, Expression<Func<T, object>> orderFiled, OrderByType orderEnum = OrderByType.Desc)
{
RefAsync<int> totalCount = 0;
var result = await Context.Queryable<T>().Where(expression).OrderByIF(orderEnum == OrderByType.Asc, orderFiled, OrderByType.Asc).OrderByIF(orderEnum == OrderByType.Desc, orderFiled, OrderByType.Desc)
.ToPageListAsync(pageIndex, pageSize, totalCount);
var pageResult = new PageResult<T>();
pageResult.Items = result;
pageResult.TotalCount = totalCount;
pageResult.TotalPage = (int)Math.Ceiling(totalCount / (double)pageSize);
return pageResult;
}
/// <summary>
/// 获取分页数据
/// </summary>
/// <param name="expression"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="orderFiled"></param>
/// <param name="orderEnum"></param>
/// <returns></returns>
public async Task<PageResult<T>> GetOffsetPageListAsync(Expression<Func<T, bool>> expression, int pageIndex, int pageSize, Expression<Func<T, object>> orderFiled, OrderByType orderEnum = OrderByType.Desc)
{
RefAsync<int> totalCount = 0;
var result = await Context.Queryable<T>().Where(expression).OrderByIF(orderEnum == OrderByType.Asc, orderFiled, OrderByType.Asc).OrderByIF(orderEnum == OrderByType.Desc, orderFiled, OrderByType.Desc)
.ToOffsetPageAsync(pageIndex, pageSize, totalCount);
var pageResult = new PageResult<T>();
pageResult.Items = result;
pageResult.TotalCount = totalCount;
pageResult.TotalPage = (int)Math.Ceiling(totalCount / (double)pageSize);
return pageResult;
}
#endregion
#region 海量业务高性能
/// <summary>
/// 新增(对于海量数据并且性能要高的)
/// </summary>
/// <param name="t"></param>
/// <returns></returns>
public async Task<bool> BulkAdd(T t)
{
try
{
int rowsAffect = await Context.Storageable(t).ToStorage().BulkCopyAsync();
return rowsAffect > 0;
}
catch (Exception ex)
{
Log.Error($"新增失败:{ex.Message}");
return false;
}
}
/// <summary>
/// 批量新增(对于海量数据并且性能要高的)
/// </summary>
/// <param name="t"></param>
/// <returns></returns>
public async Task<bool> BatchBulkAdd(List<T> t)
{
try
{
int rowsAffect = await Context.Storageable(t).ToStorage().BulkCopyAsync();
return rowsAffect > 0;
}
catch (Exception ex)
{
Log.Error($"批量新增失败:{ex.Message}");
return false;
}
}
/// <summary>
/// 更新(对于海量数据并且性能要高的)
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public async Task<bool> BulkUpdate(T entity)
{
try
{
int rowsAffect = await Context.Storageable(entity).ToStorage().BulkUpdateAsync();
return rowsAffect >= 0;
}
catch (Exception ex)
{
Log.Error($"更新失败:{ex.Message}");
return false;
}
}
/// <summary>
/// 批量更新(对于海量数据并且性能要高的)
/// </summary>
/// <param name="t"></param>
/// <returns></returns>
public async Task<bool> BatchBulkUpdate(List<T> t)
{
try
{
Context.QueryFilter = new QueryFilterProvider();//清空过滤器 否则会出现Parameter '@IsDelete0' must be defined错误
int rowsAffect = await Context.Storageable(t).ToStorage().BulkUpdateAsync();
return rowsAffect >= 0;
}
catch (Exception ex)
{
Log.Error($"更新失败:{ex.Message}");
return false;
}
}
/// <summary>
/// 批量更新(对于海量数据并且性能要高的)
/// </summary>
/// <param name="t"></param>
/// <param name="updateColumns"></param>
/// <returns></returns>
public async Task<bool> BatchBulkUpdate(List<T> t, string[] updateColumns)
{
try
{
Context.QueryFilter = new QueryFilterProvider();//清空过滤器 否则会出现Parameter '@IsDelete0' must be defined错误
int rowsAffect = await Context.Storageable(t).ToStorage().BulkUpdateAsync(updateColumns);
return rowsAffect >= 0;
}
catch (Exception ex)
{
Log.Error($"更新失败:{ex.Message}");
return false;
}
}
#endregion
#region 存储过程
/// <summary>
/// 存储过程
/// </summary>
/// <param name="procedureName"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public async Task<System.Data.DataTable> ProcedureQuery(string procedureName, object parameters)
{
return await Context.Ado.UseStoredProcedure().GetDataTableAsync(procedureName, parameters);
}
/// <summary>
/// 存储过程
/// </summary>
/// <param name="procedureName"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public async Task<List<T>> ProcedureQueryList(string procedureName, object parameters)
{
return await Context.Ado.UseStoredProcedure().SqlQueryAsync<T>(procedureName, parameters);
}
#endregion
#region Fastest
/// <summary>
/// 批量新增
/// </summary>
/// <param name="t"></param>
/// <returns></returns>
public async Task<bool> BatchFastestkAdd(List<T> t)
{
try
{
int rowsAffect = await Context.Fastest<T>().BulkCopyAsync(t);
return rowsAffect > 0;
}
catch (Exception ex)
{
Log.Error($"fastest批量新增失败:{ex.Message}");
return false;
}
}
/// <summary>
/// 批量更新
/// </summary>
/// <param name="t"></param>
/// <returns></returns>
public async Task<bool> BatchFastestUpdate(List<T> t)
{
try
{
Context.QueryFilter = new QueryFilterProvider();//清空过滤器 否则会出现Parameter '@IsDelete0' must be defined错误
int rowsAffect = await Context.Fastest<T>().BulkUpdateAsync(t);
return rowsAffect >= 0;
}
catch (Exception ex)
{
Log.Error($"fastest批量更新失败:{ex.Message}");
return false;
}
}
#endregion
}
}
创建applicationsettings.json配置文件,用于配置api接口的风格及分组显示等
{
"$schema": "https://gitee.com/dotnetchina/Furion/raw/net6/schemas/v3/furion-schema.json",
/*swagger文档描述配置*/
"SpecificationDocumentSettings": {
"DocumentTitle": "MyFurion | 规范化接口",
"DocExpansionState": "None", //文档展开方式
"GroupOpenApiInfos": [
{
"Group": "Default",
"Title": "MyFurion API接口",
"Description": "我的Furion",
"Version": "1.0.0",
"TermsOfService": "",
"Contact": {
"Name": "Furion",
"Url": "",
"Email": ""
},
"License": {
"Name": "Apache-2.0",
"Url": ""
}
}
]
},
/* controller 接口风格设置*/
"DynamicApiControllerSettings": {
"KeepName": true,
"KeepVerb": true,
"LowercaseRoute": false,
"AsLowerCamelCase": true,
"UrlParameterization": true,
"VerbToHttpMethods": [
//[ "getall", "HEAD" ], // => getall 会被复写为 `[HttpHead]`
//[ "other", "PUT" ] // => 新增一条新规则,比如,一 `[other]` 开头会转换为 `[HttpPut]` 请求
]
},
/*
跨域配置
PolicyName:跨域策略名,string 类型,必填,默认 App.Cors.Policy
WithOrigins:允许跨域的域名列表,string[] 类型,默认 *
WithHeaders:请求表头,没有配置则允许所有表头,string[] 类型
WithExposedHeaders:设置客户端可获取的响应标头,string[] 类型,默认 ["access-token", "x-access-token"]
WithMethods:设置跨域允许请求谓词,没有配置则允许所有,string[] 类型
AllowCredentials:是否允许跨域请求中的凭据,bool 类型,默认值 true
SetPreflightMaxAge:设置预检过期时间,int 类型,默认值 24小时
FixedClientToken:是否默认配置 WithExposedHeaders,bool 类型,默认 true
SignalRSupport:是否启用 SignalR 跨域支持,bool 类型,默认 false
*/
"CorsAccessorSettings": {
"SignalRSupport": true, //是否启用 SignalR 跨域支持,bool 类型,默认 false
//设置客户端可获取的响应标头,string[] 类型,默认 ["access-token", "x-access-token"]
"WithExposedHeaders": [ "access-token", "x-access-token", "environment", "Content-Disposition" ]
}
}
3.3 数据库IOC注册
在MyFurion.Start项目中,通过Nuget添加 AspNetCoreRateLimit、System.Linq.Dynamic.Core,同时添加对项目MyFurion.Application的引用
创建GlobalUsings类配置全局引用
global using System.Reflection;
global using System.Linq.Expressions;
global using Microsoft.Extensions.DependencyInjection;
global using Furion;
global using Furion.Logging;
global using SqlSugar;
global using SqlSugar.IOC;
global using System.Linq.Dynamic.Core;
global using MyFurion.Model;
global using MyFurion.Unility.Const;
创建SqlSugarSetup类,实现sqlsugar数据库IOC注册、CodeFirst、全局过滤器等功能的实现
namespace MyFurion.Start
{
/// <summary>
/// sqlsugarIOC注册
/// </summary>
public static class SqlSugarSetup
{
public static void AddSqlsugarSetup(IServiceCollection services)
{
List<IocConfig> iocConfigs = App.GetConfig<List<IocConfig>>("ConnectionConfigs");//获取数据库连接配置
SugarIocServices.AddSqlSugar(iocConfigs);
SugarIocServices.ConfigurationSugar(db =>
{
foreach (var iocItem in iocConfigs)
{
SqlSugarProvider dbClient = db.GetConnection(iocItem.ConfigId);
SetQueryFilter(dbClient);
dbClient.Aop.OnLogExecuting = (sql, pars) =>
{
Log.Information(SqlProfiler.ParameterFormat(sql, pars));
Console.WriteLine(SqlProfiler.ParameterFormat(sql, pars));
Console.WriteLine();
};
var dbtype = dbClient.CurrentConnectionConfig.DbType;
dbClient.CurrentConnectionConfig.ConfigureExternalServices = new ConfigureExternalServices()
{
//自定义类型多库兼容
EntityService = (c, p) =>
{
if (p.DataType == CommonConst.DB_STRING_MAX)
{
if (dbtype == DbType.MySql)
{
p.DataType = "longtext";
}
else if (dbtype == DbType.SqlServer)
{
p.DataType = "nvarchar(max)";
}
}
}
};
}
});
CreateTable(iocConfigs);
}
/// <summary>
/// 创建数据库表 codefirst
/// </summary>
private static void CreateTable(List<IocConfig> iocConfigs)
{
foreach (var item in iocConfigs)
{
string configId = item.ConfigId;
ISqlSugarClient db = DbScoped.SugarScope.GetConnectionScope(configId);
db.DbMaintenance.CreateDatabase();//没有数据库的时候创建数据库
var tableLists = db.DbMaintenance.GetTableInfoList();
var files = System.IO.Directory.GetFiles(AppDomain.CurrentDomain.BaseDirectory, "MyFurion.Model.dll");
if (files.Length > 0)
{
Type[] types = Assembly.LoadFrom(files[0]).GetTypes().Where(it => it.BaseType == typeof(BaseEntity)).ToArray();
//Type[] types = Assembly.LoadFrom(files[0]).GetTypes().ToArray();
foreach (var entityType in types)
{
//创建数据表
string tableName = entityType.GetCustomAttribute<SugarTable>().TableName.ToLower();//根据特性获取表名称
var configid = entityType.GetCustomAttribute<TenantAttribute>()?.configId;//根据特性获取租户id
configid = configid == null ? "0" : configid.ToString();
if (!tableLists.Any(p => p.Name == tableName) && configId == configid.ToString())
{
//创建数据表包括字段更新
db.CodeFirst.InitTables(entityType);
}
}
db.Close();
}
}
}
/// <summary>
/// 添加全局过滤器
/// </summary>
/// <param name="provider"></param>
private static void SetQueryFilter(SqlSugarProvider provider)
{
//添加全局过滤器
var files = Directory.GetFiles(AppDomain.CurrentDomain.BaseDirectory, "MyFurion.Model.dll");
if (files.Length > 0)
{
Type[] types = Assembly.LoadFrom(files[0]).GetTypes().Where(it => it.BaseType == typeof(BaseEntity)).ToArray();
foreach (var entityType in types)
{
//string tableName = entityType.GetCustomAttribute<SugarTable>().TableName;//根据特性获取表名称
var lambda = DynamicExpressionParser.ParseLambda( new[] { Expression.Parameter(entityType, "it") },typeof(bool), $"{nameof(BaseEntity.IsDeleted)} == @0",false);
provider.QueryFilter.Add(new TableFilterItem<object>(entityType, lambda, true)); //将Lambda传入过滤器
}
}
//插入/更新过滤器,用于审计日志
provider.Aop.DataExecuting = (oldValue, entityInfo) =>
{
if (entityInfo.OperationType == DataFilterType.InsertByObject)
{
//if (entityInfo.PropertyName == "CreatedUId")
//{
// entityInfo.SetValue(CurrentUserInfo.UId.ToString());//CreatedUId
//}
//if (entityInfo.PropertyName == "CreatedUName")
//{
// entityInfo.SetValue(CurrentUserInfo.Name);
//}
//if (entityInfo.PropertyName == "CreateOrgId")
//{
// entityInfo.SetValue(CurrentUserInfo.OrgId.ToString());
//}
//if (entityInfo.PropertyName == "CreateOrgName")
//{
// entityInfo.SetValue(CurrentUserInfo.OrgName.ToString());
//}
}
//update生效
if (entityInfo.OperationType == DataFilterType.UpdateByObject)
{
//if (entityInfo.PropertyName == "UpdatedTime")
//{
// entityInfo.SetValue(DateTimeOffset.Now);//修改UpdateTime字段
//}
//if (entityInfo.PropertyName == "UpdatedUId")
//{
// entityInfo.SetValue(CurrentUserInfo.UId.ToString());//修改UpdateTime字段
//}
//if (entityInfo.PropertyName == "UpdatedUName")
//{
// entityInfo.SetValue(CurrentUserInfo.Name);//修改UpdateTime字段
//}
}
};
}
}
}
3.4 Startup配置
在MyFurion.Start项目中创建Handlers文件夹,然后创建XnRestfulResultProvider类,自定义接口规范化输出数据格式
using Furion.DataValidation;
using Furion.FriendlyException;
using Furion.UnifyResult;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.Filters;
namespace MyFurion.Start
{
/// <summary>
/// 规范化RESTful风格返回值
/// </summary>
[UnifyModel(typeof(XnRestfulResult<>))]
public class XnRestfulResultProvider : IUnifyResultProvider
{
/// <summary>
/// 异常返回值
/// </summary>
/// <param name="context"></param>
/// <param name="metadata"></param>
/// <returns></returns>
public IActionResult OnException(ExceptionContext context, ExceptionMetadata metadata)
{
return new JsonResult(new XnRestfulResult<object>
{
Code = metadata.StatusCode,
Success = false,
Data = null,
Message = context.Exception.Message,// metadata.Errors,
//Extras = UnifyContext.Take(),
//Timestamp = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds()
});
}
/// <summary>
/// 成功返回值
/// </summary>
/// <param name="context"></param>
/// <param name="data"></param>
/// <returns></returns>
public IActionResult OnSucceeded(ActionExecutedContext context, object data)
{
return new JsonResult(new XnRestfulResult<object>
{
Code = StatusCodes.Status200OK,// context.Result is EmptyResult ? StatusCodes.Status204NoContent : StatusCodes.Status200OK, // 处理没有返回值情况 204
Success = true,
Data = data,
Message = "请求成功",
//Extras = UnifyContext.Take(),
//Timestamp = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds()
});
}
/// <summary>
/// 验证失败返回值
/// </summary>
/// <param name="context"></param>
/// <param name="metadata"></param>
/// <returns></returns>
public IActionResult OnValidateFailed(ActionExecutingContext context, ValidationMetadata metadata)
{
return new JsonResult(new XnRestfulResult<object>
{
Code = StatusCodes.Status400BadRequest,
Success = false,
Data = null,
Message = metadata.Message,
//Extras = UnifyContext.Take(),
//Timestamp = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds()
});
}
/// <summary>
/// 处理输出状态码
/// </summary>
/// <param name="context"></param>
/// <param name="statusCode"></param>
/// <param name="unifyResultSettings"></param>
/// <returns></returns>
public async Task OnResponseStatusCodes(HttpContext context, int statusCode, UnifyResultSettingsOptions unifyResultSettings)
{
// 设置响应状态码
UnifyContext.SetResponseStatusCodes(context, statusCode, unifyResultSettings);
switch (statusCode)
{
// 处理 401 状态码
case StatusCodes.Status401Unauthorized:
await context.Response.WriteAsJsonAsync(new XnRestfulResult<object>
{
Code = StatusCodes.Status401Unauthorized,
Success = false,
Data = null,
Message = "401 登录已过期,请重新登录",
//Extras = UnifyContext.Take(),
//Timestamp = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds()
}, App.GetOptions<JsonOptions>()?.JsonSerializerOptions);
break;
// 处理 403 状态码
case StatusCodes.Status403Forbidden:
await context.Response.WriteAsJsonAsync(new XnRestfulResult<object>
{
Code = StatusCodes.Status403Forbidden,
Success = false,
Data = null,
Message = "403 禁止访问,没有权限",
//Extras = UnifyContext.Take(),
//Timestamp = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds()
}, App.GetOptions<JsonOptions>()?.JsonSerializerOptions);
break;
default:
break;
}
}
}
/// <summary>
/// RESTful风格---XIAONUO返回格式
/// </summary>
/// <typeparam name="T"></typeparam>
public class XnRestfulResult<T>
{
/// <summary>
/// 执行成功
/// </summary>
public bool Success { get; set; }
/// <summary>
/// 状态码
/// </summary>
public int? Code { get; set; }
/// <summary>
/// 错误信息
/// </summary>
public virtual string Message { get; set; } = String.Empty;
/// <summary>
/// 数据
/// </summary>
public T? Data { get; set; }
/ <summary>
/ 附加数据
/ </summary>
//public object Extras { get; set; }
/ <summary>
/ 时间戳
/ </summary>
//public long Timestamp { get; set; }
}
}
在MyFurion.Start项目中创建Startup类,用于Service注册、日志、JSON序列化、Swagger等配置
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.HttpOverrides;
using Microsoft.AspNetCore.Server.Kestrel.Core;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;
namespace MyFurion.Start
{
/// <summary>
///
/// </summary>
public class Startup:AppStartup
{
/// <summary>
///
/// </summary>
/// <param name="services"></param>
public void ConfigureServices(IServiceCollection services)
{
services.AddSensitiveDetection();//注册脱敏词汇检测服务
services.AddControllers().AddNewtonsoftJson();//防止json数据类型转换失败
services.AddControllers().AddInjectWithUnifyResult<XnRestfulResultProvider>();//规范化输出设置
services.AddCorsAccessor();//配置跨域
//统一日期类型返回
services.AddControllersWithViews().AddNewtonsoftJson(options =>
{
options.SerializerSettings.DateFormatString = "yyyy-MM-dd HH:mm:ss";
});
services.Configure<KestrelServerOptions>(options =>
{
options.Limits.MaxRequestBodySize = int.MaxValue;
});
//设置日志
Array.ForEach(new[] { LogLevel.Information, LogLevel.Error }, logLevel =>
{
services.AddFileLogging("Logs/{1}-{0:yyyy}-{0:MM}-{0:dd}-{0:HH}.log", options =>
{
options.FileNameRule = fileName => string.Format(fileName, DateTime.UtcNow, logLevel.ToString());
options.WriteFilter = logMsg => logMsg.LogLevel == logLevel;
options.Append = true;
//options.MessageFormat = (logMsg) =>
//{
// var stringBuilder = new System.Text.StringBuilder();
// stringBuilder.Append(System.DateTime.Now.ToString("o"));
// // 其他的。。。自己组装
// return stringBuilder.ToString();
//};
});
});
SqlSugarSetup.AddSqlsugarSetup(services);
}
/// <summary>
///
/// </summary>
/// <param name="app"></param>
/// <param name="env"></param>
public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
{
if (env.IsDevelopment())
{
app.UseDeveloperExceptionPage();
}
// NGINX 反向代理获取真实IP
app.UseForwardedHeaders(new ForwardedHeadersOptions
{
ForwardedHeaders = ForwardedHeaders.XForwardedFor | ForwardedHeaders.XForwardedProto
});
app.UseUnifyResultStatusCodes();// 添加状态码拦截中间件 添加规范化结果状态码
app.UseHttpsRedirection();// 强制https
app.UseStaticFiles(); //启用静态文件
app.UseRouting();
app.UseCorsAccessor();//跨域中间件
//开启身份认证
//app.UseAuthentication();
//app.UseAuthorization();
app.UseInject("MyFurion");
app.UseEndpoints(endpoints =>
{
endpoints.MapControllers();
});
}
}
}
3.5 Swagger配置
在项目MyFurion.Model、MyFurion.Application、MyFurion.Start三个项目Debug及Release模式下设置api XML文件输出
以MyFurion.Model为配置示例
然后在Startup中的Configure中添加注册Inject
app.UseInject("MyFurion");//MyFurion swagger文档的路由前缀
配置项目默认启动页为Swagger
MyFurion.WebApi项目中,Properties/launchSettings.json配置文件中,将launchUrl修改为配置的Swagger路由地址
3.6启动配置
MyFurion.WebApi项目 删除Controllers文件夹及WeatherForecast文件,卸载Nuget中对Swagger的引用,添加对项目MyFurion.Start的引用
Program.cs中的代码改为
Serve.Run(RunOptions.Default);
appSettings.json配置文件内容改为
{
"$schema": "https://gitee.com/dotnetchina/Furion/raw/v4/schemas/v4/furion-schema.json",
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft": "Warning",
"Microsoft.Hosting.Lifetime": "Information",
"Microsoft.EntityFrameworkCore": "Information"
}
},
"AllowedHosts": "*",
/*数据库连接配置
ConnectionString:连接字符串
DbType:数据库类型 支持MySql = 0,SqlServer = 1,Sqlite = 2,Oracle = 3,PostgreSQL = 4,Dm = 5,Kdbndp = 6,Oscar = 7,MySqlConnector = 8,Access = 9,OpenGauss = 10,Custom = 900
ConfigId:租户id
IsAutoCloseConnection:自动释放和关闭数据库连接,如果有事务事务结束时关闭,否则每次操作后关闭
AllowLoadLocalInfile:大数据写入是 mysql数据配置必须
*/
"ConnectionConfigs": [
{
"ConnectionString": "Data Source=.;User ID=sa;Password=123456;Initial Catalog=MyFurionTest",
"DbType": 1,
"ConfigId": "0",
"IsAutoCloseConnection": true
}
],
"AppSettings": {
"InjectSpecificationDocument": true //如果不需要线上环境开启 Swagger 功能,则设置为false 修改时需要重新发布
}
}
4.项目使用示例展示
在Furion.Model中创建Org实体对象,用于验证CodeFirst功能
namespace MyFurion.Model
{
/// <summary>
/// 组织机构信息
/// </summary>
[SugarTable("Sys_Org")]
[Tenant(0)]
public class OrgInfo:BaseEntity
{
/// <summary>
/// 机构编码
/// </summary>
[SugarColumn(IsNullable =true,ColumnDescription ="机构编码")]
public string? OrgCode { get; set; }
/// <summary>
/// 机构名称
/// </summary>
[SugarColumn(IsNullable = true, ColumnDescription = "机构名称")]
public string? OrgName { get; set; }
}
}
在MyFurion.Application项目中创建OrgRepository类,用于实现业务代码的仓储类
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace MyFurion.Application
{
/// <summary>
/// 机构服务仓储
/// </summary>
public class OrgRepository:BaseRepository<OrgInfo>,ITransient
{
//TODO
}
}
在MyFurion.Application项目中,创建Controller文件夹,存放接口文件
创建FurionTestController
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace MyFurion.Application.Controller
{
/// <summary>
/// furionTest
/// </summary>
[ApiDescriptionSettings(Name = "FurionTest", Order = 1)]
[Route("api/furionTest")]
public class FurionTestController:IDynamicApiController
{
private readonly OrgRepository _orgRepository;
public FurionTestController(OrgRepository orgRepository)
{
_orgRepository = orgRepository;
}
/// <summary>
/// furionTestGet
/// </summary>
/// <returns></returns>
[HttpGet("furionHello")]
public string GetHello()
{
return "Hello Furion";
}
/// <summary>
/// post test
/// </summary>
/// <param name="data"></param>
/// <returns></returns>
[HttpPost("testPost")]
public string TestPost(TestPostData data)
{
return "Hello Post";
}
/// <summary>
/// 获取组织机构信息
/// </summary>
/// <returns></returns>
[HttpGet("getOrgList")]
public async Task<List<OrgInfo>> GetOrgList()
{
return await _orgRepository.GetAll();
}
}
public class TestPostData
{
public string? DataValue { get; set; }
public int TestTimes { get; set; }
}
}
数据库生成结果
项目启动页
最终项目架构