-
Notifications
You must be signed in to change notification settings - Fork 1.3k
Home
SqlSugar boasts exceptional performance, with built-in support for batch processing and pagination. It is a multi-database ORM with a robust underlying architecture. Unlike Entity Framework (EF), which primarily supports a single database with third-party extensions for additional databases, SqlSugar is inherently designed for multiple databases. Each database function in SqlSugar adheres to a standard, eliminating the need to manage differences between functions manually. In areas such as multi-database transactions, database and table creation, SqlSugar is more powerful. It supports multiple databases automatically without requiring commands or setting field type. Additionally, all operations in SqlSugar are ready to use out-of-the-box, without the need for design patterns, making it particularly suitable for rapid development needs.
| .net framework | .net core |
|---|---|
| Install-Package SqlSugar | Install-Package SqlSugarCore |
We can operate the database through SqlSugarClient or SqlSugarScope, both of which have the same APIs but different modes.
(1) SqlSugarClient: Native Mode to Access the Database
(2) SqlSugarScope: Singleton Mode to Access the Database
High Performance: Its usage is similar to Dapper, Ado, and EF. It cannot be used as a singleton. Each context requires a new object, ensuring ultra-high performance. Improper usage may lead to occasional errors. Create a new object through IOC or db.CopyNew() to avoid using the same object in multiple contexts. Detailed explanation of thread issues when using as a singleton.
Question: Why is creating a new object efficient? Because a new C# object doesn't occupy much memory or CPU until there are over 1 million concurrent requests. The database operations are managed by ADO's connection pool, and .NET ORM's performance far exceeds Java's.
Medium to High Performance: Friendly to beginners, it can solve all thread safety issues except for asynchronous operations while maintaining good performance. Its principle is implemented through AsyncLocal. For asynchronous concurrent operations like Task.WhenAll or when not writing await, use db.CopyNew() to ensure thread safety.
Both the normal API and the proper use of async automatically handle threading issues, the principle being the AsyncLocal implementation. But there are a few things you need to know about thread safety Special cases require CopyNew to handle threads
- TaskWhenAll
- scheduled tasks
var newDb=db.CopyNew();Note: It can only be used as a singleton. Continuously creating new objects is not recommended as it can lead to memory and thread issues. Detailed explanation of these issues.
using SqlSugar;
// Create a database object (ensure thread safety by creating a new instance each time, similar to EF and Dappper)
SqlSugarClient Db = new SqlSugarClient(new ConnectionConfig()
{
ConnectionString = "datasource=demo.db",
DbType = DbType.Sqlite,
LanguageType=LanguageType.English,
IsAutoCloseConnection = true
},
db => {
db.Aop.OnLogExecuting = (sql, pars) =>
{
// Get the native SQL (recommended for version 5.1.4.63 and above, good performance)
Console.WriteLine(UtilMethods.GetNativeSql(sql, pars));
// Get the non-parameterized SQL (affects performance, especially for large SQLs with many parameters, use for debugging)
// Console.WriteLine(UtilMethods.GetSqlString(DbType.SqlServer, sql, pars))
};
// Note: For multi-tenancy, set as many as you need
// db.GetConnection(i).Aop
});
// Create a database (not supported by Dameng and Oracle)
Db.DbMaintenance.CreateDatabase();
// Create tables (refer to the documentation for migration)
Db.CodeFirst.InitTables<Student>(); // Supported by all databases
// Query all records from the table
var list = Db.Queryable<Student>().ToList();
// Insert
Db.Insertable(new Student() { SchoolId = 1, Name = "jack" }).ExecuteCommand();
// Update
Db.Updateable(new Student() { Id = 1, SchoolId = 2, Name = "jack2" }).ExecuteCommand();
// Delete
Db.Deleteable<Student>().Where(it => it.Id == 1).ExecuteCommand();
// Entity class that matches the database structure
public class Student
{
[SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
public int Id { get; set; }
public int? SchoolId { get; set; }
public string? Name { get; set; }
}In this section, we will use SqlSugarClient with the Scope dependency injection pattern.
// Register context: You can obtain IOC objects in AOP. If there is an existing framework like Furion, this line can be omitted.
services.AddHttpContextAccessor();
// Register SqlSugar using AddScoped
services.AddScoped<ISqlSugarClient>(s =>
{
// Use SqlSugarClient with Scoped
SqlSugarClient sqlSugar = new SqlSugarClient(new ConnectionConfig()
{
DbType = SqlSugar.DbType.Sqlite,
ConnectionString = "DataSource=sqlsugar-dev.db",
IsAutoCloseConnection = true,
LanguageType=LanguageType.English
},
db =>
{
// This will be executed for each context
// Obtain IOC objects without requiring the same context
// var log = s.GetService<Log>();
// Obtain IOC objects requiring the same context
// var appService = s.GetService<IHttpContextAccessor>();
// var log = appService?.HttpContext?.RequestServices.GetService<Log>();
db.Aop.OnLogExecuting = (sql, pars) =>
{
// Logging or other logic here
};
});
return sqlSugar;
});
public class MyClass
{
private readonly ISqlSugarClient _db;
public MyClass(ISqlSugarClient db)
{
_db = db;
}
// Your methods using _db here
}This configuration ensures that SqlSugarClient is properly registered and available for dependency injection throughout your application.
SqlSugarScope should be used in singleton mode, with two configuration cycles:
(A) Global Effect
(B) Current Context Effect
using SqlSugar;
// Create database
SqlSugarHelper.Db.DbMaintenance.CreateDatabase(); // Dameng and Oracle do not support database creation
// Create table (refer to migration documentation)
SqlSugarHelper.Db.CodeFirst.InitTables<Student>(); // Supported by all databases
// Query all rows from the table
var list = SqlSugarHelper.Db.Queryable<Student>().ToList();
// Insert
SqlSugarHelper.Db.Insertable(new Student() { SchoolId = 1, Name = "jack" }).ExecuteCommand();
// Update
SqlSugarHelper.Db.Updateable(new Student() { Id = 1, SchoolId = 2, Name = "jack2" }).ExecuteCommand();
// Delete
SqlSugarHelper.Db.Deleteable<Student>().Where(it => it.Id == 1).ExecuteCommand();
public class SqlSugarHelper // Cannot be a generic class
{
// Instructions for multi-database usage:
// For fixed multiple databases, you can pass new SqlSugarScope(List<ConnectionConfig>, db => {}). See the documentation on multi-tenancy.
// For variable multiple databases, refer to the SaaS sub-database documentation.
// Use singleton pattern
public static SqlSugarScope Db = new SqlSugarScope(new ConnectionConfig()
{
ConnectionString = "datasource=demo.db", // Connection string
DbType = DbType.Sqlite, // Database type
IsAutoCloseConnection = true ,// If not set to true, manual close is required
LanguageType=LanguageType.English
},
db => {
// (A) Global effect configuration point, generally used for AOP and program startup configurations, effective for all contexts
// Debugging SQL event, can be removed
db.Aop.OnLogExecuting = (sql, pars) =>
{
// Recommended to get native SQL, performance is OK for version 5.1.4.63
Console.WriteLine(UtilMethods.GetNativeSql(sql, pars));
// Get non-parameterized SQL, affects performance especially with large SQL and many parameters, use for debugging
// Console.WriteLine(UtilMethods.GetSqlString(DbType.SqlServer, sql, pars))
};
// Add multiple configurations below
// db.Ado.IsDisableMasterSlaveSeparation = true;
// Note: For multi-tenancy, configure as many as needed
// db.GetConnection(i).Aop
});
}
// Entity class matching the database structure
public class Student
{
// Add IsIdentity if data is auto-increment
// Add IsPrimaryKey if database is primary key
// Note: Both attributes must be consistent with the database
[SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
public int Id { get; set; }
public int? SchoolId { get; set; }
public string? Name { get; set; }
}Verify Singleton Success SqlSugarHelper.Db.HasCode(); // Check if the hash code remains the same after service startup, indicating success For IOC, directly copying the demo ensures verification is not needed.
Setting Timeout Duration
// SQL command timeout
o.CommandTimeOut = 30; // in seconds
// Connection timeout
// Add "Connection Timeout=10" to the connection string for db.Open. The default is 30 seconds, in seconds.Returns true if the database connection is successful:
db.Ado.IsValidConnection();
Long Connection Usage (Three Methods)
Long connection within a transaction// Transactions can also be used to implement long links
using (db.Ado.OpenAlways())
{
db.Queryable...
db.Insertable...
// For example, temporary tables that are effective for the current session require a long connection; otherwise, they cannot be accessed after creation.
}It is recommended to use the default setting to avoid errors with multiple database types. However, you can also enforce a custom setting.
db.CurrentConnectionConfig.MoreSettings = new ConnMoreSettings
{
DbMinDate = DateTime.MinValue // The default minimum date is 1900-01-01 00:00:00.000
};