SQL SELECT * FROM XXX WHERE columnName in Array

Question

I'm working on some SQL code.

I'm familiar with the syntax

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

Suppose I'm writing some C# code where I want to use a C# array where I used ('1','2','3'). How do I do that?

Answer 1

You can build your SQL string dynamically.

If you know that the data in the array is good (not supplied by the user), you can just do a string.Join.

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

If you don't know that it is sanitized data, then you should use a Command with 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();