为什么是SqlSugar?因为我用习惯了啊，哈哈~也是国产优秀的ORM
安装包-NuGet
服务配置
新建一个类 用作service配置
说明下，我需要初始化的数据库连接有两个，一个是业务库，一个是日志库
public static class SqlSugarService{private static readonly ILog log = LogManager.GetLogger(typeof(SqlSugarService));public static void AddSqlSugarSevice(this IServiceCollection services){if (services == null) throw new ArgumentNullException(nameof(services));//log.Info("测试");services.AddScoped<ISqlSugarClient>(o =>{// 连接字符串var listConfig = new List<ConnectionConfig>();listConfig.Add(new ConnectionConfig{ConfigId = DBConfig.MSSQLMainDbConnId,//此链接标志，用以后面切库使用ConnectionString = AppsettingConfig.DbMSSQLMainConnection,//业务库连接字符串DbType = DbType.SqlServer,InitKeyType = InitKeyType.SystemTable,IsAutoCloseConnection = true,AopEvents = new AopEvents{OnLogExecuting = (sql, pars) =>{//MiniProfiler.Current.CustomTiming("SQL：", GetParas(pars) + "【SQL语句】：" + sql);if (AppsettingConfig.DbMSSQLMainIsLogSql)//是否记录Sql语句到日志文件中的开关{log.Info(GetParas(pars) + "【SQL语句】：" + sql);}Console.WriteLine(GetParas(pars) + "【SQL语句】：" + sql);Console.WriteLine();}}});listConfig.Add(new ConnectionConfig{ConfigId = DBConfig.MSSQLLogDbConnId,//此链接标志，用以后面切库使用ConnectionString = AppsettingConfig.DbMSSQLLogConnection,//日志库连接字符串DbType = DbType.SqlServer,InitKeyType = InitKeyType.SystemTable,IsAutoCloseConnection = true,AopEvents = new AopEvents{OnLogExecuting = (sql, pars) =>{if (AppsettingConfig.DbMSSQLLogIsLogSql)//是否记录Sql语句到日志文件中的开关{log.Info(GetParas(pars) + "【SQL语句】：" + sql);}Console.WriteLine(GetParas(pars) + "【SQL语句】：" + sql);Console.WriteLine();}}});return new SqlSugarClient(listConfig);});}private static string GetParas(SugarParameter[] pars){string key = "【SQL参数】：";foreach (var param in pars){key += $"{param.ParameterName}:{param.Value}\r\n";}return key+"<br/>";}}
StartUp中添加服务
//开启SqlSugar
services.AddSqlSugarSevice();
数据操作封装
我是写在了Repository 和IRepository 中 他们是声明和实现的关系，在AutoFact中已经使用dll方式注入了，如果自己写 可以写在StartUp中进行分别注入
业务库操作
声明接口
public interface IOwnerRepository
{
/// <summary>
/// 对外执行主体
/// </summary>
public ISqlSugarClient Db { get;}
/// <summary>
/// 插入 返回自增间的值bigint
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="o"></param>
/// <param name="ignoreColumn">需要排除的列</param>
/// <returns></returns>
Task<long> InsertReturnLongAsync<T>(T o, params string[] ignoreColumn) where T : class, new();
/// <summary>
/// 插入 返回自增间的值bigint
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="o"></param>
/// <param name="ignoreColumn">需要排除的列</param>
/// <returns></returns>
long InsertReturnLong<T>(T o, params string[] ignoreColumn) where T : class, new();
/// <summary>
/// 插入 返回自增间的值int
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="o"></param>
/// <param name="ignoreColumn">需要排除的列</param>
/// <returns></returns>
Task<int> InsertReturnIntAsync<T>(T o, params string[] ignoreColumn) where T : class, new();
/// <summary>
/// 插入 返回自增间的值int
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="o"></param>
/// <param name="ignoreColumn">需要排除的列</param>
/// <returns></returns>
int InsertReturnInt<T>(T o, params string[] ignoreColumn) where T : class, new();
/// <summary>
/// 插入 返回T
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="o"></param>
/// <param name="ignoreColumn">需要排除的列</param>
/// <returns></returns>
Task<T> InsertReturnTAsync<T>(T o, params string[] ignoreColumn) where T : class, new();
/// <summary>
/// 插入 返回T
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="o"></param>
/// <param name="ignoreColumn">需要排除的列</param>
/// <returns></returns>
T InsertReturnT<T>(T o, params string[] ignoreColumn) where T : class, new();
/// <summary>
/// 批量插入返回受影响的行数
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="o"></param>
/// <returns></returns>
Task<int> InsertListAsync<T>(List<T> o, params string[] ignoreColumn) where T : class, new();
/// <summary>
/// 批量插入返回受影响的行数
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="o"></param>
/// <returns></returns>
int InsertList<T>(List<T> o, params string[] ignoreColumn) where T : class, new();
/// <summary>
/// 执行SQL语句返回受影响的行数 适合insert/update/delete
/// </summary>
/// <param name="sql"></param>
/// <param name="sqlparms"></param>
/// <returns></returns>
Task<int> ExcuteSqlAsync(string sql, DbParameter[] sqlparms = null);
/// <summary>
/// 执行SQL语句返回受影响的行数 适合insert/update/delete
/// </summary>
/// <param name="sql"></param>
/// <param name="sqlparms"></param>
/// <returns></returns>
int ExcuteSql(string sql, DbParameter[] sqlparms = null);
/// <summary>
/// 执行SQL语句返回唯一结果
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <param name="sqlparms"></param>
/// <returns></returns>
Task<T> QuerySqlSingleAsync<T>(string sql, DbParameter[] sqlparms = null);
/// <summary>
/// 执行SQL语句返回唯一结果
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <param name="sqlparms"></param>
/// <returns></returns>
T QuerySqlSingle<T>(string sql, DbParameter[] sqlparms = null);
/// <summary>
/// 执行SQL语句返回结果集
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <param name="sqlparms"></param>
/// <returns></returns>
Task<List<T>> QuerySqlListAsync<T>(string sql, DbParameter[] sqlparms = null);
/// <summary>
/// 执行SQL语句返回结果集
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <param name="sqlparms"></param>
/// <returns></returns>
List<T> QuerySqlList<T>(string sql, DbParameter[] sqlparms = null);
/// <summary>
/// 分页查询异步
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="whereExpression">表达式树</param>
/// <param name="intPageIndex">当前页</param>
/// <param name="intPageSize">每页容量</param>
/// <param name="strOrderByFileds">排序字段</param>
/// <returns></returns>
Task<PageModel<T>> QueryPageAsync<T>(Expression<Func<T, bool>> whereExpression, int intPageIndex = 1, int intPageSize = 20, string strOrderByFileds = null) where T : class;
/// <summary>
/// 分页查询
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="whereExpression">表达式树</param>
/// <param name="intPageIndex">当前页</param>
/// <param name="intPageSize">每页容量</param>
/// <param name="strOrderByFileds">排序字段</param>
/// <returns></returns>
PageModel<T> QueryPage<T>(Expression<Func<T, bool>> whereExpression, int intPageIndex = 1, int intPageSize = 20, string strOrderByFileds = null) where T : class;
}
实现接口
public class OwnerRepository : IOwnerRepository
{
private SqlSugarClient _dbBase;
public BaseRepository(ISqlSugarClient sqlSugar)
{
_dbBase = sqlSugar as SqlSugarClient;
}
/// <summary>
/// 对外的扩展操作
/// </summary>
public ISqlSugarClient Db => _db;
private ISqlSugarClient _db
{
get
{
_dbBase.ChangeDatabase(DBConfig.MSSQLMainDbConnId);//切换到主库标记
return _dbBase;
}
}
public Task<long> InsertReturnLongAsync<T1>(T1 o, params string[] ignoreColumn) where T1 : class, new()
{
return _db.Insertable(o).IgnoreColumns(ignoreColumn).ExecuteReturnBigIdentityAsync();
}
public long InsertReturnLong<T1>(T1 o, params string[] ignoreColumn) where T1 : class, new()
{
return _db.Insertable(o).IgnoreColumns(ignoreColumn).ExecuteReturnBigIdentity();
}
public Task<int> InsertReturnIntAsync<T1>(T1 o, params string[] ignoreColumn) where T1 : class, new()
{
return _db.Insertable(o).IgnoreColumns(ignoreColumn).ExecuteReturnIdentityAsync();
}
public int InsertReturnInt<T1>(T1 o, params string[] ignoreColumn) where T1 : class, new()
{
return _db.Insertable(o).IgnoreColumns(ignoreColumn).ExecuteReturnIdentity();
}
public Task<T1> InsertReturnTAsync<T1>(T1 o, params string[] ignoreColumn) where T1 : class, new()
{
return _db.Insertable(o).IgnoreColumns(ignoreColumn).ExecuteReturnEntityAsync();
}
public T1 InsertReturnT<T1>(T1 o, params string[] ignoreColumn) where T1 : class, new()
{
return _db.Insertable(o).IgnoreColumns(ignoreColumn).ExecuteReturnEntity();
}
public Task<int> InsertListAsync<T1>(List<T1> o, params string[] ignoreColumn) where T1 : class, new()
{
return _db.Insertable(o.ToArray()).IgnoreColumns(ignoreColumn).ExecuteCommandAsync();
}
public int InsertList<T1>(List<T1> o, params string[] ignoreColumn) where T1 : class, new()
{
return _db.Insertable(o.ToArray()).IgnoreColumns(ignoreColumn).ExecuteCommand();
}
public Task<int> ExcuteSqlAsync(string sql, DbParameter[] sqlparms = null)
{
return _db.Ado.ExecuteCommandAsync(sql, sqlparms);
}
public int ExcuteSql(string sql, DbParameter[] sqlparms = null)
{
return _db.Ado.ExecuteCommand(sql, sqlparms);
}
public Task<T> QuerySqlSingleAsync<T>(string sql, DbParameter[] sqlparms = null)
{
return _db.Ado.SqlQuerySingleAsync<T>(sql, sqlparms);
}
public T QuerySqlSingle<T>(string sql, DbParameter[] sqlparms = null)
{
return _db.Ado.SqlQuerySingle<T>(sql, sqlparms);
}
public Task<List<T>> QuerySqlListAsync<T>(string sql, DbParameter[] sqlparms = null)
{
return _db.Ado.SqlQueryAsync<T>(sql, sqlparms);
}
public List<T> QuerySqlList<T>(string sql, DbParameter[] sqlparms = null)
{
return _db.Ado.SqlQuery<T>(sql, sqlparms);
}
public async Task<PageModel<T>> QueryPageAsync<T>(Expression<Func<T, bool>> whereExpression, int intPageIndex = 1, int intPageSize = 20, string strOrderByFileds = null) where T : class
{
RefAsync<int> totalCount = 0;
var list = await _db.Queryable<T>()
.WhereIF(whereExpression != null, whereExpression)
.OrderByIF(!string.IsNullOrEmpty(strOrderByFileds), strOrderByFileds)
.ToPageListAsync(intPageIndex, intPageSize, totalCount);
int pageCount = (int)(Math.Ceiling((totalCount * 1.0) / (intPageSize * 1.0)));
return new PageModel<T>() { dataCount = totalCount, pageCount = pageCount, page = intPageIndex, PageSize = intPageSize, data = list };
}
public PageModel<T> QueryPage<T>(Expression<Func<T, bool>> whereExpression, int intPageIndex = 1, int intPageSize = 20, string strOrderByFileds = null) where T : class
{
int totalCount = 0;
var list = _db.Queryable<T>()
.WhereIF(whereExpression != null, whereExpression)
.OrderByIF(!string.IsNullOrEmpty(strOrderByFileds), strOrderByFileds)
.ToPageList(intPageIndex, intPageSize, ref totalCount);
int pageCount = (int)(Math.Ceiling((totalCount * 1.0) / (intPageSize * 1.0)));
return new PageModel<T>() { dataCount = totalCount, pageCount = pageCount, page = intPageIndex, PageSize = intPageSize, data = list };
}
}
日志库操作
声明接口
public interface ILogRepository
{
/// <summary>
/// 执行数据库主体
/// </summary>
public ISqlSugarClient LogDb { get; }
/// <summary>
/// 分页查询异步
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="whereExpression">表达式树</param>
/// <param name="intPageIndex">当前页</param>
/// <param name="intPageSize">每页容量</param>
/// <param name="strOrderByFileds">排序字段</param>
/// <returns></returns>
Task<PageModel<T>> QueryPageAsync<T>(Expression<Func<T, bool>> whereExpression, int intPageIndex = 1, int intPageSize = 20, string strOrderByFileds = null) where T:class;
/// <summary>
/// 分页查询
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="whereExpression">表达式树</param>
/// <param name="intPageIndex">当前页</param>
/// <param name="intPageSize">每页容量</param>
/// <param name="strOrderByFileds">排序字段</param>
/// <returns></returns>
PageModel<T> QueryPage<T>(Expression<Func<T, bool>> whereExpression, int intPageIndex = 1, int intPageSize = 20, string strOrderByFileds = null) where T : class;
/// <summary>
/// 插入 返回自增间的值bigint异步
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="o"></param>
/// <param name="ignoreColumn">需要排除的列</param>
/// <returns></returns>
Task<long> InsertReturnLongAsync<T>(T o, params string[] ignoreColumn) where T : class, new();
/// <summary>
/// 插入 返回自增间的值bigint
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="o"></param>
/// <param name="ignoreColumn">需要排除的列</param>
/// <returns></returns>
long InsertReturnLong<T>(T o, params string[] ignoreColumn) where T : class, new();
}
实现接口
public class LogRepository : ILogRepository
{
private SqlSugarClient _dbBase;
/// <summary>
/// 对外的扩展操作
/// </summary>
public LogRepository(ISqlSugarClient sqlSugar)
{
_dbBase = sqlSugar as SqlSugarClient;
}
private ISqlSugarClient _db
{
get
{
_dbBase.ChangeDatabase(DBConfig.MSSQLLogDbConnId);
return _dbBase;
}
}
public ISqlSugarClient LogDb => _db;
public long InsertReturnLong<T>(T o, params string[] ignoreColumn) where T : class, new()
{
return _db.Insertable(o).IgnoreColumns(ignoreColumn).ExecuteReturnBigIdentity();
}
public Task<long> InsertReturnLongAsync<T>(T o, params string[] ignoreColumn) where T : class, new()
{
return _db.Insertable(o).IgnoreColumns(ignoreColumn).ExecuteReturnBigIdentityAsync();
}
public PageModel<T> QueryPage<T>(Expression<Func<T, bool>> whereExpression, int intPageIndex = 1, int intPageSize = 20, string strOrderByFileds = null) where T : class
{
int totalCount = 0;
var list = _db.Queryable<T>()
.WhereIF(whereExpression != null, whereExpression)
.OrderByIF(!string.IsNullOrEmpty(strOrderByFileds), strOrderByFileds)
.ToPageList(intPageIndex, intPageSize,ref totalCount);
int pageCount = (int)(Math.Ceiling((totalCount * 1.0) / (intPageSize * 1.0)));
return new PageModel<T>() { dataCount = totalCount, pageCount = pageCount, page = intPageIndex, PageSize = intPageSize, data = list };
}
public async Task<PageModel<T>> QueryPageAsync<T>(Expression<Func<T, bool>> whereExpression, int intPageIndex = 1, int intPageSize = 20, string strOrderByFileds = null) where T : class
{
RefAsync<int> totalCount = 0;
var list = await _db.Queryable<T>()
.WhereIF(whereExpression != null, whereExpression)
.OrderByIF(!string.IsNullOrEmpty(strOrderByFileds), strOrderByFileds)
.ToPageListAsync(intPageIndex, intPageSize, totalCount);
int pageCount = (int)(Math.Ceiling((totalCount * 1.0) / (intPageSize * 1.0)));
return new PageModel<T>() { dataCount = totalCount, pageCount = pageCount, page = intPageIndex, PageSize = intPageSize, data = list };
}
}
到需要的业务场景中使用即可
若有收获，就点个赞吧
0 人点赞
