Caching results from a MySqlDataReader

Posted on 2007-10-09
Last Modified: 2012-06-27
I am wanting to cache the results of a MySqlDataReader which is then bound to a DropDownList. I have the following code, but get the error "Invalid attempt to Read when reader is closed." when calling "ddlSuburb.DataBind()". How do I fix this problem?

        If Cache("SuburbList") Is Nothing Then
            Dim ConnectionString As String = ConfigurationManager.ConnectionStrings("CMAConnection").ConnectionString
            Dim CMAConnection As MySqlConnection = New MySqlConnection(ConnectionString)
                Dim CMACommand As MySqlCommand = New MySqlCommand()
                CMACommand.Connection = CMAConnection
                CMACommand.CommandText = "ListSuburb"
                CMACommand.CommandType = CommandType.StoredProcedure
                Dim CMAReader As MySqlDataReader = CMACommand.ExecuteReader()
                Cache("SuburbList") = CMAReader
            End Try
        End If

        Dim SuburbCache As MySqlDataReader = CType(Cache("SuburbList"), MySqlDataReader)
        ddlSuburb.DataSource = SuburbCache
Question by:DerekWatling
    LVL 26

    Accepted Solution

    never cache data reader as they hold connection .. and in your case you are closing the connection .. remember that datareader is like a forward only cursor (if you have workied in vb6) and needs the connection to the database to parse it .. cache a dataset instead ..


    Caching DataReader Objects
    Never cache DataReader objects. Because a DataReader object holds an open connection to the database, caching the object extends the lifetime of the connection, affecting other users of the database. Also, because the DataReader is a forward-only stream of data, after a client has read the information, the information cannot be accessed again. Caching it would be futile.

    Caching DataReader objects disastrously affects the scalability of your applications. You may hold connections open and eventually cache all available connections, making the database unusable until the connections are closed. Never cache DataReader objects no matter what caching technology you are using.

    Also do not cache SqlDataAdapter objects because they use DataReader objects in their underlying data access method.


    Author Comment

    How do I read the data and into an object that can be cached?

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    how to add IIS SMTP to handle application/Scanner relays into office 365.

    746 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

    14 Experts available now in Live!

    Get 1:1 Help Now