0
0

猿创征文｜【国产数据库】一文学会应用SqlSugar工作单元模式操作多数据库

🐋作者简介：博主是一位.Net开发者，同时也是RPA和低代码平台的践行者。
🐬个人主页：会敲键盘的肘子
🐰系列专栏：SqlSugar ORM
🦀专栏简介：SqlSugar是一款来自未来的ORM，拥有超前的理念，需求领跑第一线，在设计理念上就算不更新几年都不会过时，是一款真正用了功能齐全的ORM框架。博主在工作中学习和实战SqlSugar，形成本专栏，希望可以帮助到您解决问题。
🐶座右铭：总有一天你所坚持的会反过来拥抱你。

在这里插入图片描述

📖 前言

​ 随着华为、中兴事务，国产数据库市场相信是未来是趋势走向，纵观 .net core 整个圈子FreeSql和SqlSugar(排名不分先后)都已早早支持国产数据库，比如人大金仓、神通、达梦，针对不同的项目要求支持适配不同的国产数据库。今天我们使用 SqlSugar ORM 的工作单元模式来连接多个国产数据库。

​ SqlSugar是一款来自未来的ORM，开箱即用，拥有超前的理念，需求领跑第一线，在设计理念上就算不更新几年都不会过时，是一款真正用了功能齐全的ORM框架。

感谢果糖大数据科技团队对SqlSugar的维护和更新。

👉本文关键字：国产数据库、SqlSugar、ORM、人大金仓、达梦、翰高

文章目录

📑 Sqlsugar基本介绍

♈ 基本介绍

​ SqlSugar 是一款 老牌 .NET 开源ORM框架，由果糖大数据科技团队维护和更新 ，开箱即用最易上手的ORM框架 ，51Job和Boss直招简历数超过 国外框架 Nhibernate PetaPoco, 仅次于Dapper和EF Core , 占Dapper 40% 。

⭐ 功能介绍

  1. 相比EF Core【学习成本低一天学会】，多库支持更好 ，在国内拥有不逊色EF Core 的【开源生态】。
  2. 支持 .NET 百万级【大数据】写入和更新、分表和几十亿查询和统计等 拥有成熟方案。
  3. 支持 完整的SAAS一套应用 跨库查询 、租户分库 、租户分表 和 租户数据隔离。
  4. 支持【低代码】+工作流 （无实体多库兼容CRUD & JSON TO SQL ）。
  5. 语法最爽的ORM、优美的表达式、仓储、UnitOfWork、DbContext、AOP。
  6. 支持 DbFirst、CodeFirst和【WebFirst】 3种模式开发。
  7. 简单易用、功能齐全、高性能、轻量级、服务齐全、官网教程文档、有专业技术支持一天18小时服务。

⭐ 数据库支持

关系型数据库MySql、SqlServer、Sqlite、Oracle 、 postgresql、达梦、人大金仓、神通数据库、瀚高、Access 、MySqlConnector、华为 GaussDB 、南大通用 GBase 、Odbc、自定义时序数据库QuestDb （适合几十亿数据分析,模糊查询，自动分表存储 ，缺点不支持删除）列式存储库Clickhouse（适用于商业智能领域(BI)，缺点大小写必须和库一样，不支持事务）即将上线Mongodb（mongodb.entities）TDengine、Sybase…

♉ Nuget安装

⭐ 打开Nuget

​ 点击项目引用 >> 右键 Nuget管理

在这里插入图片描述

⭐ 安装SqlSugar

​ .Net Core 3 & 5 & 6 &7

在这里插入图片描述

⭐ SqlSugar源码

​ 博主这里因为需要对SqlSugar做一些修改，所以用了源码，结构如下，

在这里插入图片描述

📑 工作单元模式/IUnitOfWorK

♊ 工作单元模式/IUnitOfWorK

⭐ 数据库枚举

public enum DbType
{
    MySql ,
    SqlServer,
    Sqlite,
    Oracle,
    PostgreSQL,
    Dm, 
    Kdbndp // 人大金仓 只支持.NET CORE
}

⭐ 连接字符串（多数据库）

appsettings.json配置

