Skip to content
Advertisement

Compare items in a SQL column with array

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

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement