OleDbParameters and Parameter Names

Question

I have an SQL statement that I'm executing through OleDb, the statement is something like this:

INSERT INTO mytable (name, dept) VALUES (@name, @dept);

I'm adding parameters to the OleDbCommand like this:

OleDbCommand Command = new OleDbCommand();
Command.Connection = Connection;

OleDbParameter Parameter1 = new OleDbParameter();
Parameter1.OleDbType = OleDbType.VarChar;
Parameter1.ParamterName = "@name";
Parameter1.Value = "Bob";

OleDbParameter Parameter2 = new OleDbParameter();
Parameter2.OleDbType = OleDbType.VarChar;
Parameter2.ParamterName = "@dept";
Parameter2.Value = "ADept";

Command.Parameters.Add(Parameter1);
Command.Parameters.Add(Parameter2);

The problem I've got is, if I add the parameters to command the other way round, then the columns are populated with the wrong values (i.e. name is in the dept column and vice versa)

Command.Parameters.Add(Parameter2);
Command.Parameters.Add(Parameter1);

My question is, what is the point of the parameter names if parameters values are just inserted into the table in the order they are added command? The parameter names seems redundant?

Garlicky

Answer 1

Parameter NAMES are generic in the SQL support system (i.e. not OleDb specific). Pretty much ONLY OleDb / Odbc do NOT use them. They are there because OleDb is a specific implementation of the generic base classes.

Citrange

That's slightly annoying, oh well, will have to modify the code to add the values in the correct order, Thanks – Garlicky

it is freaking hugely annoying. Seriously. And if you ever try out ODBC I think, you are in for "?" as param name or something like that (long time ago) ;) – Citrange

Strange, because DbParameter does NOT have a ParameterName field - you would think that if OleDbParameter does not USE the ParameterName, they would not ADD it to the class.. – Shonna

Answer 2

The Problem is that OleDb (and Odbc too) does not support named parameters.
It only supports what's called positional parameters.

In other words: The name you give a parameter when adding it to the commands parameters list does not matter. It's only used internally by the OleDbCommand class so it can distinguish and reference the parameters.

What matters is the order in which you add the parameters to the list. It must be the same order as the parameters are referenced in the SQL statement via the question mark character (?).

But here is a solution that allows you to use named parameters in the SQL statement. It basically replaces all parameter references in the SQL statement with question marks and reorders the parameters list accordingly. It works the same way for the OdbcCommand class, you just need to replace "OleDb" with "Odbc" in the code.

Use the code like this:

command.CommandText = "SELECT * FROM Contact WHERE FirstName = @FirstName";
command.Parameters.AddWithValue("@FirstName", "Mike");
command.ConvertNamedParametersToPositionalParameters();

And here is the code

public static class OleDbCommandExtensions
{
    public static void ConvertNamedParametersToPositionalParameters(this OleDbCommand command)
    {
        //1. Find all occurrences of parameter references in the SQL statement (such as @MyParameter).
        //2. Find the corresponding parameter in the commands parameters list.
        //3. Add the found parameter to the newParameters list and replace the parameter reference in the SQL with a question mark (?).
        //4. Replace the commands parameters list with the newParameters list.

        var newParameters = new List<OleDbParameter>();

        command.CommandText = Regex.Replace(command.CommandText, "(@\\w*)", match =>
        {
            var parameter = command.Parameters.OfType<OleDbParameter>().FirstOrDefault(a => a.ParameterName == match.Groups[1].Value);
            if (parameter != null)
            {
                var parameterIndex = newParameters.Count;

                var newParameter = command.CreateParameter();
                newParameter.OleDbType = parameter.OleDbType;
                newParameter.ParameterName = "@parameter" + parameterIndex.ToString();
                newParameter.Value = parameter.Value;

                newParameters.Add(newParameter);
            }

            return "?";
        });

        command.Parameters.Clear();
        command.Parameters.AddRange(newParameters.ToArray());
    }
}

Capricecapricious