Don't require parameter name to include prefix

[natemcmaster]

SqlClient allows adding a ParameterName without the prefix, such as @, but Sqlite requires the prefix be included.

Example: this works in SqlClient, but with Sqlite the parameter is not bound to @BronieName

    var conn = new SqlConnection("Data Source=(localdb)\\MSSQLLocalDB;");
    conn.Open();
    var command = conn.CreateCommand();
    command.CommandText = "SELECT * from Bronies.dbo.Pegasus WHERE Name = @BronieName";
    var parameter = command.CreateParameter();
    parameter.ParameterName="BronieName"; /// <------ this is not required to be @BronieName by SqlClient, but is in Sqlite -->
    parameter.Value="Rainbow Dash 0";
    command.Parameters.Add(parameter);
    command.ExecuteReader()

[bricelam]

Decided not to do this. We don't know if the user specified their query using @Parameter, :Parameter, or $Parameter. It doesn't seem right to just assume they used @.

[sebastienros]

So in the case of Dapper, they do clean the input and output parameters by removing the prefix before sending it to the driver.

It would solve some scenarios if you decided to use @ to be auto-injected. At least it would not break it more that if it's completely missing.

[bricelam]

Reopening, you have a point 😄

[natemcmaster]

Should we only auto-prepend '@'? Sqlite can also use these prefixes: (see the docs on param binding)

• ?NNN
• :VVV
• $VVV

[bricelam]

No ordinal parameters. Our choices are :, @, or $. I think $ is probably idiomatic in SQLite while @ is idiomatic in ADO.NET.

[sebastienros]

Most documented sql queries in ADO.NET use '@', if one should be added I think this should be it.

[natemcmaster]

We decided not to this originally because this introduces a new ambiguity. Consider the following:

                var command = connection.CreateCommand();
                command.CommandText = "SELECT $type, @type, :type";
                command.Parameters.AddWithValue("type", 1);

Which parameter is bound?

If we only prepend @, then what should happen here?

                var command = connection.CreateCommand();
                command.CommandText = "SELECT $type";
                command.Parameters.AddWithValue("type", 1);

We can add a heuristic that tries first to append @ followed by others, but this heuristic seems more obscure requiring parameter names exactly match the SQL.

[sebastienros]

Right, the first case doesn't work with or without the suggested trick. But in some cases the trick will help. It's not a solution which will solve all cases, but most common ones.

[natemcmaster]

Here is what I propose (not the actual implementation, but you get the idea):

if(!Bind(paramName, value))
{
    if(!Bind("@" + paramName, value))
    {
        if(!Bind("$" + paramName, value))
        {
            if(!Bind(":" + paramName, value))
            {
                Fail();
            }
        }
    }
}

[bricelam]

We have two options:

1. Probe the specified parameters for the correct prefix (if this is even possible) and throw when it's ambiguous.
2. Just prepend @

I'll investigate 1 first, but if it's not possible, we'll just do 2.