To encapsulate database connection in business objects or not?

Question

I usually like to create the database connection myself and control its lifetime manually with `using{}'. For example:

SqlConnection sqlConnection = new SqlConnection( connectionString );
using( sqlConnection ) {
    BusinessObject myBusinessObject = new BusinessObject( sqlConnection );
    // do stuff with the business object
    ...
}

This way it is visible and obvious that I am using a resource that needs to be cleaned up appropriately. However this does end up being a lot of repetitive effort. I'm tempted to create the Sql connection inside the business object and implement IDisposable on it. I would close the connection in the Dispose() method.

using( BusinessObject myBusinessObject = new BusinessObject() ) {
    // do stuff with myBusinessObject
    ...
}

The problem I'm having is that it might not be that obvious that the business object needs to be disposed unless you see it in use.

How would you guys do it?

Answer 1

Well, first I'd leave connections to the repository.

Second, I wouldn't keep a connection hanging around on an object - I'd only use it for a unit of work (i.e. a single method). The chances are that (due to pooling) you'll get back the same physical connection anyway. The system already goes a long way to handling such things so that you don't have to.

The trickier case is transactions, where TransactionScope is a lot easier than passing a db-transaction object around.

Answer 2

Business objects should be reasonably (or completely) dumb with respect to the database. You should implement some sort of access layer object (repository or data context) that knows how to persist your business objects to the database and keep the connection logic there rather than putting the code in each of your business objects. Your repository or context would be disposable so that it could clean up after itself. @Marc's suggestion that you follow the Unit of Work pattern is a good one.

You might want to look at LINQtoSQL, nHibernate, Subsonic, etc. to either use them or at least for ideas on how to structure a good data layer if you insist on writing your own. From personal experience I can tell you that using an existing technology is much easier than writing and maintaining your own.

Answer 3

I don't think a business object ought to know or care whether or not it's persistent. An individual persistent business object can't know when it's part of a larger unit of work; that's the service layer's responsibility. Leave the connection out of the business objects. The service layer is the right place to acquire connections, usually from a connection pool, set transaction boundaries, commit or rollback, and clean up.