CODEWITHSUNDEEP
c#mysqlentity-frameworktransactionscope
Rajeev Kumar
Rajeev Kumar

Reputation: 4963

Handle multiple database connection in one transaction scope

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

Answers (1)

James Gardner
James Gardner

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

Related Questions