Technology Sharing

한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina

Using SqlSugar for multi-database operations mainly involves the following steps:

1. Configure database connection

First, you need to configure the project in the configuration file (such asappsettings.json、web.configorapp.config) to configure multiple database connection strings. Each connection string corresponds to a different database.

For example, inappsettings.jsonYou can configure it like this:

json复制代码

2. Create a SqlSugarClient instance

For each database you need to operate, you need to create aSqlSugarClient, and pass in the corresponding database connection configuration when creating it.

csharp复制代码

3. Perform database operations

Now, you can use theseSqlSugarClientInstances are used to perform database operations. According to your business needs, select the corresponding instance to operate the corresponding database.

csharp复制代码

4. Notes on cross-database operations

• Cross-database query: SqlSugar itself does not directly support cross-database joint queries (JOIN). If you need to perform joint queries between different databases, you may need to merge data at the application layer, or consider using a database-level solution (such as SQL Server's linked server).
• Transaction Processing: For operations that require data consistency across multiple databases, you may need to use distributed transactions. SqlSugar itself does not directly provide support for distributed transactions, but you can use .NET'sSystem.Transactionsnamespace to manage distributed transactions. However, be aware of the performance and complexity of distributed transactions.
• Performance Considerations: When performing multi-database operations, consider the performance and load of each database. Avoid performing a large number of concurrent operations on multiple databases at the same time, which may cause performance issues.

5. Conclusion

Using SqlSugar for multi-database operations mainly involves configuring multiple database connections, creating multipleSqlSugarClientInstances are created and the corresponding instances are selected to perform database operations based on business needs. It should be noted that cross-database queries and distributed transaction processing may require additional consideration and configuration. In addition, attention should be paid to the performance and load of each database to ensure the stability and performance of the system.

Attached is the SqlSugar application code:

using SqlSugar;

//Create a database object (use the same method as EF Dappper to ensure thread safety through new)
SqlSugarClient Db = new SqlSugarClient(new ConnectionConfig()
{
    ConnectionString = "datasource=demo.db",
    DbType = DbType.Sqlite,
    IsAutoCloseConnection = true
},
db => {
 
    db.Aop.OnLogExecuting = (sql, pars) =>
    {

//Get native SQL recommendation 5.1.4.63 Performance OK
        Console.WriteLine(UtilMethods.GetNativeSql(sql, pars));

//Getting non-parameterized SQL has an impact on performance, especially for large SQL with many parameters, debugging is required
        //Console.WriteLine(UtilMethods.GetSqlString(DbType.SqlServer,sql,pars))

    };

//Note that there are several settings for multi-tenancy
    //db.GetConnection(i).Aop

});

//Build database
Db.DbMaintenance.CreateDatabase(); // DAMO and Oracle do not support database creation

//Create table (see document migration)
Db.CodeFirst.InitTables<Student> (); //All libraries support

//Query all the tables
var list = Db.Queryable<Student>().ToList();

//insert
Db.Insertable(new Student() { SchoolId = 1, Name = "jack" }).ExecuteCommand();

//renew
Db.Updateable(new Student() { Id = 1, SchoolId = 2, Name = "jack2" }).ExecuteCommand();

//delete
Db.Deleteable<Student>().Where(it => it.Id == 1).ExecuteCommand();

Console.WriteLine("Congratulations, you have already gotten started. You only need to look up the documentation for what you need later.");
Console.ReadKey();
 

//The entity has the same structure as the database
public class Student
{
//The data is self-incrementing and needs to add IsIdentity
//The database is the primary key, you need to add IsPrimaryKey
//Note: Two properties must be completely consistent with the database
    [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
    public int Id { get; set; }
    public int? SchoolId { get; set; }
    public string? Name { get; set; }
}