CODEWITHSUNDEEP
c#databaseodbc
Tomáš Zato
Tomáš Zato

Reputation: 53139

ODBC parametrized query with unknown number of values?

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?

Upvotes: 0

Views: 1218

Answers (2)

Steve
Steve

Reputation: 216293

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?

Upvotes: 1

Zohar Peled
Zohar Peled

Reputation: 82474

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....
}

Upvotes: 1

Related Questions