How to determine whether SqlConnection is enlisted into a System.Transactions' tx or not?

Question

Hello. When we using a transation from System.Transactions (creating TransationScope for an instance) by default all Sql-connections (System.Data.SqlClient.SqlConnection) (but is't also the true for Oracle.DataAccess.OracleConnection) are enlisted on opening. That's called auto-enlistment. Nice feature. But it can be turned off throught a connection string's parameter (enlist=false). In that case connection being opened wouldn't be enlisted. But it can be enlisted manually later. So my question is: for some given SqlConnection's instance how can I determine whether that connection is enlisted or not (into an System.Transaction). I can look at connection string for the parameter. But this won't do because as I said connection could be enlisted manually.

Answer 1

The framework doesn't appear to allow that.

Perhaps we could discuss why you need to know this information? The TransactionScopeOptions give you some flexibility about when to create transactions.

However, refusing "no" for an answer, a little source browsing later and I've created this code, which DOES work. Note, that this code could cease to function at anytime with patches to the framework!!!!

    public static bool IsEnlisted(SqlConnection con)
    {
        object o = typeof(SqlConnection).GetField("_innerConnection", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(con);
        o = o.GetType().GetField("_enlistedTransaction", BindingFlags.Instance | BindingFlags.NonPublic);
        return o != null;
    }

Again, this is making use of private variables and internal classes within the .NET framework. While it works today, it may not tomorrow.

Answer 2

Thanks for your answer. Yes, I known the fact that needed for me information exists inside SqlConnection and OracleConnection. And surely, accessing private fields is very unstable solution.

So, why do I need such info?

I'll try be short ;) I'm developing some framework which have got a data-access layer. This layer unifies working with connection-command-parameter concepts. You know, using pure ado.net objects for different RDBMS is very awkward. So this layer is used by higher layer - persistence layer. The persistence layer's goal is persisting domain objects' state. And sometimes the persistence layer requires issuing a "save tran" statement (i.e. making a SAVEPOINT in a transaction). Different RDBMS has different point of view on allowing savepoints in a distributed transaction. MSSQL doesn't support such savepoints. Oracle does support. So in my Connection's abstraction (which inside holds real SqlConnection or OracleConnection) I have a property - SavepointAllowed. Code in this property must be able to learn is real underlying coonection (e.g. SqlConnection) enlisted into a distributed transaction or not. If it's enlisted then everything depends on "supporting savepoints in a distributed tx", otherwise then everything depends on "just supporting savepoints".