I've been trying to convert a database class to using the Using statement to help eliminate possible memory leaks. I'm a bit unsure about how to do this with datareaders since you can't close the connection and still read the reader. Can someone tell me if the following is correct? Since the return is within the using blocks, what happens to the connections and objects? Is there a better way?
Friend Function ExecuteDataReader(ByVal ConnString As String, ByVal SqlQuery As String, ByVal CmdType As System.Data.CommandType) As MySqlDataReader
Try
Using SqlConn As New MySqlConnection(ConnString)
Using SqlCmd As New MySqlCommand(SqlQuery, SqlConn)
SqlCmd.CommandType = CmdType
SqlConn.Open()
Using drReader As MySqlDataReader = SqlCmd.ExecuteReader()
Return (drReader)
End Using
End Using
End Using
Catch ex As Exception
Throw New Exception("Error in MySqlDataBaseOperations.ExecuteDataReader with error: " & ex.ToString)
End Try
End Function
The code in the finally block gets run after try / catch. Declare all objects beforehand without "Using" and clean up all objects after your code finished.