SqlSugar 是一款 老牌 .NET 开源多库架构ORM框架(EF Core单库架构),由果糖大数据科技团队
维护和更新 ,开箱即用最易上手的.NET ORM框架 。生态圈丰富,目前开源生态仅次于EF Core,但是在需要
多库兼容的项目或产品中更加偏爱SqlSugar
Github源码:https://github.com/donet5/SqlSugar
CSDN 下载链接:
https://download.csdn.net/download/rotion135/88279938
数据库支持
关系型数据库 | MySql、SqlServer、Sqlite、Oracle 、 postgresql、达梦、 人大金仓(国产推荐)、神通数据库、瀚高、Access 、OceanBase MySqlConnector、华为 GaussDB 、南大通用 GBase、MariaDB、、Tidb、 Odbc、Percona Server, Amazon Aurora、Azure Database for MySQL、PolarDB Google Cloud SQL for MySQL、kunDB 、自定义数据库 |
时序数据库 | TDengine (支持群集,缺点不支持更新,语法比较弱支持的东西少) QuestDb(适合几十亿数据分析,模糊查询,适合单机,语法强大,自动分表存储 ,缺点不支持删除) |
列式存储库 | Clickhouse(适用于商业智能领域(BI),缺点大小写必须和库一样,不支持事务) |
即将上线 | Mongodb(mongodb.entities)即将开发 Sybase、hana、FireBird、InfluxDB litedb、 |
好,废话不多说,直接说说代码中该如何使用:
-------------2024.03.16 更新---------------------------------------------------
BaseDAO 增加事务方法(原有的设计事务处理上有点问题)
-----------------------------------------------------------------------------------
DAO层的封装
BaseDAO
using SqlSugar;
using System;
using System.Configuration;
using System.Data;
namespace PCZD.SQL.Library.DAO
{
public class BaseDAO
{
private string ConfigId = Guid.NewGuid().ToString();
/// <summary>
/// 数据库连接类型
/// </summary>
protected SqlSugar.DbType _defaultDBType = SqlSugar.DbType.MySql;
protected string _defaultConnString = SQLGlobal.SQLConnection;
/// <summary>
/// 数据库
/// </summary>
public ISqlSugarClient DB { get; private set; }
/// <summary>
/// 事务对象
/// </summary>
public IDbTransaction DbTransaction { get; private set; }
/// <summary>
/// 数据库连接属性
/// 用于使用事务时的连接参数传递
/// </summary>
public IAdo DBAdaptor
{
get { return DB.Ado; }
}
#region 默认数据库连接
/// <summary>
/// 使用默认的数据库类型和已知连接初始化DB
/// </summary>
/// <param name="dbType"></param>
/// <param name="connKey"></param>
public BaseDAO(IAdo ado = null)
{
if (ado == null)
{
this.DB = new SqlSugarProvider(new ConnectionConfig()
{
ConfigId = ConfigId,
ConnectionString = _defaultConnString,
DbType = _defaultDBType,
IsAutoCloseConnection = true,
});
this.DB.Open();
}
else
{
this.DB = ado.Context;
}
}
#endregion
#region 自定义数据库连接
/// <summary>
/// 使用自定义数据库类型和连接字符串初始化
/// <para>
/// 适用于自定义数据库连接(例如业务中连接第二种数据库)
/// </para>
/// </summary>
/// <param name="dbType"></param>
/// <param name="connKey"></param>
public BaseDAO(SqlSugar.DbType dbType, string connString)
{
_defaultDBType = dbType;
_defaultConnString = connString;
this.DB = new SqlSugarProvider(new ConnectionConfig()
{
ConfigId = ConfigId,
ConnectionString = _defaultConnString,
DbType = _defaultDBType,
IsAutoCloseConnection = true,
});
this.DB.Open();
}
#endregion
/// <summary>
/// 开启事务
/// </summary>
/// <returns></returns>
public bool BeginTran()
{
this.DBAdaptor.BeginTran();
DbTransaction = this.DB.Ado.Transaction;
return this.DBAdaptor.IsAnyTran();
}
/// <summary>
/// 提交事务
/// </summary>
/// <returns></returns>
public bool CommitTran()
{
DbTransaction?.Commit();
return true;
}
/// <summary>
/// 事务回滚
/// </summary>
/// <returns></returns>
public bool RollbackTran()
{
DbTransaction?.Rollback();
return true;
}
}
}
增删查改的操作,以ConfigDAO为例子:
/// <summary>
/// 系统配置数据
/// </summary>
public class ConfigDAO : BaseDAO
{
public ConfigDAO(IAdo ado = null) : base(ado) { }
/// <summary>
/// 查询所有的配置数据
/// </summary>
/// <returns></returns>
public IEnumerable<t_sys_config> GetAllConfig()
{
var sql = base.DB.Queryable<t_sys_config>();
return sql.ToList();
}
/// <summary>
/// 获取配置数据列表
/// </summary>
/// <param name="parent">父节点编码,默认根节点root</param>
/// <param name="cfgCode">配置项编码</param>
/// <returns></returns>
public IEnumerable<t_sys_config> GetConfigList(string parent, string cfgCode="")
{
if (string.IsNullOrEmpty(parent))
{
//父节点为空时,默认取根节点数据
parent = "root";
}
var sql = base.DB.Queryable<t_sys_config>();
sql = sql.Where(t => t.ParentCode == parent);
if (!string.IsNullOrEmpty(cfgCode))
{
sql = sql.Where(t => t.CfgCode == cfgCode);
}
return sql.ToList();
}
/// <summary>
/// 获取配置信息
/// </summary>
/// <param name="cfgCode">配置编码</param>
/// <returns></returns>
public t_sys_config GetConfig(string cfgCode)
{
var sql = base.DB.Queryable<t_sys_config>().Where(t => t.CfgCode == cfgCode);
return sql.Single();
}
/// <summary>
/// 新增前检测 true-正常,可新增 false-已存在相同数据
/// </summary>
/// <param name="cfgCode">配置编码</param>
/// <returns></returns>
public BaseResult CheckBeforeInsert(string cfgCode)
{
var sql = base.DB.Queryable<t_sys_config>().Where(t => t.CfgCode == cfgCode);
return sql.Count() <= 0;
}
/// <summary>
/// 新增配置数据
/// 正常系统中不进行使用,配置需要脚本初始化
/// </summary>
/// <param name="entity">配置数据</param>
/// <returns></returns>
public BaseResult Insert(t_sys_config entity)
{
var sql = base.DB.Insertable(entity);
return sql.ExecuteCommand() > 0;
}
/// <summary>
/// 修改配置数据
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public BaseResult Update(t_sys_config entity)
{
var sql = base.DB.Updateable(entity).Where(x => x.CfgCode == entity.CfgCode);
return sql.ExecuteCommand() > 0;
}
}
事务处理:
UserUgDAO dao = new UserUgDAO();
try
{
DateTime now = DateTime.Now;
string targetUser = models[0].UserCode;
var dataList = dao.GetUserUgList(targetUser);
List<t_userug> addList = new List<t_userug>();
List<t_userug> delList = new List<t_userug>();
//从源数据及目标数据中,筛选出需要新增及删除的数据
delList = (from userug in dataList
where !(
from left in dataList join right in models on left.GroupCode equals right.GroupCode select left
).Contains(userug)
select userug).ToList();
addList = (from ug in models
where !(
from left in models join right in dataList on left.GroupCode equals right.GroupCode select left
).Contains(ug)
select new t_userug()
{
ID = base.GetGuid(),
UserCode = ug.UserCode,
GroupCode = ug.GroupCode,
Description = "",
CreateTime = now,
CreateUserCode = userCode,
}
).ToList();
BaseResult res = BaseResult.Successed;
//开启事务
dao.BeginTran();
if (delList != null && delList.Count > 0)
{
res = dao.DeleteBatch(delList);
if (!res)
{
//事务回滚
dao.RollbackTran();
return res;
}
}
if (addList != null && addList.Count > 0)
{
res = dao.InsertBatch(addList);
if (!res)
{
//事务回滚
dao.RollbackTran();
return res;
}
}
//刷新来自用户组的角色继承
res = RefreshUserRoleByUG(addList, delList, targetUser, userCode, dao.DBAdaptor);
if (!res)
{
//事务回滚
dao.RollbackTran();
return res;
}
//提交事务
dao.CommitTran();
return BaseResult.Successed;
}
catch (Exception ex)
{
ApiLog.Error("ModifyUserUg 异常", ex);
dao.RollbackTran();
return new BaseResult(false, Language("Edit_Failure") + ex.Message);
}