SELECT * FROM Customers WHERE Country = 'Germany'
SELECT * FROM Customers WHERE Country = @p
Note that the command parameter (@p) replaces a literal value that will change every time you execute the command. Command parameters have some interesting advantages over string concatenation:
- Command parameters are strongly typed;
- Command parameters make the command run faster;
- Command parameters are safe because they avoid SQL injection attacks.
The OLE DB provider requires that parameterized commands be prepared (compiled) before executing. Contrary to the managed ADO .NET provider, the OLE DB provider does check the command for errors and enumerates all the given parameters and even infers their data types (your code can then read this informtion and use it). Preparing a command with the ADO .NET provider for CF merely marks the command as requiring preparation before executing. Either way, a prepared command runs faster than an unprepared one.
Finally, with command parameters there is no way to let your user change the syntax of the underlying SQL command. If you use string concatenation to build command strings (with user input), you risk exposing your code to SQL injection attacks that may break your application code or even its security.
Since version 3.0, SQL Compact supports named parameters meaning that you can give your command parameters some meaningful names. All command parameter identifiers must be prepended by a '@' character. If you are still using SQL CE 2.0 then you are out of luck as all command parameters are identified by a single '?' character and distinguishable by their ordinal position only.
After this brief introduction, I will look at the details of implementing a SQL command parameter management code and some of the finer details of binding, such as what to do with BLOBs.