Why do I need OleDbCommand.Prepare()?

Question

I'm working with a datagrid and adapter that correspond with an MSAccess table through a stored query (named "UpdatePaid", 3 paramaters as shown below) like so:

    OleDbCommand odc = new OleDbCommand("UpdatePaid", connection);

    OleDbParameter param;

    odc.CommandType = CommandType.StoredProcedure;

    param = odc.Parameters.Add("v_iid", OleDbType.Double);
    param.SourceColumn = "I";
    param.SourceVersion = DataRowVersion.Original;

    param = odc.Parameters.Add("v_pd", OleDbType.Boolean);
    param.SourceColumn = "Paid";
    param.SourceVersion = DataRowVersion.Current;

    param = odc.Parameters.Add("v_Projected", OleDbType.Currency);
    param.SourceColumn = "ProjectedCost";
    param.SourceVersion = DataRowVersion.Current;

    odc.Prepare();

    myAdapter.UpdateCommand = odc;

    ...

    myAdapter.Update();

It works fine...but the really weird thing is that it didn't until I put in the odc.Prepare() call.

My question is thus: Do I need to do that all the time when working with OleDb stored procs/queries? Why? I also have another project coming up where I'll have to do the same thing with a SqlDbCommand... do I have to do it with those, too?

Answer 1

I don't use it with SqlDbCommand. It seems as a bug to me that it's required. It should only be nice to have if you're going to call a procedure multiple times in a row. Maybe I'm wrong and there's a note in documentation about providers that love this call too much.

Answer 2

Are you using the JET OLEDB Provider? or MSDASQL + JET ODBC?

You should not need to call Prepare(), but I believe that's driver/provider dependent.

You definitely don't need to use Prepare() for System.Data.SqlClient.

Answer 3

This is called, oddly enough, a prepared statement, and they're actually really nice. Basically what happens is you either create or get a sql statement (insert, delete, update) and instead of passing actual values, you pass "?" as a place holder. This is all well and good, except what we want is our values to get passed in instead of the "?".

So we prepare the statement so instead of "?", we pass in parameters as you have above that are going to be the values that go in in place of the place holders.

Preparing parses the string to find where parameters can replace the question marks so all you have to do is enter the parameter data and execute the command.

Within oleDB, stored queries are prepared statements, so a prepare is required. I've not used stored queries with SqlDB, so I'd have to defer to the 2 answers previous.