Sql Sugar 使用
-
sql sugar orm文档地址： SqlSugar ORM 5.X 官网 、文档、教程 - SqlSugar 5x - .NET果糖网 (donet5.com)
-
sql sugar 基本用法大全，来自博主 @天才卧龙 ：https://www.cnblogs.com/chenwolong/p/13858384.html
.net 5.0 + sqlsugarcore(5.0.4.2)
一、SqlSugarScope 、SqlSugarClient 、SqlConnection区别
一、区别
scope是对client的进一步封装，为了支持线程安全，并且在不同上下文中自动new出一个client，在编写代码时不需要考虑他线程是否安全
二、总结
他们3者的关系应该是这样的：
SqlSugarScope 底层+自动释放+上下文安全
SqlSugarClient 底层+自动释放控制
SqlConnection 底层
三、引申
1.什么是上下文?
异步情况： 在同一串await 中是一个上下文
同步情况： 在同一个线程是同一个上下文
同一个SqlSugarScope做到了在同一个上下文共享一个对象，不同上下文自动去NEW
二、Code First、DB First
一、code first
1.创建方式
/// <summary>
/// 代码优先
/// </summary>
public static void CodeFirstShow()
{
ConnectionConfig config = new ConnectionConfig()
{
ConnectionString = "",
DbType = DbType.SqlServer,
IsAutoCloseConnection = true,
};
using (SqlSugarClient db = new SqlSugarClient(config))
{
//如果不存在创建数据库
db.DbMaintenance.CreateDatabase();
Type[] types = Assembly
.LoadFrom("类库")
.GetTypes()
.ToArray();
db.CodeFirst.SetStringDefaultLength(200).InitTables(types);
}
}
2.code first 上下文创建数据库
public class SugarDbContext
{
public SqlSugarClient Db;
public SugarDbContext()
{
Db = new SqlSugarClient(new ConnectionConfig()
{
ConnectionString = "server=.;database=StudentDb;uid=cnpy;pwd=cnpy1314;",
DbType = DbType.SqlServer,//设置数据库类型
IsAutoCloseConnection = true,//自动释放数据库，如果存在事务，在事务结束之后释放。
InitKeyType = InitKeyType.Attribute//从实体特性中读取主键自增列信息
});
Db.Aop.OnLogExecuting = (sql, pars) =>
{
Console.WriteLine(sql + "\r\n" + Db.Utilities.SerializeObject
(pars.ToDictionary(it => it.ParameterName, it => it.Value)));
Console.WriteLine();
};
}
public void CreateTable(bool Backup = false, int StringDefaultLength = 50, params Type[] types)
{
Db.CodeFirst.SetStringDefaultLength(StringDefaultLength);
Db.DbMaintenance.CreateDatabase();
if (Backup)
{
Db.CodeFirst.BackupTable().InitTables(types);
}
else
{
Db.CodeFirst.InitTables(types);
}
}
public SimpleClient<Students> studentDb { get { return new SimpleClient<Students>(Db); } }
public SimpleClient<Schools> schoolDb { get { return new SimpleClient<Schools>(Db); } }
}
二、DB First
1.创建方式
/// <summary>
/// 数据库优先
/// </summary>
public static void DbFirstShow() {
ConnectionConfig config = new ConnectionConfig() {
ConnectionString="",
DbType=DbType.SqlServer,
IsAutoCloseConnection=true,
};
using (SqlSugarClient db=new SqlSugarClient(config))
{
//IsCreateAttribute:代表生成sqlsugar特性.
db.DbFirst
.IsCreateAttribute()
.CreateClassFile("类库所在文件夹","类库名称");
}
}
数据表
! 联表查询
联表查询：
SqlSugarClient Db = new SqlSugarClient(new ConnectionConfig() {
ConnectionString = "Data Source=.;Initial Catalog=CoreShop;User ID=sa;Password=sa123;",
DbType = DbType.SqlServer,
IsAutoCloseConnection = true
});
Db.Aop.OnLogExecuting = (sql, pars) =>
{
Console.WriteLine(sql);//输出sql,查看执行sql 性能无影响
//5.0.8.2 获取无参数化SQL 对性能有影响，特别大的SQL参数多的，调试使用
//UtilMethods.GetSqlString(DbType.SqlServer,sql,pars)
};
var cart = Db.Queryable<CoreCmsCart>().ToList();
var goods= Db.Queryable<CoreCmsProducts, CoreCmsGoods, CoreCmsCart>((o, i, c) => new JoinQueryInfos(
JoinType.Left, o.goodsId == i.id, //左连接 左链接 左联
JoinType.Left, o.id == c.productId))
.Select((o, i, c) => new { name=i.name, price = o.price })
.ToList();
//实战应用：
var view = new object ();
var items = _coreCmsGoodsServices.QueryMuch<CoreCmsProducts, CoreCmsGoods, CoreCmsCart, GoodsDetailsVM>(
(p, g, c) => new object[] { JoinType.Left, p.goodsId == g.id, JoinType.Left, p.id == c.productId },
(p, g, c) => new GoodsDetailsVM { Id = c.id, name = g.name, price = p.price }
);
三、项目使用
1、启动sqlsugar服务
/// <summary>
/// SqlSugar 启动服务
/// </summary>
public static class SqlSugarSetup
{
public static void AddSqlSugarSetup(this IServiceCollection services)
{
if (services == null) throw new ArgumentNullException(nameof(services));
//注入 ORM
SugarIocServices.AddSqlSugar(new IocConfig()
{
//数据库连接
ConnectionString = AppSettingsConstVars.DbSqlConnection,
//判断数据库类型
DbType = AppSettingsConstVars.DbDbType == IocDbType.MySql.ToString() ? IocDbType.MySql : IocDbType.SqlServer,
//是否开启自动关闭数据库连接-//不设成true要手动close
IsAutoCloseConnection = true,
});
//设置参数
services.ConfigurationSugar(db =>
{
db.CurrentConnectionConfig.InitKeyType = InitKeyType.Attribute;
//db.CurrentConnectionConfig.ConfigureExternalServices = new ConfigureExternalServices()
//{
// //判断是否开启redis设置二级缓存方式
// DataInfoCacheService = AppSettingsConstVars.RedisUseCache ? (ICacheService)new SqlSugarRedisCache() : new SqlSugarMemoryCache()
//};
//执行SQL 错误事件，可监控sql（暂时屏蔽，需要可开启）
//db.Aop.OnLogExecuting = (sql, p) =>
//{
// NLogUtil.WriteFileLog(NLog.LogLevel.Error, LogType.Other, "SqlSugar执行SQL错误事件打印Sql", sql);
//};
//执行SQL 错误事件
db.Aop.OnError = (exp) =>
{
NLogUtil.WriteFileLog(NLog.LogLevel.Error, LogType.Other, "SqlSugar", "执行SQL错误事件", exp);
};
//设置更多连接参数
//db.CurrentConnectionConfig.XXXX=XXXX
//db.CurrentConnectionConfig.MoreSetting=new MoreSetting(){}
//读写分离等都在这儿设置
});
}
}
2、注入服务
//添加数据库连接SqlSugar注入支持
services.AddSqlSugarSetup();
3.创建工作单元接口、实现 UnitOfWork
- 接口 IUnitOfWork
using SqlSugar;
public interface IUnitOfWork
{
SqlSugarScope GetDbClient();//获取db对象
void BeginTran();//开启事务
void CommitTran();//提交事务
void RollbackTran();//回滚是u我
}
- 实现 UnitOfWork
点击查看代码
public class UnitOfWork : IUnitOfWork
{
private readonly ISqlSugarClient _sqlSugarClient;
public UnitOfWork()
{
_sqlSugarClient = DbScoped.SugarScope;
}
/// <summary>
/// 获取DB，保证唯一性
/// </summary>
/// <returns></returns>
public SqlSugarScope GetDbClient()
{
// 必须要as，后边会用到切换数据库操作
return _sqlSugarClient as SqlSugarScope;
}
public void BeginTran()
{
GetDbClient().BeginTran();
}
public void CommitTran()
{
try
{
GetDbClient().CommitTran(); //
}
catch (Exception ex)
{
GetDbClient().RollbackTran();
NLogUtil.WriteFileLog(LogLevel.Error, LogType.Web, "事务提交异常", "事务提交异常", new Exception("事务提交异常", ex));
throw;
}
}
public void RollbackTran()
{
GetDbClient().RollbackTran();
}
}
