252 lines
9.9 KiB
Markdown
252 lines
9.9 KiB
Markdown
|
|
|||
|
|
<p align="center">
|
|||
|
|
<span>English</span> |
|
|||
|
|
<a href="https://www.donet5.com/Home/Doc"><font color="red">中文</font></a>
|
|||
|
|
</p>
|
|||
|
|
|
|||
|
|
## SqlSugar ORM
|
|||
|
|
SqlSugar is .NET open source ORM framework, maintained and updated by Fructose Big Data Technology team, the most easy-to-use ORM out of the box
|
|||
|
|
|
|||
|
|
Advantages: [Low code] [High performance] [Super simple] [Comprehensive features] [ Multi-database compatible] [Suitable products]
|
|||
|
|
|
|||
|
|
## Support .NET
|
|||
|
|
.net framework.net core3.1.ne5.net6.net7.net8 .net9 .net10
|
|||
|
|
|
|||
|
|
## Support database
|
|||
|
|
MySql、SqlServer、Sqlite、Oracle 、 postgresql、达梦、
|
|||
|
|
人大金仓(国产推荐)、神通数据库、瀚高、Access 、OceanBase
|
|||
|
|
TDengine QuestDb Clickhouse MySqlConnector、华为 GaussDB
|
|||
|
|
南大通用 GBase、MariaDB、Tidb、Odbc、Percona Server,
|
|||
|
|
Amazon Aurora、Azure Database for MySQL、
|
|||
|
|
Google Cloud SQL for MySQL、custom database
|
|||
|
|
|
|||
|
|
## Description
|
|||
|
|
1. Truly achieve zero SQL ORM table building, index and CRUD all support
|
|||
|
|
2. Support.NET millions of big data write, update, subtable and has billions of query statistics mature solutions
|
|||
|
|
3. Support SAAS complete application: cross-database query, audit, tenant sub-database, tenant sub-table and tenant data isolation
|
|||
|
|
4. Support low code + workflow (dynamic class building, dynamic table building, non-entity multi-library compatible with CRUD, JSON TO SQL, custom XML, etc.)
|
|||
|
|
5. Support ValueObject, discriminator, repository, UnitOfWork, DbContext, AOP
|
|||
|
|
|
|||
|
|
## Documentation
|
|||
|
|
|Other |Select | Insert | Update | Delete|
|
|||
|
|
| ----- | --------- | ----------- | ------- |------- |
|
|||
|
|
<a target="_blank" href="https://github.com/donet5/SqlSugar/wiki/NUGET">Nuget</a>| <a href="https://github.com/DotNetNext/SqlSugar/wiki/1.1--Query‐Simple">Query</a> | <a target="_blank" href="https://github.com/DotNetNext/SqlSugar/wiki/3.Insert"> Insert </a> |<a target="_blank" href="https://github.com/DotNetNext/SqlSugar/wiki/2.Update">Update</a>| <a target="_blank" href="https://github.com/DotNetNext/SqlSugar/wiki/4.1-Delete">Delete</a> |
|
|||
|
|
<a target="_blank" href="https://github.com/donet5/SqlSugar/wiki"> Start guide</a> | <a target="_bank" href="https://github.com/DotNetNext/SqlSugar/wiki/1.2-Query%E2%80%90Join">Join query </a> |<a href="https://github.com/DotNetNext/SqlSugar/wiki/3.2-Dynamic-Insert">Insert without entity </a> | <a href="https://github.com/DotNetNext/SqlSugar/wiki/2.2-Dynamic-Update">Update without entity</a> | <a href="https://github.com/DotNetNext/SqlSugar/wiki/4.2-Dynamic-Delete"> Delete without entity </a> | |
|
|||
|
|
|<a href="https://www.donet5.com/Home/Doc?typeId=2246">Multiple databases</a> | <a target="_bank" href="https://github.com/DotNetNext/SqlSugar/wiki/1.6-Query%E2%80%90Include">Include query</a>|<a target="_bank" href="https://www.donet5.com/Home/Doc?typeId=2430">Include Insert</a>| <a target="_bank" href="https://www.donet5.com/Home/Doc?typeId=2432">Include Update</a>| <a target="_bank" href="https://www.donet5.com/Home/Doc?typeId=2431">Include Delete</a>
|
|||
|
|
|<a href="https://www.donet5.com/Home/Doc"><font color="red">中文文档</font></a>|<a href="https://www.donet5.com/Home/Doc?typeId=2244">Cross database query</a>|<a href="https://www.donet5.com/Home/Doc?typeId=2420">Insert by json</a>|<a href="https://www.donet5.com/Home/Doc?typeId=2420">Update by json</a>|<a href="https://www.donet5.com/Home/Doc?typeId=2420">Delete by json</a>|
|
|||
|
|
|
|||
|
|
## Feature characteristic
|
|||
|
|
|
|||
|
|
### Feature1 : Join query
|
|||
|
|
Super simple query syntax
|
|||
|
|
```cs
|
|||
|
|
var query = db.Queryable<Order>()
|
|||
|
|
.LeftJoin<Custom> ((o, cus) => o.CustomId == cus.Id)
|
|||
|
|
.LeftJoin<OrderItem> ((o, cus, oritem ) => o.Id == oritem.OrderId)
|
|||
|
|
.LeftJoin<OrderItem> ((o, cus, oritem , oritem2) => o.Id == oritem2.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])
|
|||
|
|
Left JOIN [OrderDetail] oritem2 ON ([o].[Id] = [oritem2].[OrderId])
|
|||
|
|
WHERE
|
|||
|
|
([o].[Id] = @Id0)
|
|||
|
|
```
|
|||
|
|
### Feature2 :Include Query、Insert、Delete and Update
|
|||
|
|
```cs
|
|||
|
|
|
|||
|
|
//Includes
|
|||
|
|
var list=db.Queryable<Test>()
|
|||
|
|
.Includes(x => x.Provinces,x=>x.Citys ,x=>x.Street) //multi-level
|
|||
|
|
.Includes(x => x.ClassInfo)
|
|||
|
|
.ToList();
|
|||
|
|
|
|||
|
|
//Includes+left join
|
|||
|
|
var list5= db.Queryable<Student_004>()
|
|||
|
|
.Includes(x => x.school_001, x => x.rooms)
|
|||
|
|
.Includes(x => x.books)
|
|||
|
|
.LeftJoin<Order>((x, y) => x.Id==y.sid)
|
|||
|
|
.Select((x,y) => new Student_004DTO
|
|||
|
|
{
|
|||
|
|
SchoolId = x.SchoolId,
|
|||
|
|
books = x.books,
|
|||
|
|
school_001 = x.school_001,
|
|||
|
|
Name=y.Name
|
|||
|
|
})
|
|||
|
|
.ToList();
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
### Feature3 : Page query
|
|||
|
|
```cs
|
|||
|
|
|
|||
|
|
int pageIndex = 1;
|
|||
|
|
int pageSize = 20;
|
|||
|
|
int totalCount=0;
|
|||
|
|
var page = db.Queryable<Student>().ToPageList(pageIndex, pageSize, ref totalCount);
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
### Feature4 : Dynamic expression
|
|||
|
|
```cs
|
|||
|
|
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))+'%')
|
|||
|
|
)
|
|||
|
|
```
|
|||
|
|
### Feature5 : Multi-tenant transaction
|
|||
|
|
```cs
|
|||
|
|
//Creaate database object
|
|||
|
|
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();
|
|||
|
|
```
|
|||
|
|
### Feature6 : Singleton Pattern
|
|||
|
|
Implement transactions across methods
|
|||
|
|
```CS
|
|||
|
|
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();
|
|||
|
|
}
|
|||
|
|
```
|
|||
|
|
### Feature7 : Query filter
|
|||
|
|
```cs
|
|||
|
|
//set filter
|
|||
|
|
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+'%')
|
|||
|
|
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
### Feature8 : Insert or update
|
|||
|
|
insert or update
|
|||
|
|
```cs
|
|||
|
|
Db.Storageable(list2).ExecuteCommand();
|
|||
|
|
Db.Storageable(list2).PageSize(1000).ExecuteCommand();
|
|||
|
|
Db.Storageable(list2).PageSize(1000,exrows=> { }).ExecuteCommand();
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
### Feature9 : Auto split table
|
|||
|
|
Split entity
|
|||
|
|
```cs
|
|||
|
|
[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; }
|
|||
|
|
}
|
|||
|
|
```
|
|||
|
|
Split query
|
|||
|
|
```cs
|
|||
|
|
var lis2t = db.Queryable<OrderSpliteTest>()
|
|||
|
|
.SplitTable(DateTime.Now.Date.AddYears(-1), DateTime.Now)
|
|||
|
|
.ToPageList(1,2);
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
### Feature10 : Big data insert or update
|
|||
|
|
```cs
|
|||
|
|
10.1 BulkCopy
|
|||
|
|
db.Fastest<Order>().BulkCopy(lstData);//insert
|
|||
|
|
db.Fastest<Order>().PageSize(100000).BulkCopy(insertObjs);
|
|||
|
|
db.Fastest<System.Data.DataTable>().AS("order").BulkCopy(dataTable);
|
|||
|
|
|
|||
|
|
10.2 BulkUpdate
|
|||
|
|
db.Fastest<Order>().BulkUpdate(GetList())//update
|
|||
|
|
db.Fastest<Order>().PageSize(100000).BulkUpdate(GetList())
|
|||
|
|
db.Fastest<Order>().BulkUpdate(GetList(),new string[] { "Id"});//no primary key
|
|||
|
|
db.Fastest<Order>().BulkUpdate(GetList(), new string[]{"id"},
|
|||
|
|
new string[]{"name","time"})//Set the updated column
|
|||
|
|
//DataTable
|
|||
|
|
db.Fastest<System.Data.DataTable>().AS("Order").BulkUpdate(dataTable,"Id");//Id is primary key
|
|||
|
|
db.Fastest<System.Data.DataTable>().AS("Order").BulkUpdate(dataTable,"Id",Set the updated column);
|
|||
|
|
|
|||
|
|
|
|||
|
|
10.3 BulkMerge （5.1.4.109）
|
|||
|
|
db.Fastest<Order>().BulkMerge(List);
|
|||
|
|
db.Fastest<Order>().PageSize(100000).BulkMerge(List);
|
|||
|
|
|
|||
|
|
|
|||
|
|
10.4 BulkQuery
|
|||
|
|
db.Queryable<Order>().ToList();//Slightly faster than Dapper
|
|||
|
|
//Suitable for big data export
|
|||
|
|
List<Order> order = new List<Order>();
|
|||
|
|
db.Queryable<Order>().ForEach(it=> { order.Add(it); } ,2000);
|
|||
|
|
|
|||
|
|
10.5 BulkDelete
|
|||
|
|
db.Deleteable<Order>(list).PageSize(1000).ExecuteCommand();
|
|||
|
|
|
|||
|
|
```
|