C#, SQL, and OR

Question

I'm building some SQL criteria that will often have multiple possible criteria values. Typically, one would use OR in SQL to achieve this - like so:

SELECT * FROM table WHERE criteria = 'This' OR criteria = 'That'

Rather than just building a string in C# by iterating through a list, is there a preferred way to add this functionality to SqlCommand? For example, like the following pseudo-example:

foreach (int i in ListOfCriteriaValues)
{
    SqlCommand.BulkParameters.Add("@placeholder", SqlDbType.Int).Value = i;
}

Output:

//ListOfCriteriaValues has four members (1, 2, 3, 4)    

"SELECT * FROM table WHERE criteria = 1 OR criteria = 2 OR criteria = 3 OR criteria = 4"

Sorta like https://stackoverflow.com/questions/5595353/how-to-pass-table-value-parameters-to-stored-procedure-from-net-code? — itsme86, Jan 18 '18 at 17:55
I should also add that you probably mean "WHERE criteria in (1,2,3,4)". Otherwise, your SQL syntax, as is, is incorrect. — itsme86, Jan 18 '18 at 17:57
I've never seen that syntax before, though maybe you are using some RDBMS product I'm not familiar with that supports it. Isn't this `where criteria in (1, 2, 3, 4)`? — William Robertson, Jan 18 '18 at 17:58
Possible duplicate of [How to pass table value parameters to stored procedure from .net code](https://stackoverflow.com/questions/5595353/how-to-pass-table-value-parameters-to-stored-procedure-from-net-code) — Taylor Buchanan, Jan 18 '18 at 18:00
According to MS docs, the appropriate syntax for SQL is thus: `(Shift = 'Evening' OR Shift = 'Night')`. Edited appropriately. — , Jan 18 '18 at 18:03

lucky · Answer 1 · 2018-01-18T18:13:58.093

1

No, you should set the parameters one by one or you can create your sql command with string builder things but there is no support to add parameters as a collection like BulkParameters.

Also, you can use simply IN rather than sequential OR clauses

using (SqlCommand command = new SqlCommand("SELECT * FROM table WHERE criteria IN (@placeholder,@placeholder2)", connection))
{
    command.Parameters.AddWithValue("@placeholder", 1);
    command.Parameters.AddWithValue("@placeholder2", 2);
    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {

        }
    }
}

edited Jan 18 '18 at 18:13

answered Jan 18 '18 at 18:06

lucky

12,734
4
24
46

Why `IN` as opposed to `OR`? Something to do with sequential evaluation, or am I missing the point? – Jan 18 '18 at 18:07
Additionally, what happens if I have a variable number of possible criteria? – Jan 18 '18 at 18:08
Is there any difference between "criteria = 1 OR criteria = 2 OR criteria = 3 OR criteria = 4" and "criteria IN (1,2,3,4)" ? Why would you use multiple ORs ? – lucky Jan 18 '18 at 18:09
I had to look up the difference. The possibility remains of having separate criteria types, otherwise `IN` is appropriate. – Jan 18 '18 at 18:10
1

@Stormcloak when the `IN` operator is used with scalar values then it is semantically identical to `OR` - indeed SQL Server will actually rewrite `IN` to `OR` when it compiles `CHECK CONSTRAINT` rules, for example. – Dai Jan 18 '18 at 18:12

Dai · Accepted Answer · 2018-01-18T18:43:09.427

T-SQL does not support variadic parameters, and the IN operator is not dynamically variadic, so if you do have this:

SELECT * FROM foo WHERE x IN ( @a, @b, @c, @d )

...and you have more than 4 possible values you'll need to manually add them to your SQL string, like so:

Object[] values = ...

SqlCommand cmd = connection.CreateCommand();
StringBuilder sb = new StringBuilder(" SELECT * FROM foo WHERE x IN (");
for( Int32 i = 0; i < values.Length; i++ ) {
    if( i > 0 ) sb.Append(", ");
    String name = " @param" + i.ToString( CultureInfo.InvariantCulture );
    sb.Append( name );
    cmd.Parameters.Add( name ).Value = values[i];
}
sb.Append( ")" );
cmd.CommandText = sb.ToString();

Obviously this doesn't work for complex queries.

Note that this approach is safe from SQL injection because we never put any values into the SQL string itself.

Another approach is a Table-Valued Parameter, which is much faster and scales to hundreds of thousands of values. SQL Server and SqlClient supports it, but other database systems and client libraries don't.

Define a table TYPE in your database for the values - even if they're an array of scalar values you still need to define the type.
See instructions here on how to use Table-Value Parameters with SqlCommand: How to pass table value parameters to stored procedure from .net code
Perform a JOIN on the table-values:
```
SELECT * FROM foo INNER JOIN @tableParam AS bar ON foo.x = bar.x
```
You can also use the IN operator with a subquery (that uses the table-parameter), but performance is no better, or worse, compared to a JOIN:
```
SELECT * FROM foo WHERE x IN ( SELECT x FROM @tableParam )
```

C#, SQL, and OR

2 Answers2