Question about memory and DB connections

[BobRoyAce]

I have a function, shown below, that returns a SqlDataReader. The
function creates a SqlConnection that it uses to create the
SqlDataReader. If I run this function over and over, does it cause a
"memory leak" since the SqlConnections aren't being disposed of? When
a generated SqlDataReader goes away, does that automatically free the
memory for the associated SqlConnection? For example, let's say I have
the following Sub:

Private Sub DoSomething()
Dim sSQL As String = "SELECT * FROM MyTable"
Dim rdr As SqlClient.SqlDataReader = GetDataReaderForSQL(sSQL)

' Do something with rdr...

End Sub

What should I do, at the end of the DoSomething Sub above, to make
sure I don't create a memory leak?

Public Function GetDataReaderForSQL(ByVal sSQL As String) As
SqlClient.SqlDataReader
Dim cnn As New
SqlClient.SqlConnection(My.Settings.GRPConnectionString.ToString)
cnn.Open()

Dim cmd As New SqlClient.SqlCommand(sSQL, cnn)
Dim rdr As SqlClient.SqlDataReader

Try
rdr =
cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
Catch ex As Exception
MessageBox.Show("GetDataReaderForSQL" & vbCrLf & vbCrLf &
ex.Message & vbCrLf & ex.StackTrace, "GRP", _
MessageBoxButtons.OK, MessageBoxIcon.Warning)

rdr = Nothing
End Try

Return rdr
End Function ' GetDataReaderForSQL

 

[Kerry Moorman]

BobRoyAce,

When you are finished with the datareader you need to close it. Since you
used the CommandBehavior.CloseConnection option, this will also close the
connection.

In your Catch block in GetDataReaderForSQL I'm not sure that setting the
reader to Nothing is useful. Instead, you might do something like this:

'If the data reader exists and is not closed, then close it
If rdr IsNot Nothing _
AndAlso rdr.IsClosed = False Then dr.Close()

'If the connection exists and is not closed, then close it
If cnn IsNot Nothing _
AndAlso cnn.State <> ConnectionState.Closed Then cn.Close()

Kerry Moorman

 

[BobRoyAce]

BobRoyAce,

When you are finished with the datareader you need to close it. Since you
used the CommandBehavior.CloseConnection option, this will also close the
connection.

But, does closing the connection actually free it from memory, or
trigger later garbage collection?

In your Catch block in GetDataReaderForSQL I'm not sure that setting the
reader to Nothing is useful. Instead, you might do something like this:

'If the data reader exists and is not closed, then close it
If rdr IsNot Nothing _
AndAlso rdr.IsClosed = False Then dr.Close()

'If the connection exists and is not closed, then close it
If cnn IsNot Nothing _
AndAlso cnn.State <> ConnectionState.Closed Then cn.Close()

I changed exception logic, replacing rdr = Nothing with...

'If the data reader exists and is not closed, then close it
If (rdr IsNot Nothing) AndAlso (rdr.IsClosed = False) Then
rdr.Close()
End If

'If the connection exists and is not closed, then close it
If (cnn IsNot Nothing) AndAlso (cnn.State <> ConnectionState.Closed)
Then
cnn.Close()
End If

 

[Kerry Moorman]

BobRoyAce,

Calling Close (or Dispose) on an open connection object closes the
connection, an unmanaged resource.

The garbage collector will free up the memory used by the connection object
when the object goes out of scope (or is set to Nothing).

However, even if the garbage collector frees up the memory used by the
connection object, the unmanaged connection will not be closed unless you
have explicitly called Close (or Dispose) on the open connection.

It is the programmer's job to call Close (or Dispose) on an open connection.
It is the garbage collector's job to manage memory.

Kerry Moorman

 

[Michel Posseth [MCP]]

BobRoyAce,

Calling Close (or Dispose) on an open connection object closes the
connection, an unmanaged resource.

The garbage collector will free up the memory used by the connection
object
when the object goes out of scope (or is set to Nothing).

However, even if the garbage collector frees up the memory used by the
connection object, the unmanaged connection will not be closed unless you
have explicitly called Close (or Dispose) on the open connection.

It is the programmer's job to call Close (or Dispose) on an open
connection.
It is the garbage collector's job to manage memory.

Kerry Moorman

I would recomend when possible to use Using statements for anny object wich
implements Idisposable ( possible since VS 2005 ) this will take care of
good cleanup and another benefit is that it gives you a Scope setting within
a method .

If above is not possible always use Close and Dispose .

The garbage collector will free up the memory used by the connection
object
when the object goes out of scope (or is set to Nothing).

Well the GC is a lazy garbage man , it comes when it feels it is necesary
or when it is explicitly called

Setting objects to Nothing will only upgrade the objects for pickup , but it
doesn`t necesary mean that they are actually picked up

Michel