开发框架Furion之Winform+SqlSugar
分别创建项目名称为MyFurion.WFSqlsugar.Model（实体类库）、MyFurion.WFSqlsugar.Setup（service注册类库）、MyFurion.WFSqlsugar.Application（仓储业务类库）三个子项目。添加Furion.Extras.DatabaseAccessor.SqlSugar、System.Configuration.Configuratio
·
目录
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;
}
}
}3.源代码下载
WinformFurion: winform +Furion+SqlSugar框架，codefirst模式、包含日志、积累仓储的增删改查等
开放原子开发者工作坊旨在鼓励更多人参与开源活动，与志同道合的开发者们相互交流开发经验、分享开发心得、获取前沿技术趋势。工作坊有多种形式的开发者活动，如meetup、训练营等，主打技术交流，干货满满，真诚地邀请各位开发者共同参与！
更多推荐
3
0- 0
已为社区贡献8条内容
所有评论(0)