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

最新推荐文章于 2024-08-25 20:18:05 发布

2401_84572976

最新推荐文章于 2024-08-25 20:18:05 发布

阅读量367

点赞数 3

分类专栏：程序员文章标签：大数据面试学习

本文链接：https://blog.csdn.net/2401_84572976/article/details/138765601

版权

程序员专栏收录该内容

60 篇文章 1 订阅

订阅专栏

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化资料的朋友，可以戳这里获取

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！

♊ 工作单元模式/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>


![img](https://img-blog.csdnimg.cn/img_convert/42b6a75d43da0512e640e5dcca3f3baa.png)
![img](https://i-blog.csdnimg.cn/blog_migrate/01ee8e15abc2d40a5a66953daec3d409.png)

**网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。**

**[需要这份系统化资料的朋友，可以戳这里获取](https://bbs.csdn.net/forums/4f45ff00ff254613a03fab5e56a57acb)**


**一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！**

        /// <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>


[外链图片转存中...(img-kQcrc8iW-1715515844832)]
[外链图片转存中...(img-oPabb2v2-1715515844832)]

**网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。**

**[需要这份系统化资料的朋友，可以戳这里获取](https://bbs.csdn.net/forums/4f45ff00ff254613a03fab5e56a57acb)**


**一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！**