MySqlDataReader multiple use - best practice

MySqlDataReader multiple use - best practice

Postby ca_cruiser » Mon 13 Jul 2009 14:49

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:

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
Posts: 13
Joined: Tue 17 May 2005 12:59

Postby Shalex » Tue 14 Jul 2009 10:51

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.
Devart Team
Posts: 7539
Joined: Thu 14 Aug 2008 12:44

Re: MySqlDataReader multiple use - best practice

Postby anti » Fri 07 Aug 2009 16:40

You should close the reader after each open, this may be related to this issue:
Posts: 3
Joined: Fri 07 Aug 2009 14:20

Return to dotConnect for MySQL