Is there a way to create an ADO.NET connection and ignore the ambient Transaction?

Question

I have a situation where I am running in a WCF service that has TransactionScopeRequired=true, which means there will always be an ambient transaction.

However, I need to start a new connection that will be around for the lifetime of the application, which means I can't have it use the abmbient transaction.

Any ideas on how to do this? Just doing this will automatically use the ambient transaction:

Assert.IsNotNull(System.Transactions.Transaction.Current);
var conn = new OracleConnection("my connection string");
conn.Open(); // <-- picks up ambient transaction, but I don't want that

Actually the example could be made simpler by saying this:

OracleConnection conn; // <-- this is actually held around in another object that has a very long lifetime, well past the TransactionScope.
using(var tx = new TransactionScope())
{
    conn = new OracleConnection("my connection string");
    conn.Open(); // <-- picks up ambient transaction, but I don't want that
    // ... do stuff
}

I don't want my connection to actually pick up the TransactionScope. In the actual code there is a lot more going on that does do DB actions within the scope, I just have 1 that I need to keep around past the lifetime of the transaction scope.

---

I guess the real situation is worth mentioning. What actually happens here is that during a WCF service call, I add an object to a cache using the Enterprise Library Caching block. This object is a data table, but also holds on to an open connection to Oracle that has Continuous Notification set up. This gives me the ability to automatically refresh my cached dataset when the underlying Oracle tables change.

The data cache item can be accessed by any number of WCF initialized threads, all of which run in their own transaction scope. I guess you could think of it as putting an OracleConnection object in a cache. A better block of text/exampe code would be like:

//beginning of a WCF service call
using (var tx = new TransactionScope())
{
    var conn = new OracleConnection();
    var cmd = new OracleCommand();
    // set up OCN on the cmd and connection
    var reader = cmd.ExecuteReader();
    cache.Add("conn", conn);
    cache.Add("cmd", cmd);
}

//beginning of a second wcf service call
using (var tx = new TransactionScope())
{
    var conn = cache.Get("conn");
    var cmd = cache.Get("cmd");
    var reader = cmd.ExecuteReader();
    // user reader to reload some data
}

Point being I have a connection that has a long lifetime across multiple threads and transaction scopes.

Answer 1

Have you tried one of the TransactionScope constructors that allows you to set the scope? Setting the scope to "Requires New" creates a new transaction for your connection to enlist in. Setting the scope to "Suppress" makes it so that your connection doesn't enlist in any transaction. At least, thats how I read the documentation. I've never had that specific need, myself.

using(var tx = new TransactionScope(TransactionScopeOption.RequiresNew))
{
    conn = new OracleConnection("my connection string");
    conn.Open();
}