Build efficient SQL statements with multiple parameters in C#

I have a list of items with different ids which represent a SQL table’s PK values. Is there any way to build an efficient and safe statement?

Since now I’ve always prepared a string representing the statement and build it as I traversed the list via a foreach loop.

Here’s an example of what I’m doing:

string update = "UPDATE table SET column = 0 WHERE";

foreach (Line l in list)
{
  update += " id = " + l.Id + " OR";
}

// To remove last OR
update.Remove(update.Length - 3);

MySqlHelper.ExecuteNonQuery("myConnectionString", update);

Which feels very unsafe and looks very ugly.

Is there a better way for this?

Answer

So yeah, in SQL you’ve got the ‘IN’ keyword which allows you to specify a set of values.

This should accomplish what you would like (syntax might be iffy, but the idea is there)

var ids = string.Join(',', list.Select(x => x.Id))

string update = $"UPDATE table SET column = 0 WHERE id IN ({ids})";

MySqlHelper.ExecuteNonQuery("myConnectionString", update);

However, the way you’re performing your SQL can be considered dangerous (you should be fine as this just looks like ids from a DB, who knows, better to be safe than sorry). Here you’re passing parameters straight into your query string, which is a potential risk to SQL injection which is very dangerous. There are ways around this, and using the inbuilt .NET ‘SqlCommand’ object

https://www.w3schools.com/sql/sql_injection.asp

https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand?view=dotnet-plat-ext-6.0