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

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

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

"NuTcAsE" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

"NuTcAsE" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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

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/...ity/newsgroups
"Owen Mortensen" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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
>
>