"ConnectionStrings": {
    "kdbndp": "data source=127.0.0.1;database=test;Port=54321;UID=SYSTEM;PWD=system", //测试版本用人大金仓数据库
    "Oracledb": "data source=127.0.0.1/mes;user id=mes_open;password=123456", //Oracle数据库
    "MySqldb": "data source=127.0.0.1;database=production;uid=root;pwd=123456",//MySql数据库
    "DMdb":"PORT=5236;DATABASE=DAMENG;HOST=127.0.0.1;PASSWORD=SYSDBA;USER ID=SYSDBA",//DM数据库
    "HGdb":"PORT=5432;DATABASE=SqlSugar4xTest;HOST=localhost;PASSWORD=haosql;USER ID=postgres",//瀚高数据库
  },

♉ 实现

⭐ UnitOfWork.cs 工作单元实现

using Microsoft.AspNetCore.Http;
using Microsoft.Extensions.Configuration;
using SqlSugar;
using System;

namespace Sys.Infrastructure.Data
{
    /// <summary>
    /// 工作单元实现
    /// </summary>
    public abstract class UnitOfWork : IUnitOfWork
    {
        public UnitOfWork(IConfiguration configuration, IHttpContextAccessor accessor)
        {
            Configuration = configuration;
            HttpContextAccessor = accessor;
        }

        protected IConfiguration Configuration { get; set; }
        public IHttpContextAccessor HttpContextAccessor { get; set; }
        protected DateTime StartTime { get; set; }
        protected DateTime EndTime { get; set; }

        public SqlSugarClient Db { get; protected set; }

        public void BeginTran() => Db.BeginTran();

        public void CommitTran()
        {
            try
            {
                Db.CommitTran();
            }
            catch (Exception ex)
            {
                Db.RollbackTran();
                throw ex;
            }
        }

        public void RollbackTran() => Db.RollbackTran();
    }
}

⭐ KdbndpOfWork.cs（人大金仓数据库）

using Microsoft.AspNetCore.Http;
using Microsoft.Extensions.Configuration;
using Newtonsoft.Json;
using Serilog;
using SqlSugar;
using System;
using System.IO;
using System.Linq;

namespace Sys.Infrastructure.Data
{
    public class KdbndpOfWork : UnitOfWork, IUnitOfWork
    {
        public KdbndpOfWork(IConfiguration configuration, IHttpContextAccessor accessor) : base(configuration, accessor)
        {
            Db = new SqlSugarClient(new ConnectionConfig
            {
                ConnectionString = configuration.GetConnectionString("kdbndpdb"), //数据库连接在 appsettings.json 中配置
                DbType = DbType.Kdbndp,
                IsAutoCloseConnection = true,
                IsShardSameThread = true,
                AopEvents = new AopEvents
                {
                    OnLogExecuted = OnLogExecuted,
                    OnLogExecuting = OnLogExecuting,
                    OnError = OnError
                }
            });
        }

        /// <summary>
        /// 当数据库操作执行出错时
        /// </summary>
        /// <param name="exception"></param>
        private static void OnError(SqlSugarException exception)
        {

        }

        /// <summary>
        /// 当数据库操作执行时
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        private static void OnLogExecuting(string sql, SugarParameter[] parameters)
        {
            //StartTime = DateTime.Now;
        }

        /// <summary>
        /// 当数据库操作执行完毕后
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        private static void OnLogExecuted(string sql, SugarParameter[] parameters)
        {
           // EndTime = DateTime.Now;
            
            //CreateHttpSqlLog(sql, parameters);
        }

       
    }
}

⭐ DMOfWork.cs（达梦数据库）

using Microsoft.AspNetCore.Http;
using Microsoft.Extensions.Configuration;
using Newtonsoft.Json;
using Serilog;
using SqlSugar;
using System;
using System.IO;
using System.Linq;

namespace Sys.Infrastructure.Data
{
    public class DMOfWork : UnitOfWork, IUnitOfWork
    {
        public DMOfWork(IConfiguration configuration, IHttpContextAccessor accessor) : base(configuration, accessor)
        {
            Db = new SqlSugarClient(new ConnectionConfig
            {
                ConnectionString = configuration.GetConnectionString("DMdb"), //数据库连接在 appsettings.json 中配置
                DbType = DbType.DM,
                IsAutoCloseConnection = true,
                IsShardSameThread = true,
                AopEvents = new AopEvents
                {
                    OnLogExecuted = OnLogExecuted,
                    OnLogExecuting = OnLogExecuting,
                    OnError = OnError
                }
            });
        }

