ODBC parameterized query with an unknown number of values?

Question

I want to select multiple values ​​from a database ( ODBC datasource ). The table is simple:

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

Let's say I want to select the values ​​where the 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 there is no need to emphasize that this will be a very bad way to access the database.

So how can I make this the parameterized ODBC command described here ?

Answer 1

There is nothing elegant to solve this problem. You can use the IN clause, but you still need to build the parameters one by one. Therefore, instead of returning a string, you can return OdbcCommand prepared with all the parameters required by your list of names. You just need to add the connection and execute (or transfer the connection as well 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, this may be due to the DataType options. NVarChar, VarChar, Text, String. Some db may respond differently to this type. In which database are you testing this code?

Answer 2

Well, the simplest solution is probably to combine the sql parameter for each value in your list, and then add that value as a parameter in 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.... }