Compare items in a SQL column with array

arrays, c#, sql, sql-server

Solution

You can build an SQL query with an `IN(@PARAM)` clause, and Join them with `String.Join`, like this:

using(SqlConnection connection = new SqlConnection(
        connectionString)) {
    using(SqlCommand command = new SqlCommand()) {
            SqlDataReader reader = null;
        try {
            command.Connection = connection;
            command.CommandText = "SELECT * FROM <TABLE> WHERE <FIELD> IN (@PARAM)";
            command.CommandType = CommandType.Text;
            command.Parameters.Add(new SqlParameter(@"@PARAM",
                    String.Join(",", <array>)));
            connection.Open();
            reader = command.ExecuteReader();
            if(reader.HasRows) {
                // logic
            }
        } catch(SqlException) {
            throw;
        }
    }
}

The items must be enclosed with ' ' (single quotes).

Problem

I have a SQL server table where I want to query for items on rows which have a value equal to an item in Array. Here's my example: ``` Column1 Column2 Column3 ------- ------- ------- aaa 100 200 bbb 100 400 aaa 200 78 ccc 200 200 ``` And my array: ``` string[] arr = {"ddd", "aaa", "fff", "bbb"} ``` So, I want to return from SQL rows matching items in the array. my SQL query should return items where column1 matches an item in the array: ``` Column1 Column2 Column3 ------- ------- ------- aaa 100 200 bbb 100 400 aaa 200 78 ``` This all in C# by the way. Help will be appreciate.

Original source