        /// <summary>
        /// 当数据库操作执行出错时
        /// </summary>
        /// <param name="exception"></param>
        private static void OnError(SqlSugarException exception)
        {

        }

        /// <summary>
        /// 当数据库操作执行时
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        private static void OnLogExecuting(string sql, SugarParameter[] parameters)
        {
            //StartTime = DateTime.Now;
        }

        /// <summary>
        /// 当数据库操作执行完毕后
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        private static void OnLogExecuted(string sql, SugarParameter[] parameters)
        {
           // EndTime = DateTime.Now;
            
            //CreateHttpSqlLog(sql, parameters);
        }

       
    }
}

⭐ MysqlOfWork.cs（MySQL数据库）

using Microsoft.AspNetCore.Http;
using Microsoft.Extensions.Configuration;
using Newtonsoft.Json;
using Serilog;
using SqlSugar;
using Supcon.APS.Common;
using Supcon.APS.Domain.Core;
using Supcon.APS.Domain.Entities;
using Supcon.APS.Domain.Entities.Sys;
using Supcon.APS.Domain.IRepository;
using System;
using System.IO;
using System.Linq;

namespace Sys.Infrastructure.Data
{
    public class MysqlOfWork : UnitOfWork, IUnitOfWork
    {
        public MysqlOfWork(IConfiguration configuration, IHttpContextAccessor accessor) : base(configuration, accessor)
        {
            Db = new SqlSugarClient(new ConnectionConfig
            {
                ConnectionString = configuration.GetConnectionString("Mysqldb"), //数据库连接在 appsettings.json 中配置
                DbType = DbType.MySQL,
                IsAutoCloseConnection = true,
                IsShardSameThread = true,
                AopEvents = new AopEvents
                {
                    OnLogExecuted = OnLogExecuted,
                    OnLogExecuting = OnLogExecuting,
                    OnError = OnError
                }
            });
        }

        /// <summary>
        /// 当数据库操作执行出错时
        /// </summary>
        /// <param name="exception"></param>
        private static void OnError(SqlSugarException exception)
        {

        }

        /// <summary>
        /// 当数据库操作执行时
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        private static void OnLogExecuting(string sql, SugarParameter[] parameters)
        {
            //StartTime = DateTime.Now;
        }

        /// <summary>
        /// 当数据库操作执行完毕后
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        private static void OnLogExecuted(string sql, SugarParameter[] parameters)
        {
           // EndTime = DateTime.Now;
            
            //CreateHttpSqlLog(sql, parameters);
        }

       
    }
}

⭐ OracleOfWork.cs（Oracle数据库）

using Microsoft.AspNetCore.Http;
using Microsoft.Extensions.Configuration;
using Newtonsoft.Json;
using Serilog;
using SqlSugar;
using Supcon.APS.Common;
using Supcon.APS.Domain.Core;
using Supcon.APS.Domain.Entities;
using Supcon.APS.Domain.Entities.Sys;
using Supcon.APS.Domain.IRepository;
using System;
using System.IO;
using System.Linq;

namespace Sys.Infrastructure.Data
{
    public class OracleOfWork : UnitOfWork, IUnitOfWork
    {
        public OracleOfWork(IConfiguration configuration, IHttpContextAccessor accessor) : base(configuration, accessor)
        {
            Db = new SqlSugarClient(new ConnectionConfig
            {
                ConnectionString = configuration.GetConnectionString("Oracledb"), //数据库连接在 appsettings.json 中配置
                DbType = DbType.Oracle,
                IsAutoCloseConnection = true,
                IsShardSameThread = true,
                AopEvents = new AopEvents
                {
                    OnLogExecuted = OnLogExecuted,
                    OnLogExecuting = OnLogExecuting,
                    OnError = OnError
                }
            });
        }

        /// <summary>
        /// 当数据库操作执行出错时
        /// </summary>
        /// <param name="exception"></param>
        private static void OnError(SqlSugarException exception)
        {

        }

        /// <summary>
        /// 当数据库操作执行时
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        private static void OnLogExecuting(string sql, SugarParameter[] parameters)
        {
            //StartTime = DateTime.Now;
        }

