.NET9.0的BlazorWebApp项目中EFCore【事务】使用备忘

一、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 fails await 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 fails scope.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："); }