vb.net proper way to use Using statement with data readers

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
rutledgjAsked:
Who is Participating?
 
sindhuxyzCommented:
You have used correct way; and YES dispose method will be called even if you have returned from Using statement

using statement is the same as:
try{
// do something with the connection
// maybe return
}finally{
mySQLconn.Dispose();
}
0
 
IronhoofsCommented:
Use Try..Catch.. Finally:


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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.