        /// <summary>
        /// 当数据库操作执行完毕后
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        private static void OnLogExecuted(string sql, SugarParameter[] parameters)
        {
           // EndTime = DateTime.Now;
            
            //CreateHttpSqlLog(sql, parameters);
        }

       
    }
}

⭐ HGdbOfWork.cs（瀚高数据库）

using Microsoft.AspNetCore.Http;
using Microsoft.Extensions.Configuration;
using Newtonsoft.Json;
using Serilog;
using SqlSugar;
using Supcon.APS.Common;
using Supcon.APS.Domain.Core;
using Supcon.APS.Domain.Entities;
using Supcon.APS.Domain.Entities.Sys;
using Supcon.APS.Domain.IRepository;
using System;
using System.IO;
using System.Linq;

namespace Sys.Infrastructure.Data
{
    public class HGdbOfWork : UnitOfWork, IUnitOfWork
    {
        public HGdbOfWork(IConfiguration configuration, IHttpContextAccessor accessor) : base(configuration, accessor)
        {
            Db = new SqlSugarClient(new ConnectionConfig
            {
                ConnectionString = configuration.GetConnectionString("HGdb"), //数据库连接在 appsettings.json 中配置
                DbType = SqlSugar.DbType.PostgreSQL,
                IsAutoCloseConnection = true,
                MoreSettings=new ConnMoreSettings() {
                        PgSqlIsAutoToLower=false //数据库存在大写字段的 ，需要把这个设为false ，并且实体和字段名称要一样
                },
                AopEvents = new AopEvents
                {
                    OnLogExecuting = (sql, p) =>
                    {
                        Console.WriteLine(sql);
                        Console.WriteLine(string.Join(",", p?.Select(it => it.ParameterName + ":" + it.Value)));
                    }
                }
            });
        }

        /// <summary>
        /// 当数据库操作执行出错时
        /// </summary>
        /// <param name="exception"></param>
        private static void OnError(SqlSugarException exception)
        {

        }

        /// <summary>
        /// 当数据库操作执行时
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        private static void OnLogExecuting(string sql, SugarParameter[] parameters)
        {
            //StartTime = DateTime.Now;
        }

        /// <summary>
        /// 当数据库操作执行完毕后
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        private static void OnLogExecuted(string sql, SugarParameter[] parameters)
        {
           // EndTime = DateTime.Now;
            
            //CreateHttpSqlLog(sql, parameters);
        }

       
    }
}

📑 仓储

♊ 定义

仓储有一套自带的数据库操作方法，比起 db.xx.xxx来说可能更简便些满足一些常用需求, 复杂的功能还是用db.xxx.xxx。

⭐ 仓储接口

public interface IRepository<TEntity> : IRepository<Guid, TEntity, IUnitOfWork>
            where TEntity : ApsEntity, new()
    {

    }

/// <summary>
/// 人大金仓仓储接口
/// </summary>
public interface IKdbndpRepository<TEntity> : IRepository<int, TEntity, IUnitOfWork>
        where TEntity : SupplyChanEntity, new()
    {

    }

/// <summary>
///达梦仓储接口
/// </summary>
public interface IDMRepository<TEntity> : IRepository<int, TEntity, IUnitOfWork>
        where TEntity : SupplyChanEntity, new()
    {

    }

/// <summary>
/// 翰高仓储接口
/// </summary>
public interface IHGRepository<TEntity> : IRepository<int, TEntity, IUnitOfWork>
        where TEntity : SupplyChanEntity, new()
    {

    }

/// <summary>
/// MySQL仓储接口
/// </summary>
public interface IMySQLRepository<TEntity> : IRepository<int, TEntity, IUnitOfWork>
        where TEntity : SupplyChanEntity, new()
    {

    }

