An issue of SqlCommand with parameters for IN [duplicate]

Question

Apparently, the following code does not print anything out as it is expected.. I am sure it is to do with the fact that I tried to put a list of items in to @namelist. Clearly, it is not just a text replacement.

How can I solve this problem? Thanks

using (var connection = new SqlConnection(_connectionString))
{
    connection.Open();

    using (var cmd = connection.CreateCommand())
    {
        cmd.CommandText = @"select column_name, table_name from information_schema.columns where table_name in (@namelist)";
        cmd.Parameters.AddWithValue("@namelist",  "'tableOne', 'tableTwo'");

        var reader = cmd.ExecuteReader();

        while (reader.Read())
        {
            var a = reader[0];
            Console.WriteLine(a);
        }
    }
}

Answer 1

Unfortunately, SQL parameters aren't resolved that way, in other words, the backend doesn't just build a safe-string replacing each parameter with its value. Instead, you'll have to dynamically build a parameter list:

cmd.CommandText = @"select column_name, table_name from information_schema.columns where table_name in (@p1, @p2, @p3)"; // This can be built dynamically

And then add each parameter:

cmd.Parameters.AddWithValue("@p1", "tableOne");
cmd.Parameters.AddWithValue("@p2", "tableTwo");
cmd.Parameters.AddWithValue("@p3", "tableThree");

You could of course add these parameters in a loop if the number was unknown until runtime:

for(var i = 0; i < myParams.length; i++)
{
   cmd.Parameters.AddWithValue("@p" + i.ToString(), myParams[i]);
}

If your list of tables were stored in an enum, or you could escape them or validate them with a regular expression, it would also be fairly safe to just build the raw SQL yourself and not use parameters at all.

This is, of course, one of the big reasons I use PostgreSQL; native support for arrays.

Answer 2

As has been noted, "in" lists etc are notoriously awkward in ado.net; because of this, some tools offer convenience methods to help. For example, Dapper offers a variant on the "in" syntax which it automatically expands to the correct parameterized form (still retaining injection safety etc) - in both type-bound and "dynamic" usage. For example:

string[] namelist = ...
foreach(var row in conn.Query(@"
    select column_name, table_name
    from information_schema.columns
    where table_name in @namelist",
        new { namelist } ))
{
    string col = row.column_name,
         table = row.table_name;
    // .. 
}

This also avoids the need to mess around with db-command/parameter/reader. Note the "in" without brackets which it uses to recognise this pattern.