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?
-
3Are you using LINQ to SQL? If not, what are you using for data access?Mehrdad Afshari– Mehrdad Afshari2010-12-09 00:56:44 +00:00Commented Dec 9, 2010 at 0:56
-
Is myColumn string or integer data? If integer, you should use tsql : myColumn in (1,2,3)Lill Lansey– Lill Lansey2010-12-09 01:01:30 +00:00Commented Dec 9, 2010 at 1:01
Add a comment
|
4 Answers
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();
1 Comment
Brian
Check the following answer for an extension method that is a bit nicer to use than this answer. stackoverflow.com/questions/2377506/…
SQL doesn't support using a single variable for a comma separated list of values via the IN clause, so that means your C# code has to convert the array into that comma separated list. That list is then concatenated into the query before the query is executed.
Otherwise, you need to look at using the databases' native dynamic SQL syntax - but that still means you have to get the C# array into SQL to be manipulated...
Comments
I would go through a 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 a general string so the outcome is: (6,3,abc); Not to hard, and then just insert that into the statement.
Comments
You simply need to do a string.Join to create the array into a string which you can pass as a parameter to the query.
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();
1 Comment
devrooms
Yes Donnie, you are correct with the mainstream sql engines - I am used to querying an Infomix SE database over an ODBC connection, of which this syntax is accepted.
