OleDbParameters and Parameter Names

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