locked
enterprise library filling my connection pool RRS feed

  • Question

  • User-979037941 posted

    Dear devs,

    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")
    Try
    Dim reader As DbDataReader = DirectCast(db.ExecuteReader(cmd), RefCountingDataReader).InnerReader
    Dim Result As New List(Of ClientType)
    While reader.Read()
    Result.Add(EntityHelper.GetEntity(Of ClientType, Integer)(reader))
    End While
    reader.Close()
    Return Result
    Catch ex As Exception
    Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, Policies.RepositoryLayer)
    If rethrow Then Throw
    Return Nothing
    Finally
    
    End Try
    End Function
    

    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.

    thanks

    Wednesday, May 9, 2012 7:42 AM

Answers

  • User465171450 posted

    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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 16, 2012 10:06 AM

All replies

  • User465171450 posted

    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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 16, 2012 10:06 AM
  • User-979037941 posted

    Thanks mark, i will take your notes into consideration

    Tuesday, May 22, 2012 5:52 AM