/// <summary>
/// Oracle仓储接口
/// </summary>
public interface IOracleRepository<TEntity> : IRepository<int, TEntity, IUnitOfWork>
        where TEntity : SupplyChanEntity, new()
    {

    }

    public interface IRepository<TKey, TEntity, TUnitOfWork>
        where TEntity : Entity<TKey>, new()
        where TUnitOfWork : IUnitOfWork
    {
        #region 查询

        /// <summary>
        /// 是否存在满足指定条件的数据
        /// </summary>
        /// <param name="whereExpression">条件表达式</param>
        /// <returns></returns>
        bool IsExist(Expression<Func<TEntity, bool>> whereExpression);

        /// <summary>
        /// 统计数据条数
        /// </summary>
        /// <param name="whereExpression">条件表达式</param>
        /// <returns></returns>
        int Count(Expression<Func<TEntity, bool>> whereExpression = null);

        /// <summary>
        /// 获取一条数据
        /// </summary>
        /// <param name="whereExpression">条件表达式</param>
        /// <returns></returns>
        Task<TEntity> SingleAsync(Expression<Func<TEntity, bool>> whereExpression);

        /// <summary>
        /// 查询
        /// </summary>
        /// <returns></returns>
        Task<List<TEntity>> QueryAsync();

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="strWhere">查询字符串</param>
        /// <returns></returns>
        Task<List<TEntity>> QueryAsync(string strWhere);

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="whereExpression">条件表达式</param>
        /// <returns></returns>
        Task<List<TEntity>> QueryAsync(Expression<Func<TEntity, bool>> whereExpression);

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="strWhere">查询字符串</param>
        /// <param name="strOrderByFileds">排序字段，如：Name Asc, Age Desc</param>
        /// <returns></returns>
        Task<List<TEntity>> QueryAsync(string strWhere, string strOrderByFileds);

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="whereExpression">条件表达式</param>
        /// <param name="strOrderByFileds">排序字段，如：Name Asc, Age Desc</param>
        /// <returns></returns>
        Task<List<TEntity>> QueryAsync(Expression<Func<TEntity, bool>> whereExpression, string strOrderByFileds);

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="whereExpression">条件表达式</param>
        /// <param name="orderByExpression">排序表达式</param>
        /// <param name="isAsc">是否为升序</param>
        /// <returns></returns>
        Task<List<TEntity>> QueryAsync(Expression<Func<TEntity, bool>> whereExpression, Expression<Func<TEntity, object>> orderByExpression, bool isAsc = true);

        /// <summary>
        /// 查询前 N 条数据
        /// </summary>
        /// <param name="whereExpression">条件表达式</param>
        /// <param name="count">记录数</param>
        /// <param name="strOrderByFileds">排序字段，如：Name Asc, Age Desc</param>
        /// <returns></returns>
        Task<List<TEntity>> TopAsync(
            Expression<Func<TEntity, bool>> whereExpression,
            int count,
            string strOrderByFileds);

        /// <summary>
        /// 查询前 N 条数据
        /// </summary>
        /// <param name="strWhere">条件字符串</param>
        /// <param name="count">记录数</param>
        /// <param name="strOrderByFileds">排序字段，如：Name Asc, Age Desc</param>
        /// <returns></returns>
        Task<List<TEntity>> TopAsync(
            string strWhere,
            int count,
            string strOrderByFileds);

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="strWhere">条件字符串</param>
        /// <param name="pageIndex">当前页数</param>
        /// <param name="pageSize">分页大小</param>
        /// <param name="strOrderByFileds">排序字段，如：Name Asc, Age Desc</param>
        /// <param name="totalCount">总记录数</param>
        /// <returns></returns>
        List<TEntity> Pages(string strWhere, int pageIndex, int pageSize, string strOrderByFileds, out int totalCount);

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="whereExpression">条件表达式</param>
        /// <param name="pageIndex">当前页数</param>
        /// <param name="pageSize">分页大小</param>
        /// <param name="orderByExpression">排序表达式</param>
        /// <param name="isAsc">是否为升序</param>
        /// <param name="totalCount">总记录数</param>
        /// <returns></returns>
        List<TEntity> Pages(Expression<Func<TEntity, bool>> whereExpression,
            int pageIndex, int pageSize, Expression<Func<TEntity, object>> orderByExpression, bool isAsc,
            out int totalCount);

        /// <summary> 
        /// 多表查询
        /// </summary> 
        /// <typeparam name="T1">实体1</typeparam> 
        /// <typeparam name="T2">实体2</typeparam> 
        /// <typeparam name="TResult">返回类型</typeparam>
        /// <param name="joinExpression">关联表达式 (join1, join2) => new object[] { JoinType.Left, join1.UserNo == join2.UserNo }</param> 
        /// <param name="selectExpression">返回表达式 (s1, s2) => new { Id =s1.UserNo, Id1 = s2.UserNo }</param>
        /// <param name="whereLambda">查询表达式 (w1, w2) => w1.UserNo == "")</param> 
        /// <returns></returns>
        Task<List<TResult>> QueryMuchAsync<T1, T2, TResult>(
            Expression<Func<T1, T2, object[]>> joinExpression,
            Expression<Func<T1, T2, TResult>> selectExpression,
            Expression<Func<T1, T2, bool>> whereLambda = null) where T1 : class, new();

        /// <summary> 
        ///多表查询
        /// </summary>
        Task<List<TResult>> QueryMuchAsync<T1, T2, T3, TResult>(
            Expression<Func<T1, T2, T3, object[]>> joinExpression,
            Expression<Func<T1, T2, T3, TResult>> selectExpression,
            Expression<Func<T1, T2, T3, bool>> whereLambda = null) where T1 : class, new();

        /// <summary> 
        ///多表查询
        /// </summary>
        Task<List<TResult>> QueryMuchAsync<T1, T2, T3, T4, TResult>(
            Expression<Func<T1, T2, T3, T4, object[]>> joinExpression,
            Expression<Func<T1, T2, T3, T4, TResult>> selectExpression,
            Expression<Func<T1, T2, T3, T4, bool>> whereLambda = null) where T1 : class, new();

        Task<List<TResult>> QueryMuchAsync<T1, T2, T3, T4, T5, TResult>(
            Expression<Func<T1, T2, T3, T4, T5, object[]>> joinExpression,
            Expression<Func<T1, T2, T3, T4, T5, TResult>> selectExpression,
            Expression<Func<T1, T2, T3, T4, T5, bool>> whereLambda = null) where T1 : class, new();

        Task<List<TResult>> QueryMuchAsync<T1, T2, T3, T4, T5, T6, TResult>(
            Expression<Func<T1, T2, T3, T4, T5, T6, object[]>> joinExpression,
            Expression<Func<T1, T2, T3, T4, T5, T6, TResult>> selectExpression,
            Expression<Func<T1, T2, T3, T4, T5, T6, bool>> whereLambda = null) where T1 : class, new();

        #endregion

        #region 新增

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="entities">实体（集合）</param>
        /// <returns></returns>
        Task<List<TEntity>> AddAsync(params TEntity[] entities);

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="entity">实体</param>
        /// <param name="insertColumns">指定要插入的列</param>
        /// <returns></returns>
        Task<TEntity> AddAsync(TEntity entity, Expression<Func<TEntity, object>> insertColumns);

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="entities">实体集合</param>
        /// <param name="insertColumns">指定要插入的列</param>
        /// <returns></returns>
        Task<List<TEntity>> AddAsync(TEntity[] entities, Expression<Func<TEntity, object>> insertColumns);

        #endregion

        #region 更新

        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="entity">实体</param>
        /// <param name="updateColumns">指定要更新的列</param>
        /// <param name="ignoreColumns">指定要忽略的列</param>
        /// <returns></returns>
        Task<bool> UpdateAsync(TEntity entity,
            Expression<Func<TEntity, object>> updateColumns = null,
            Expression<Func<TEntity, object>> ignoreColumns = null);

        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="entities">实体集合</param>
        /// <param name="updateColumns">指定要更新的列</param>
        /// <param name="ignoreColumns">指定要忽略的列</param>
        /// <returns></returns>
        Task<bool> UpdateAsync(TEntity[] entities,
            Expression<Func<TEntity, object>> updateColumns = null,
            Expression<Func<TEntity, object>> ignoreColumns = null);

        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="whereExpression">条件表达式</param>
        /// <param name="anonymous">要更新的值（匿名对象）</param>
        /// <returns></returns>
        Task<int> UpdateAsync(Expression<Func<TEntity, bool>> whereExpression, dynamic anonymous);

        #endregion

        #region 删除

        /// <summary>
        /// 删除(Support IPhantomEntity)
        /// </summary>
        /// <param name="entities">实体（集合）</param>
        /// <returns></returns>
        Task<int> DeleteAsync(params TEntity[] entities);

        /// <summary>
        /// 删除(Support IPhantomEntity)
        /// </summary>
        /// <param name="ids">主键（集合）</param>
        /// <returns></returns>
        Task<int> DeleteAsync(params TKey[] ids);

        / <summary>
        / 删除(Support IPhantomEntity)
        / </summary>
        / <param name="whereExpression">条件表达式</param>
        / <returns></returns>
        //Task<int> DeleteAsync(Expression<Func<TEntity, bool>> whereExpression);

        / <summary>
        / 删除(Support IPhantomEntity)
        / </summary>
        / <param name="strWhere">条件字符串</param>
        / <returns></returns>
        //Task<int> DeleteAsync(string strWhere);

        #endregion

        #region 更新或新增

        /// <summary>
        /// 更新或插入
        /// http://www.codeisbug.com/Doc/8/1172
        /// </summary>
        /// <param name="entity">实体</param>
        /// <param name="upsertColumns">指定要更新或插入的列</param>
        /// <param name="ignoreColumns">指定要忽略的列</param>
        /// <returns></returns>
        TEntity UpsertAsync(TEntity entity,
            Expression<Func<TEntity, object>> upsertColumns = null,
            Expression<Func<TEntity, object>> ignoreColumns = null);

        /// <summary>
        /// 更新或插入
        /// http://www.codeisbug.com/Doc/8/1172
        /// </summary>
        /// <param name="entities">实体集合</param>
        /// <param name="upsertColumns">指定要更新或插入的列</param>
        /// <param name="ignoreColumns">指定要忽略的列</param>
        /// <returns></returns>
        Task<List<TEntity>> UpsertAsync(List<TEntity> entities,
            Expression<Func<TEntity, object>> upsertColumns = null,
            Expression<Func<TEntity, object>> ignoreColumns = null);

        /// <summary>
        /// 更新或插入
        /// </summary>
        /// <param name="entity">实体</param>
        /// <param name="insertColumns">指定要插入的列</param>
        /// <param name="ignoreInsertColumns">指定要忽略的插入列</param>
        /// <param name="updateColumns">指定要更新的列</param>
        /// <param name="ignoreUpdateColumns">指定要忽略的更新列</param>
        /// <returns></returns>
        TEntity UpsertAsync(TEntity entity,
            Expression<Func<TEntity, object>> insertColumns = null,
            Expression<Func<TEntity, object>> ignoreInsertColumns = null,
            Expression<Func<TEntity, object>> updateColumns = null,
            Expression<Func<TEntity, object>> ignoreUpdateColumns = null);

        /// <summary>
        /// 更新或插入
        /// </summary>
        /// <param name="entities">实体集合</param>
        /// <param name="insertColumns">指定要插入的列</param>
        /// <param name="ignoreInsertColumns">指定要忽略的插入列</param>
        /// <param name="updateColumns">指定要更新的列</param>
        /// <param name="ignoreUpdateColumns">指定要忽略的更新列</param>
        /// <returns></returns>
        Task<List<TEntity>> UpsertAsync(List<TEntity> entities,
            Expression<Func<TEntity, object>> insertColumns = null,
            Expression<Func<TEntity, object>> ignoreInsertColumns = null,
            Expression<Func<TEntity, object>> updateColumns = null,
            Expression<Func<TEntity, object>> ignoreUpdateColumns = null);

        #endregion
    }

