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?

Answer 1

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());
    }
}