Connection Management in a Smart Client app using Ent Lib 5.0

Topics: Data Access Application Block
Feb 20, 2012 at 11:10 AM

Hi,

We have a smart client 2010 application that makes calls into a Business layer and then continues to make calls into a separate Data layer.

There are approx. 160 users of the application. 

The Data layer consists of about 15 separate dll components each relating to an area of functionality in the application.

When any of these Data layer components are accessed for the first time a reference to a database instance is created i.e:

Database myDb = EnterpriseLibraryContainer.Current.GetInstance<Database>(myDbConnectionString);

The code then performs all necessary data related transactions on this instance.

My Questions are as follows:

1. Does the 'myDb' instance need to be cleared from memory when a data layer component finishes what it needs to do? If so, how is this done?

2. We are currently closing any individual connections using a combination of myConnection.Close() and myConnection.Dispose(). Is either one of these more appropriate or better for performance? All other connections are automatically closed by implementing 'using(){}'.

3. If say, five separate data layer components were called sequentially, would the same connection be re-used for the each of the five data related transactions? i.e. Do the separate data layer components share the same connection pool since they each have a separate reference to the same database instance?

4. Is it necessary or good practice to have a custom .Dispose() method on the data layer components, which could check for open connections and close them if necessary as well as close other resources local to the data layer classes?  

 

Feb 20, 2012 at 10:39 PM

There seems to be some fundamental confusion here. Let me start by stating this:

A Database object is not a database connection.

Database is actually a pretty lightweight class - pretty much all it stores state wise is your connection string. Any connections required by the methods of Database are opened, used, and closed automatically by the methods inside Database. You need to worry any connections you've explicitly opened, but the DAAB handles the ones it uses internally correctly.

As far as your questions:

1) 'myDB' doesn't need to be cleared from memory. It also doesn't need to be cached or shared - it's equally as cheap to just create one when you need it. Lazy construction is way overkill. You'll note that the Database class doesn't implement IDisposable - that's a big clue right there that no cleanup is required.

2) In general, across the framework if you've got close() or dispose() there's no difference between the two. Use whichever you want (I prefer using blocks myself). WCF famously screwed this up, but everyone else got it right.

3) Connection pooling is determined based on the connection string, not on which Database instance you're using. If you're using the same connection string, you're using the same pool.

4) That depends on how you've implemented your data layers. Do you have stuff that needs cleaning up afterwards? Then add a dispose method.