CODEWITHSUNDEEP
c#oledb
ainwood
ainwood

Reputation: 1048

Syntax for adding parameters to a query

I was reminding myself of the syntax for parameterized queries (C# OleDB library), and the first couple of examples I stumbled across were straight-forward, but I noticed something about the syntax:

sqlCommand.CommandText = "SELECT * FROM Customer WHERE Name LIKE @Name;";
sqlCommand.Parameters.AddWithValue("@Name", "%" + searchString + "%");

What I noticed was the SQL format for identifying what will be replaced with the parameters was different from what I have been typically using. I typically use a colon and square brackets:

sqlCommand.CommandText = "SELECT * FROM Customer WHERE [Name] LIKE :Name;";
sqlCommand.Parameters.AddWithValue(":Name", "%" + searchString + "%");

Now, the two examples I was looking at with the '@' were from sql-server examples, where I normally only work with Access and Oracle. But I saw other access examples using the '@' as well.

Does it really make any difference, or is it just a style thing? I recall that (using access at least) the engine ignores the parameter names and just inserts the parameters in the order that they are listed.

I note from the MSDN that:

The OLE DB.NET Framework Data Provider uses positional parameters that are marked with a question mark (?) instead of named parameters.

This reinforces my view that the parameter name / syntax doesn't matter and is ignored. However, even if this is correct, is there a good habit to get into that provides more robustness for other frameworks etc?

Upvotes: 2

Views: 1514

Answers (2)

DRapp
DRapp

Reputation: 48139

Obviously parameterizing queries is important to help prevent you from sql-injection. Different databases use different constructs to use parameters. For example SQL-Server uses the "@" as a place-holder for a parameter but DOES allow for parameters to be named... likewise, others may use ":" also as a named-place-holder. However, with OleDB, it uses a single "?" as the place-holder and is NOT named. Therefore, you must add your parameters in exact order as they represent in your query command.

Also, I have found instances when using NAMED parameters, that if you have a column and parameter by the same, it might not work as intended as the database might resolve itself by the column -- which is not the intended purpose. Rename the parameter with some prefix (or suffix) to help clarify. Such example might be..

sqlCommand.CommandText = "update MyCustomerTable set email = @email where customerID = @customerID";

sqlCommand.Parameters.AddWithValue("email", "[email protected]");
sqlCommand.Parameters.AddWithValue("customerID", someIDValue );

Also note.. you don't actually include the "@" or ":" for named parameters. The engines will know how to handle them.

all looks good, but to prevent ambiguity if the "parameter" is not found and it falls back on the column name, try..

sqlCommand.CommandText = "update MyCustomerTable set email = @parmEmail where customerID = @parmCustomerID";

sqlCommand.Parameters.AddWithValue("parmEmail", "[email protected]");
sqlCommand.Parameters.AddWithValue("parmCustomerID", someIDValue );

This way there is no confusion.

Now, back to the "?" place-holder. If you have a single parameter value that is applied multiple times, you need to add the parameter for each instance. If named parameters are allowed, you might get away with..

    sqlCommand.CommandText = 
@"update SomeTable
   set Rate1 = Rate1 * @newRateFactor,
       Rate2 = Rate2 * @newRateFactor";

    sqlCommand.Parameters.AddWithValue("newRateFactor", 1.15);

Notice only a single named-parameter is required... but with the "?", you have to add it EACH TIME

    sqlCommand.CommandText = 
@"update SomeTable
   set Rate1 = Rate1 * ?,
       Rate2 = Rate2 * ?";

    sqlCommand.Parameters.AddWithValue("ratePlaceHolder1", 1.15);
    sqlCommand.Parameters.AddWithValue("ratePlaceHolder2", 1.15);

Doing things like sql inserts and updates can also get tricky when you have a bunch of parameters for all column names. You can still give a parameter NAME value, but it must be in the same ordinal position within the query for execution.

Upvotes: 3

Patrick Hofman
Patrick Hofman

Reputation: 156978

This reinforces my view that the parameter name / syntax doesn't matter and is ignored.

That is certainly not true. The parameter prefix you are using does matter, but ODBC is a little more relax on that since it has to support a lot of platforms. Never ever assume though you can just throw in any garbage, because it will bite you.

You indeed have to use the ? for parameters in OLE DB. The names you give when supplying the parameters are ignored, the order is all that matters there.

Upvotes: 0

Related Questions