string array -> longId array in a SQL statement

Question

I am getting a string from HttpContext.Current.Request["key"] which i then do split(',') if its not null.

Now i would like to write a sql statement deleting any msgId (long/Int64) that matches the keys, how should i write it? The only way i unserstand is to convert the string[] to long[] then loop a SQL statement. Is there a better way?

PS: Using sqlite ATM but will be switching to another SQL engine. (i havent chosen the server yet so i dont know which)

Answer 1

DELETE FROM table WHERE id IN (1,2,3,keys)

Would that do it?

Answer 2

I would suggest you use Ben's solution above, but with a little change. I have observed that when you use many items in sql (it was with sql 2000, don't know if its still an issue with sql 2005) in statement the query tends to go slow. Try multiple delete statements in number of items in the 'in' statement exceeds 100.

Ex: DELETE FROM table WHERE id IN (1,2,3 ... n)
    DELETE FROM table WHERE id IN (1,2,3 ... n)

If you are not expecting many items then there is no need to do this.