CODEWITHSUNDEEP
c#sqlarrays
Vivian River
Vivian River

Reputation: 32390

SQL SELECT * FROM XXX WHERE columnName in Array

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?

Upvotes: 7

Views: 25115

Answers (4)

devrooms
devrooms

Reputation: 3149

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();

Upvotes: -1

Brian
Brian

Reputation: 38025

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();

Upvotes: 12

OMG Ponies
OMG Ponies

Reputation: 332581

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...

Upvotes: 2

lolraccoon
lolraccoon

Reputation: 89

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.

Upvotes: 0

Related Questions