Solved  proper way to use Using statement with data readers

Posted on 2011-10-10
Last Modified: 2012-05-12
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

                    Using SqlConn As New MySqlConnection(ConnString)
                        Using SqlCmd As New MySqlCommand(SqlQuery, SqlConn)
                            SqlCmd.CommandType = CmdType
                            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
Question by:rutledgj
    LVL 5

    Accepted Solution

    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:
    // do something with the connection
    // maybe return
    LVL 7

    Expert Comment

    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.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
    If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now