Skip to main content

10.1 SqlSugar 集成

关于拓展包

由于 SqlSugar 高速发展,新版本带来了诸多特性,而 Furion.Extras.DatabaseAccessor.SqlSugar 拓展包更新不及时导致不能第一时间体验新特性。

所以,和 SqlSugar 作者商量后,决定全面推荐使用 SqlSugar 原生拓展包即可。以下文档已更新查看旧文档

温馨提醒

Furion 包中默认集成了 EFCore如果不使用 EFCore,可安装纯净版 Furion.Pure 代替 Furion

10.1.1 SqlSugar ORM

SqlSugar.NET/C# 平台非常优秀的 ORM 框架,目前 Nuget 总下载突破 1000KGithub 关注量也高达 3.7K,是目前当之无愧的国产优秀 ORM 框架之一。

SqlSugar 高性能,具有百万级插入、更新大数据分表等特色功能。

10.1.2 功能介绍

  • 支持 SqlServer、MySql、PgSql、Oracle 百万级插入和更新
  • 支持全自动分表
  • 支持多库事务
  • 支持 CodeFirst
  • 支持联表查询、嵌套查询、导航查询、子查询和动态 JSON 查询等查询操作
  • 支持配置查询
  • 支持工具生成实体和代码生成实体
  • 支持数据库 MySql、SqlServer、Sqlite、Oracle、postgresql、达梦、人大金仓、神通数据库

10.1.3 官网文档

点击以下链接可以跳转到 SqlSugar 官网查看详细 API

入门查询插入更新删 除
安装简单查询
入门联表

10.1.4 Furion 集成

  1. 创建一个拓展类:
public static class SqlsugarSetup
{
public static void AddSqlsugarSetup(this IServiceCollection services, IConfiguration configuration, string dbName = "db_master")
{
//如果多个数数据库传 List<ConnectionConfig>
var configConnection=new ConnectionConfig()
{
DbType = SqlSugar.DbType.MySql,
ConnectionString = configuration.GetConnectionString(dbName),
IsAutoCloseConnection = true,
};

SqlSugarScope sqlSugar = new SqlSugarScope(configConnection,
db =>
{
//单例参数配置,所有上下文生效
db.Aop.OnLogExecuting = (sql, pars) =>
{
//Console.WriteLine(sql);//输出sql
};
});

services.AddSingleton<ISqlSugarClient>(sqlSugar);//这边是SqlSugarScope用AddSingleton
}
}

使用注入

//1.构造函数注入
SqlSugar.ISqlSugarClient db;
public WeatherForecastController(ISqlSugarClient db)
{

this.db = db;
}

//2.手动获取
App.GetService<ISqlSugarClient>();
  1. Startup.cs 中注册:
services.AddSqlsugarSetup(App.Configuration);
小知识

如果需要多库配置,可查看 https://www.donet5.com/home/Doc?typeId=2246

10.1.5 特色功能

10.1.5.1 联表查询

  • Linq/Lambda
var query5 = db.Queryable<Order>()
.LeftJoin<Custom> ((o, cus) => o.CustomId == cus.Id)
.LeftJoin<OrderItem> ((o, cus, oritem ) => o.Id == oritem.OrderId)
.Where(o => o.Id == 1)
.Select((o, cus) => new ViewOrder { Id = o.Id, CustomName = cus.Name })
.ToList();
  • 生成 SQL
SELECT
[o].[Id] AS [Id],
[cus].[Name] AS [CustomName]
FROM
[Order] o
Left JOIN [Custom] cus ON ([o].[CustomId] = [cus].[Id])
Left JOIN [OrderDetail] oritem ON ([o].[Id] = [oritem].[OrderId])
WHERE
([o].[Id] = @Id0)

10.1.5.2 分页查询

int pageIndex = 1; 
int pageSize = 20;
int totalCount=0;
var page = db.Queryable<Student>().ToPageList(pageIndex, pageSize, ref totalCount);

10.1.5.3 动态表达式

  • Linq/Lambda
var names= new string [] { "a","b"};
Expressionable<Order> exp = new Expressionable<Order>();

foreach (var item in names)
{
exp.Or(it => it.Name.Contains(item.ToString()));
}

var list= db.Queryable<Order>().Where(exp.ToExpression()).ToList();
  • 生成 SQL
SELECT [Id],[Name],[Price],[CreateTime],[CustomId]
FROM [Order] WHERE (
([Name] like '%'+ CAST(@MethodConst0 AS NVARCHAR(MAX))+'%') OR
([Name] like '%'+ CAST(@MethodConst1 AS NVARCHAR(MAX))+'%')
)

10.1.5.4 仓储方法

新建一个仓储类,如果想扩展方法写到仓储类中

public class Repository<T> : SimpleClient<T> where T : class, new()
{
public Repository(ISqlSugarClient context = null) : base(context)//默认值等于null不能少
{
base.Context = App.GetService<ISqlSugarClient>();//用手动获取方式支持切换仓储
}
}

继承仓储类就可以使用仓储API了

//查询
var data1 = base.GetById(1);//根据id查询
var data4 = base.GetSingle(it => it.Id == 1);//查询单条记录,结果集不能超过1,不然会提示错误
var data = base.GetFirst(it => it.Id == 1);//查询第一条记录

var data2 = base.GetList();//查询所有
var data3 = base.GetList(it => it.Id == 1); //根据条件查询

var p = new PageModel() { PageIndex = 1, PageSize = 2 };
var data5 = base.GetPageList(it => it.Name == "xx", p);
Console.Write(p.PageCount);

var data6 = base.GetPageList(it => it.Name == "xx", p, it => it.Name, OrderByType.Asc);
Console.Write(p.PageCount);

