最近为了整合公司项目中间件API的开发，耗费了不少人力，其中涉及到多个且不同类型的数据库（不知道之前的项目立案，数据库到底是怎么设计，整的我写一些业务逻辑得写好几套代码，简直不要太恶心）
因为之前有接触过sqlsuagr orm框架，所以用sqlsuagr 做了sqlsugar 多租户设置（库与实体一对一，库与实体一对多，实体自动生成带有模型验证、注解、Tenant特性等）
下面简单介绍一下sqlsuagr orm框架
sqlsuagr：
sqlsuagr是国产orm框架且官网社区活跃度高,文档齐全上手快，更新时间久，性能听说比EFcore要好（千万级数据量），中型数据量感觉差别不大。
EFcore：
EFcore是微软开发的，专门为了配合.Net开发，可以说是和.NET 系列配套。
两者各有千秋
sqlsuagr使用：
添加NuGet包，注意依赖版本
修改配置文件appsettings.json
-
DbFirst
实体自动生成带有模型验证、注解、Tenant特性【这一块可以单独写一个层】
/// <summary>
/// 初始化SqlSugarClient
/// </summary>
/// <returns>返回SqlSugarClient对象</returns>
public static SqlSugarClient GetInstance(DbType dbType)
{
var strConnectionString = string.Empty;
switch (dbType)
{
case DbType.SqlServer:
//strConnectionString = @"Server=DESKTOP-9GRFFRR;database=Test_Demo;Trusted_Connection=True;MultipleActiveResultSets=True;";
strConnectionString = @"server = IP; database =ERP_PROD; uid = dbadmin; pwd = cltest22; Connect Timeout = 1200;";
break;
case DbType.Oracle:
strConnectionString = @"Data Source= (DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST =IP)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME =LABELING)));User ID=label;Password=label20221123;";
//strConnectionString = "Data Source= (DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST =localhost)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME =ORCL)));User ID=TEST;Password=TEST;";
break;
case DbType.MySql:
strConnectionString = @"Server=DESKTOP-9GRFFRR;database=Test_Demo;Trusted_Connection=True;MultipleActiveResultSets=True;";
break;
}
db = new SqlSugarClient(new ConnectionConfig()
{
ConnectionString = strConnectionString,
DbType = dbType,
IsAutoCloseConnection = true
});
return db;
}
调用生成方法
var modelpath = @“Models”; 存放路径
Dictionary<string, string>
(“库名”, “表名”); ALL=库所有表
public static void Main(string[] args)
{
try
{
Console.WriteLine("Hello World!");
var modelpath = @"Models\";
var path = Directory.GetCurrentDirectory();
path = path.Substring(0, path.IndexOf(@"\bin"));
path = $"{path.Substring(0, path.LastIndexOf(@"\") + 1)}{modelpath}";
Dictionary<string, string> tabledic = new Dictionary<string, string>();
//("库名", "表名"); ALL=库所有表
//tabledic.Add("Test_Demo","PPACKINFOR");
tabledic.Add("ERP_PROD", "ALL");
//tabledic.Add("TEST2", "USER_INFOR2000000");
//tabledic.Add("TEST3", "USER_INFOR3000001");
//tabledic.Add("TEST4", "USER_INFOR4000002");
//tabledic.Add("TEST5", "USER_INFOR5000003");
DbFirst(DbType.SqlServer, path, tabledic);
Console.WriteLine("生成成功");
//Console.WriteLine(Msg.ThisExist);
Console.ReadKey();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
根据表生产实体
/// <summary>
/// 根据表生产实体
/// </summary>
/// <param name="path"></param>
public static void DbFirst(DbType dbType, string path, Dictionary<string, string> tabledic)
{
List<string> basename = tabledic.Keys.ToList();
List<string> tablename = tabledic.Values.ToList();
//db.DbFirst.IsCreateAttribute().CreateClassFile(path);
var db = GetInstance(dbType);
//ALL所有表
List<DbTableInfo> list = db.DbMaintenance.GetTableInfoList().Where(x => tablename.Contains("ALL")? x.Name.IsNormalized() : tablename.Contains(x.Name.Trim().ToUpper())).ToList();
//所有视图
List<DbTableInfo> viewList = db.DbMaintenance.GetViewInfoList();
var allList = list.Concat(viewList);
//循环所有的表和视图 他们属于同一个类 DbTableInfo
foreach (DbTableInfo table in allList)
{
//首字母转大写
string table_name = table.Name.Substring(0, 1).ToUpper() + table.Name.Substring(1).ToLower().Replace("_", "");
//映射表增加 实体名称 和表名称
db.MappingTables.Add(table_name, table.Name);
//根据表名 获取所有表字段
List<DbColumnInfo> dd = db.DbMaintenance.GetColumnInfosByTableName(table.Name);
var @base = tabledic.FirstOrDefault(q => tablename.Contains("ALL") ? q.Value == "ALL" : q.Value == table.Name).Key;
foreach (DbColumnInfo item in dd)
{
string columnname = item.DbColumnName.ToLower();
//映射字段添加 （字段名，字段名，表名）
db.MappingColumns.Add(columnname, columnname, table_name);
}
db.DbFirst.
SettingClassTemplate(old =>
{
string snp = "\r\n ";
var sugartable = GetClassTemplate().Replace("{SugarTable}", @$"{snp}[Tenant(""{@base}"")]{snp}[SugarTable(""{table.Name}"",""{@base}"")]");
return sugartable;
})
.SettingNamespaceTemplate(old =>
{
return old;
})
.SettingPropertyDescriptionTemplate(old =>
{
//自定义的模板
return old;//GetPropertyDescriptionTemplate();
})
.SettingPropertyTemplate((columns, temp, type) => {
string comp = "\r\n ";
var columnattribute = $"{comp}[SugarColumn({{0}})]";
List<string> attributes = new List<string>();
if (columns.IsPrimarykey)
attributes.Add("IsPrimaryKey=true");
if (columns.IsIdentity)
attributes.Add("IsIdentity=true");
if (attributes.Count == 0)
columnattribute = string.Empty;
List<string> customattributes = new List<string>();
if (!columns.IsNullable)
customattributes.Add($@"{comp}[Required(ErrorMessage =""{string.Format(Msg.Required, columns.DbColumnName)}"" )]");
if (!columns.DataType.ToLower().Equals("int"))
customattributes.Add($@"{comp}[StringLength({columns.Length}, ErrorMessage =""{string.Format(Msg.Length, columns.DbColumnName, columns.Length)}"" )]");
var t= temp.Replace("{PropertyType}", type)
.Replace("{PropertyName}", columns.DbColumnName)
.Replace("{SugarColumn}", attributes.Count > 0 ? string.Format(columnattribute, string.Join(",", attributes)) : string.Join("", customattributes));
return t;
})
.SettingConstructorTemplate(old =>
{
return old;
}).IsCreateAttribute().Where(table.Name).CreateClassFile(path);
}
}
生成模板（带命名空间，模型验证，及一些特性）
/// <summary>
/// 命名空间的模板
/// </summary>
/// <returns></returns>
public static string GetClassTemplate()
{
return @"using System.ComponentModel.DataAnnotations;
{using}
namespace {Namespace}
{
{ClassDescription}{SugarTable}
public partial class {ClassName}
{
public {ClassName}() { }
{Constructor}{PropertyName}
}
}
";
}
/// <summary>
/// 字段的模板
/// </summary>
/// <returns></returns>
public static string GetPropertyDescriptionTemplate()
{
return @"
/// <summary>
/// Remark:{PropertyDescription}
/// Default:{DefaultValue}
/// Nullable:{IsNullable}
/// </summary>";
}
如图所示已经批量生成了并且待有模型验证，主键，表特性
-
注入ISqlSugarClient 数据库链接
具体教程
参考
public void ConfigureServices(IServiceCollection services)
{
services.AddControllers();
services.AddSwaggerGen(c =>
{
c.SwaggerDoc("v1", new OpenApiInfo { Title = "WebAPI", Version = "v1", Description = "WebApi接口" });
var xmlFilename = $"{Assembly.GetExecutingAssembly().GetName().Name}.xml";
c.IncludeXmlComments(Path.Combine(AppContext.BaseDirectory, xmlFilename), true);
});
//注册
//禁用自带模型验证
services.Configure<ApiBehaviorOptions>(options =>
{
options.SuppressModelStateInvalidFilter = true;
});
//大概有一下类型 AddScoped、AddSingleton、AddTransient（生命周期不同） 注册 方法、实体、 或接口
//services.方法();
//services.AddScoped(实体);
//services.AddTransient(实体);
//services.AddSingleton(实体);
//services.AddScoped<接口, 接口实现类>();
//services.AddTransient<接口, 接口实现类>();
//services.AddSingleton<接口, 接口实现类>();
}
多租户 （多个数据库注入） 并且添加AOP
参考1
参考2
参考3
/// <summary>
/// SqlSugar 启动服务
/// </summary>
public static class SqlsugarSetup
{
public static void AddSqlsugarSetup(this IServiceCollection services)
{
var DBConfig = MutiInitConn();
//后台固定所有库
//var configConnection = new List<ConnectionConfig>
//{
// new ConnectionConfig(){ConfigId="A",DbType=DbType.SqlServer,ConnectionString="",IsAutoCloseConnection=true},
// /*........*/
// new ConnectionConfig(){ConfigId="A",DbType=DbType.SqlServer,ConnectionString="",IsAutoCloseConnection=true}
//};
//appsettings.js 动态获取
var configConnection = new List<ConnectionConfig>();
DBConfig.DBS.ForEach(m =>
{
configConnection.Add(new ConnectionConfig()
{
ConfigId = m.ConnId/*.ToLower()*/,
ConnectionString = m.Connection,
DbType = (DbType)m.DBType,
IsAutoCloseConnection = true,
MoreSettings = new ConnMoreSettings()
{
IsAutoRemoveDataCache = true
}
//InitKeyType = InitKeyType.SystemTable
});
});
//多租户循环添加AOP
SqlSugarClient sqlSugar = new SqlSugarClient(configConnection);
configConnection.ForEach(c =>
{
SqlSugarProvider client = sqlSugar.GetConnection(c.ConfigId);
//每次Sql执行前事件
client.Aop.OnLogExecuting = (sql, pars) =>
{
var queryString = new KeyValuePair<string, SugarParameter[]>(sql, pars);
if (sql.StartsWith("UPDATE") || sql.StartsWith("INSERT"))
{
Console.ForegroundColor = ConsoleColor.Blue;
Console.WriteLine($"==============新增/修改操作==============");
Console.WriteLine(ToSqlExplain.GetSql(queryString));
}
if (sql.StartsWith("DELETE"))
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine($"==============删除操作==============");
}
if (sql.StartsWith("SELECT"))
{
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine($"==============查询操作==============");
}
Console.WriteLine(ToSqlExplain.GetSql(queryString));
Console.ForegroundColor = ConsoleColor.White;
};
//每次Sql执行后事件
client.Aop.OnLogExecuted = (sql, pars) =>
{
//执行时间超过10秒
if (client.Ado.SqlExecutionTime.TotalSeconds > 10)
{
Console.WriteLine(sql);
}
};
//SQL报错
client.Aop.OnError = (exp) =>
{
Console.WriteLine(exp.Sql);
};
});
//SqlSugarScope(单列模式 ) 用AddSingleton
//多个连接对象注入服务，如果有事务操作必须采用 AddScoped
services.AddSingleton<ISqlSugarClient>(sqlSugar);
}
//public static DbConfig MutiConnectionString => MutiInitConn();
/// <summary>
/// 解析appsettings
/// </summary>
/// <returns></returns>
public static DbConfig MutiInitConn()
{
var dBConnection = new DbConfig();
var listdatabase = new List<DBS>();
string Path = "appsettings.json";
using (var file = new StreamReader(Path))
using (var reader = new JsonTextReader(file))
{
var jObj = (JObject)JToken.ReadFrom(reader);
dBConnection.MainDB = jObj["MainDB"].ObjToString();
dBConnection.MutiDBEnabled = jObj["MutiDBEnabled"].ObjToBool();
if (!string.IsNullOrWhiteSpace("DBS"))
{
var secJt = jObj["DBS"];
if (secJt != null)
{
for (int i = 0; i < secJt.Count(); i++)
{
if (secJt[i]["Enabled"].ObjToBool())
{
listdatabase.Add(new DBS()
{
ConnId = secJt[i]["ConnId"].ObjToString(),
Connection = secJt[i]["Connection"].ObjToString(),
DBType = (secJt[i]["DBType"].ObjToInt()),
});
}
}
}
}
dBConnection.DBS = listdatabase;
return dBConnection;
}
}
/// <summary>
/// 拼接出完整的Sql，方便查看
/// </summary>
public class ToSqlExplain
{
public static string GetSql(KeyValuePair<string, SugarParameter[]> queryString)
{
var sql = queryString.Key;//sql语句
var par = queryString.Value;//参数
//字符串替换MethodConst1x会替换掉MethodConst1所有要从后往前替换,不能用foreach,后续可以优化
for (int i = par.Length - 1; i >= 0; i--)
{
if (par[i].ParameterName.StartsWith("@") && par[i].ParameterName.Contains("UnionAll"))
{
sql = sql.Replace(par[i].ParameterName, par[i].Value.ToString());
}
}
for (int i = par.Length - 1; i >= 0; i--)
{
if (par[i].ParameterName.StartsWith("@Method"))
{
sql = sql.Replace(par[i].ParameterName, "'" + par[i].Value.ToString() + "'");
}
}
for (int i = par.Length - 1; i >= 0; i--)
{
if (par[i].ParameterName.StartsWith("@Const"))
{
sql = sql.Replace(par[i].ParameterName, par[i].Value.ToString());
}
}
for (int i = par.Length - 1; i >= 0; i--)
{
if (par[i].ParameterName.StartsWith("@"))
{
//值拼接单引号 拿出来的sql不会报错
sql = sql.Replace(par[i].ParameterName, "'" + Convert.ToString(par[i].Value) + "'");
}
}
return sql;
}
}
}
ConfigureServices里面注入AddSqlsugarSetup()
services.AddSqlsugarSetup();
Controllers 构造方法应用实现服务
/// <summary>
/// Controller
/// </summary>
[ApiController]
[Route("[controller]/[action]")]
public class ProcessController : ControllerBase
{
private readonly ILogger<ProcessController> _logger;
private readonly IServices _services;
public ProcessController(ILogger<ProcessController> logger, IServices services)
{
_logger = logger;
_services = services;
}
[HttpPost]
public List<Ppackinfor> Select(Ppackinfor ppackinfor)
{
var list = _services.ListTentity<Ppackinfor>();
return list;
}
}
IServices接口
public interface IServices
{
List<Ppackinfor> List();
List<TEntity> ListTentity<TEntity>();
}
接口实现类
多租户
因为是设置多租户(跨库) 所以增删该查会发生一些方法改变 //根据特性直接CRUD var
list=db.QueryableWithAttrWithAttr().ToList();//5.0.9.1 全自动切换库查询
db.InsertWithAttr(list).ExecuteCommand() ;//5.0.9.1 全自动切换库插入
db.UpdateWithAttr(list).ExecuteCommand() ;//5.0.9.1 全自动切换库更新
db.DeleteableWithAttr(list).ExecuteCommand() ;//5.0.9.1 全自动切换库删除
sqlsugar 多租户设置AOP
public class Services : IServices
{
private SqlSugarClient _db;
public Services(ISqlSugarClient db)
{
_db = db as SqlSugarClient;
}
public List<TEntity> ListTentity<TEntity>()
{
var list = _db.QueryableWithAttr<TEntity>().ToList();
return list;
}
这个时候已经完成的跨库查询，并且实现了AOP