SqlSugar 是一款 老牌 .NET 开源多库架构ORM框架（EF Core单库架构），由果糖大数据科技团队
维护和更新 ，开箱即用最易上手的.NET ORM框架 。生态圈丰富，目前开源生态仅次于EF Core，但是在需要
多库兼容的项目或产品中更加偏爱SqlSugar
Github源码：https://github.com/donet5/SqlSugar
Gitee源码：https://gitee.com/dotnetchina/SqlSugar
DAO层的封装
BaseDAO
using SqlSugar;
using System.Configuration;
namespace Api.DAO
{
public class BaseDAO
{
/// <summary>
/// 数据库连接类型
/// </summary>
protected SqlSugar.DbType _defaultDBType = SqlSugar.DbType.MySql;
protected string _defaultConnString = ConfigurationManager.AppSettings["MySQLConnection"].ToString();
/// <summary>
/// 数据库
/// </summary>
public ISqlSugarClient DB { 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()
{
ConnectionString = _defaultConnString,
DbType = _defaultDBType,
IsAutoCloseConnection = true,
});
}
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()
{
ConnectionString = _defaultConnString,
DbType = _defaultDBType,
IsAutoCloseConnection = true,
});
}
#endregion
}
}增删查改的操作，以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;
}
}事务处理：
/// <summary>
/// 新增完整的订单数据
/// </summary>
/// <param name="model">订单数据</param>
/// <param name="products">订单明细数据</param>
/// <param name="userCode">操作人用户编码</param>
/// <returns></returns>
public BaseResult InsertOrder(OrderModel model, List<OrderProductModel> products, string userCode)
{
BaseResult result = BaseResult.Successed;
OrderDAO dao=new OrderDAO();
try
{
DateTime now = DateTime.Now;
//数据检测
t_order entity = new t_order();
MapHelper.Map(model, entity);
entity.CreateTime = now;
entity.CreateUserCode = userCode;
entity.ModifyTime = now;
entity.ModifyUserCode = userCode;
if (string.IsNullOrEmpty(entity.ID))
{
entity.ID = GetGuid();
}
string errormsg = Entity.DataValidation.Validate(entity);
if (!string.IsNullOrEmpty(errormsg))
{
return new BaseResult(false, errormsg);
}
if (!dao.CheckBeforeInsert(entity.OrderId))
{
return new BaseResult(false, $"订单[{entity.OrderId}]已存在");
}
var _group = products.GroupBy(x => x.MaterialCode);
if (_group.Count() != products.Count)
{
result = new BaseResult(false, "订单明细中存在重复的物料");
return result;
}
//开启事务
dao.DBAdaptor.BeginTran();
//新增数据
var res = dao.Insert(entity);
if(!res)
{
dao.DBAdaptor.RollbackTran();
return res;
}
OrderProductBLL productBLL = new OrderProductBLL();
foreach (var product in products)
{
//dao.DBAdaptor - 数据库连接传递，包括事务状态
res = productBLL.Insert(product, userCode, dao.DBAdaptor);
if (!res)
{
dao.DBAdaptor.RollbackTran();
return res;
}
}
//提交事务
dao.DBAdaptor.CommitTran();
}
catch(Exception ex)
{
if(dao.DBAdaptor.IsAnyTran())
{
dao.DBAdaptor.RollbackTran();
}
throw ex;
}
return result;
}
