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.
Advertisement
Answer
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).