Net Core 3.1 实现SqlSugar多库操作

一、前言

二、关于SqlSugar

三、功能实现

1.项目创建

10.启动接口，验证数据连接及数据表的创建

一、前言

在开发过程中，会经常用到跨库查询的问题，对于相同类型的数据库，只需要添加一个数据连接，但是对于不同类型的数据库，还需要搭建不同数据库的环境，不论从开发效率还是项目后期维护方面，都是有诸多不便的，那么推荐使用SqlSugar实现多库（多租户）

二、关于SqlSugar

SqlSugar是一款老牌 .NET 开源ORM框架

.NET中唯一【全自动分表组件】+【读写分离】+【SAAS分库+多库事务+差异日志】+【大数据读写】官方完美整合的ORM框架
支持【低代码】+工作流
拥有重多【成熟案例】和良好的【生态】
支持.NET 百万级【大数据】写入、更新和读取
使用【最简单】，语法最爽的ORM、仓储、UnitOfWork、DbContext、丰富表达式AOP
支持 DbFirst、CodeFirst和【WebFirst】 3种模式开发
Github star 数仅次于EF 和 Dapper,每月nuget下载量超过1万
简单易用、功能齐全、高性能、轻量级、服务齐全、官网教程文档、有专业技术支持一天18小时服务
支持关系数据库：MySql、SqlServer、Sqlite、Oracle 、 postgresql、达梦、人大金仓、神通数据库、瀚高、Access 、MySqlConnector、华为 GaussDB、自定义扩展
支持时序数据库：QuestDb 适合几十亿数据分析统计、分表(性能最强时序数据库)

关于SqlSugar的教程及详细使用请参见SqlSugar ORM 5.X 官网、文档、教程 - SqlSugar 5x - .NET果糖网

三、功能实现

1.项目创建

创建.net core WebApi项目，创建项目结构如下

项目结构说明：

MySqlsugar：api接口

Common：通用方法，包括仓储注入、数据库实例化、appsettings配置读取等

Controller：接口文件

Sqlsugar.Business：业务类库

Models：数据表实体类

Repository：业务仓储

ShareDomain：枚举、Dto等数据

2.Nuget管理包引入

1)在Sqlsugar.Business项目中引入SqlSugar.IOC、SqlSugarCore

2)在MySqlsugar项目中引入System.Linq.Dynamic.Core

3)MySqlsugar项目添加对 Sqlsugar.Business项目的引用

3.数据表实体类创建

Models目录下创建

数据表基类


using System;
using System.Collections.Generic;
using System.Text;
using SqlSugar;
 
namespace Sqlsugar.Business.Models
{
    /// 
    /// 基类
    /// 
    public class BaseEntity
    {
        public BaseEntity()
        {
            CreateTime = DateTime.Now;
            IsDeleted = false;
        }
        /// 
        /// 主键ID
        /// 
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
        public long Id { get; set; }
        /// 
        /// 创建人
        /// 
        [SugarColumn(IsOnlyIgnoreUpdate = true)]
        public string CreateBy { get; set; }
        /// 
        /// 创建时间
        /// 
        [SugarColumn(IsOnlyIgnoreUpdate = true)]
        public DateTime CreateTime { get; set; }
        /// 
        /// 修改人
        /// 
        [SugarColumn(IsOnlyIgnoreInsert = true, IsNullable = true)]
        public string ModifyBy { get; set; }
        /// 
        /// 修改时间
        /// 
        [SugarColumn(IsOnlyIgnoreInsert = true, IsNullable = true)]
        public DateTime? ModifyTime { get; set; }
        /// 
        /// 是否删除
        /// 
        public bool IsDeleted { get; set; }
    }
}

分别创建人员、菜单、图书信息三个数据表实体


using System;
using System.Collections.Generic;
using System.Text;
using SqlSugar;
 
