目录
该文档是配置SqlSugar多租户和加仓储教程。使用 SqlSugar 添加仓储层可以带来多方面的优势,尤其在提高代码的可维护性、可测试性和开发效率方面,提升代码的整体质量,促进团队合作,并降低维护成本。这对于长期发展的项目来说尤为重要。
1.添加包
添加 SqlSugarCore 的NuGet包。
操作:点击打开到要添加的项目 → 依赖项 → 包 → 右键点击 管理NuGet包
→ 搜索 SqlSugarCore → 安装
2.配置连接字符串
点击到添加的项目 → 右键添加 → 新建项 → 右上角搜索 json → 修改名字为 dbsettings →添加
添加自己的数据库连接
{
//数据库连接字符串
"ConnectionStrings": {
"DefaultDbString": "Server=你的数据库地址;Port=数据库端口;Database=数据库名称;User ID=用户;Password=密码;SslMode=Required;",
"DefaultDbNumber": 0,
"DefaultDbType": "MySql",
"DbConfigs": [
{
"DbNumber": "1",
"DbType": "SqlServer",
"DbString": "Server=你的数据库地址;Initial Catalog=数据库名称;User ID=用户;Password=密码;Encrypt=True;TrustServerCertificate=True;"
},
{
"DbNumber": 2,
"DbType": "MySql",
"DbString": "Server=你的数据库地址;Port=数据库端口;Database=数据库名称;User ID=用户;Password=密码;SslMode=Required;"
}
]
}
}
案例如下
3.配置SqlSugar
3.1.添加基础类
添加 DbConnEnum 枚举、ConnectionStringsOptions 类、DbConfig 类
public enum DbConnEnum
{
/// <summary>
/// SqlServer
/// </summary>
[Description("Mysql")]
Default = 0,
/// <summary>
/// Mysql
/// </summary>
[Description("Sqlserver")]
SystemSqlserver = 1,
/// <summary>
/// Mysql
/// </summary>
[Description("Mysql")]
SystemMysql = 2,
}
/// <summary>
/// 数据库配置
/// </summary>
public class ConnectionStringsOptions
{
/// <summary>
/// 默认数据库编号
/// </summary>
public int DefaultDbNumber { get; set; }
/// <summary>
/// 默认数据库类型
/// </summary>
public string DefaultDbType { get; set; }
/// <summary>
/// 默认数据库连接字符串
/// </summary>
public string DefaultDbString { get; set; }
/// <summary>
/// 业务库集合
/// </summary>
public List<DbConfig> DbConfigs { get; set; }
}
/// <summary>
/// 数据库参数
/// </summary>
public class DbConfig
{
/// <summary>
/// 数据库编号
/// </summary>
public string DbNumber { get; set; }
/// <summary>
/// 数据库类型
/// </summary>
public string DbType { get; set; }
/// <summary>
/// 数据库连接字符串
/// </summary>
public string DbString { get; set; }
}
案例如下
3.2.添加方法
3.2.1.读取配置方法
添加获取配置方法
/// <summary>
/// 全局配置
/// </summary>
public static class Config
{
/// <summary>
/// 从指定的 JSON 配置文件中读取配置,并反序列化为指定类型
/// </summary>
/// <typeparam name="T">目标配置类型(如 RedisSettings、DatabaseSettings 等)</typeparam>
/// <param name="fileName">JSON 配置文件名(如 "appsettings.json")</param>
/// <param name="sessions">配置节点名称(如 "RedisSettings")</param>
/// <returns>返回绑定后的强类型配置对象</returns>
public static T GetSetting<T>(string fileName, string sessions)
{
//创建 ConfigurationBuilder 实例,用于构建配置
var builder = new ConfigurationBuilder()
//设置配置文件的基础路径为当前程序运行目录
.SetBasePath(Directory.GetCurrentDirectory())
//添加 JSON 文件作为配置源:
//- fileName: 指定要加载的 JSON 文件
//- optional: false 表示文件必须存在,否则抛出异常
//- reloadOnChange: true 表示文件修改时自动重新加载
.AddJsonFile(fileName, optional: false, reloadOnChange: true);
//构建配置对象(IConfigurationRoot)
IConfigurationRoot config = builder.Build();
//获取指定配置节点(sessions),并将其反序列化为类型 T
var conn = config.GetSection(sessions).Get<T>();
//返回反序列化后的配置对象
return conn;
}
}
案例如下
3.2.2.枚举扩展方法
添加枚举扩展方法方便扩展枚举
/// <summary>
/// 枚举扩展
/// </summary>
public static class EnumExtesion
{
/// <summary>
/// 枚举扩展方法 - 获取枚举值的Description特性描述
/// </summary>
/// <param name="value">枚举值</param>
/// <returns>描述内容</returns>
public static string Description(this Enum value)
{
// 参数验证
if (value == null)
throw new ArgumentNullException(nameof(value));
// 获取枚举类型的字段信息
FieldInfo fi = value.GetType().GetField(value.ToString());
// 获取字段上的DescriptionAttribute特性数组
DescriptionAttribute[] attributes = (DescriptionAttribute[])fi.GetCustomAttributes(
typeof(DescriptionAttribute), // 要查找的特性类型
false // 不搜索继承链
);
// 如果有Description特性则返回其描述,否则返回枚举字符串
return attributes.Length > 0 ? attributes[0].Description : value.ToString();
}
}
案例如下
3.3.添加管理类(重要)
SqlSugar数据库上下文管理类,用于管理多数据库连接实例
using Frame4_LibraryCore.BaseConfig;
using Frame6_LibraryUtility.Enum;
using Microsoft.Extensions.Configuration;
using SqlSugar;
using DbType = SqlSugar.DbType;
namespace Frame3_DataRepository.SqlSugarRepository
{
/// <summary>
/// SqlSugar数据库上下文管理类,用于管理多数据库连接实例
/// </summary>
public class DatabaseSqlSugar
{
/// <summary>
/// 数据库连接字典,按数据库编号存储SqlSugarScope实例
/// Key: 数据库编号(int)
/// Value: SqlSugarScope实例
/// </summary>
private readonly Dictionary<int, SqlSugarScope> _dbs = new();
/// <summary>
/// 主数据库实例(默认操作使用的数据库)
/// </summary>
public ISqlSugarClient MainDb { get; private set; }
/// <summary>
/// 构造函数,初始化数据库连接
/// </summary>
/// <param name="configuration">配置接口,用于获取连接字符串</param>
public DatabaseSqlSugar(IConfiguration configuration)
{
// 从配置文件获取数据库连接配置
var connectionStringOption = Config.GetSetting<ConnectionStringsOptions>("dbsettings.json", "ConnectionStrings");
// 添加默认数据库连接
AddDatabase(connectionStringOption.DefaultDbNumber, connectionStringOption.DefaultDbType, connectionStringOption.DefaultDbString);
// 添加其他配置的数据库连接
if (connectionStringOption.DbConfigs != null)
{
foreach (var config in connectionStringOption.DbConfigs)
{
AddDatabase(int.Parse(config.DbNumber), config.DbType, config.DbString);
}
}
// 设置主数据库实例
MainDb = _dbs[connectionStringOption.DefaultDbNumber];
}
/// <summary>
/// 添加数据库连接
/// </summary>
/// <param name="dbNumber">数据库编号</param>
/// <param name="dbType">数据库类型字符串</param>
/// <param name="connectionString">连接字符串</param>
private void AddDatabase(int dbNumber, string dbType, string connectionString)
{
// 创建SqlSugarScope实例
var db = new SqlSugarScope(new ConnectionConfig()
{
ConnectionString = connectionString,
DbType = (DbType)Enum.Parse(typeof(DbType), dbType), // 转换数据库类型枚举
IsAutoCloseConnection = true, // 启用自动关闭连接
InitKeyType = InitKeyType.Attribute // 使用特性方式初始化主键
});
// 绑定AOP事件
BindAopEvents(db, dbNumber);
// 添加到数据库字典
_dbs[dbNumber] = db;
}
/// <summary>
/// 根据实体类型获取对应的数据库实例
/// </summary>
/// <typeparam name="TEntity">实体类型</typeparam>
/// <returns>对应的SqlSugarClient实例</returns>
/// <exception cref="KeyNotFoundException">当找不到对应的数据库时抛出</exception>
public ISqlSugarClient GetDbByEntity<TEntity>()
{
var type = typeof(TEntity);
// 获取实体上的Tenant特性(用于多租户/分库)
var tenantAttr = (TenantAttribute)Attribute.GetCustomAttribute(type, typeof(TenantAttribute));
// 获取数据库编号,默认为0
int dbNumber = (int)tenantAttr.configId != 0 ? (int)tenantAttr.configId : 0;
if (_dbs.TryGetValue(dbNumber, out var db))
{
// 确保AOP事件已绑定
BindAopEvents(db, dbNumber);
return db;
}
throw new KeyNotFoundException($"找不到编号为 {dbNumber} 的数据库连接");
}
/// <summary>
/// 绑定AOP事件(主要用于SQL日志记录)
/// </summary>
/// <param name="db">SqlSugarClient实例</param>
/// <param name="dbNumber">数据库编号</param>
private void BindAopEvents(ISqlSugarClient db, int dbNumber)
{
// SQL执行完成事件
db.Aop.OnLogExecuted = (sql, pars) =>
{
// 根据SQL类型设置不同颜色
if (sql.TrimStart().StartsWith("SELECT", StringComparison.OrdinalIgnoreCase))
{
Console.ForegroundColor = ConsoleColor.Green; // 查询语句用绿色
}
else if (sql.TrimStart().StartsWith("DELETE", StringComparison.OrdinalIgnoreCase))
{
Console.ForegroundColor = ConsoleColor.Red; // 删除语句用红色
}
else
{
Console.ForegroundColor = ConsoleColor.Blue; // 其他语句用蓝色
}
// 构建完整SQL(替换参数)
string completeSql = sql;
if (pars != null && pars.Length > 0)
{
Dictionary<string, object> parameterValues = new();
foreach (var p in pars)
{
parameterValues[p.ParameterName] = p.Value;
}
foreach (var kvp in parameterValues)
{
completeSql = completeSql.Replace(kvp.Key, FormatParameterValue(kvp.Value));
}
}
// 输出SQL日志
Console.WriteLine($"【Sql时间】:{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff")}\r\n" +
$"【Sql库号】:{((DbConnEnum)dbNumber).Description()} \r\n" +
$"【Sql语句】:{completeSql} \r\n" +
$"【Sql耗时】: {db.Ado.SqlExecutionTime.TotalMilliseconds:F2} 毫秒\r\n");
};
}
/// <summary>
/// 关闭所有数据库连接
/// </summary>
public void CloseAllConnections()
{
foreach (var db in _dbs.Values)
{
db.Dispose(); // 释放数据库连接资源
}
}
/// <summary>
/// 格式化参数值,用于SQL日志输出
/// </summary>
/// <param name="value">参数值</param>
/// <returns>格式化后的字符串</returns>
private static string FormatParameterValue(object value)
{
if (value is string stringValue)
{
return $"'{stringValue}'"; // 字符串类型加引号
}
else if (value is DateTime dateTimeValue)
{
return $"'{dateTimeValue.ToString("yyyy-MM-dd HH:mm:ss")}'"; // 日期时间格式化
}
else if (value is bool boolValue)
{
return boolValue ? "1" : "0"; // 布尔值转数字
}
else
{
return value?.ToString() ?? "NULL"; // 其他类型直接转字符串,null值转为NULL
}
}
}
}
案例如下
4.配置仓储
基于SqlSugar的通用仓储实现类,提供对实体TEntity的CRUD操作及基础查询功能
using SqlSugar;
using System.Linq.Expressions;
namespace Frame3_DataRepository.SqlSugarRepository
{
/// <summary>
/// 基于SqlSugar的通用仓储实现类
/// 提供对实体TEntity的CRUD操作及基础查询功能
/// </summary>
/// <typeparam name="TEntity">实体类型,必须是引用类型且有公共无参构造函数</typeparam>
public class SqlSugarRepository<TEntity> : ISqlSugarRepository<TEntity> where TEntity : class, new()
{
/// <summary>
/// 数据库工厂实例,用于获取数据库连接
/// </summary>
private readonly DatabaseSqlSugar _dbFactory;
/// <summary>
/// 构造函数,通过依赖注入初始化数据库工厂
/// </summary>
/// <param name="dbFactory">数据库工厂实例</param>
public SqlSugarRepository(DatabaseSqlSugar dbFactory)
{
_dbFactory = dbFactory;
}
/// <summary>
/// 当前实体对应的数据库客户端
/// 根据实体上的TenantAttribute自动选择对应的数据库
/// </summary>
protected ISqlSugarClient _db => _dbFactory.GetDbByEntity<TEntity>();
/// <summary>
/// 实体查询集合(延迟加载)
/// 可用于构建复杂查询
/// </summary>
public virtual ISugarQueryable<TEntity> Entities => _db.Queryable<TEntity>();
#region 查询方法
/// <summary>
/// 根据条件表达式获取第一条记录
/// </summary>
/// <param name="whereExpression">查询条件表达式</param>
/// <returns>符合条件的第一条记录,若无则返回null</returns>
public TEntity First(Expression<Func<TEntity, bool>> whereExpression)
{
return _db.Queryable<TEntity>().First(whereExpression);
}
/// <summary>
/// 异步获取符合条件的第一条记录
/// </summary>
/// <param name="whereExpression">查询条件表达式</param>
/// <returns>包含查询结果的Task</returns>
public async Task<TEntity> FirstAsync(Expression<Func<TEntity, bool>> whereExpression)
{
return await _db.Queryable<TEntity>().FirstAsync(whereExpression);
}
/// <summary>
/// 获取所有记录列表
/// </summary>
/// <returns>实体列表</returns>
public List<TEntity> ToList()
{
return _db.Queryable<TEntity>().ToList();
}
/// <summary>
/// 异步获取所有记录列表
/// </summary>
/// <returns>包含实体列表的Task</returns>
public async Task<List<TEntity>> ToListAsync()
{
return await _db.Queryable<TEntity>().ToListAsync();
}
/// <summary>
/// 根据主键ID获取实体
/// 默认查找名为"Id"的属性作为主键
/// </summary>
/// <param name="id">主键值</param>
/// <returns>对应实体</returns>
/// <exception cref="InvalidOperationException">当实体没有Id属性时抛出</exception>
public TEntity GetById(object id)
{
var keyProperty = typeof(TEntity).GetProperty("Id");
if (keyProperty == null)
throw new InvalidOperationException($"实体{typeof(TEntity).Name}不包含Id属性");
return _db.Queryable<TEntity>().First(it => keyProperty.GetValue(it).Equals(id));
}
/// <summary>
/// 异步根据主键ID获取实体
/// </summary>
/// <param name="id">主键值</param>
/// <returns>包含查询结果的Task</returns>
public async Task<TEntity> GetByIdAsync(object id)
{
var keyProperty = typeof(TEntity).GetProperty("Id");
if (keyProperty == null)
throw new InvalidOperationException($"实体{typeof(TEntity).Name}不包含Id属性");
return await _db.Queryable<TEntity>().FirstAsync(it => keyProperty.GetValue(it).Equals(id));
}
#endregion
#region 新增方法
/// <summary>
/// 插入单个实体
/// </summary>
/// <param name="entity">要插入的实体</param>
/// <returns>影响的行数</returns>
public int Insert(TEntity entity)
{
return _db.Insertable(entity).ExecuteCommand();
}
/// <summary>
/// 批量插入实体集合
/// </summary>
/// <param name="entity">实体集合</param>
/// <returns>影响的行数</returns>
public int Insert(List<TEntity> entity)
{
return _db.Insertable(entity).ExecuteCommand();
}
/// <summary>
/// 异步插入单个实体
/// </summary>
/// <param name="entity">要插入的实体</param>
/// <returns>包含影响行数的Task</returns>
public async Task<int> InsertAsync(TEntity entity)
{
return await _db.Insertable(entity).ExecuteCommandAsync();
}
/// <summary>
/// 异步批量插入实体集合
/// </summary>
/// <param name="entity">实体集合</param>
/// <returns>包含影响行数的Task</returns>
public async Task<int> InsertAsync(List<TEntity> entity)
{
return await _db.Insertable(entity).ExecuteCommandAsync();
}
#endregion
#region 更新方法
/// <summary>
/// 更新单个实体
/// 自动忽略null值和默认值字段
/// </summary>
/// <param name="entity">要更新的实体</param>
/// <returns>影响的行数</returns>
public int Update(TEntity entity)
{
return _db.Updateable(entity)
.IgnoreColumns(ignoreAllNullColumns: true, ignoreAllDefaultValue: true)
.ExecuteCommand();
}
/// <summary>
/// 批量更新实体集合
/// </summary>
/// <param name="entity">实体集合</param>
/// <returns>影响的行数</returns>
public int Update(List<TEntity> entity)
{
return _db.Updateable(entity)
.IgnoreColumns(ignoreAllNullColumns: true, ignoreAllDefaultValue: true)
.ExecuteCommand();
}
/// <summary>
/// 异步更新单个实体
/// </summary>
/// <param name="entity">要更新的实体</param>
/// <returns>包含影响行数的Task</returns>
public async Task<int> UpdateAsync(TEntity entity)
{
return await _db.Updateable(entity)
.IgnoreColumns(ignoreAllNullColumns: true, ignoreAllDefaultValue: true)
.ExecuteCommandAsync();
}
/// <summary>
/// 异步批量更新实体集合
/// </summary>
/// <param name="entity">实体集合</param>
/// <returns>包含影响行数的Task</returns>
public async Task<int> UpdateAsync(List<TEntity> entity)
{
return await _db.Updateable(entity)
.IgnoreColumns(ignoreAllNullColumns: true, ignoreAllDefaultValue: true)
.ExecuteCommandAsync();
}
/// <summary>
/// 高性能批量更新
/// </summary>
/// <param name="entity">实体集合</param>
/// <returns>影响的行数</returns>
/// <exception cref="ArgumentException">当实体集合为空时抛出</exception>
public int BulkUpdate(List<TEntity> entity)
{
if (entity == null || !entity.Any())
throw new ArgumentException("更新实体集合不能为空");
return _db.Fastest<TEntity>().BulkUpdate(entity);
}
/// <summary>
/// 异步高性能批量更新
/// </summary>
/// <param name="entity">实体集合</param>
/// <returns>包含影响行数的Task</returns>
public async Task<int> BulkUpdateAsync(List<TEntity> entity)
{
if (entity == null || !entity.Any())
throw new ArgumentException("更新实体集合不能为空");
return await _db.Fastest<TEntity>().BulkUpdateAsync(entity);
}
#endregion
#region 删除方法
/// <summary>
/// 根据主键ID删除记录
/// </summary>
/// <param name="id">主键值</param>
/// <returns>影响的行数</returns>
/// <exception cref="InvalidOperationException">当实体没有Id属性时抛出</exception>
public int DeleteById(object id)
{
var keyProperty = typeof(TEntity).GetProperty("Id");
if (keyProperty == null)
throw new InvalidOperationException($"实体{typeof(TEntity).Name}不包含Id属性");
var expression = Expression.Lambda<Func<TEntity, bool>>(
Expression.Equal(
Expression.Property(Expression.Parameter(typeof(TEntity), "it"), "Id"),
Expression.Constant(id)
),
new[] { Expression.Parameter(typeof(TEntity), "it") }
);
return _db.Deleteable<TEntity>().Where(expression).ExecuteCommand();
}
/// <summary>
/// 异步根据主键ID删除记录
/// </summary>
/// <param name="id">主键值</param>
/// <returns>包含影响行数的Task</returns>
public async Task<int> DeleteByIdAsync(object id)
{
var keyProperty = typeof(TEntity).GetProperty("Id");
if (keyProperty == null)
throw new InvalidOperationException($"实体{typeof(TEntity).Name}不包含Id属性");
var expression = Expression.Lambda<Func<TEntity, bool>>(
Expression.Equal(
Expression.Property(Expression.Parameter(typeof(TEntity), "it"), "Id"),
Expression.Constant(id)
),
new[] { Expression.Parameter(typeof(TEntity), "it") }
);
return await _db.Deleteable<TEntity>().Where(expression).ExecuteCommandAsync();
}
#endregion
#region 查询构造器
/// <summary>
/// 根据条件表达式构建查询
/// </summary>
/// <param name="predicate">查询条件</param>
/// <returns>可继续构建的查询对象</returns>
public virtual ISugarQueryable<TEntity> Where(Expression<Func<TEntity, bool>> predicate)
{
return AsQueryable(predicate);
}
/// <summary>
/// 根据条件构建查询
/// 当condition为true时应用predicate条件
/// </summary>
/// <param name="condition">是否应用条件</param>
/// <param name="predicate">查询条件</param>
/// <returns>可继续构建的查询对象</returns>
public virtual ISugarQueryable<TEntity> Where(bool condition, Expression<Func<TEntity, bool>> predicate)
{
return AsQueryable().WhereIF(condition, predicate);
}
/// <summary>
/// 获取基础查询构造器
/// </summary>
/// <returns>可继续构建的查询对象</returns>
public virtual ISugarQueryable<TEntity> AsQueryable()
{
return Entities;
}
/// <summary>
/// 根据条件获取查询构造器
/// </summary>
/// <param name="predicate">查询条件</param>
/// <returns>可继续构建的查询对象</returns>
public virtual ISugarQueryable<TEntity> AsQueryable(Expression<Func<TEntity, bool>> predicate)
{
return Entities.Where(predicate);
}
#endregion
#region 事务管理
/// <summary>
/// 在当前数据库开启事务
/// </summary>
public void CurrentBeginTran()
{
_db.AsTenant().BeginTran();
}
/// <summary>
/// 提交当前数据库事务
/// </summary>
public void CurrentCommitTran()
{
_db.AsTenant().CommitTran();
}
/// <summary>
/// 回滚当前数据库事务
/// </summary>
public void CurrentRollbackTran()
{
_db.AsTenant().RollbackTran();
}
#endregion
}
}
案例如下
4.1.仓储接口添加
为了更好的使用仓储,把仓储的每个方法添加接口。
using SqlSugar;
using System.Linq.Expressions;
namespace Frame3_DataRepository.SqlSugarRepository
{
/// <summary>
/// 仓储接口
/// </summary>
/// <typeparam name="TEntity"></typeparam>
public interface ISqlSugarRepository<TEntity> where TEntity : class, new()
{
/// <summary>
/// 查询单条数据
/// </summary>
/// <param name="whereExpression"></param>
/// <returns></returns>
TEntity First(Expression<Func<TEntity, bool>> whereExpression);
/// <summary>
/// 查询单条数据 (异步)
/// </summary>
/// <param name="whereExpression"></param>
/// <returns></returns>
Task<TEntity> FirstAsync(Expression<Func<TEntity, bool>> whereExpression);
/// <summary>
/// 获取列表
/// </summary>
/// <returns></returns>
List<TEntity> ToList();
/// <summary>
/// 获取列表 (异步)
/// </summary>
/// <returns></returns>
Task<List<TEntity>> ToListAsync();
/// <summary>
/// 根据Id获取数据
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
/// <exception cref="InvalidOperationException"></exception>
TEntity GetById(object id);
/// <summary>
/// 根据Id获取数据 (异步)
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
/// <exception cref="InvalidOperationException"></exception>
Task<TEntity> GetByIdAsync(object id);
/// <summary>
/// 添加数据
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
int Insert(TEntity entity);
/// <summary>
/// 批量添加新增数据
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
int Insert(List<TEntity> entity);
/// <summary>
/// 添加数据 (异步)
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
Task<int> InsertAsync(TEntity entity);
/// <summary>
/// 批量添加数据 (异步)
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
Task<int> InsertAsync(List<TEntity> entity);
/// <summary>
/// 修改数据
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
int Update(TEntity entity);
/// <summary>
/// 批量修改数据
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
int Update(List<TEntity> entity);
/// <summary>
/// 修改数据 (异步)
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
Task<int> UpdateAsync(TEntity entity);
/// <summary>
/// 批量修改数据 (异步)
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
Task<int> UpdateAsync(List<TEntity> entity);
/// <summary>
/// 大批量修改
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
/// <exception cref="ArgumentException"></exception>
int BulkUpdate(List<TEntity> entity);
/// <summary>
/// 大批量修改 (异步)
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
/// <exception cref="ArgumentException"></exception>
Task<int> BulkUpdateAsync(List<TEntity> entity);
/// <summary>
/// 根据Id删除数据
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
/// <exception cref="InvalidOperationException"></exception>
int DeleteById(object id);
/// <summary>
/// 根据Id删除数据 (异步)
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
/// <exception cref="InvalidOperationException"></exception>
Task<int> DeleteByIdAsync(object id);
/// <summary>
/// 根据表达式查询多条记录
/// </summary>
/// <param name="predicate"></param>
/// <returns></returns>
ISugarQueryable<TEntity> Where(Expression<Func<TEntity, bool>> predicate);
/// <summary>
/// 根据表达式查询多条记录
/// </summary>
/// <param name="condition"></param>
/// <param name="predicate"></param>
/// <returns></returns>
ISugarQueryable<TEntity> Where(bool condition, Expression<Func<TEntity, bool>> predicate);
/// <summary>
/// 构建查询分析器
/// </summary>
/// <returns></returns>
ISugarQueryable<TEntity> AsQueryable();
/// <summary>
/// 构建查询分析器
/// </summary>
/// <param name="predicate"></param>
/// <returns></returns>
ISugarQueryable<TEntity> AsQueryable(Expression<Func<TEntity, bool>> predicate);
}
}
案例如下
5.注册
添加好以上后就可以把SqlSugar和仓储在文件 Program 或 Startup 里注册。
// 注册 DatabaseSqlSugar(单例或作用域都可以)
builder.Services.AddSingleton<DatabaseSqlSugar>(sp =>
{
// 假设你有 IConfiguration 可用
var configuration = sp.GetRequiredService<IConfiguration>();
return new DatabaseSqlSugar(configuration);
});
// 注册Sqlsugar仓储
builder.Services.AddScoped(typeof(ISqlSugarRepository<>), typeof(SqlSugarRepository<>));
6.使用
添加 entity 数据库实体类
using Frame6_LibraryUtility.Enum;
using SqlSugar;
namespace Frame2_DataModel.Entity.User
{
/// <summary>
/// User实体
/// </summary>
[SugarTable("User", TableDescription = "用户表"), Tenant((int)DbConnEnum.SystemMysql)]
public class UserEntity : BaseEntity
{
/// <summary>
/// 用户Id主键
/// </summary>
[SugarColumn(IsPrimaryKey = true)]
public string Id { get; set; }
/// <summary>
/// 用户登录名
/// </summary>
public string UserId { get; set; }
/// <summary>
/// 用户密码
/// </summary>
public string pwd { get; set; }
/// <summary>
/// 用户姓名
/// </summary>
public string UserName { get; set; }
public string Age { get; set; }
}
}
案例如下
使用仓储
public class UserService : BaseService, IUserService
{
private readonly ISqlSugarRepository<UserEntity> _dbUser;
/// <summary>
/// 依赖注入
/// </summary>
/// <param name="sqlSugar"></param>
/// <param name="tokenService"></param>
/// <param name="iCurrentUser"></param>
/// <param name="mapper"></param>
public UserService(ISqlSugarRepository<UserEntity> dbUser)
{
_dbUser = dbUser;
}
/// <summary>
/// 获取所有test
/// </summary>
/// <returns></returns>
public async Task<ResultModel<List<UserEntity>>> GetUserAsync()
{
var result = new ResultModel<List<UserEntity>>();
var users = await _dbUser.ToListAsync();
result.Msg = "获取成功";
result.Data = users;
return result;
}
}
案例如下