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 calling ExecuteNonQuery. Be careful how you use using - you don't want to dispose the MySqlCommand 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 about using 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.

Tags:

C#

Mysql