The OleDbParameter's name obsession

Question

Since the OleDbParameter does not use named parameters (due to its nature), why is it that the .NET OleDbParameter class expects a name? (string parametername ...)

All constructors require a parameter name, and I'm never sure what name to give it; my name is ok? or my grandmothers name?

Kev · Answer 1 · 2008-12-23T18:25:11.260

Although the OleDb/Odbc providers use positional parameters instead of named parameters -

the parameters will need to be identified in some way inside the OleDbParameter collection should you need to reference them.
importantly when the parameterised sql statement is constructed, a variable for each parameter is declared and assigned it's respective value. and then used in the sql statement that is executed.

Take for example:

string sql = "SELECT * FROM MyTable WHERE Field = ?";
OleDbCommand cmd = new OleDbCommmand(sql, sqlConnection);
cmd.Parameters.Add("@FieldValue", OleDbType.Int32, 42);
OleDbDataReader dr = cmd.ExecuteReader();

The SQL executed would be something like (or close to I think):

DECLARE @FieldValue INT;
SET @FieldValue = 42;
SELECT * FROM MyTable WHERE Field = @FieldValue

You would be advised to use a parameter name that best matches the name of the column being operated on.

Ricardo Villamil · Answer 2 · 2008-12-23T18:22:20.983

1

Just like everything else in programming, name it something meaningful to your context! (name, orderid, city, etc).

You use the names to access the parameters collection by name while in your c# code:

OleDbCommand command = new OleDbCommand();
...//Add your parameters with names, then reference them later if you need to:
command.Parameters["name"].Value = "your name or your grandmothers name here";

This is also useful when you use OUT parameters to extract the value after you execute your statement:

OleDbParameter outParam = new OleDbParameter();
outParam.Direction = ParameterDirection.Output;
outParam.DbType = DbType.Date;
outParam.ParameterName = "outParam";
command.Parameters.Add(outParam);
command.ExecuteNonQuery();
DateTime outParam = Convert.ToDateTime(command.Parameters["outParam"].Value);

edited Dec 23 '08 at 18:22

answered Dec 23 '08 at 18:15

Ricardo Villamil

5,031
2
30
26

Note: `command.Parameters["name"].Value = "your name or your grandmothers name here";` is __not__ a valid way to _add_ a parameter, only to change the value of an _existing_ one. I initially inferred that it would add it to the collection, if not present. – Ben Mosher Jan 12 '13 at 02:24
I never said that's how you add a param, I said (in the comment right above) to add your params first, then reference them later, using the square bracket notation – Ricardo Villamil Feb 15 '13 at 21:03
1

That's reasonable; I just initially interpreted it as being associated w/ the line below, and not the '...'. Thus my warning to future readers. Not criticizing; just clarifying. – Ben Mosher Feb 16 '13 at 23:01

score 1 · Answer 3 · answered Dec 23 '08 at 18:49

There are interfaces and factories to provide a degree of independence of the underlying provider for data access - IDataParameter, DbParameter, DbProviderFactory etc.

To support this, all parameters can be named, even when the name is not used by the underlying provider.

The OleDbParameter's name obsession

3 Answers3

Linked