namespace Sqlsugar.Business.Models
{
    /// 
    /// 用户信息表
    /// 
    [SugarTable("Sys_UserInfo")]
    [Tenant("0")]
    public class UserInfo:BaseEntity
    {
        /// 
        /// 登录名
        /// 
        public string LoginName { get; set; }
        /// 
        /// 密码
        /// 
        public string PassWord { get; set; }
        /// 
        /// 姓名/昵称
        /// 
        public string UserName { get; set; }
        /// 
        /// 电子邮箱
        /// 
        [SugarColumn(IsNullable = true)]
        public string Email { get; set; }
        /// 
        /// 联系电话
        /// 
        [SugarColumn(IsNullable = true)]
        public string PhoneNum { get; set; }
        /// 
        /// 出生日期
        /// 
        [SugarColumn(IsNullable = true, ColumnDataType = "date")]
        public DateTime? BirthDate { get; set; }
        /// 
        /// 岗位/职位
        /// 
        [SugarColumn(IsNullable = true)]
        public string Post { get; set; }
        /// 
        /// 部门
        /// 
        [SugarColumn(IsNullable = true)]
        public long? DeptID { get; set; }
        /// 
        /// 角色
        /// 
        [SugarColumn(IsNullable = true, ColumnDataType = "nvarchar(max)")]
        public string RoleIds { get; set; }
        /// 
        /// 密码错误次数
        /// 
        [SugarColumn(IsNullable = true)]
        public int? ErrorCount { get; set; }
        /// 
        /// 锁定时间
        /// 
        [SugarColumn(IsNullable = true)]
        public DateTime? LockTime { get; set; }
    }
}


using System;
using System.Collections.Generic;
using System.Text;
using SqlSugar;
 
namespace Sqlsugar.Business.Models
{
    /// 
    /// 菜单信息表
    /// 
    [SugarTable("Sys_Menu")]
    [Tenant("0")]
    public class Menu:BaseEntity
    {
        /// 
        /// 菜单名称
        /// 
        public string MenuName { get; set; }
        /// 
        /// 路径
        /// 
        [SugarColumn(IsNullable =true)]
        public string MenuPath { get; set; }
    }
}


using System;
using System.Collections.Generic;
using System.Text;
using SqlSugar;
 
namespace Sqlsugar.Business.Models
{
    /// 
    /// 图书信息
    /// 
    [SugarTable("B_BookInfo")]
    [Tenant("1")]
    public class BookInfo:BaseEntity
    {
        /// 
        /// 图书名称
        /// 
        public string BookName { get; set; }
        /// 
        /// 作者
        /// 
        public string Author { get; set; }
    }
}

4.仓储

创建BaseRepository


