C#, EntityFramework, LINQ 跨資料庫進行Transaction控制

利用 dbContext.Database.BeginTransaction() 來控制

範例程式

string ora_con_str = ConfigurationManager.ConnectionStrings["ora_cnn_str"].ToString();
string ms_con_str = ConfigurationManager.ConnectionStrings["ms_cnn_str"].ToString();
 
SqlConnection msDbConn = new SqlConnection(ms_con_str);
OracleConnection oraDbConn = new OracleConnection(ora_con_str);
 
msDbConn.Open();
oraDbConn.Open();
 
using (DbContext dbContextTT = new DbContext(oraDbConn, false))
using (DbContext dbContextMS = new DbContext(msDbConn, false))
{
    using (var transactionTT = dbContextTT.Database.BeginTransaction())
    using (var transactionMS = dbContextMS.Database.BeginTransaction())
    {
        try
        {
            sql = @"
            insert into TEST_ALEX(A,C) select '111', 33 from dual
            ";
            rec = dbContextTT.Database.ExecuteSqlCommand(sql);
 
            sql = @"
            insert into A (A1, A2, A3) select 'A11','22','33'
            ";
            rec = dbContextMS.Database.ExecuteSqlCommand(sql);
 
            transactionTT.Commit();
            transactionMS.Commit();
        }
        catch (Exception exp)
        {
            //不需要下Rollback, 後續離開[using transaction]的區塊, 會自動Rollback
            //MSSQL不知為何在執行失敗後, 會導致[transactionMS的connection變成null],無法執行Rollback, 但[transactionTT]卻不會發生connection為null情況, 可以正常執行Rollback
            //transactionMS.Rollback();
            //transactionTT.Rollback();
            throw exp;
        }
    }
}

利用TransactionScope (資料庫需開啟[MSDTC, 分散式交易])

using (TransactionScope scope = new TransactionScope())
{
    using (var dbContext1 = new MyDbContext1()) // 第一个数据库上的 DbContext
    using (var dbContext2 = new MyDbContext2()) // 第二个数据库上的 DbContext
    {
        try
        {
            // 在 dbContext1 上执行操作
            var entity1 = new MyEntity1 { Name = "Entity 1" };
            dbContext1.MyEntities1.Add(entity1);
            dbContext1.SaveChanges();
 
            // 在 dbContext2 上执行操作
            var entity2 = new MyEntity2 { Name = "Entity 2" };
            dbContext2.MyEntities2.Add(entity2);
            dbContext2.SaveChanges();
 
            // 提交分布式事务
            scope.Complete();
        }
        catch (Exception ex)
        {
            // 处理异常
            Console.WriteLine($"发生异常: {ex.Message}");
        }
    }
}