Using the "IN" statement with SQL Command Object and C # 2.0

Question

I would call the sql statement, for example:

Select * From Table Where Column in ('value1', 'value2', 'value3') 

It's as simple as setting the value of the command parameter to " ('value1', 'value2', 'value3') "?

Answer 1

@Charles: you are going in the right direction, but we use parameterized queries mainly to prevent SQL injection. Entering "external" values ​​( params string[] args ) hardcoded in the queries causes problems. You can repeat the arguments, but you still have to use the following parameters:

  string[] values = new [] {"value1", "value2", "value3", "value4"}; StringBuilder query = new StringBuilder("Select * From Table Where Column in ("); SqlCommand cmd = new SqlCommand(); cmd.Connection = new SqlConnection("Your connection string"); for(int i = 0; i < columns.Length; i++) { string arg = string.Format("@arg{0}", i); cmd.Parameters.AddwithValue(arg, SanatizeSqlString(columns[i])); sb.AppendFormat("{0}, ", arg); } sb = sb.Remove(sb.Length -2, 2); sb.Append(")"); cmd.CommandText = sb.ToString(); 

Thus, you will receive a request such as:

 select * from table where column in (@arg0, @arg1, @arg2, @arg3) 

Answer 2

if you have only three parameters for the in clause, then yes, you can use the parameters. Otherwise, you can build dynamic SQL (be careful with SQL injection attacks).

Another approach is to create a UDF that takes a delimited string and returns a table. then you can change your request:

 select * from table inner join dbo.fn_stringToTable(@params) 

Answer 3

Another option is to set the SqlCommand text type to “text” and build the entire Sql string in the code ... Assuming the column is varchar, and you have values ​​in a string expression named "paramValues"

  StringBuilder sbSql = new StringBuilder ("Select * From Table Where Column in ("); string[] paramValues = new string[] {"value1", "value2", "value3"}; foreach (string val in paramValues) sbSql.Append("'" + val + "', "); sbSql = sbSql.Remove(sbSql.Length - 2, 2); sbSql.Append(")"); SqlCommand cmd = new SqlCommand(sbSql.ToString()); cmd.CommandType = CommandType.Text;