⭐ 通用实体

public abstract class Entity<TKey>
    {
        [SugarColumn(IsPrimaryKey = true)]
        public virtual TKey Id { get; set; }
    }

    public abstract class KdbndpEntity : Entity<Guid>, IPhantomEntity
    {
        public KdbndpEntity()
        {
            Id = GuidGenerator.Current.Create();
        }

        /// <summary>
        /// 创建时间
        /// </summary>
        [SugarColumn(ColumnName = "create_time", IsOnlyIgnoreInsert = true, IsOnlyIgnoreUpdate = true)]
        public virtual DateTime CreateTime { get; set; } = DateTime.Now;

        /// <summary>
        /// 更新时间
        /// </summary>
        [SugarColumn(ColumnName = "update_time", IsNullable = true, IsOnlyIgnoreInsert = true, IsOnlyIgnoreUpdate = true)]
        public virtual DateTime? UpdateTime { get; set; }

        [SugarColumn(ColumnName = SqlSugarConst.PhantomColumnName)]
        public bool IsDeleted { get; set; }
        public virtual string GetMainKey()
        {
            return this.Id.ToString();
        }
    }

⭐ 用户表实体

/// <summary>
    /// 用户表
    /// </summary>
    [SugarTable("sys_user")]
    public class User : KdbndpEntity
    {
        /// <summary>
        /// 用户名
        /// </summary>
        public string Username { get; set; }
        /// <summary>
        /// 昵称
        /// </summary>
        public string Nickname { get; set; }
        /// <summary>
        /// 手机
        /// </summary>
        public string Phone { get; set; }
        /// <summary>
        /// 邮箱
        /// </summary>
        public string Email { get; set; }
        /// <summary>
        /// 密码
        /// </summary>
        public string Password { get; set; }

    }

