ORM C# 封装SqlSugar 操作数据库_sqlsugar 基类封装

SqlSugar 是一款 老牌 .NET 开源多库架构ORM框架（EF Core单库架构），由果糖大数据科技团队

维护和更新 ，开箱即用最易上手的.NET ORM框架 。生态圈丰富，目前开源生态仅次于EF Core，但是在需要

多库兼容的项目或产品中更加偏爱SqlSugar 

Github源码：https://github.com/donet5/SqlSugar 

Gitee源码：SqlSugar ORM: sqlsugar 是国内最受欢迎的 ORM 框架，支持.NET CORE 和 MySql、SqlServer、Sqlite、Oracle 、 postgresql、ClickHouse、GaussDB 、TDengine 、OceanBase、OpenGauss、Tidb 、达梦、人大金仓 数据库，.NET多库支持最好的ORM，支持真实的批量操作，另外还有媲美DAPPER的性能

CSDN 下载链接：

https://download.csdn.net/download/rotion135/88279938

数据库支持

好，废话不多说，直接说说代码中该如何使用：

-------------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);
  }