update multiple rows using list of id in c#

Question

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 ','.'

There's plenty examples out there of how to do this. Here's one — sr28
– sr28, Commented Apr 20, 2020 at 11:09

Ehsan K. harchegani · Accepted Answer · 2020-04-20 11:03:38Z

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));

answered Apr 20, 2020 at 11:03

Ehsan K. harchegani

3,1381 gold badge19 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

svvc · Accepted Answer · 2020-04-20 11:12:14Z

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);
}

answered Apr 20, 2020 at 11:12

svvc

331 silver badge7 bronze badges

1 Comment

Selim Yildiz Over a year ago

Don't use string concatenation to build SQL Query, it causes SQL Injections. See: stackoverflow.com/questions/332365/…

Collectives™ on Stack Overflow

update multiple rows using list of id in c#

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related