System.Data.SQLite parameterized queries with multiple values?

Question

I am trying to do run a bulk deletion using parameterized queries. Currently, I have the following code:

pendingDeletions = new SQLiteCommand(@"DELETE FROM [centres] WHERE [name] = $name", conn);

foreach (string name in selected)
    pendingDeletions.Parameters.AddWithValue("$name", name);

pendingDeletions.ExecuteNonQuery();

However, the value of the parameter seems to be overwritten each time and I end up just removing the last centre. What is the correct way to execute a parameterized query with a list of values?

Answer 1

Only go through the work of creating and mapping out the parameter once instead of each time the loop cycles back, also using transactions is suggested by the author to improve performance https://www.sqlite.org/faq.html#q19

using(SQLiteTransaction trans=conn.BeginTransaction())
{
    pendingDeletions = new SQLiteCommand(@"DELETE FROM [centres] WHERE [name] = '$name'", conn);
    p=pendingDeletions.Parameters.AddWithValue("$name", "");  <--

    foreach (string name in selected) 
    {
        p.Value = name;
        pendingDeletions.ExecuteNonQuery(); 
    }
    trans.Commit();
}

Answer 2

Rezzie, your current code is equivalent to:

pendingDeletions = new SQLiteCommand(@"DELETE FROM [centres] WHERE [name] = $name", conn);


foreach (string name in selected)
{
    pendingDeletions.Parameters.AddWithValue("$name", centre.Name);
}

pendingDeletions.ExecuteNonQuery();

Which means you are only executing the query once, with the last value in your 'selected' enumerable.

This is the prime reason that I ALWAYS ALWAYS ALWAYS use block delimiters on conditionals and loops ALWAYS.

So, if you enclose the parameter assignment and the query execution in the loop you should be good to go.

pendingDeletions = new SQLiteCommand(@"DELETE FROM [centres] WHERE [name] = $name", conn);


foreach (string name in selected)
{
    pendingDeletions.Parameters.AddWithValue("$name", centre.Name);
    pendingDeletions.ExecuteNonQuery();
}