♉ 各个仓储实现

⭐ 用户表仓储接口（人大金仓）

    /// <summary>
    /// 用户表仓储接口
    /// </summary>
    public interface IUserRepository : IKdbndpRepository<User>
    {
    }

⭐ 用户表仓储（人大金仓）

public class UserRepository : Repository<User>, IUserRepository
    {
        public UserRepository(KdbndpOfWork unitOfWork) : base(unitOfWork)
        {

        }
    }

⭐ 用户表仓储接口（达梦）

    /// <summary>
    /// 用户表仓储接口
    /// </summary>
    public interface IUserRepository : IDMRepository<User>
    {
    }

⭐ 用户表仓储（达梦）

public class UserRepository : Repository<User>, IUserRepository
    {
        public UserRepository(DMOfWork unitOfWork) : base(unitOfWork)
        {

        }
    }

⭐ 用户表仓储接口（翰高）

    /// <summary>
    /// 用户表仓储接口
    /// </summary>
    public interface IUserRepository : IHGRepository<User>
    {
    }

⭐ 用户表仓储（翰高）

public class UserRepository : Repository<User>, IUserRepository
    {
        public UserRepository(HGOfWork unitOfWork) : base(unitOfWork)
        {

        }
    }

⭐ 多表查询

public List<UserModel> Query(UserQuery query)
        {
            return Db.Queryable<User, UserRole>((p, userRole) => p.RoleId == userRole.Id)
                     .WhereIF(!IsNullOrEmpty(query.Username), p => p.Username.Contains(query.Username, StringComparison.OrdinalIgnoreCase))
                     .WhereIF(!IsNullOrEmpty(query.Nickname), p => p.Nickname == query.Nickname)
                     .WhereIF(!IsNullOrEmpty(query.RoleName), (p, userRole) => userRole.Id.Contains(query.Id, StringComparison.OrdinalIgnoreCase))
                     .Select((p, unitType) =>
                     
                         new UserModel()
                         {
                             Username = p.Username,
                             Nickname = p.Nickname,
                             CreateTime = p.CreateTime,
                             Phone = p.Phone,
                             Password = p.Password,
                             Id = p.Id,
                             RoleId = userRole.Id.ToString(),
                             UpdateTime = p.UpdateTime,
                             Email=p.Email
                         }
                     ).ToList();
                     
        }

📙 结语

​ 本文主要介绍了SqlSugar使用工作单元模式操作多个数据库的实战，包括多类型MySQL、人大金仓、达梦等数据，或者同一种数据库的多个库。

文章中出现的任何错误请大家批评指出，一定及时修改。

希望写在这里的小伙伴能给个三连支持！

标签：
本文转载自: https://blog.csdn.net/baidu_33146219/article/details/127291753
版权归原作者 会敲键盘的肘子 所有， 如有侵权，请联系我们删除。
登录后发布评论

“猿创征文｜【国产数据库】一文学会应用SqlSugar工作单元模式操作多数据库”的评论:

还没有评论
关于作者
...
overfit同步小助手
文章同步
相关阅读

网络安全法-网络运行安全

使用selenium/drissionpage时如何阻止chrome自动跳转http到https

docker desktop 里部署的Open WebUI 管理员密码忘记了的处理方法

在ubuntu20.04中搭建onsite比赛运行环境

利用开源的低代码表单设计器FcDesigner高效管理和渲染复杂表单结构

Kafka学习笔记

【前端】浏览器输入url到页面呈现发生了什么？

文章导航