List<IConditionalModel> conModels = new List<IConditionalModel>();
conModels.Add(new ConditionalModel(){FieldName="id",ConditionalType=ConditionalType.Equal,FieldValue="1"});//id=1
var data7 = base.GetPageList(conModels, p, it => it.Name, OrderByType.Asc);
base.AsQueryable().Where(x => x.Id == 1).ToList();

//插入
base.Insert(insertObj);
base.InsertRange(InsertObjs);
var id = base.InsertReturnIdentity(insertObj);
base.AsInsertable(insertObj).ExecuteCommand();

//删除
base.Delete(insertObj);
base.DeleteById(1);
base.DeleteByIds(new object [] { 1, 2 }); //数组带是 ids方法 ,封装传 object [] 类型
base.Delete(it => it.Id == 1);
base.AsDeleteable().Where(it => it.Id == 1).ExecuteCommand();

//更新
base.Update(insertObj);
base.UpdateRange(InsertObjs);
base.Update(it => new Order() { Name = "a", }, it => it.Id == 1);
base.AsUpdateable(insertObj).UpdateColumns(it=>new { it.Name }).ExecuteCommand();

//高级操作
base.AsSugarClient // 获取完整的db对象
base.AsTenant // 获取多库相关操作

//切换仓储
base.ChangeRepository<Repository<OrderItem>>() //支持多租户和扩展方法,使用SqlSugarScope单例(或者SqlSugarClient Scope注入)
base.Change<OrderItem>()//只支持自带方法和单库

10.1.5.5 多库事务

SqlSugarClient db = new SqlSugarClient(new List<ConnectionConfig>()
{
new ConnectionConfig(){ ConfigId="0", DbType=DbType.SqlServer, ConnectionString=Config.ConnectionString, IsAutoCloseConnection=true },
new ConnectionConfig(){ ConfigId="1", DbType=DbType.MySql, ConnectionString=Config.ConnectionString4 ,IsAutoCloseConnection=true}
});

var mysqldb = db.GetConnection("1"); // mysql db
var sqlServerdb = db.GetConnection("0"); // sqlserver db

db.BeginTran();

mysqldb.Insertable(new Order()
{
CreateTime = DateTime.Now,
CustomId = 1,
Name = "a",
Price = 1
}).ExecuteCommand();
mysqldb.Queryable<Order>().ToList();
sqlServerdb.Queryable<Order>().ToList();

db.CommitTran();

10.1.5.6 单例模式

public static SqlSugarScope Db = new SqlSugarScope(new ConnectionConfig()
{
DbType = SqlSugar.DbType.SqlServer,
ConnectionString = Config.ConnectionString,
IsAutoCloseConnection = true
},
db=> {
db.Aop.OnLogExecuting = (s, p) =>
{
Console.WriteLine(s);
};
});

using (var tran = Db.UseTran())
{
new Test2().Insert(XX);
new Test1().Insert(XX);
.....

tran.CommitTran();
}

10.1.5.7 全局过滤器

db.QueryFilter.Add(new TableFilterItem<Order>(it => it.Name.Contains("a")));  

db.Queryable<Order>().ToList();
// SELECT [Id],[Name],[Price],[CreateTime],[CustomId] FROM [Order] WHERE ([Name] like '%'+@MethodConst0+'%')

db.Queryable<OrderItem, Order>((i, o) => i.OrderId == o.Id)
.Where(i => i.OrderId != 0)
.Select("i.*").ToList();
// SELECT i.* FROM [OrderDetail] i ,[Order] o WHERE ( [i].[OrderId] = [o].[Id] ) AND
// ( [i].[OrderId] <> @OrderId0 ) AND ([o].[Name] like '%'+@MethodConst1+'%')

10.1.5.8 添加或者更新

var x = Db.Storageable(list2).ToStorage();  
x.AsInsertable.ExecuteCommand();
x.AsUpdateable.ExecuteCommand();
var x = Db.Storageable(list).SplitInsert(it => !it.Any()).ToStorage()
x.AsInsertable.ExecuteCommand();

10.1.5.9 自动分表

[SplitTable(SplitType.Year)] // Table by year (the table supports year, quarter, month, week and day)
[SugarTable("SplitTestTable_{year}{month}{day}")]
public class SplitTestTable
{
[SugarColumn(IsPrimaryKey =true)]
public long Id { get; set; }

public string Name { get; set; }

//When the sub-table field is inserted, which table will be inserted according to this field.
//When it is updated and deleted, it can also be convenient to use this field to
//find out the related table
[SplitField]
public DateTime CreateTime { get; set; }
}
var lis2t = db.Queryable<OrderSpliteTest>()
.SplitTable(DateTime.Now.Date.AddYears(-1), DateTime.Now)
.ToPageList(1,2); 

10.1.5.10 大数据插入,更新,插入或者更新

//Insert A million only takes a few seconds
db.Fastest<RealmAuctionDatum>().BulkCopy(GetList());

//update A million only takes a few seconds
db.Fastest<RealmAuctionDatum>().BulkUpdate(GetList());//A million only takes a few seconds完
db.Fastest<RealmAuctionDatum>().BulkUpdate(GetList(),new string[]{"id"},new string[]{"name","time"})//no primary key

//if exists update, else insert
var x= db.Storageable<Order>(data).ToStorage();
x.BulkCopy();
x.BulkUpdate();

//set table name
db.Fastest<RealmAuctionDatum>().AS("tableName").BulkCopy(GetList())

//set page
db.Fastest<Order>().PageSize(300000).BulkCopy(insertObjs);

10.1.5.11 更多功能

可查阅 SqlSugar 官网

10.1.6 反馈与建议

与我们交流

给 Furion 提 Issue


了解更多

想了解更多 SqlSugar 知识可查阅 SqlSugar 官网