Devart Forums

What is the best practice for using MySqlDataReader for multiple query's ?

I have a procedure that does something like below. The problem is that the MySqlDataReader gets corrupted after 5-6 iterations.

I noticed that if I do a closeConnection then openConnection, I do not get any corruption in the MySqlDataReader.

MySqlDataReader mr;

for many transaction types:
OpenDataSet(sqlStmt)

procedure OpenDataSet(string sqlStmt)
if ( mr != null && !mr.IsClosed ) mr.Close()
// if closeConnection here then all is fine.
if ( connection not open) openConnection()
mr = ExecuteReader()
case transactionType
do your thing
mr.Close()

According to the best practices for using ADO.NET, it is recommended to always close the DataReader when you are finished reading the data. If the Connection you are using is only used to return the DataReader, close it immediately after closing the DataReader. An alternative to explicitly closing the Connection is to pass CommandBehavior.CloseConnection to the ExecuteReader method to ensure that the associated connection is closed when the DataReader is closed. This is especially useful if you return a DataReader from a method and do not have control over the closing of the DataReader or associated connection.

But I cannot reproduce the problem with a corruption when connection is not closed explicitly after every usage of MySqlDataReader (only reader.Close()). If the problem persists with the latest version of dotConnect for MySQL (5.20.33 at the moment), please send to support*devart*com a small test project with the DDL and DML script. We will try to reproduce the problem.

You should close the reader after each open, this may be related to this issue:

http://www.devart.com/forums/viewtopic.php?t=15525