Pass List<string> Into SQL Parameter

Question

The program is in C#, and I'm trying to pass a List<string> as a parameter.

List<string> names = new List<string>{"john", "brian", "robert"};

In plain SQL, the query will look like this:

DELETE FROM Students
WHERE name = 'john' or name = 'brian' or name = 'robert'

When running a SQL command in C# code, I know that the proper way of doing it is to use parameters instead of concatenating everything into one giant string.

command.CommmandText = "DELETE FROM Students WHERE name = @name";
command.Parameters.Add(new MySqlParameter("@name", String.Format("'{0}'", String.Join("' or name = '", names)));
command.NonQuery();

The above method did not work. It didn't throw any error/exception, it just simply didn't work the way I want it to.

How should I go about doing this?

I thought about looping through the List<string> and just execute on every single name.

foreach(string name in names)
{
    command.CommmandText = "DELETE FROM Students WHERE name = @name";
    command.Parameters.Add(new MySqlParameter("@name", name));
    command.NonQuery();
    command.Parameters.Clear();
}

But this will take a long time as the actual List<string> is quite large. I want to try execute at little as possible.

Thanks!

Decane

command.CommmandText = "DELETE FROM Students WHERE name in( @name )"; – Mcclendon

You cannot include SQL commands in the value of a parameter. In this case you are literally comparing name to "john or name = brian or name = robert" – Hardball

@RobertRozas You cannot put a list of values into one parameter. – Hardball

I know, it's just a comment, but the right solution is to use IN...and parameterize each value...or implode the array and glue to match the IN format – Mcclendon

Actually you can just as easily write code to add name = @n1 or name= @n2 or name = @n3 to the query and create all the parameters. Using In makes for more concise code, but the main point is that you need more than one parameter here. – Hardball

Answer 1

You can parameterize each value in the list in an IN clause:

List<string> names = new List<string> { "john", "brian", "robert" };
string commandText = "DELETE FROM Students WHERE name IN ({0})";
string[] paramNames = names.Select(
    (s, i) => "@tag" + i.ToString()
).ToArray();

string inClause = string.Join(",", paramNames);
using (var command = new SqlCommand(string.Format(commandText, inClause), con))
{
    for (int i = 0; i < paramNames.Length; i++)
    {
        command.Parameters.AddWithValue(paramNames[i], names[i]);
    }
    int deleted = command.ExecuteNonQuery();
} 

which is similar to:

"... WHERE Name IN (@tag0,@tag1,@tag2)"

command.Parameters["@tag0"].Value = "john";
command.Parameters["@tag1"].Value = "brian";
command.Parameters["@tag2"].Value = "robert";

Adapted from: https://mcmap.net/q/41380/-parameterize-an-sql-in-clause

Indurate

Answer 2

You can do it via join, create temp table and do it.

        StringBuilder sb = new StringBuilder();

        sb.Append("create table #names(name varchar(50)); ");
        sb.Append("insert into #names(name) values");
        for(int i = 0; i< names.Count; i++)
        {
            sb.Append($"('{names[i]}')");
            if (i == (names.Count - 1))
            {
                sb.Append(";");
            }
            else
            {
                sb.Append(",");
            }
        }
        command.CommmandText = @"sb.ToString() 
                                DELETE 
                                 FROM Students s
                                 join #names n on s.name = n.name";

Stead