Connection Pooling LEAK?

[Owen Mortensen]

Why is this not working (i.e., it leaks a connection EVERY TIME)?

<code>
Imports System.Configuration
Imports System.Data.SqlClient
Imports System.Web

Public Class dBUtils
Implements IDisposable

'***************************
'* PRIVATE Local variables *
'***************************
Private strConnectionString As String
Private objConn As SqlClient.SqlConnection
Private nCommandTimeout As Integer
Private objTrans As SqlTransaction

'***********************************************************************************
'* New()
Private Sub New()
'The constructor with no parameters is PRIVATE so no that the object
cannot be
'created without passing in the database identifier.
End Sub

Public Sub New(ByVal strDatabaseIdentifier As String)
strConnectionString =
ConfigurationSettings.AppSettings(strDatabaseIdentifier)
nCommandTimeout =
CInt(ConfigurationSettings.AppSettings("CommandTimeout"))
objConn = New SqlClient.SqlConnection(strConnectionString)
objConn.Open()
End Sub

'***********************************************************************************
'* GetDataReader( strSQL, booIsStoredProc )
*
Public Function GetDataReader(ByVal strSQL As String, ByVal
booIsStoredProc As Boolean) As SqlDataReader
'execute SQL string and return a dataReader object
Dim objCommand As New SqlCommand(strSQL, objConn)
Dim objDataReader As SqlDataReader

If booIsStoredProc Then
objCommand.CommandType = CommandType.StoredProcedure
Else
objCommand.CommandType = CommandType.Text
End If
objCommand.CommandTimeout = nCommandTimeout
Dim objResult As SqlDataReader = objCommand.ExecuteReader()
objCommand.Dispose()

Return objResult

End Function

'***********************************************************************************
'* BeginTrans
Public Sub BeginTrans()
objTrans = objConn.BeginTransaction()
End Sub

'***********************************************************************************
'* CommitTrans
Public Sub CommitTrans()
Try
objTrans.Commit()
Catch e As Exception
'Do nothing here
End Try
End Sub

'***********************************************************************************
'* RollbackTrans
Public Sub RollbackTrans()
Try
objTrans.Rollback()
Catch e As Exception
'do nothing here
End Try
End Sub

'***********************************************************************************
'* Dispose()
Public Sub Dispose() Implements System.IDisposable.Dispose
Dispose(True)
GC.SuppressFinalize(Me)
End Sub

Private Sub Dispose(ByVal booDisposing As Boolean)
If booDisposing Then
Try
HttpContext.Current.Response.Write("Disposing<br>")
objConn.Close()
objConn = Nothing
Catch ex As Exception
'Do nothing
End Try

End If
End Sub

'***********************************************************************************
'* Finalize()
Protected Overrides Sub Finalize()
Me.Dispose(False)
MyBase.Finalize()
End Sub
End Class

Then, the calling routine does this:
Dim objDb as dBUtils
objDb = New dBUtils("SQL_db1")
Dim objRS As SqlDataReader
objRS = objDb.GetDataReader("SELECT * FROM tbl1", false)
do while objRS.Read()
....
loop
objRS.Close()
objDb.Dispose()
</code>

Now, the problem is, EVERY SINGLE TIME this is executed, I get another
connection added to the connection pool. Eventually, I run out of pooled
connections and the open times out.

Any ideas?

TIA,
Owen

 

[NuTcAsE]

Im asuming this is an ASP.Net application. Asuming the code is executed
whenever a user clicks a button, the following steps happen:

1. A new instance of DbUtils is created
2. Execute reader is called,
3. Processing is done, and DbUtils.Dispose is called
4. In DbUtils the connection is closed and set to nothing * (Dispose is
never called on the connection)

Now if you look at the sequence above, if you have 300 ppl hitting the
same page at the same time, you may at any given point in time going to
have 300 connections open... this may not seem bad as this normally
happens in most web apps. But the Dispose function of your dbUtils
never disposes of those connections. So youve still got unmanaged
connections that hvnt been released and so a connection to the pool is
always added. This situtation will remain until the GC kicks in and
manually finalizes those connections.

In the Dispose function of the dbUtils, add objConn.Dispose also. This
should then free up the connection pool.

Hope this helps.

NuTcAsE

 

[Owen Mortensen]

Thanks. I'll do that. I did find the leak: it was outside the dbUtils
object in another part of the site. Single-step debugging really helped that
one.

Owen

 

[David Browne]

Im asuming this is an ASP.Net application. Asuming the code is executed
whenever a user clicks a button, the following steps happen:

1. A new instance of DbUtils is created
2. Execute reader is called,
3. Processing is done, and DbUtils.Dispose is called
4. In DbUtils the connection is closed and set to nothing * (Dispose is
never called on the connection)

Which is fine since SQLConnection.Close will return the connection to the
pool.

Now if you look at the sequence above, if you have 300 ppl hitting the
same page at the same time, you may at any given point in time going to
have 300 connections open... this may not seem bad as this normally
happens in most web apps.

There will be fewer than 300 because there are typically not 300 threads in
the ASP.NET worker thread pool.

But the Dispose function of your dbUtils
never disposes of those connections.
Wrong.

So youve still got unmanaged
connections that hvnt been released and so a connection to the pool is
always added. This situtation will remain until the GC kicks in and
manually finalizes those connections.

Again, Nope. SqlConnection.Close is fine. SqlConnection.Dispose would be
fine too, but there's nothing really wrong with just using .Close.

The only flaw in the OP's code is


Private Sub Dispose(ByVal booDisposing As Boolean)
If booDisposing Then
Try
HttpContext.Current.Response.Write("Disposing<br>")
objConn.Close()
objConn = Nothing
Catch ex As Exception
'Do nothing
End Try

End If
End Sub

Should be

Private Sub Dispose(ByVal booDisposing As Boolean)
If booDisposing Then
objConn.Close()
objConn = Nothing
Try
HttpContext.Current.Response.Write("Disposing<br>")
Catch ex As Exception
'Do nothing
End Try

End If
End Sub

So an exception on
HttpContext.Current.Response.Write("Disposing<br>")
won't cause a connection leak.

David




David

 

[William Ryan eMVP]

Owen: It looks like David has answered the crusx of your problem., but if
I may chime in - There are probably more elegant ways to accomplish what you
want - particularly in the context of a Try/Catch/Finally block and using
the CloseConnection enumeration for the CommandBehavior.

Also, as a matter of pragmatism - catching System.Exception and doing
nothing with it is bad++ At each point you close then trap the exception -
why not move it to a finally block - if a StackOVerflow happened - you don't
know that the code would execute. Granted this isn't going to be a very
common scenario - but it doesn't cost anything to do and serves as a much
more solid insurance policyh.

--
W.G. Ryan MVP Windows - Embedded

Have an opinion on the effectiveness of Microsoft Embedded newsgroups?
Let Microsoft know!
https://www.windowsembeddedeval.com/community/newsgroups