ODBC parametrized query with unknown number of val

问题:

I want to select several values from database (ODBC datasource). The table is this simple:

| name | value |
+------+-------+
| abcd | 12345 |

Say I want to select values where name is name1, name2 and name3:

SELECT name, value FROM my_table WHERE name="name1" OR name="name2" OR name="name3"

Now I could generate this command:

public string MakeCommand(List<string> names) {
    string command = "SELECT name, value FROM my_table WHERE ";
    bool first = true;
    foreach(string name in names) {
        if(first)
            first = false;
        else
            command+=" OR ";
        command+="name=\""+name+"\"";
    }
}

I hope it's not necessary to emphasize that this would be very bad way to access the database.

So how can I make this a parametrized ODBC command, as described here?

回答1:

Well, the simplest solution is probably to concatenate a parameter to the sql for each value in your list, and then add this value as a parameter to the OdbcCommand.

using(var command = new OdbcCommand())
{
    string sql = "SELECT name, value FROM my_table WHERE name IN(";

    for(int i=0; i < names.Count; i++) {
        sql = $"{sql} @{i},";
        command.Parameters.Add($"@{i}", OdbcType.VarChar).Value = names[i];
    }

    command.CommandText = sql.TrimEnd(",") +");";
    command.Connection = con;
    // fill a data set or execute a data reader here....
}

回答2:

There is no elegant solution to this problem. You can use the IN clause but, still you need to build the parameters one by one. So instead of returning a string you can return the OdbcCommand prepared with all the parameters required by your list of names. Just need to add the connection and execute (or pass also the connection and prepare everything here)

public OdbcCommand MakeCommand(List<string> names, OdbcConnection con) {
    List<OdbcParameter> parameters = new List<OdbcParameter>();
    List<string> placeholders = new List<string>();
    foreach(string name in names) {
        OdbcParameter p = new OdbcParameter("?", OdbcType.NVarChar);
        p.Value = name;
        parameters.Add(p);
        placeholders.Add("?")
    }
    string command = "SELECT name, value FROM my_table WHERE name IN(";
    command = command + string.Join(",", placeholders.ToArray()) + ")";
    OdbcCommand cmd = new OdbcCommand();
    cmd.CommandText = command;
    cmd.Connection = con;
    cmd.Parameters.AddRange(parameters.ToArray());
    return cmd;
}

If you still have problems, then it could be something linked to the parameters DataType. NVarChar, VarChar, Text, String. Some db could react differently to this type. What database are you testing this code against?