using System;
using System.Collections.Generic;
using System.Text;
using System.Linq;
using System.Reflection;
using System.Linq.Expressions;
using Sqlsugar.Business.Models;
using Sqlsugar.Business.ShareDomain;
using SqlSugar;
using SqlSugar.IOC;
 
 
namespace Sqlsugar.Business.Repository
{
    /// 
    /// 基类仓储
    /// 
    /// 
    public class BaseRepository<T> : SimpleClient<T> where T : BaseEntity, new()
    {
        public ITenant itenant = null;//多租户事务
        public BaseRepository(ISqlSugarClient context = null) : base(context)
        {
            //通过特性拿到ConfigId
            var configId = typeof(T).GetCustomAttribute()?.configId;
            if (configId != null)
            {
                Context = DbScoped.SugarScope.GetConnectionScope(configId);//根据类传入的ConfigId自动选择
            }
            else
            {
                Context = context ?? DbScoped.SugarScope.GetConnectionScope(0);//没有默认db0
            }
            itenant = DbScoped.SugarScope;//处理多租户事务、GetConnection、IsAnyConnection等功能
            CreateDB(Context,configId.ToString());
        }
        private void CreateDB(ISqlSugarClient client,string configID)
        {
            client.DbMaintenance.CreateDatabase();//没有数据库的时候创建数据库
            var tableLists = client.DbMaintenance.GetTableInfoList();
            var files = System.IO.Directory.GetFiles(AppDomain.CurrentDomain.BaseDirectory, "Sqlsugar.Business.dll");
            if (files.Length > 0)
            {
                //Type[] types = Assembly.LoadFrom(files[0]).GetTypes().Where(it => it.BaseType == typeof(BaseEntity)).ToArray();
                Type[] types = Assembly.LoadFrom(files[0]).GetTypes().Where(it => it.BaseType == typeof(BaseEntity)&& it.GetCustomAttribute().configId.ToString()==configID).ToArray();
                foreach (var entityType in types)
                {
                    //创建数据表
                    string tableName = entityType.GetCustomAttribute().TableName;//根据特性获取表名称
                    //var configid = entityType.GetCustomAttribute()?.configId;//根据特性获取租户id
                    //configid = configid == null ? "0" : configid.ToString();
                    if (!tableLists.Any(p => p.Name == tableName))
                    {
                        //创建数据表包括字段更新
                        client.CodeFirst.InitTables(entityType);
                    }
                }
            }
        }
        /// 
        /// 新增
        /// 
        /// 
        /// 
        public bool Add(T t)
        {
            try
            {
                int rowsAffect = Context.Insertable(t).IgnoreColumns(true).ExecuteCommand();
                return rowsAffect > 0;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        /// 
        /// 批量新增
        /// 
        /// 
        /// 
        public bool Insert(List t)
        {
            try
            {
                int rowsAffect = Context.Insertable(t).ExecuteCommand();
                return rowsAffect > 0;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        /// 
        /// 插入设置列数据
        /// 
        /// 
        /// 
        /// 
        /// 
        public bool Insert(T parm, Expressionobject>> iClumns = null, bool ignoreNull = true)
        {
            try
            {
                int rowsAffect = Context.Insertable(parm).InsertColumns(iClumns).IgnoreColumns(ignoreNullColumn: ignoreNull).ExecuteCommand();
                return rowsAffect > 0;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        /// 
        /// 更新
        /// 
        /// 
        /// 
        /// 
        public bool Update(T entity, bool ignoreNullColumns = false)
        {
            try
            {
                int rowsAffect = Context.Updateable(entity).IgnoreColumns(ignoreNullColumns).ExecuteCommand();
                return rowsAffect >= 0;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        /// 
        /// 根据实体类更新指定列 eg：Update(dept, it => new { it.Status });只更新Status列，条件是包含
        /// 
        /// 
        /// 
        /// 
        /// 
        public bool Update(T entity, Expressionobject>> expression, bool ignoreAllNull = false)
        {
            try
            {
                int rowsAffect = Context.Updateable(entity).UpdateColumns(expression).IgnoreColumns(ignoreAllNull).ExecuteCommand();
                return rowsAffect >= 0;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        /// 
        /// 根据实体类更新指定列 eg：Update(dept, it => new { it.Status }, f => depts.Contains(f.DeptId));只更新Status列，条件是包含
        /// 
        /// 
        /// 
        /// 
        /// 
        public bool Update(T entity, Expressionobject>> expression, Expressionbool>> where)
        {
            try
            {
                int rowsAffect = Context.Updateable(entity).UpdateColumns(expression).Where(where).ExecuteCommand();
                return rowsAffect >= 0;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        /// 
        /// 更新指定列 eg：Update(w => w.NoticeId == model.NoticeId, it => new SysNotice(){ UpdateTime = DateTime.Now, Title = "通知标题" });
        /// 
        /// 
        /// 
        /// 
        public bool Update(Expressionbool>> where, Expression> columns)
        {
            try
            {
                int rowsAffect = Context.Updateable().SetColumns(columns).Where(where).RemoveDataCache().ExecuteCommand();
                return rowsAffect >= 0;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        /// 
        /// 事务 eg:var result = UseTran(() =>{SysRoleRepository.UpdateSysRole(sysRole);DeptService.DeleteRoleDeptByRoleId(sysRole.ID);DeptService.InsertRoleDepts(sysRole);});
        /// 
        /// 
        /// 
        public bool UseTran(Action action)
        {
            try
            {
                var result = Context.Ado.UseTran(() => action());
                return result.IsSuccess;
            }
            catch (Exception ex)
            {
                Context.Ado.RollbackTran();
                return false;
            }
        }
        /// 
        /// 删除
        /// 
        /// 主键id
        /// 是否真删除
        /// 
        public bool Delete(object[] ids, bool IsDelete = false)
        {
            int rowsAffect = 0;
            try
            {
                if (IsDelete)
                {
                    rowsAffect = Context.Deleteable().In(ids).ExecuteCommand();
                }
                else
                {
                    //假删除 实体属性有isdelete或者isdeleted 请升级到5.0.4.9+，（5.0.4.3存在BUG）
                    rowsAffect = Context.Deleteable().In(ids).IsLogic().ExecuteCommand();
                }
                return rowsAffect >= 0;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        /// 
        /// 根据id获取数据
        /// 
        /// 主键值
        /// 泛型实体
        public T GetEntityById(long id)
        {
            return Context.Queryable().First(p => p.Id == id);
        }
        /// 
        /// 数据是否存在
        /// 
        /// 
        /// 
        public bool IsExists(Expressionbool>> expression)
        {
            return Context.Queryable().Where(expression).Any();
        }
        /// 
        /// 获取所有数据
        /// 
        /// 
        public List<T> GetAll()
        {
            return Context.Queryable().ToList();
        }
        /// 
        /// 根据查询条件获取数据
        /// 
        /// 
        /// 
        public List<T> GetListByWhere(Expressionbool>> expression)
        {
            return Context.Queryable().Where(expression).ToList();
        }
        /// 
        /// 根据查询条件获取数据
        /// 
        /// 
        /// 排序字段
        /// 排序方式
        /// 
        public List GetList(Expressionbool>> expression, Expressionobject>> orderFiled, OrderByType orderEnum = OrderByType.Asc)
        {
            return Context.Queryable().Where(expression).OrderByIF(orderEnum == OrderByType.Asc, orderFiled, OrderByType.Asc).OrderByIF(orderEnum == OrderByType.Desc, orderFiled, OrderByType.Desc).ToList();
        }
        /// 
        /// 获取分页数据
        /// 
        /// 
        /// 
        /// 
        /// 
        public PagedInfo GetPageList(Expressionbool>> expression, int pageIndex, int PageSize)
        {
            int totalCount = 0;
            var result = Context.Queryable().Where(expression).ToPageList(pageIndex, PageSize, ref totalCount);
            var pageResult = new PagedInfo();
            pageResult.Result = result;
            pageResult.TotalNum = totalCount;
            return pageResult;
        }
        /// 
        /// 获取分页数据
        /// 
        /// 
        /// 
        /// 
        /// 
        public PagedInfo GetPageListAsync(Expressionbool>> expression, int pageIndex, int PageSize)
        {
            RefAsync<int> totalCount = 0;
            var result = Context.Queryable().Where(expression).ToPageListAsync(pageIndex, PageSize, totalCount);
            var pageResult = new PagedInfo();
            pageResult.Result = result.Result;
            pageResult.TotalNum = totalCount;
            return pageResult;
        }
        /// 
        /// 获取分页数据
        /// 
        /// 
        /// 
        /// 
        /// 
        /// 
        /// 
        public PagedInfo GetPageList(Expressionbool>> expression, int pageIndex, int PageSize, Expressionobject>> orderFiled, OrderByType orderEnum = OrderByType.Asc)
        {
            int totalCount = 0;
            var result = Context.Queryable().Where(expression).OrderByIF(orderEnum == OrderByType.Asc, orderFiled, OrderByType.Asc).OrderByIF(orderEnum == OrderByType.Desc, orderFiled, OrderByType.Desc)
                .ToPageList(pageIndex, PageSize, ref totalCount);
            var pageResult = new PagedInfo();
            pageResult.Result = result;
            pageResult.TotalNum = totalCount;
            return pageResult;
        }
        /// 
        /// 获取分页数据
        /// 
        /// 
        /// 
        /// 
        /// 
        /// 
        /// 
        public PagedInfo GetPageListAsync(Expressionbool>> expression, int pageIndex, int PageSize, Expressionobject>> orderFiled, OrderByType orderEnum = OrderByType.Asc)
        {
            RefAsync<int> totalCount = 0;
            var result = Context.Queryable().Where(expression).OrderByIF(orderEnum == OrderByType.Asc, orderFiled, OrderByType.Asc).OrderByIF(orderEnum == OrderByType.Desc, orderFiled, OrderByType.Desc)
                .ToPageListAsync(pageIndex, PageSize, totalCount);
            var pageResult = new PagedInfo();
            pageResult.Result = result.Result;
            pageResult.TotalNum = totalCount;
            return pageResult;
        }
    }
}

分别创建用户和图书的仓储


using System;
using System.Collections.Generic;
using System.Text;
using Sqlsugar.Business.Models;
 
namespace Sqlsugar.Business.Repository
{
    public class UserInfoRepository:BaseRepository<UserInfo>
    {
        /// 
        /// 保存数据
        /// 
        /// 
        /// 
        /// 
        public bool Save(UserInfo item, string userid)
        {
            var model = GetById(item.Id);
            if (model == null)
            {
                item.CreateTime = DateTime.Now;
                item.CreateBy = userid;
                return Add(item);
            }
            else
            {
                item.ModifyBy = userid;
                item.ModifyTime = DateTime.Now;
                return Update(item, false);
            }
        }
        /// 
        /// 获取用户信息
        /// 
        /// 
        /// 
        public UserInfo GetEntity(long id)
        {
            return GetById(id);
        }
    }
}


using System;
using System.Collections.Generic;
using System.Text;
using Sqlsugar.Business.Models;
 
namespace Sqlsugar.Business.Repository
{
    public class BookInfoRepository : BaseRepository<BookInfo>
    {
        /// 
        /// 保存数据
        /// 
        /// 
        /// 
        /// 
        public bool Save(BookInfo item, string userid)
        {
            var model = GetById(item.Id);
            if (model == null)
            {
                item.CreateTime = DateTime.Now;
                item.CreateBy = userid;
                return Add(item);
            }
            else
            {
                item.ModifyBy = userid;
                item.ModifyTime = DateTime.Now;
                return Update(item, false);
            }
        }
        /// 
        /// 获取用户信息
        /// 
        /// 
        /// 
        public BookInfo GetEntity(long id)
        {
            return GetById(id);
        }
    }
}

5.appsettings数据读取

MySqlsugar中Common中创建AppSettings.cs


using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
 
namespace MySqlsugar.Common
{
    public class AppSettings
    {
        static IConfiguration Configuration { get; set; }
 
        public AppSettings(IConfiguration configuration)
        {
            Configuration = configuration;
        }
 
        /// 
        /// 封装要操作的字符
        /// 
        /// 节点配置
        /// 
        public static string App(params string[] sections)
        {
            try
            {
                if (sections.Any())
                {
                    return Configuration[string.Join(":", sections)];
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
 
            return "";
        }
 
        /// 
        /// 递归获取配置信息数组
        /// 
        /// 
        /// 
        /// 
        public static List<T> App<T>(params string[] sections)
        {
            List list = new List();
            // 引用 Microsoft.Extensions.Configuration.Binder 包
            Configuration.Bind(string.Join(":", sections), list);
            return list;
        }
        public static T Bind<T>(string key, T t)
        {
            Configuration.Bind(key, t);
 
            return t;
        }
 
        public static T GetAppConfig<T>(string key, T defaultValue = default)
        {
            T setting = (T)Convert.ChangeType(Configuration[key], typeof(T));
            var value = setting;
            if (setting == null)
                value = defaultValue;
            return value;
        }
 
        /// 
        /// 获取配置文件 
        /// 
        /// eg: WeChat:Token
        /// 
        public static string GetConfig(string key)
        {
            return Configuration[key];
        }
    }
}

Startup.cs中配置

6.数据库连接配置

在appsettings.json中添加数据库连接配置


  /*数据库连接配置
   ConnectionString：连接字符串
   DbType：数据库类型 支持MySql = 0,SqlServer = 1,Sqlite = 2,Oracle = 3,PostgreSQL = 4,Dm = 5,Kdbndp = 6,Oscar = 7,MySqlConnector = 8,Access = 9,OpenGauss = 10,Custom = 900
   ConfigId：租户id
   IsAutoCloseConnection：自动释放和关闭数据库连接，如果有事务事务结束时关闭，否则每次操作后关闭
  */
  "ConnectionConfigs": [
    {
      "ConnectionString": "Data Source=.;User ID=sa;Password=123456;Initial Catalog=AdminManage",
      "DbType": 1,
      "ConfigId": "0",
      "IsAutoCloseConnection": true
    },
    {
      "ConnectionString": "Data Source=.;User ID=sa;Password=123456;Initial Catalog=Book",
      "DbType": 1,
      "ConfigId": "1",
      "IsAutoCloseConnection": true
    }
  ]

7.SqlSugar IOC注入

在Common中创建SqlsugarSetup.cs


using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Threading.Tasks;
using System.Reflection;
using SqlSugar;
using SqlSugar.IOC;
using Sqlsugar.Business.Models;
using Microsoft.Extensions.DependencyInjection;
 
namespace MySqlsugar.Common
{
    public static class SqlsugarSetup
    {
        public static void AddSqlsugarSetup()
        {
            List connectionConfigs = AppSettings.App(new string[] { "ConnectionConfigs" });
            //sqlsugar注册
            SugarIocServices.AddSqlSugar(connectionConfigs);
            //多租户日志打印设置/全局过滤器
            SugarIocServices.ConfigurationSugar(db =>
            {
                connectionConfigs.ForEach(item =>
                {
                    SetQueryFilter(db.GetConnection(item.ConfigId));
                    SetLog(db,item.ConfigId);
                });
            });
        }
        //日志
        private static void SetLog(SqlSugarClient db,string configid)
        {
            db.GetConnection(configid).Aop.OnLogExecuting = (sql, para) => {
                //var param = para.Select(it => it.Value).ToArray();
                string sqlQuery=UtilMethods.GetSqlString(DbType.SqlServer, sql, para);
                Console.WriteLine(sqlQuery);
                Console.WriteLine();
            };
        }
        /// 
        /// 添加全局过滤器
        /// 
        /// 
        private static void SetQueryFilter(SqlSugarProvider provider)
        {
            //添加全局过滤器
            var files = System.IO.Directory.GetFiles(AppDomain.CurrentDomain.BaseDirectory, "Sqlsugar.Business.dll");
            if (files.Length > 0)
            {
                Type[] types = Assembly.LoadFrom(files[0]).GetTypes().Where(it => it.BaseType == typeof(BaseEntity)).ToArray();
                foreach (var entityType in types)
                {
                    var lambda = System.Linq.Dynamic.Core.DynamicExpressionParser.ParseLambda(
                        new[] { Expression.Parameter(entityType, "it") },
                        typeof(bool), $"{nameof(BaseEntity.IsDeleted)} ==  @0",
                        false);
                    provider.QueryFilter.Add(new TableFilterItem<object>(entityType, lambda, true)); //将Lambda传入过滤器
                }
            }
        }
    }
}

Startup.cs中

8.仓储注入

Common中创建ConfigRepository.cs


using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.Extensions.DependencyInjection;
using Sqlsugar.Business.Repository;
 
namespace MySqlsugar.Common
{
    /// 
    /// 仓储注入
    /// 
    public static class ConfigRepository
    {
        public static void ConfigureServices(IServiceCollection services)
        {
            services.AddScoped();
            services.AddScoped();
        }
    }
}

Startup.cs中

9.Controller创建

分别创建用户和图书两个接口文件

10.启动接口，验证数据连接及数据表的创建

接口启动分别调用用户和图书接口

接口调用完成，会自动创建相关数据库及数据表

相关阅读:
玩一玩Spring容器
 CoppeliaSim机器人模拟器与Matlab Simulink环境
 element-UI 列表渲染图片，后台回传Base64
ASP.NET Core - 选项系统之源码介绍
 【Redis】# 常见报错Unsatisfied dependency、设置密码、主从配置
 在Oracle中创建DBLINK
uboot源码分析(基于S5PV210)之uboot的命令体系与环境变量
 cdn与云服务器有什么区别
 代码随想录-029-541.反转字符串II
美创科技获通信网络安全服务能力评定（应急响应一级）认证！
原文地址：https://blog.csdn.net/liwan09/article/details/126124716