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=1var 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 dbvar 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 secondsdb.Fastest<RealmAuctionDatum>().BulkCopy(GetList()); //update A million only takes a few secondsdb.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 namedb.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 官网

演练场