Net Core 3.1 实现SqlSugar多库操作

目录

一、前言

二、关于SqlSugar

三、功能实现

1.项目创建

2.Nuget管理包引入

3.数据表实体类创建

4.仓储

5.appsettings数据读取

6.数据库连接配置

7.SqlSugar IOC注入

8.仓储注入

9.Controller创建

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" />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 : SimpleClient where T : BaseEntity, new(){public ITenant itenant = null;//多租户事务public BaseRepository(ISqlSugarClient context = null) : base(context){//通过特性拿到ConfigIdvar 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, Expression<Func> 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, Expression<Func> 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, Expression<Func> expression, Expression<Func> 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(Expression<Func> where, Expression<Func> 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(Expression<Func> expression){return Context.Queryable().Where(expression).Any();}/// 
/// 获取所有数据/// 
/// public List GetAll(){return Context.Queryable().ToList();}/// 
/// 根据查询条件获取数据/// 
/// /// public List GetListByWhere(Expression<Func> expression){return Context.Queryable().Where(expression).ToList();}/// 
/// 根据查询条件获取数据/// 
/// /// 排序字段/// 排序方式/// public List GetList(Expression<Func> expression, Expression<Func> 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(Expression<Func> 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(Expression<Func> expression, int pageIndex, int PageSize){RefAsync 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(Expression<Func> expression, int pageIndex, int PageSize, Expression<Func> 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(Expression<Func> expression, int pageIndex, int PageSize, Expression<Func> orderFiled, OrderByType orderEnum = OrderByType.Asc){RefAsync 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{/// 
/// 保存数据/// 
/// /// /// 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{/// 
/// 保存数据/// 
/// /// /// 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 App(params string[] sections){List list = new List();// 引用 Microsoft.Extensions.Configuration.Binder 包Configuration.Bind(string.Join(":", sections), list);return list;}public static T Bind(string key, T t){Configuration.Bind(key, t);return t;}public static T GetAppConfig(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