I came across some very nice code from Alazel Acheson that shows how to create a connection scope class, which can manage database connection lifetimes per thread across multiple methods. This offers better performance when used with TransactionScope, so multiple adapters could be used without the transaction being promoted to a distributed transaction. The original article is http://blogs.msdn.com/dataaccess/archive/2006/02/14/532026.aspx with an update at http://blogs.msdn.com/dataaccess/archive/2006/03/29/564299.aspx

I've made a new class that inherits from DataAccessAdapter, and overridden CreateNewPhysicalConnection to work with the ConnectionScope class. This works well, but I've run into an issue when the adapter is disposed. By default, the adapter will close and dispose the connection, but I want to avoid this because the ConnectionScope was made to manage the connection. For now, I've simply overridden Dispose so that nothing gets disposed if I'm in a system transaction with a valid ConnectionScope.

Is there a way I can dispose of everything else in the adapter except for the connection? Do I even need to worry about disposing managed resoruces inside adapter? A new overload for Dispose in DataAccessAdapterBase would do the trick, or make _activeConnection protected instead of private so I could simply set it to null... Or perhaps make this a built-in feature? Any other suggestions are welcome.

Which database are you using? I know that with Firebird and Oracle it is important to dispose the adapter.

I'm using SQL 2005, along with LLBLGen v2.

Is there a way I can dispose of everything else in the adapter except for the connection?

As you did by inherting from the DataAccessAdapter, where you can override the Dispose method. Or you could modify the daaAdapter.template to add the overriden method.

Do I even need to worry about disposing managed resoruces inside adapter?

I don't think so.

A new overload for Dispose in DataAccessAdapterBase would do the trick, or make _activeConnection protected instead of private so I could simply set it to null... Or perhaps make this a built-in feature? Any other suggestions are welcome

Same as before you can have your code in a sub-class of your own, or modify the template that generates the code.

The thing is, the Dispose method I'm overriding is from DataAccessAdapterBase, which is in the ORMSupportClasses dll. I didn't think that this could be customized through the templates, can it?

Anyways, my Dispose method looks like this at the moment:

protected override void Dispose(bool isDisposing)
{
    // Check if we're in a system transaction with a valid connection scope
    if (this.InSystemTransaction && DbConnectionScope.Current != null)
    {
        // TODO: Dispose everything except the connection...
        // Do not call base.Dispose otherwise the connection will be closed
    }
    else
        base.Dispose(isDisposing)
}

The base dispose method has been posted here - http://llblgen.com/TinyForum/Messages.aspx?ThreadID=6656#38373 and it shows all of the objects being disposed... I just want to make sure that the private _physicalTransaction and _systemTransaction objects are being cleaned up properly, but I can't access those fields from my Dispose function.

In a derived class you should call the base' dispose method after your own dispose code.

I agree that calling the base dispose method should always be done. However, what's missing in the current implementation of DataAccessAdapterBase is a way to leave the connection open when the adapter is disposed. I've hacked together a workaround using reflection, but ideally there would be a built-in solution...

Backslash wrote:

I agree that calling the base dispose method should always be done. However, what's missing in the current implementation of DataAccessAdapterBase is a way to leave the connection open when the adapter is disposed. I've hacked together a workaround using reflection, but ideally there would be a built-in solution...

That's by definition not what's it designed for, as a connection is tied to an adapter. What some people have done in the past is overriding the CreatePhysicalConnection routine to make it possible to pass a connection to an adapter. However IMHO it's easier to simply pass the adapter around.

Hmm, I was hoping to avoid passing the adapter around, but that would do the trick. I think I'll just stick with my workaround for the time being. For those that are interested, the TransactionScope limitation is discussed further here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=584296&SiteID=1

Thanks for the quick support!