Last post May 22, 2012 05:52 AM by jaffar
May 09, 2012 07:42 AM|jaffar|LINK
im facing a problem with the enterprise library data access, the below is a sample code:
Public Function SelectAll(Optional ByRef criteria As CriteriaBase = Nothing) As List(Of ClientType) Implements IClientTypeRepository.SelectAll
Dim db As Database = RepositoryDatabases.GetDatabaseConnection
Dim cmd As DbCommand = db.GetStoredProcCommand("dbo.MSP_SelectSP")
Dim reader As DbDataReader = DirectCast(db.ExecuteReader(cmd), RefCountingDataReader).InnerReader
Dim Result As New List(Of ClientType)
Result.Add(EntityHelper.GetEntity(Of ClientType, Integer)(reader))
Catch ex As Exception
Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, Policies.RepositoryLayer)
If rethrow Then Throw
the problem is on each request from the page i see a new connection is established. icheck connections using "netstat" on the DB server command window.
my question is: does enterprise connection need to be closed by the programmer? or the library handls the opening, closing, and Disposing of the connections by it self?
because after a while of using the website, i got error "max pool connection reached" for a single user.
May 16, 2012 10:06 AM|markfitzme|LINK
You should always explicitely close your connections whether in EL or not. Best is to also create the data reader as a null outside the try loop, that way if the try block fails, you can then ensure it gets closed and destroyed in the catch block or. I forget
if VB has a finally block, but that is an even better place to call it unless you pass the datareader as a return value in which case ensure it is closed in the calling function.
Also, call the dispose methode on the commands, connection, and readers as well. This helps ensure they release any managed resources. These are all essentially good practices to do for database connectivity regarless if they are EL or not. Keep in mind,
you want a new connection each time you are making a call, or block of calls. You want to open the connection right before you need it, and close it as soon as you can. Connections are the most valuable resource you have so you want to use them for only as
long as necessary.
May 22, 2012 05:52 AM|jaffar|LINK
Thanks mark, i will take your notes into consideration