SQL SELECT * FROM XXX WHERE columnName in an array

Question

I am working on some SQL code.

I am familiar with the syntax

SELECT * FROM myTable WHERE myColumn in ('1','2','3'); 

Suppose I write C # code where I want to use a C # array where I used ('1','2','3') . How to do it?

Answer 1

You can build your SQL string dynamically.

If you know that the data in the array is good (not provided by the user), you can simply create a string. Joyne.

 var sql = string.Format("SELECT * FROM myTable WHERE myColumn in ({0})", string.Join(", ", myArray)); 

If you do not know that this is disinfected data, you should use the command with the parameters.

 var myArray = new string[] { "1", "2", "3" }; //var sql = string.Format("SELECT * FROM myTable WHERE myColumn in ({0})", string.Join(", ", myArray)); var cmd = new System.Data.SqlClient.SqlCommand(); var sql = new System.Text.StringBuilder(); sql.Append("SELECT * FROM myTable WHERE myColumn in ("); for (var i = 0; i < myArray.Length; i++) { cmd.Parameters.Add("@" + i, myArray[i]); if (i > 0) sql.Append(", "); sql.Append("@" + i); } sql.Append(")"); cmd.CommandText = sql.ToString(); 

Answer 2

SQL does not support using a single variable for a comma-separated list of values ​​through an IN clause, so your C # code should convert the array to this comma-separated list. This list is then combined into a query before executing the query.

Otherwise, you need to take a look at using the built-in dynamic syntax of SQL databases, but that still means that you need to force the C # array to process SQL ...

Answer 3

I would go through the for loop and format it the way you want. For example, suppose you have an array with: 6.3, abc. Use a for loop to add it to the common line, so the result is: (6,3, abc); Not hard, and then just insert this into the statement.

Answer 4

You just need to do string.Join to create an array into a string, which you can pass as a parameter to the request.

For example:

 var values = new string[] { "1", "2", "3" }; var parameterString = string.Join(",", values); var sql = "SELECT * FROM myTable WHERE myColumn in (@param)"; var cmd = new SqlCommand(sql, connectionstring); cmd.Parameters.AddWithValue("@param", parameterString); var reader = cmd.ExecuteReader();