目錄
1.開發(fā)環(huán)境
2.項(xiàng)目搭建
2.1 創(chuàng)建WinFrom主項(xiàng)目
2.2 創(chuàng)建子項(xiàng)目
2.3 實(shí)體類庫(kù)基礎(chǔ)類信息配置
2.3.1 Nuget包及項(xiàng)目引用
2.3.2 實(shí)體基類創(chuàng)建??
2.4 倉(cāng)儲(chǔ)業(yè)務(wù)類庫(kù)基礎(chǔ)配置
2.4.1 Nuget包及項(xiàng)目引用
2.4.2 Dtos實(shí)體
2.4.3 倉(cāng)儲(chǔ)基類
?2.5 service注冊(cè)類庫(kù)基礎(chǔ)配置
2.5.1 config配置文件配置
2.5.2 Nuget包及項(xiàng)目引用
?2.5.4 SqlSugar數(shù)據(jù)庫(kù)配置
2.5.5?service服務(wù)注冊(cè)
2.6 主項(xiàng)目啟動(dòng)配置
?2.7 示例
2.7.1 codefirst 示例
?2.7.2倉(cāng)儲(chǔ)查詢調(diào)用示例
3.源代碼下載
1.開發(fā)環(huán)境
- Visual studio 2022
- SQLServer
- .Net6
關(guān)于SqlSugar參見(jiàn)教程配置實(shí)體 - SqlSugar 5x - .NET果糖網(wǎng)
2.項(xiàng)目搭建
2.1 創(chuàng)建WinFrom主項(xiàng)目
Visual Studio 2022—?jiǎng)?chuàng)建新項(xiàng)目—項(xiàng)目模板中選擇 Windows窗體應(yīng)用
?
至此?項(xiàng)目初次創(chuàng)建完成?
2.2 創(chuàng)建子項(xiàng)目
分別創(chuàng)建項(xiàng)目名稱為MyFurion.WFSqlsugar.Model（實(shí)體類庫(kù)）、MyFurion.WFSqlsugar.Setup（service注冊(cè)類庫(kù)）、MyFurion.WFSqlsugar.Application（倉(cāng)儲(chǔ)業(yè)務(wù)類庫(kù)）三個(gè)子項(xiàng)目
項(xiàng)目最終架構(gòu)
2.3 實(shí)體類庫(kù)基礎(chǔ)類信息配置
2.3.1 Nuget包及項(xiàng)目引用
在MyFurion.WFSqlsugar.Model項(xiàng)目中添加SqlSugarCore
2.3.2 實(shí)體基類創(chuàng)建??
創(chuàng)建基類BaseEntity.cs類
using SqlSugar;
namespace MyFurion.WFSqlsugar.Model
{
/// <summary>
/// 實(shí)體基類
/// </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>
/// 構(gòu)造函數(shù)
/// </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>
/// 創(chuàng)建人id
/// </summary>
[SugarColumn(IsOnlyIgnoreUpdate = true, IsNullable = true, ColumnDescription = "創(chuàng)建人id")]
public string ?CreateUserId { get; set; }
/// <summary>
/// 創(chuàng)建人姓名
/// </summary>
[SugarColumn(IsOnlyIgnoreUpdate = true, IsNullable = true, ColumnDescription = "創(chuàng)建人姓名")]
public string? CreateUser { get; set; }
/// <summary>
/// 創(chuàng)建時(shí)間
/// </summary>
[SugarColumn(IsOnlyIgnoreUpdate = true, IsNullable = false, ColumnDescription = "創(chuàng)建時(shí)間")]
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>
/// 修改時(shí)間
/// </summary>
[SugarColumn(IsOnlyIgnoreInsert =true, IsNullable = true, ColumnDescription = "修改時(shí)間")]
public DateTime? ModifyTime { get; set; }
/// <summary>
/// 創(chuàng)建組織機(jī)構(gòu)ID
/// </summary>
[SugarColumn(IsOnlyIgnoreUpdate = true, IsNullable = true, ColumnDescription = "創(chuàng)建組織機(jī)構(gòu)ID")]
public string? CreateOrgId { get; set; }
/// <summary>
/// 創(chuàng)建組織機(jī)構(gòu)名稱
/// </summary>
[SugarColumn(IsOnlyIgnoreUpdate = true, IsNullable = true, ColumnDescription = "創(chuàng)建組織機(jī)構(gòu)名稱")]
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>
/// 刪除時(shí)間
/// </summary>
[SugarColumn(IsOnlyIgnoreInsert = true, IsNullable = true, ColumnDescription = "刪除時(shí)間")]
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 倉(cāng)儲(chǔ)業(yè)務(wù)類庫(kù)基礎(chǔ)配置
2.4.1 Nuget包及項(xiàng)目引用
在MyFurion.WFSqlsugar.Application項(xiàng)目中添加Furion、SqlSugar.IOC
添加對(duì)MyFurion.WFSqlsugar.Model項(xiàng)目的引用
2.4.2 Dtos實(shí)體
項(xiàng)目中創(chuàng)建Dtos文件，用于存放查詢條件以及輸出信息數(shù)據(jù)類
首先創(chuàng)建PageResult.cs和PageInputBase.cs，用于倉(cāng)儲(chǔ)基類中使用
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace MyFurion.WFSqlsugar.Application.Dtos
{
/// <summary>
/// 分頁(yè)數(shù)據(jù)
/// </summary>
/// <typeparam name="T"></typeparam>
public class PageResult<T>
{
/// <summary>
/// 頁(yè)碼
/// </summary>
public int PageNo { get; set; }
/// <summary>
/// 分頁(yè)大小
/// </summary>
public int PageSize { get; set; }
/// <summary>
/// 頁(yè)總數(shù)
/// </summary>
public int TotalPage { get; set; }
/// <summary>
/// 數(shù)據(jù)總數(shù)
/// </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>
/// 當(dāng)前頁(yè)碼
/// </summary>
public int PageNo { get; set; } = 1;
/// <summary>
/// 頁(yè)碼容量
/// </summary>
public int PageSize { get; set; } = 20;
/// <summary>
/// 搜索開始時(shí)間
/// </summary>
public string? SearchBeginTime { get; set; }
/// <summary>
/// 搜索結(jié)束時(shí)間
/// </summary>
public string?SearchEndTime { get; set; }
}
}
2.4.3 倉(cāng)儲(chǔ)基類
創(chuàng)建倉(cāng)儲(chǔ)基類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;//多租戶事務(wù)
//private readonly ISqlSugarRepository repository;
public BaseRepository(ISqlSugarClient context = null) : base(context)
{
//通過(guò)特性拿到ConfigId
var configId = typeof(T).GetCustomAttribute<TenantAttribute>()?.configId;
if (configId != null)
{
Context = DbScoped.SugarScope.GetConnectionScope(configId);//根據(jù)類傳入的ConfigId自動(dòng)選擇
}
else
{
Context = context ?? DbScoped.SugarScope.GetConnectionScope(0);//沒(méi)有默認(rèn)db0
}
//Context = DbScoped.SugarScope.GetConnectionScopeWithAttr<T>();
itenant = DbScoped.SugarScope;//設(shè)置租戶接口
}
#region 基礎(chǔ)業(yè)務(wù)
/// <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>
/// 插入設(shè)置列數(shù)據(jù)
/// </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($"插入設(shè)置列數(shù)據(jù)失?。簕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>
/// 根據(jù)實(shí)體類更新指定列 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($"根據(jù)實(shí)體類更新指定列失敗：{ex.Message}");
return false;
}
}
/// <summary>
/// 根據(jù)實(shí)體類更新指定列 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($"根據(jù)實(shí)體類更新指定列失敗：{ex.Message}");
return false;
}
}
/// <summary>
/// 更新指定列 eg：Update(w => w.NoticeId == model.NoticeId, it => new SysNotice(){ UpdateTime = DateTime.Now, Title = "通知標(biāo)題" });
/// </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>
/// 事務(wù) 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($"事務(wù)執(zhí)行失?。簕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
{
//假刪除 實(shí)體屬性有isdelete或者isdeleted 請(qǐng)升級(jí)到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>
/// 根據(jù)查詢條件刪除
/// </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
{
//假刪除 實(shí)體屬性有isdelete或者isdeleted 請(qǐng)升級(jí)到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($"根據(jù)查詢條件刪除失?。簕ex.Message}");
return false;
}
}
/// <summary>
/// 根據(jù)id獲取數(shù)據(jù)
/// </summary>
/// <param name="id">主鍵值</param>
/// <returns>泛型實(shí)體</returns>
public async Task<T> GetEntityById(long id)
{
return await Context.Queryable<T>().FirstAsync(p => p.Id == id);
}
/// <summary>
/// 數(shù)據(jù)是否存在
/// </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>
/// 獲取所有數(shù)據(jù)
/// </summary>
/// <returns></returns>
public async Task<List<T>> GetAll()
{
return await Context.Queryable<T>().ToListAsync();
}
/// <summary>
/// 根據(jù)查詢條件獲取數(shù)據(jù)
/// </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>
/// 根據(jù)查詢條件獲取數(shù)據(jù)(動(dòng)態(tài)表格拼接查詢條件)
/// </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>
/// 根據(jù)查詢條件獲取數(shù)據(jù)
/// </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>
/// 獲取分頁(yè)數(shù)據(jù)
/// </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>
/// 獲取分頁(yè)數(shù)據(jù)
/// </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>
/// 獲取分頁(yè)數(shù)據(jù)
/// </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>
/// 獲取分頁(yè)數(shù)據(jù)
/// </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>
/// 獲取分頁(yè)數(shù)據(jù)
/// </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 海量業(yè)務(wù)高性能
/// <summary>
/// 新增（對(duì)于海量數(shù)據(jù)并且性能要高的）
/// </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>
/// 批量新增（對(duì)于海量數(shù)據(jù)并且性能要高的）
/// </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>
/// 更新（對(duì)于海量數(shù)據(jù)并且性能要高的）
/// </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>
/// 批量更新（對(duì)于海量數(shù)據(jù)并且性能要高的）
/// </summary>
/// <param name="t"></param>
/// <returns></returns>
public async Task<bool> BatchBulkUpdate(List<T> t)
{
try
{
Context.QueryFilter = new QueryFilterProvider();//清空過(guò)濾器 否則會(huì)出現(xiàn)Parameter '@IsDelete0' must be defined錯(cuò)誤
int rowsAffect = await Context.Storageable(t).ToStorage().BulkUpdateAsync();
return rowsAffect >= 0;
}
catch (Exception ex)
{
Log.Error($"更新失?。簕ex.Message}");
return false;
}
}
/// <summary>
/// 批量更新（對(duì)于海量數(shù)據(jù)并且性能要高的）
/// </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();//清空過(guò)濾器 否則會(huì)出現(xiàn)Parameter '@IsDelete0' must be defined錯(cuò)誤
int rowsAffect = await Context.Storageable(t).ToStorage().BulkUpdateAsync(updateColumns);
return rowsAffect >= 0;
}
catch (Exception ex)
{
Log.Error($"更新失?。簕ex.Message}");
return false;
}
}
#endregion
#region 存儲(chǔ)過(guò)程
/// <summary>
/// 存儲(chǔ)過(guò)程
/// </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>
/// 存儲(chǔ)過(guò)程
/// </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();//清空過(guò)濾器 否則會(huì)出現(xiàn)Parameter '@IsDelete0' must be defined錯(cuò)誤
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注冊(cè)類庫(kù)基礎(chǔ)配置
2.5.1 config配置文件配置
?首先在主項(xiàng)目MyFurion.WFSqlSugar中新增App.config配置文件，并配置數(shù)據(jù)庫(kù)連接配置
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<!--數(shù)據(jù)庫(kù)連接-->
<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包及項(xiàng)目引用
在項(xiàng)目MyFurion.WFSqlsugar.Setup中添加對(duì)項(xiàng)目MyFurion.WFSqlsugar.Application的引用
添加Furion.Extras.DatabaseAccessor.SqlSugar、System.Configuration.ConfigurationManager、System.Linq.Dynamic.Core
?2.5.4 SqlSugar數(shù)據(jù)庫(kù)配置
?創(chuàng)建SqlSugarSet.cs文件，用于對(duì)SqlSugar數(shù)據(jù)庫(kù)連接的配置數(shù)據(jù)
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>
/// 數(shù)據(jù)庫(kù)配置
/// </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執(zhí)行語(yǔ)句日志
dbClient.Aop.OnLogExecuting = (sql, pars) =>
{
try
{
Log.Information(SqlProfiler.ParameterFormat(sql, pars));
}
catch (Exception ex)
{
Log.Error($"日志錯(cuò)誤{ex.StackTrace}");
}
};
}
});
if (isCodefirst == "1")
{
CreateTable(iocConfigs);
}
}
/// <summary>
/// 創(chuàng)建數(shù)據(jù)庫(kù)表 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();//沒(méi)有數(shù)據(jù)庫(kù)的時(shí)候創(chuàng)建數(shù)據(jù)庫(kù)
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)
{
//創(chuàng)建數(shù)據(jù)表
string tableName = entityType.GetCustomAttribute<SugarTable>().TableName.ToLower();//根據(jù)特性獲取表名稱
var configid = entityType.GetCustomAttribute<TenantAttribute>()?.configId;//根據(jù)特性獲取租戶id
configid = configid == null ? "0" : configid.ToString();
if (!tableLists.Any(p => p.Name == tableName) && configId == configid.ToString())
{
//創(chuàng)建數(shù)據(jù)表包括字段更新
db.CodeFirst.InitTables(entityType);
}
}
db.Close();
}
}
}
/// <summary>
/// 添加全局過(guò)濾器
/// </summary>
/// <param name="provider"></param>
private static void SetQueryFilter(SqlSugarProvider provider)
{
//添加全局過(guò)濾器
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;//根據(jù)特性獲取表名稱
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傳入過(guò)濾器
}
}
//插入/更新過(guò)濾器,用于審計(jì)日志
provider.Aop.DataExecuting = (oldValue, entityInfo) =>
{
//新增時(shí)操作
if (entityInfo.OperationType == DataFilterType.InsertByObject)
{
}
//修改時(shí)操作
if (entityInfo.OperationType == DataFilterType.UpdateByObject)
{
if (entityInfo.PropertyName == "ModifyTime")
{
entityInfo.SetValue(DateTimeOffset.Now);//修改UpdateTime字段
}
}
};
}
}
}2.5.5?service服務(wù)注冊(cè)
創(chuàng)建YourStartup.cs，用于Furion框架的相關(guān)service的服務(wù)注冊(cè)
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();//使用數(shù)據(jù)庫(kù)配置
services.AddLogging();//日志服務(wù)
//設(shè)置日志
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 主項(xiàng)目啟動(dòng)配置
在項(xiàng)目MyFurion.WFSqlSugar添加對(duì)項(xiàng)目MyFurion.WFSqlsugar.Setup的引用
Program.cs中添加代碼
Serve.RunNative(includeWeb: false);//furion框架配置，默認(rèn)啟動(dòng)
?2.7 示例
2.7.1 codefirst 示例
創(chuàng)建SystemUser.cs實(shí)體類，配置相關(guān)表名及租戶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>
/// 賬號(hào)
/// </summary>
[SugarColumn(Length = 50, ColumnDescription = "賬號(hào)")]
public string Account { get; set; }
/// <summary>
/// 密碼（默認(rèn)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; }
}
}
啟動(dòng)項(xiàng)目，自動(dòng)創(chuàng)建數(shù)據(jù)表，效果展示
?2.7.2倉(cāng)儲(chǔ)查詢調(diào)用示例
用戶倉(cāng)儲(chǔ)
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>
/// 用戶倉(cāng)儲(chǔ)業(yè)務(wù)
/// </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>
/// 分頁(yè)數(shù)據(jù)
/// </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頁(yè)面調(diào)用查詢
文章來(lái)源：http://www.zghlxwxcb.cn/news/detail-425568.html
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模式、包含日志、積累倉(cāng)儲(chǔ)的增刪改查等文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-425568.html
到了這里，關(guān)于開發(fā)框架Furion之Winform+SqlSugar的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
