Reputation: 4963
I hv written following function having two database context
in one transaction scope
. I am using MySql
with EF 5.0
private static void SyncPremiumStores(JoyRydeWebPortalData.joyryde_WebPortalEntities contextWebPortal, JoyRydeMallStoreData.joyryde_MallStoreEntities contextMallStore)
{
using (TransactionScope scope = new TransactionScope())
{
foreach (var objWebPortalPremiumStore in contextWebPortal.tbl_premium_store.Where(x => x.INT_DATA_TRANS_STATUS == 0).ToList())
{
try
{
var objMallStore = contextMallStore.tbl_store.Where(x => x.LNG_STORE_ID == objWebPortalPremiumStore.LNG_STORE_ID).FirstOrDefault();
if (objMallStore != null)
{
JoyRydeMallStoreData.tbl_premium_store objMallPremiumStore = new JoyRydeMallStoreData.tbl_premium_store()
{
DAT_CREATED = objWebPortalPremiumStore.DAT_CREATED,
DAT_PREMIUM_FROM = objWebPortalPremiumStore.DAT_PREMIUM_FROM,
DAT_PREMIUM_TO = objWebPortalPremiumStore.DAT_PREMIUM_TO,
LNG_PRIMARY_STORE_ID = objMallStore.LNG_PRIMARY_STORE_ID,
LNG_STORE_ID = objMallStore.LNG_STORE_ID,
TXT_PACK_NAME = ""
};
contextMallStore.tbl_premium_store.Add(objMallPremiumStore);
objWebPortalPremiumStore.INT_DATA_TRANS_STATUS = 1;
}
contextMallStore.SaveChanges();
contextWebPortal.SaveChanges();
scope.Complete();
}
catch (Exception ex)
{
LogUtility.WriteErrorLog(ex);
}
}
}
}
On execution, it throws me error on
var objMallStore = contextMallStore.tbl_store.Where(x => x.LNG_STORE_ID == objWebPortalPremiumStore.LNG_STORE_ID).FirstOrDefault();
line with inner exception message
Multiple simultaneous connections or connections with different connection strings inside the same transaction are not currently supported.
I need to update two different databases in single transaction. How it can be done ??
Upvotes: 2
Views: 2628
Reputation: 506
I believe this is a MySQL limitation, as only XA Transactions support distributed transactions (multiple separate transactional resources participating in a global transaction).
To the best of my knowlledge, the MySql .net connector does support distributed transactions though. Try setting AutoEnlist=false
in the connection string.
Upvotes: 6