数据库操作
大约 7 分钟
数据库操作
本项目数据库采用 ORM Sqlsugar，数据仓储模式，对其简单的进行了封装操作
数据仓储
BaseRepository.cs文件
查看代码
using Infrastructure;
using Infrastructure.Extensions;
using Mapster;
using SqlSugar;
using SqlSugar.IOC;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq.Expressions;
using System.Reflection;
using ZR.Model;
namespace ZR.Repository
{
/// <summary>
///
/// </summary>
/// <typeparam name="T"></typeparam>
public class BaseRepository<T> : SimpleClient<T> where T : class, 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(1);//没有默认db0
}
//Context = DbScoped.SugarScope.GetConnectionScopeWithAttr<T>();
itenant = DbScoped.SugarScope;//设置租户接口
}
#region add
/// <summary>
/// 插入实体
/// </summary>
/// <param name="t"></param>
/// <returns></returns>
public int Add(T t, bool ignoreNull = true)
{
return Context.Insertable(t).IgnoreColumns(ignoreNullColumn: ignoreNull).ExecuteCommand();
}
public int Insert(List<T> t)
{
return Context.Insertable(t).ExecuteCommand();
}
public int Insert(T parm, Expression<Func<T, object>> iClumns = null, bool ignoreNull = true)
{
return Context.Insertable(parm).InsertColumns(iClumns).IgnoreColumns(ignoreNullColumn: ignoreNull).ExecuteCommand();
}
public IInsertable<T> Insertable(T t)
{
return Context.Insertable<T>(t);
}
#endregion add
#region update
public IUpdateable<T> Updateable(T entity)
{
return Context.Updateable(entity);
}
/// <summary>
/// 实体根据主键更新
/// </summary>
/// <param name="entity"></param>
/// <param name="ignoreNullColumns"></param>
/// <returns></returns>
public int Update(T entity, bool ignoreNullColumns = false)
{
return Context.Updateable(entity).IgnoreColumns(ignoreNullColumns).ExecuteCommand();
}
/// <summary>
/// 实体根据主键更新指定字段
/// return Update(user, t => new { t.NickName, }, true);
/// </summary>
/// <param name="entity"></param>
/// <param name="expression"></param>
/// <param name="ignoreAllNull"></param>
/// <returns></returns>
public int Update(T entity, Expression<Func<T, object>> expression, bool ignoreAllNull = false)
{
return Context.Updateable(entity).UpdateColumns(expression).IgnoreColumns(ignoreAllNull).ExecuteCommand();
}
/// <summary>
/// 根据指定条件更新指定列 eg：Update(new SysUser(){ }, it => new { it.Status }, f => f.Userid == 1));
/// 只更新Status列，条件是包含
/// </summary>
/// <param name="entity"></param>
/// <param name="expression"></param>
/// <param name="where"></param>
/// <returns></returns>
public int Update(T entity, Expression<Func<T, object>> expression, Expression<Func<T, bool>> where)
{
return Context.Updateable(entity).UpdateColumns(expression).Where(where).ExecuteCommand();
}
public int Update(SqlSugarClient client, T entity, Expression<Func<T, object>> expression, Expression<Func<T, bool>> where)
{
return client.Updateable(entity).UpdateColumns(expression).Where(where).ExecuteCommand();
}
/// <summary>
///
/// </summary>
/// <param name="entity"></param>
/// <param name="list"></param>
/// <param name="isNull">默认为true</param>
/// <returns></returns>
public int Update(T entity, List<string> list = null, bool isNull = true)
{
if (list == null)
{
list = new List<string>()
{
"Create_By",
"Create_time"
};
}
return Context.Updateable(entity).IgnoreColumns(isNull).IgnoreColumns(list.ToArray()).ExecuteCommand();
}
/// <summary>
/// 更新指定列 eg：Update(w => w.NoticeId == model.NoticeId, it => new SysNotice(){ Update_time = DateTime.Now, Title = "通知标题" });
/// </summary>
/// <param name="where"></param>
/// <param name="columns"></param>
/// <returns></returns>
public int Update(Expression<Func<T, bool>> where, Expression<Func<T, T>> columns)
{
return Context.Updateable<T>().SetColumns(columns).Where(where).RemoveDataCache().ExecuteCommand();
}
#endregion update
public DbResult<bool> UseTran(Action action)
{
try
{
var result = Context.Ado.UseTran(() => action());
return result;
}
catch (Exception ex)
{
Context.Ado.RollbackTran();
Console.WriteLine(ex.Message);
throw;
}
}
public IStorageable<T> Storageable(T t)
{
return Context.Storageable<T>(t);
}
public IStorageable<T> Storageable(List<T> t)
{
return Context.Storageable(t);
}
/// <summary>
///
/// </summary>
/// <param name="client"></param>
/// <param name="action">增删改查方法</param>
/// <returns></returns>
public DbResult<bool> UseTran(SqlSugarClient client, Action action)
{
try
{
var result = client.AsTenant().UseTran(() => action());
return result;
}
catch (Exception ex)
{
client.AsTenant().RollbackTran();
Console.WriteLine(ex.Message);
throw;
}
}
public bool UseTran2(Action action)
{
var result = Context.Ado.UseTran(() => action());
return result.IsSuccess;
}
#region delete
public IDeleteable<T> Deleteable()
{
return Context.Deleteable<T>();
}
/// <summary>
/// 批量删除
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public int Delete(object[] obj, string title = "")
{
return Context.Deleteable<T>().In(obj).EnableDiffLogEventIF(title.IsNotEmpty(), title).ExecuteCommand();
}
public int Delete(object id, string title = "")
{
return Context.Deleteable<T>(id).EnableDiffLogEventIF(title.IsNotEmpty(), title).ExecuteCommand();
}
public int DeleteTable()
{
return Context.Deleteable<T>().ExecuteCommand();
}
public bool Truncate()
{
return Context.DbMaintenance.TruncateTable<T>();
}
#endregion delete
#region query
public bool Any(Expression<Func<T, bool>> expression)
{
return Context.Queryable<T>().Where(expression).Any();
}
public ISugarQueryable<T> Queryable()
{
return Context.Queryable<T>();
}
public (List<T>, int) QueryableToPage(Expression<Func<T, bool>> expression, int pageIndex = 0, int pageSize = 10)
{
int totalNumber = 0;
var list = Context.Queryable<T>().Where(expression).ToPageList(pageIndex, pageSize, ref totalNumber);
return (list, totalNumber);
}
public (List<T>, int) QueryableToPage(Expression<Func<T, bool>> expression, string order, int pageIndex = 0, int pageSize = 10)
{
int totalNumber = 0;
var list = Context.Queryable<T>().Where(expression).OrderBy(order).ToPageList(pageIndex, pageSize, ref totalNumber);
return (list, totalNumber);
}
public (List<T>, int) QueryableToPage(Expression<Func<T, bool>> expression, Expression<Func<T, object>> orderFiled, string orderBy, int pageIndex = 0, int pageSize = 10)
{
int totalNumber = 0;
if (orderBy.Equals("DESC", StringComparison.OrdinalIgnoreCase))
{
var list = Context.Queryable<T>().Where(expression).OrderBy(orderFiled, OrderByType.Desc).ToPageList(pageIndex, pageSize, ref totalNumber);
return (list, totalNumber);
}
else
{
var list = Context.Queryable<T>().Where(expression).OrderBy(orderFiled, OrderByType.Asc).ToPageList(pageIndex, pageSize, ref totalNumber);
return (list, totalNumber);
}
}
public List<T> SqlQueryToList(string sql, object obj = null)
{
return Context.Ado.SqlQuery<T>(sql, obj);
}
/// <summary>
/// 根据主值查询单条数据
/// </summary>
/// <param name="pkValue">主键值</param>
/// <returns>泛型实体</returns>
public T GetId(object pkValue)
{
return Context.Queryable<T>().InSingle(pkValue);
}
/// <summary>
/// 根据条件查询分页数据
/// </summary>
/// <param name="where"></param>
/// <param name="parm"></param>
/// <returns></returns>
public PagedInfo<T> GetPages(Expression<Func<T, bool>> where, PagerInfo parm)
{
var source = Context.Queryable<T>().Where(where);
return source.ToPage(parm);
}
public PagedInfo<T> GetPages(Expression<Func<T, bool>> where, PagerInfo parm, Expression<Func<T, object>> order, OrderByType orderEnum = OrderByType.Asc)
{
var source = Context.Queryable<T>().Where(where).OrderByIF(orderEnum == OrderByType.Asc, order, OrderByType.Asc).OrderByIF(orderEnum == OrderByType.Desc, order, OrderByType.Desc);
return source.ToPage(parm);
}
public PagedInfo<T> GetPages(Expression<Func<T, bool>> where, PagerInfo parm, Expression<Func<T, object>> order, string orderByType)
{
return GetPages(where, parm, order, orderByType == "desc" ? OrderByType.Desc : OrderByType.Asc);
}
/// <summary>
/// 查询所有数据(无分页,请慎用)
/// </summary>
/// <returns></returns>
public List<T> GetAll(bool useCache = false, int cacheSecond = 3600)
{
return Context.Queryable<T>().WithCacheIF(useCache, cacheSecond).ToList();
}
#endregion query
/// <summary>
/// 此方法不带output返回值
/// var list = new List<SugarParameter>();
/// list.Add(new SugarParameter(ParaName, ParaValue)); input
/// </summary>
/// <param name="procedureName"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public DataTable UseStoredProcedureToDataTable(string procedureName, List<SugarParameter> parameters)
{
return Context.Ado.UseStoredProcedure().GetDataTable(procedureName, parameters);
}
/// <summary>
/// 带output返回值
/// var list = new List<SugarParameter>();
/// list.Add(new SugarParameter(ParaName, ParaValue, true)); output
/// list.Add(new SugarParameter(ParaName, ParaValue)); input
/// </summary>
/// <param name="procedureName"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public (DataTable, List<SugarParameter>) UseStoredProcedureToTuple(string procedureName, List<SugarParameter> parameters)
{
var result = (Context.Ado.UseStoredProcedure().GetDataTable(procedureName, parameters), parameters);
return result;
}
}
/// <summary>
/// 分页查询扩展
/// </summary>
public static class QueryableExtension
{
/// <summary>
/// 读取列表
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="source">查询表单式</param>
/// <param name="parm">分页参数</param>
/// <returns></returns>
public static PagedInfo<T> ToPage<T>(this ISugarQueryable<T> source, PagerInfo parm)
{
var page = new PagedInfo<T>();
var total = 0;
page.PageSize = parm.PageSize;
page.PageIndex = parm.PageNum;
page.Result = source.OrderByIF(parm.Sort.IsNotEmpty(), $"{parm.Sort.ToSqlFilter()} {(!string.IsNullOrWhiteSpace(parm.SortType) && parm.SortType.Contains("desc") ? "desc" : "asc")}")
.ToPageList(parm.PageNum, parm.PageSize, ref total);
page.TotalNum = total;
return page;
}
/// <summary>
/// 转指定实体类Dto
/// </summary>
/// <typeparam name="T"></typeparam>
/// <typeparam name="T2"></typeparam>
/// <param name="source"></param>
/// <param name="parm"></param>
/// <returns></returns>
public static PagedInfo<T2> ToPage<T, T2>(this ISugarQueryable<T> source, PagerInfo parm)
{
var page = new PagedInfo<T2>();
var total = 0;
page.PageSize = parm.PageSize;
page.PageIndex = parm.PageNum;
var result = source
.OrderByIF(parm.Sort.IsNotEmpty(), $"{parm.Sort.ToSqlFilter()} {(!string.IsNullOrWhiteSpace(parm.SortType) && parm.SortType.Contains("desc") ? "desc" : "asc")}")
.ToPageList(parm.PageNum, parm.PageSize, ref total);
page.TotalNum = total;
page.Result = result.Adapt<List<T2>>();
return page;
}
}
}
使用事务
/// <summary>
/// 修改数据权限信息
/// </summary>
/// <param name="sysRoleDto"></param>
/// <returns></returns>
public bool AuthDataScope(SysRoleDto sysRoleDto)
{
var result = UseTran(() =>
{
var oldMenus = SelectUserRoleMenus(sysRoleDto.RoleId);
var newMenus = sysRoleDto.MenuIds;
//并集菜单
var arr_c = oldMenus.Intersect(newMenus).ToArray();
//获取减量菜单
var delMenuIds = oldMenus.Where(c => !arr_c.Contains(c)).ToArray();
//获取增量
var addMenuIds = newMenus.Where(c => !arr_c.Contains(c)).ToArray();
RoleMenuService.DeleteRoleMenuByRoleIdMenuIds(sysRoleDto.RoleId, delMenuIds);
sysRoleDto.MenuIds = addMenuIds.ToList();
sysRoleDto.DelMenuIds= delMenuIds.ToList();
InsertRoleMenu(sysRoleDto);
});
if (!result.IsSuccess)
{
throw new Exception("提交数据异常," + result.ErrorMessage, result.ErrorException);
}
}
service 中使用 Ado
- 使用存储过程
/// <summary>
///使用Ado
/// </summary>
/// <returns></returns>
public DataTable GetTest(int xxx)
{
string sql = "proc_test";
var s1 = new SugarParameter("@xxx", xxx);
return Context.Ado.UseStoredProcedure().GetDataTable(sql);
}
更多用法传送门
切库
- 方式 1、通过 Config 获取数据库，比较灵活
[SugarTable("sys_config", "配置表")]
[Tenant("0")] //对应ConfigId=0
public class SysConfig
{
[SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
public int ConfigId { get; set; }
public string ConfigName { get; set; }
}
var db1 = DbScoped.SugarScope.GetConnection(configId) //非线程安全
var db1 = DbScoped.SugarScope.GetConnectionScope(configId); //线程安全 (5.0.7.7)
- 方式 2、通过特性获取数据库，一个实体对一个库的时候非常方便
var db2=DbScoped.SugarScope.GetConnectionWithAttr<T>() //非线程安全
var db2=DbScoped.SugarScope.GetConnectionScopeWithAttr<T>() //线程安全(5.0.7.7)
更多使用传送门
- 方式 3
Repository 和Service 中切库
//使用配置为test的数据库
var db = itenant.GetConnectionScope("test");//线程安全
//var db = itenant.GetConnection("test");//非线程安全
var result = db.Ado.UseStoredProcedure().SqlQuery<SysUser>("proc_xxx");
var result2 = db.Queryable<SysUser>().ToList()
Aop 操作
文件 ZR.ServiceCore.SqlSugar.SqlsugarSetup
差异化日志(审计)
//差异日志功能
db.GetConnectionScope(configId).Aop.OnDiffLogEvent = it =>
{
//操作前记录 包含： 字段描述 列名 值 表名 表描述
var editBeforeData = it.BeforeData;//插入Before为null，之前还没进库
//操作后记录 包含： 字段描述 列名 值 表名 表描述
var editAfterData = it.AfterData;
var sql = it.Sql;
var parameter = it.Parameters;
var data = it.BusinessData;//这边会显示你传进来的对象
var time = it.Time;
var diffType = it.DiffType;//enum insert 、update and delete
string name = App.UserName;
foreach (var item in editBeforeData)
{
var pars = db.Utilities.SerializeObject(item.Columns.ToDictionary(it => it.ColumnName, it => it.Value));
SqlDiffLog log = new()
{
BeforeData = pars,
BusinessData = data?.ToString(),
DiffType = diffType.ToString(),
Sql = sql,
TableName = item.TableName,
UserName = name,
AddTime = DateTime.Now,
ConfigId = configId
};
if (editAfterData != null)
{
var afterData = editAfterData?.First(x => x.TableName == item.TableName);
var afterPars = db.Utilities.SerializeObject(afterData?.Columns.ToDictionary(it => it.ColumnName, it => it.Value));
log.AfterData = afterPars;
}
//logger.WithProperty("title", data).Info(pars);
db.GetConnectionScope(0)
.Insertable(log)
.ExecuteReturnSnowflakeId();
}
};
- 功能说明
用来记录增(没意义)、删、改变化之前的数据，更详细的介绍请看下方链接
如何使用
//删除
var response = _ArticleCategoryService.Delete([1], '文章删除');
//修改
Context.Updateable(sysUser).EnableDiffLogEvent("更新数据").ExecuteCommand();
//添加
Insertable(sysUser).EnableDiffLogEvent("添加").ExecuteReturnIdentity();
更多用于请参考 SQLsugar 文档