SqlCeCommand.Parameters, More Efficient?

[jonfroehlich]

Hi all,

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?"

Thanks,

j

[Ilya Tumanov [MS]]

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.

--
Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.

*** Want to find answers instantly? Here's how... ***

1. Go to
http://groups-beta.google.com/group/microsoft.public.dotnet.framework.compactframework?hl=en
2. Type your question in the text box near "Search this group" button.
3. Hit "Search this group" button.
4. Read answer(s).

"jonfroehlich" <jonfro...@gmail.com> wrote in message
news:1155602313.0...@h48g2000cwc.googlegroups.com...

[jonfroehlich]

Ilya,

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.

Thanks again,

j

[Ilya Tumanov [MS]]

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

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?

--
Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.

*** Want to find answers instantly? Here's how... ***

1. Go to
http://groups-beta.google.com/group/microsoft.public.dotnet.framework.compactframework?hl=en
2. Type your question in the text box near "Search this group" button.
3. Hit "Search this group" button.
4. Read answer(s).

"jonfroehlich" <jonfro...@gmail.com> wrote in message

news:1155622677.3...@75g2000cwc.googlegroups.com...