Hi,

I am planning to use LLBLGen with SQL CE 3.5 database on the desktop application.

By default, DataAccessAdapter creates a new database connection, and disposed it internally. However, releasing a SQL CE database connection is an expensive operation - because there is no connection pool for SQL CE database, and also it releases database file. http://blogs.msdn.com/b/sqlservercompact/archive/2009/05/05/sql-server-compact-garbage-collection-whys-and-hows.aspx

To avoid a connection open/close for every DataAccessAdapter instance due to performance reason, I've made a new class that inherits from DataAccessAdapter, and overridden CreateNewPhysicalConnection as follow:

public class DataAccessAdapterExtended : DataAccessAdapter
{

    private System.Data.Common.DbConnection _connection;
    
    public MyDataAccessAdapter(DbConnection connection) : base(true)
    {
        // Provide an active database connection in CTor
        this._connection = connection;
    }

    protected override System.Data.Common.DbConnection CreateNewPhysicalConnection(string connectionString)
    {
        // Returns the same active database connection
        return this._connection;
    }
}

However, I can not dispose "DataAccessAdapterExtended" instance, because it disposes the physical connection instance internally. It means, there are some internal instances of DataAccessAdapter, which is not disposed correctly - and may cause a memory leak!

Would it be possible to have an another virtual method in DataAccessAdapterBase, which is responsible for disposing a database connection?

Class: DataAccessAdapterBase

protected virtual void DisposePhysicalConnection(System.Data.Common.DbConnection connection)
{
    // ... DataAccessAdapterBase should dispose the physical connection in this method
    // ... so developer can override this behaviour easily.
}

Currently, Adapter allows me to handle creation of the physical connection, but it does not allow me to manage dispose/closing - which causes me a trouble.

Would you please suggest me an alternative or any better way to handle this situation?

Kind Regards,

The first thing that pops up is why don't you extend the life of the adapter object as long as you need, while keeping the connection open by setting KeepConnectionOpen to true on the adapter?

Hi,

Thanks for your quick response.

The project uses Services and Repository pattern. Repository's responsibility to perform any database operation - and services do not have to know about it, and should not worry about how these database operation should be performed.

For example, OrderService communicates with OrderRepository for saving an order and it's lines. Also, the same service executes other services within a same transaction to update balance, stock, journal etc by calling BalanceService, StockService etc.

These services do not know what is an adapter. Also, if the project needs to change with another ORM, it can be done by just replacing Repository code only.

These services keep an active connection reference with a transaction - as services know about commit or rollback a transaction if any validation issue happens.

To achieve this separation, LLBLGen dlls have been referenced to repository layer only. The service layer does not have any reference to LLBLGen files.

Currently, Adapter allows me to handle creation of the physical connection, but does not allow me to manage dispose/closing - which seems an incorrect workflow. It's better to have an override method to manage connection disposal.

Thanks again, and happy to hear from your input on it.

Kind Regards,

An adapter instance creates 1 connection instance for the lifetime of the adapter. So when the adapter is disposed, the connection is disposed. The adapter closes the connection when it thinks it doesn't need it to be open anymore. It does that by calling the virtual method CloseConnection().

the method you propose has no value, as the connection object isn't disposed during the lifetime of the adapter object, it's disposed when the adapter is disposed. Dispose is done with the virtual method Dispose(bool).

You have all 3 methods at your disposal to customize the workflow: - CreateNewPhysicalConnection - CloseConnection - Dispose(bool).

You can override all 3 in a partial class of DataAccessAdapter in your generated project and customize the code in the base' method.

What I don't understand is what you're planning to do: you can keep an adapter instance alive in each repository and that will already solve your problem. If that's not your plan (and your code you posted is therefore not usable) and if you're planning to share the open connection object across repositories, I've to advice against it. Don't share connections across adapter instances.

Hi Otis,

Thanks for your response.

The following methods have been override already to customize the workflow: - CreateNewPhysicalConnection - Return an original active connection (provided in CTor). - CloseConnection - Do Nothing as do not want to close a connection. - Dispose(bool) - Do nothing to avoid an active connection disposal. It's better to Dispose this instance to release internal unmanaged memory - except an active connection as it does not create it.

The active connection has been provided to DataAccessAdapter, and it's expected to keep it an active connection after the disposal of the DataAccessAdapter.

Currently, DataAccessAdapter is not allowed at services layer, so it can not be shared against other repositories, and it must be disposed without closing an active connection. Is there any downside for keeping an active connection? Please note that TransactionScope is managed by services as well.

As I said before, the project uses SQL CE 3.5 database, and closing/opening a connection causes a performance problem because there is no connection pool.

It would be better to have a protected method "DisposePhysicalConnection", which is called from "Dispose()" method - so it can be overridden easily.

Kind Regards,

I think the service instance should keep the referenced repository instance live as much as needed. And in turn the repository is holding on the Adapter instance, which has an open connection. This way you don't need to keep connections opened across adapters.