0

I'm having list of ids and I want to update the database table. I'm doing something like this:

sql = "update table set col = 'something' where id in (@Ids)"

using (var connection = new SqlConnection(_connection)){
   connection.Query(sql, new { Ids = ids});
}

The error is:

System.Data.SqlClient.SqlException: 'Incorrect syntax near ','.'

Selim Yildiz
  • 5,254
  • 6
  • 18
  • 28
svvc
  • 33
  • 7
  • There's plenty examples out there of how to do this. [Here's one](https://stackoverflow.com/questions/21709305/how-to-directly-execute-sql-query-in-c/21709663) – sr28 Apr 20 '20 at 11:09

2 Answers2

1

the simpleset way is this:

var parameters = new string[ids.Length];
var cmd = new SqlCommand();
for (int i = 0; i < ids.Length; i++)
{
    parameters[i] = string.Format("@Id{0}", i);
    cmd.Parameters.AddWithValue(parameters[i], ids[i]);
}

  cmd.CommandText = string.Format("update table set col = 'something' where id in ({0})", string.Join(", ", parameters));
Ehsan Kiani
  • 3,050
  • 1
  • 17
  • 19
0

Solution : This is working for me

sql = "update table set col = 'something' where id in ("+string.Join(",", ids) + ");";

using (var connection = new SqlConnection(_connection)){
   connection.Query(sql);
}
svvc
  • 33
  • 7
  • Don't use string concatenation to build SQL Query, it causes SQL Injections. See: https://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work – Selim Yildiz Apr 20 '20 at 11:15