0

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?

Tomáš Zato
  • 50,171
  • 52
  • 268
  • 778

2 Answers2

1

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?

Steve
  • 213,761
  • 22
  • 232
  • 286
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....
}
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • I tried this. There were no errors but also no results. I tried to do the same thing using `WHERE IN ()` with actual names. It worked correctly. But the parametric substitution does not work. – Tomáš Zato Sep 28 '17 at 15:27
  • Did you also try Steve's suggestion? – Zohar Peled Sep 28 '17 at 17:04
  • Yes, same problem. I have no idea what the issue could be. I checked the ODBC logs and I don't see a problem. – Tomáš Zato Sep 28 '17 at 17:04
  • It's a long shot, but try to use multiple `or` conditions instead of `in`. I don't think it's going to change anything but It's an easy change to make so it's worth a try. – Zohar Peled Sep 28 '17 at 17:06