Managing distributed transactions without enabling MSDTC...

Is it possible? Short answer is no. Long answer is yes, but only to certain extend. Another stupid attempt to solve problem in a situation where developers are the lowest being in the organisation. In the perfect world where things working as expected, dealing with distributed transaction is a snap.

    ...
    using(TransactionScope scope = new TransactionScope())
    {
        using (SqlConnection conn1 ...)
        {
            ...
        }

        using (OracleConnection conn2 ...)
        {
            ...
        }

        scope.Complete();
    }
    ...

However the nightmare begin when your Server/DB admin give you a big "NO" to MSDTC but the other party insisted for data rollback if anything goes wrong during the process. So what I did was to create my own transaction manager class. Before I begin, I need to have a wrapper class that contain a connection and transaction objects, this is where the begintransaction happen. The class will be manage by the transaction manager. Technically it's just managing a group of local transactions.

    using System;
    using System.Data;
    
    public class EnlistedDBConnection : IDisposable
    {
        private IDbConnection _connection;
        private IDbTransaction _transaction;
        
        public IDbConnection Connection
        {
            get { return _connection; }
        }
        
        public IDbTransaction Transaction
        {
            get { return _transaction; }
        }
        
        public EnlistedDBConnection(IDbConnection connection)
        {
            _connection = connection;
            
            if (_connection.State == ConnectionState.Closed) _connection.Open();
            _transaction = connection.BeginTransaction();
        }
        
        internal void Commit()
        {
            _transaction.Commit();
        }
        
        internal void Rollback()
        {
            _transaction.Rollback();
        }

        #region IDisposable Members

        public void Dispose()
        {
            if (_connection != null && _connection.State == ConnectionState.Open) _connection.Close();
            if (_connection != null) _connection.Dispose();
            if (_transaction != null) _transaction.Dispose();
        }

        #endregion
    }

Code for the transaction manager. Before commiting, I need to ensure all connections are alive, if any of it down, cancel everything. However there's still one worst case scenario that this code couldn't handle, after done with connection checking, while committing suddenly one connection down in the process. It is not possible to rollback the committed transaction. So chances for orphan data are still there.

    using System;
    using System.Data;
    using System.Collections.Generic;
    
    public class BasicTransactionManager : IDisposable
    {
        private bool _isCommited;
        private List<EnlistedDBConnection> _enlistedConnections;
    
        public BasicTransactionManager() : this(new List<EnlistedDBConnection>())
        {
        }
        
        private BasicTransactionManager(List<EnlistedDBConnection> enlistedConnections)
        {
            _enlistedConnections = enlistedConnections; 
        }
        
        public EnlistedDBConnection Enlist(IDbConnection connection)
        {
            EnlistedDBConnection item = new EnlistedDBConnection(connection);
            _enlistedConnections.Add(item);
            
            return item;
        }
        
        public void Complete()
        {
            Commit();
        }
        
        /// 

        /// While in the loop, worst case scenario that this logic couldn't handle is that
        /// if the first transaction committed, then the second one failed, chances for orphan data
        /// to occur are there because we couldn't rollback something that has been commited.
        /// 
        private void Commit()
        {
            // if one of the db connection failed, cancel everything
            if(!VerifyConnection()) throw new Exception("DB connection failed.");
            
            string message = String.Empty;
            foreach (EnlistedDBConnection enlistedConnection in _enlistedConnections)
            {
                try
                {
                    enlistedConnection.Commit();
                }
                catch(Exception ex)
                {
                    message += String.Format("{0}\r\n", ex.Message);
                }
            }
            if(!String.IsNullOrEmpty(message)) throw new Exception(message);
            
            _isCommited = true;
        }
        
        private void Rollback()
        {
            string message = String.Empty;
            foreach (EnlistedDBConnection enlistedConnection in _enlistedConnections)
            {
                if(enlistedConnection.Connection != null && enlistedConnection.Connection.State == ConnectionState.Open)
                {
                    try
                    {
                        enlistedConnection.Rollback();
                    }
                    catch (Exception ex)
                    {
                        message += String.Format("{0}\r\n", ex.Message);
                    }
                }
            }
            if (!String.IsNullOrEmpty(message)) throw new Exception(message);
        }
        
        private bool VerifyConnection()
        {
            bool _allOpened = true;
            
            foreach(EnlistedDBConnection enlistedConnection in _enlistedConnections)
            {
                if(enlistedConnection.Connection == null || enlistedConnection.Connection.State != ConnectionState.Open)
                {
                    _allOpened = false;
                    break;
                }
            }
            
            return _allOpened;
        }

        #region IDisposable Members

        public void Dispose()
        {
            if (!_isCommited) Rollback();
            
            _enlistedConnections.ForEach( delegate(EnlistedDBConnection item) { item.Dispose(); } );
            // _enlistedConnections.ForEach( item => item.Dispose() );
        }

        #endregion
    }

This is how I use it in my business object. EnlistedDBConnection exposed connection and transaction property. That's how I obtain the instance and pass it to Command object.

    using (BasicTransactionManager transaction = new BasicTransactionManager())
    {
        EnlistedDBConnection dbcon1 = transaction.Enlist(/* your db connection object */);
        EnlistedDBConnection dbcon2 = transaction.Enlist(/* your db connection object */);

        ...

        transaction.Complete();
    }

Note:
This solution is not by any mean to be a replacement to MSDTC (not even in your wet dream). However, it is better to have something rather than nothing. I'd rather use this solution instead of executing another command to undo the changes manually which I feel ridiculous.