MySql statement prepare "not sticking"
Try running the prepare before adding your parameters.
Preparing an SQL statement that is going to be executed only once would not bring any performance benefit, so I'll assume you are executing it multiple times:
- Ensure you are reusing the same
MySqlCommand
object while repeatedly callingExecuteNonQuery
. Be careful how you useusing
- you don't want to dispose theMySqlCommand
object too early. - Only assign new parameter values before each new execution - don't change the statement text or add/remove parameters.
- You'll also possibly need to keep the
MySqlConnection
alive during all that time. Be careful aboutusing
here as well.
BTW, some ADO.NET providers ignore Prepare
method altogether and "prepare" the statement only on the first execution (ODP.NET does that, not sure about MySQL). If you did everything correctly, this should have no impact on performance whatsoever...
It looks like you're doing a using on the MySqlCommand object, at least in your second example. To get any benefit of prepared statements, you would need to not dispose of the connection and command objects. You also will want to call prepare after setting CommandText and before setting any parameters.
https://dev.mysql.com/doc/connector-net/en/connector-net-programming-prepared-preparing.html
Also prepared statement parameters in MySQL aren't named, they're specified based on order. The CommandText should just contain question marks where the parameters are, and the parameter objects need to be added in exactly that order.
I came here from this:
MySqlCommand Prepare() never sets IsPrepared to true
This might be your problem -- your connection string for .net needs to have the "Ignore Prepare" option set to false.
http://dev.mysql.com/doc/connector-net/en/connector-net-connection-options.html
Sigh. That poster read the source code. It seems that many others do not have this issue (perhaps they already know about the connection string option?). I cannot explain why it isn't in the documentation for 'prepare', or why it is turned off by default.. A link to a discussion:
http://grokbase.com/p/mysql/dotnet/0721kvem8t/prepared-statements
Too bad this didn't fix my problem -- just thought you'd like to know. It did end up allowing 'prepare' to execute and turn on the IsPrepared flag.