Usage of SqlCeCommand.Parameters

I posted the following to microsoft.public.sqlserver.ce:

I'm curious about the Parameters property in SqlCeCommand. Is this the preferred/best usage of the SqlCeCommand object? I suppose one benefit of using Parameters is the ability to set binary/image data (as those are byte arrays) for insertions/updates. Are there other benefits? I tend to format all of my command statements like the following:

SqlCeCommand cmd = sqlConn.CreateCommand();
cmd.CommandText = String.Format("INSERT INTO {0} (ActionName,
ActionType) VALUES ('{1}', '{2}')", "Actions", actionName, actionType);
cmd.Transaction = sqlTransact;
cmd.ExecuteNonQuery();

instead of

SqlCeCommand cmd = sqlConn.CreateCommand();
cmd.CommandText = "INSERT INTO @tablename (ActionName, ActionType)
VALUES (@actionname, @actiontype)";
cmd.Parameters["@tablename"].Value = "Actions";
cmd.Parameters["@actionname"].Value = actionName;
cmd.Parameters["@actiontype"].Value = actionType;
cmd.Transaction = sqlTransact;
cmd.ExecuteNonQuery();

What's the "best practice?"

Ilya Tumanov, a member of the .NET Compact Framework Team, responded with:

Yes, parameterized query is a preferred way. Benefits are:

• No need to create bunch of string objects all the time (in your code).
• No need to convert parameters from binaries to strings (in your code).
• No need to parse strings to get binaries back (in SQL CE).
• No locale/format specific issues as binary is format less and locale independent.
• No need to redo each command from scratch, prepared execution plan can be used with just different parameters.
• Works for all data types including binary/image.

Performance benefits from using parameters range from significant to really huge.

(link)

Update 08/16/2006 12:57AM: I followed up the above post with:

Thanks so much for this thorough response--it clarified much for me. One follow-up question, what do you mean by "No need to redo each command from scratch, prepared execution plan can be used with just different parameters?" This is most certainly my naivete here with respect to SQL; a short elaboration would probably suffice.

Illya once again responded:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlc...

Preparing Commands
To be able to execute a query, the database engine must first parse, compile, and optimize the SQL statement. Often, this work can be done once if the command is to be executed multiple times, potentially saving time. If clients expect to run a query more than once, it is recommended that the command be prepared once, then call Execute multiple times. This should maximize performance by avoiding query recompilation. Commands can be prepared prior to execution by calling ICommandPrepare::Prepare. This is equivalent to compiling the command.

Real life equivalent would be a move to another apartment. Would you rather move your stuff item by item or pack it and move it all at once?