dotNET OleDbParameter's name obsession

Question

Since OleDbParameter thing does not use named parameters, (due to its nature) why is the .NEt OleDbParameter classes obsession with (string parametername ... All constructors require a parameter name, and I think what name to give it .. my name is ok? or my grandmothers name?

Answer 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);

Answer 2

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

1. the parameters will need to be identified in some way inside the OleDbParameter collection should you need to reference them.

2. 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.

Answer 3

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.