9/22/03
This is actually going to be pretty short, and the main reason I'm
writing it is that it was something that I came across and as far as
I can tell, it's undocumented. It involves calling the Prepare()
method on a SqlCommand object or an OleDbCommand object.
The purpose of the Prepare() method call is to generate a compiled
version of your SQL code so that if you're reusing the same SQL statement,
or a statement with parameters and simply changing the parameters, the SQL
statement doesn't need to be recompiled for each call.
What may not be intuitive (or maybe it is and I'm just the idiot who
couldn't intuit it) is that the prepared SQL commands are tied to the
connections with which they're prepared. Let me explain. When you create a
SqlCommand or OleDbCommand object, it doesn't necessarily have to be attached
to a connection object. When you call Prepare() however, the command
object must be associated with a connection. This makes sense, since it's
the underlying SQL engine that compiles the command.
The problem is that, with SqlCommand, if you want to sever the connection but
maintain the command for later use, your Prepare() call is lost. This
does not appear to be the case with OleDbCommand, which doesn't appear to
unprepare commands on closing.
When the SqlCommand.Prepare() call has prepared the command, it adds
the command to a list in the associated SqlConnection of prepared commands. When
the connection is closed, the connection goes through each command in that list
and explicitly unprepares them.
This does not appear to be the case with the implementation of OleDbCommand.
But on the other hand, I have to admit some confusion in how the preparation
process actually works under OleDbCommand. But from what I can tell, the
preparation is entirely unaffected by the closing of the associated connection.
I have yet to run any benchmarks to verify this, but plan to soon and will
post the results as time permits.
I post this not as a definitive explanation, but more of a warning. The
unpreparing of the SqlCommands required re-architecting a DAL component that I
built and I hope this information can save others some time.
