一、DbContext.Database.BeginTransactionAsync() 模式
1. 注意事項：連接字符串中啟用了?MARS（Multiple Active Result Sets：MultipleActiveResultSets=True ）后，無法創建 保存點（保存點與 SQL Server 的多重活動結果集不兼容），此時，使用注入的 DbContext（例如：@inject ApplicationDbContext? FirstDb） 將報錯。解決辦法：在 事務 中自己 new 一個 DbContext。
"ConnectionStrings": {"Ky1SqlServerConnection": "Server=localhost;Database=kyglxt;Trusted_Connection=True;MultipleActiveResultSets=True;TrustServerCertificate=True;User=sa;Password=123456","Ky2SQLiteConnection": "Data Source=SQLiteFile.db",}?上述語句為 appsettings.json 中保存的 連接字符串。
/* SQL Server 事務測試 —— ok
* 注：Multiple Active Result Sets(MARS) is enabled：MultipleActiveResultSets = True; 時，不能使用注入的 DbContext，在 事務 中自己 new 一個 DbContext。*/
try
{using (var FirstDb = new KyglxtContext(new DbContextOptionsBuilder<KyglxtContext>().UseSqlServer(AppsettingsJsonService.Configuration["ConnectionStrings:Ky1SqlServerConnection"]).Options)){var transaction = await FirstDb.Database.BeginTransactionAsync();var FirstDbEntity01 = new 用戶表 { 用戶名 = $"Entity in FirstDb：{DateTime.Now}" };FirstDb.Add(FirstDbEntity01);await FirstDb.SaveChangesAsync();System.Console.WriteLine("————save 1，end");await Task.Delay(2000);System.Console.WriteLine("————delay end");var FirstDbEntity02 = new 用戶表 { 名稱 = $"Entity in FirstDb：{DateTime.Now}" };FirstDb.Add(FirstDbEntity02);await FirstDb.SaveChangesAsync();System.Console.WriteLine("————save 2，end");// Commit transaction if all commands succeed, transaction will auto-rollback when disposed if either commands failsawait transaction.CommitAsync();System.Console.WriteLine("————try，ok");}
}
catch (Exception ex)
{System.Console.WriteLine(ex.Message);System.Console.WriteLine(ex.InnerException?.Message);
}
finally { }2. 對于?環境事務（TransactionScope） ，SQLite 不支持，針對多個數據庫使用 事務 功能時，不能包含?SQLite。解決辦法1：生產環境，SQLite 換成?SQL Server 。解決辦法2：手動處理。
/* 手動處理 多個數據庫，支持 SQLite */try{using var FirstDb = new KyglxtContext(new DbContextOptionsBuilder<KyglxtContext>().UseSqlServer(AppsettingsJsonService.Configuration["ConnectionStrings:Ky1SqlServerConnection"]).Options);using var SecondDb = new ApplicationDbContext(new DbContextOptionsBuilder<ApplicationDbContext>().UseSqlite(AppsettingsJsonService.Configuration["ConnectionStrings:Ky2SQLiteConnection"]).Options);var FirstTransaction = await FirstDb.Database.BeginTransactionAsync();var SecondTransaction = await SecondDb.Database.BeginTransactionAsync();try{var firstEntity = new 用戶表 { 用戶名 = $"BeginTransactionAsync，2：{DateTime.Now}" };FirstDb.Add(firstEntity);await FirstDb.SaveChangesAsync();System.Console.WriteLine("FirstDb————save 1，end");await Task.Delay(2000);System.Console.WriteLine("BeginTransactionAsync，2————————delay end");var secondEntity = new ApplicationUser { UserName = $"BeginTransactionAsync，2：{DateTime.Now}" };SecondDb.Add(secondEntity);await SecondDb.SaveChangesAsync();System.Console.WriteLine("SecondDb————save 2，end");await FirstTransaction.CommitAsync();await SecondTransaction.CommitAsync();System.Console.WriteLine("BeginTransactionAsync，2——————try，ok");}catch (Exception ex){await FirstTransaction.RollbackAsync();await SecondTransaction.RollbackAsync();System.Console.WriteLine($"BeginTransactionAsync，2——————catch，{ex.Message}");System.Console.WriteLine($"BeginTransactionAsync，2——————catch，{ex.InnerException?.Message}");}}catch (Exception ex){System.Console.WriteLine($"BeginTransactionAsync，2——————catch，{ex.Message}");System.Console.WriteLine($"BeginTransactionAsync，2——————catch，{ex.InnerException?.Message}");}finally{System.Console.WriteLine("BeginTransactionAsync，2——————finally：");}二、環境事務，System.Transactions、TransactionScope：支持操作多個數據庫
/* 環境事務：支持操作多個數據庫，但是 SQLite 不支持 TransactionScope */try{using (var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled)){using (var FirstDb = new KyglxtContext(new DbContextOptionsBuilder<KyglxtContext>().UseSqlServer(AppsettingsJsonService.Configuration["ConnectionStrings:Ky1SqlServerConnection"]).Options)){var firstEntity = new 用戶表 { 用戶名 = $"TransactionScope：{DateTime.Now}" };FirstDb.Add(firstEntity);await FirstDb.SaveChangesAsync();System.Console.WriteLine("FirstDb————save 1，end");await Task.Delay(2000);System.Console.WriteLine("TransactionScope————————delay end");using (var SecondDb = new ApplicationDbContext(new DbContextOptionsBuilder<ApplicationDbContext>().UseSqlite(AppsettingsJsonService.Configuration["ConnectionStrings:Ky2SQLiteConnection"]).Options)){var secondEntity = new ApplicationUser { UserName = $"TransactionScope：{DateTime.Now}" };SecondDb.Add(secondEntity);await SecondDb.SaveChangesAsync();}System.Console.WriteLine("SecondDb————save 2，end");}// Commit transaction if all commands succeed, transaction will auto-rollback when disposed if either commands failsscope.Complete();System.Console.WriteLine("TransactionScope——————try，ok");}}catch (Exception ex){// Handle the exception (e.g., log it)System.Console.WriteLine($"TransactionScope——————catch，{ex.Message}");System.Console.WriteLine($"TransactionScope——————catch，{ex.InnerException?.Message}");}finally{System.Console.WriteLine("TransactionScope——————finally：");}
