Caching results from a MySqlDataReader

Posted on 2007-10-09
Medium Priority
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

Rejojohny earned 750 total points
ID: 20041519
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 ..

from http://msdn2.microsoft.com/en-us/library/ms978502.aspx

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

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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

609 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