发布：2023/5/29 23:32:48作者：管理员 来源：本站 浏览次数：1166
1.开发环境
2.项目搭建
2.1 创建WinFrom主项目文章来源站点https://www.yii666.com/
2.2 创建子项目
2.3 实体类库基础类信息配置
2.3.1 Nuget包及项目引用
2.3.2 实体基类创建
2.4 仓储业务类库基础配置
2.4.1 Nuget包及项目引用
2.4.2 Dtos实体
2.4.3 仓储基类
2.5 service注册类库基础配置
2.5.1 config配置文件配置
2.5.2 Nuget包及项目引用
2.5.4 SqlSugar数据库配置
2.5.5 service服务注册
2.6 主项目启动配置
2.7 示例
2.7.1 codefirst 示例
2.7.2仓储查询调用示例
1.开发环境
Visual studio 2022
SQLServer
.Net6
关于SqlSugar参见教程配置实体 - SqlSugar 5x - .NET果糖网
2.项目搭建
2.1 创建WinFrom主项目
Visual Studio 2022—创建新项目—项目模板中选择 Windows窗体应用
至此 项目初次创建完成
2.2 创建子项目
分别创建项目名称为MyFurion.WFSqlsugar.Model（实体类库）、MyFurion.WFSqlsugar.Setup（service注册类库）、MyFurion.WFSqlsugar.Application（仓储业务类库）三个子项目
项目最终架构
2.3 实体类库基础类信息配置
2.3.1 Nuget包及项目引用
在MyFurion.WFSqlsugar.Model项目中添加SqlSugarCore
2.3.2 实体基类创建
创建基类BaseEntity.cs类
using SqlSugar;
namespace MyFurion.WFSqlsugar.Model
{
/// <summary>
/// 实体基类
/// </summary>
[SugarIndex("index_{table}_id", nameof(Id), OrderByType.Asc, IsUnique = true)]
[SugarIndex("index_{table}_createdate", nameof(CreateTime), OrderByType.Asc)]
[SugarIndex("index_{table}_sort", nameof(SortNum), OrderByType.Asc)]
[SugarIndex("index_{table}_del", nameof(IsDeleted), OrderByType.Asc)]
[SugarIndex("index_{table}_orgid", nameof(CreateOrgId), OrderByType.Asc)]
public class BaseEntity
{
/// <summary>
/// 构造函数
/// </summary>
public BaseEntity()
{
Id = SnowFlakeSingle.Instance.NextId();
CreateTime = DateTime.Now;
IsDeleted = true;
}
/// <summary>
/// id
/// </summary>
[SugarColumn(IsPrimaryKey =true, ColumnDescription ="主键")]
public long Id { get; set; }
/// <summary>
/// 创建人id
/// </summary>
[SugarColumn(IsOnlyIgnoreUpdate = true, IsNullable = true, ColumnDescription = "创建人id")]
public string ?CreateUserId { get; set; }
/// <summary>
/// 创建人姓名
/// </summary>
[SugarColumn(IsOnlyIgnoreUpdate = true, IsNullable = true, ColumnDescription = "创建人姓名")]
public string? CreateUser { get; set; }
/// <summary>
/// 创建时间
/// </summary>
[SugarColumn(IsOnlyIgnoreUpdate = true, IsNullable = false, ColumnDescription = "创建时间")]
public DateTime CreateTime { get; set; }
/// <summary>
/// 修改人id
/// </summary>
[SugarColumn(IsOnlyIgnoreInsert = true, IsNullable = true, ColumnDescription = "修改人id")]
public string? ModifyUserId { get; set; }
/// <summary>
/// 修改人姓名
/// </summary>
[SugarColumn(IsOnlyIgnoreInsert = true, IsNullable = true, ColumnDescription = "修改人姓名")]
public string? ModifyUser { get; set; }
/// <summary>
/// 修改时间
/// </summary>
[SugarColumn(IsOnlyIgnoreInsert =true, IsNullable = true, ColumnDescription = "修改时间")]
public DateTime? ModifyTime { get; set; }
/// <summary>
/// 创建组织机构ID
/// </summary>
[SugarColumn(IsOnlyIgnoreUpdate = true, IsNullable = true, ColumnDescription = "创建组织机构ID")]
public string? CreateOrgId { get; set; }
/// <summary>
/// 创建组织机构名称
/// </summary>
[SugarColumn(IsOnlyIgnoreUpdate = true, IsNullable = true, ColumnDescription = "创建组织机构名称")]
public string? CreateOrgName { get; set; }
/// <summary>
/// 排序
/// </summary>
[SugarColumn(IsNullable = false, ColumnDescription = "排序")]
public int SortNum { get; set; }
/// <summary>
/// 备注
/// </summary>
[SugarColumn(IsNullable = true, ColumnDescription = "备注",ColumnDataType ="nvarchar(max)")]
public string? Remark { get; set; }
/// <summary>
/// 是否删除
/// </summary>
[SugarColumn(ColumnDescription = "是否删除")]
public bool IsDeleted { get; set; }
/// <summary>
/// 删除原因
/// </summary>
[SugarColumn(ColumnDescription = "删除原因", IsNullable = true)]
public string? DeleteReason { get; set; }
/// <summary>
/// 删除时间
/// </summary>
[SugarColumn(IsOnlyIgnoreInsert = true, IsNullable = true, ColumnDescription = "删除时间")]
public DateTime? DeleteTime { get; set; }
/// <summary>
/// 删除人
/// </summary>
[SugarColumn(ColumnDescription = "删除人", IsNullable = true)]
public string? DeleteUser { get; set; }
/// <summary>
/// 删除单位
/// </summary>
[SugarColumn(ColumnDescription = "删除单位", IsNullable = true)]
public string? DeleteOrg { get; set; }
/// <summary>
/// 多租户ID
/// </summary>
[SugarColumn(ColumnDescription = "多租户ID", DefaultValue = "0")]
public long TenantId { get; set; } = 0;
}
}
2.4 仓储业务类库基础配置
2.4.1 Nuget包及项目引用
在MyFurion.WFSqlsugar.Application项目中添加Furion、SqlSugar.IOC
添加对MyFurion.WFSqlsugar.Model项目的引用
2.4.2 Dtos实体
项目中创建Dtos文件，用于存放查询条件以及输出信息数据类
首先创建PageResult.cs和PageInputBase.cs，用于仓储基类中使用
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace MyFurion.WFSqlsugar.Application.Dtos
{
/// <summary>
/// 分页数据
/// </summary>
/// <typeparam name="T"></typeparam>
public class PageResult<T>
{
/// <summary>
/// 页码
/// </summary>
public int PageNo { get; set; }
/// <summary>
/// 分页大小
/// </summary>
public int PageSize { get; set; }
/// <summary>
/// 页总数
/// </summary>
public int TotalPage { get; set; }
/// <summary>
/// 数据总数
/// </summary>
public int TotalRows { get; set; }
/// <summary>
/// 记录集合
/// </summary>
public List<T> Rows { get; set; } = new();
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace MyFurion.WFSqlsugar.Application.Dtos
{
public class PageInputBase
{
/// <summary>
/// 搜索值
/// </summary>
public string? SearchValue { get; set; }
/// <summary>
/// 当前页码
/// </summary>
public int PageNo { get; set; } = 1;
/// <summary>
/// 页码容量
/// </summary>
public int PageSize { get; set; } = 20;
/// <summary>
/// 搜索开始时间
/// </summary>
public string? SearchBeginTime { get; set; }
/// <summary>
/// 搜索结束时间
/// </summary>
public string?SearchEndTime { get; set; }
}
}
2.4.3 仓储基类
创建仓储基类BaseRepository.cs
using System.Reflection;
using System.Linq.Expressions;
using System.Data;
using MyFurion.WFSqlsugar.Model;
using MyFurion.WFSqlsugar.Application.Dtos;
using SqlSugar;
using SqlSugar.IOC;
using Furion.Logging;
namespace MyFurion.WFSqlsugar.Application
{
public class BaseRepository<T> : SimpleClient<T> where T : BaseEntity, new()
{
public ITenant itenant = null;//多租户事务
//private readonly ISqlSugarRepository repository;
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.Rows = result;
pageResult.TotalRows = 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.Rows = result;
pageResult.TotalRows = 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.Rows = result;
pageResult.TotalRows = 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.Rows = result;
pageResult.TotalRows = 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.Rows = result;
pageResult.TotalRows = 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<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
}
}
2.5 service注册类库基础配置
2.5.1 config配置文件配置
首先在主项目MyFurion.WFSqlSugar中新增App.config配置文件，并配置数据库连接配置
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<!--数据库连接-->
<add key="SqlserverConn" value="Data Source=.;User ID=sa;Password=123456;Initial Catalog=MyFurionTest"/>
<!--是否需要codeFirst模式-->
<add key="IsCodeFirst" value="1"/>
</appSettings>
</configuration>
2.5.2 Nuget包及项目引用
在项目MyFurion.WFSqlsugar.Setup中添加对项目MyFurion.WFSqlsugar.Application的引用
添加Furion.Extras.DatabaseAccessor.SqlSugar、System.Configuration.ConfigurationManager、System.Linq.Dynamic.Core
2.5.4 SqlSugar数据库配置
创建SqlSugarSet.cs文件，用于对SqlSugar数据库连接的配置数据
using System.Linq.Expressions;
using System.Linq.Dynamic.Core;
using System.Reflection;
using MyFurion.WFSqlsugar.Model;
using SqlSugar;
using SqlSugar.IOC;
using Furion.Logging;
namespace MyFurion.WFSqlsugar.Setup
{
/// <summary>
/// 数据库配置
/// </summary>
public class SqlSugarSet
{
public static void AddSqlsugarSetup()
{
string sqlserverConn = System.Configuration.ConfigurationManager.AppSettings["SqlserverConn"].ToString();
string isCodefirst= System.Configuration.ConfigurationManager.AppSettings["IsCodeFirst"].ToString();
//string orcaleConn = System.Configuration.ConfigurationManager.AppSettings["OrcaleConn"].ToString();
List<IocConfig> iocConfigs = new List<IocConfig>()
{
new IocConfig (){ ConnectionString=sqlserverConn,ConfigId=0,IsAutoCloseConnection=true,DbType=IocDbType.SqlServer},
//new IocConfig (){ ConnectionString=orcaleConn,ConfigId=1,IsAutoCloseConnection=true,DbType=IocDbType.Oracle}
};
SugarIocServices.AddSqlSugar(iocConfigs);
SugarIocServices.ConfigurationSugar(db =>
{
foreach (var iocItem in iocConfigs)
{
SqlSugarProvider dbClient = db.GetConnection(iocItem.ConfigId);
SetQueryFilter(dbClient);
//sql执行语句日志
dbClient.Aop.OnLogExecuting = (sql, pars) =>
{
try
{
Log.Information(SqlProfiler.ParameterFormat(sql, pars));
}
catch (Exception ex)
{
Log.Error($"日志错误{ex.StackTrace}");
}
};
}
});
if (isCodefirst == "1")
{
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.WFSqlsugar.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 = System.IO.Directory.GetFiles(AppDomain.CurrentDomain.BaseDirectory, "MyFurion.WFSqlsugar.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.OperationType == DataFilterType.UpdateByObject)
{
if (entityInfo.PropertyName == "ModifyTime")
{
entityInfo.SetValue(DateTimeOffset.Now);//修改UpdateTime字段
}
}
};
}
}
}
2.5.5 service服务注册
创建YourStartup.cs，用于Furion框架的相关service的服务注册
using Furion;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
namespace MyFurion.WFSqlsugar.Setup
{
public class YourStartup : AppStartup
{
public void ConfigureServices(IServiceCollection services)
{
//services.AddRemoteRequest();
services.AddDatabaseAccessor();//使用数据库配置
services.AddLogging();//日志服务
//设置日志
System.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, System.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();
//};
});
});
SqlSugarSet.AddSqlsugarSetup();
}
}
}
2.6 主项目启动配置
在项目MyFurion.WFSqlSugar添加对项目MyFurion.WFSqlsugar.Setup的引用
Program.cs中添加代码
Serve.RunNative(includeWeb: false);//furion框架配置，默认启动
2.7 示例
2.7.1 codefirst 示例
创建SystemUser.cs实体类，配置相关表名及租户id
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using SqlSugar;
namespace MyFurion.WFSqlsugar.Model
{
[SugarTable("Sys_User")]
[Tenant("0")]
public class SystemUser:BaseEntity
{
/// <summary>
/// 账号
/// </summary>
[SugarColumn(Length = 50, ColumnDescription = "账号")]
public string Account { get; set; }
/// <summary>
/// 密码（默认MD5加密）
/// </summary>
[SugarColumn(Length = 50, ColumnDescription = "密码")]
public string Password { get; set; }
/// <summary>
/// 昵称
/// </summary>
[SugarColumn(Length = 20, IsNullable = true, ColumnDescription = "昵称")]
public string NickName { get; set; }
/// <summary>
/// 姓名
/// </summary>
[SugarColumn(Length = 20, IsNullable = true, ColumnDescription = "姓名")]
public string UserName { get; set; }
}
}
启动项目，自动创建数据表，效果展示
2.7.2仓储查询调用示例
用户仓储
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Furion.DependencyInjection;
using MyFurion.WFSqlsugar.Model;
using MyFurion.WFSqlsugar.Application.Dtos;
using SqlSugar;
using System.Linq.Expressions;
namespace MyFurion.WFSqlsugar.Application
{
/// <summary>
/// 用户仓储业务
/// </summary>
public class UserRepository : BaseRepository<SystemUser>, ITransient
{
/// <summary>
/// 账户是否已存在
/// </summary>
/// <param name="account"></param>
/// <param name="id"></param>
/// <returns></returns>
public async Task<bool> IsExists(string account, long id)
{
return await Context.Queryable<SystemUser>().AnyAsync(x => x.Account == account && x.Id != id);
}
/// <summary>
/// 分页数据
/// </summary>
/// <param name="input"></param>
/// <returns></returns>
public async Task<PageResult<SystemUser>> GetPageList(PageInputBase input)
{
var exp = Expressionable.Create<SystemUser>();
exp.AndIF(!string.IsNullOrWhiteSpace(input.SearchValue), it => it.UserName.Contains(input.SearchValue)||it.Account.Contains(input.SearchValue));
Expression<Func<SystemUser, dynamic>> sortPredicate = x => x.CreateTime;
return await GetPageListAsync(exp.ToExpression(),input.PageNo,input.PageSize, sortPredicate);
}
}
}
form页面调用查询
using Furion;
using MyFurion.WFSqlsugar.Application;
using MyFurion.WFSqlsugar.Application.Dtos;
using MyFurion.WFSqlsugar.Model;
namespace MyFurion.WFSqlSugar
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void btnSearch_Click(object sender, EventArgs e)
{
UserRepository userRepository= App.GetService<UserRepository>();
var data = userRepository.GetPageList(new PageInputBase() { PageNo=1,PageSize=20}).Result;
}
}
}
© Copyright 2014 - 2024 柏港建站平台 ejk5.com. 渝ICP备16000791号-4