CODEWITHSUNDEEP
c#mysql
Bernhard Hiller
Bernhard Hiller

Reputation: 2397

Without any SELECT(!): "There is already an open DataReader associated with this Connection"

I know that there are tantamount of question re "There is already an open DataReader associated with this Connection" (edited: originally wrote "Command") - but in all cases there is some SELECT command involved.

My case is different: on this machine, no SELECT commands are executed (they are on a different physical computer), only INSERT and UPDATE commands.

How can that happen? Does a command.ExecuteNonQuery() or a command.Prepare() open a DataReader nonetheless? That would be a WTF, but at least an explanation.

Upvotes: 0

Views: 118

Answers (2)

Bernhard Hiller
Bernhard Hiller

Reputation: 2397

Based on Bradley Grainger's comment (threadsafety issues of MySqlConnection), I could get rid of the problem by making sure that no queries are run in parallel.

This means that the original exception message is likely wrong (some strange paths as mentioned in Damien_The_Unbeliever's answer cannot be excluded).

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239704

Having reviewed the reference source for SqlCommand and OleDbCommand, both of those have some fast paths in their ExecuteNonQuery method implementations that will try to avoid opening a data reader but both have a slow path which will fall back to using a data reader (apparently, the data reader path has to cope with all possible options and they don't want to repeat all of that code, not unreasonably).

I would suspect that whichever option1 you're using to connect to will have a similar implementation. So, first and foremost, it doesn't have to be SELECTs causing the conflict.

The fix should be simple though and is good general advice. Don't share any database objects. Sure, have one source for your connection string but, in general, if you need a connection object, new it up there and then and use it in a using statement. Same for command objects. For readers, you won't new them up yourself but you still want the using.

If you don't reuse your database objects, you should never get this error. The only exception (in my book) is if you're using client controlled transactions (e.g. TransactionScope or similar) where you do want to share connection objects. But you still don't need to share the command objects. And if the transaction is so broad that you're losing track of all of the commands that (may have) executed on it, I'd suggest it's too big.

1Last time I looked there were a couple of competing specific implementations of the IDbConnection et al hierarchy, plus OleDbCommand and family can be used.

Upvotes: 2

Related Questions