1

How to write mysql update query for updating multiple rows of datagridview in single statement by passing parameters and then executing all together in c#.net windows application

for example create update query like is given below

foreach(DatagridViewRow dr in datagridview)
{
   sql = "update table1 set name=@name where id=@id; update table1 set name=@name where id=@id; update table1 set name=@name where id=@id;"
}

How to create a sql query with parameters? So that after creation of query; we can execute it together to make changes in multiple rows.

Manish Jain
  • 1,197
  • 1
  • 11
  • 32
vivek agarwal
  • 13
  • 1
  • 6
  • Check out this option, which doesn't require datagridview and can be handled in a stored procedure - https://stackoverflow.com/a/65950733/3714181 – Liam Jan 29 '21 at 08:04

1 Answers1

0

You don't need to put multiple Update queries together. Here take a look: 

string sql = "update table1 set name=@name where id=@id";
MySqlParameter pId = new MySqlParameter("@id", SqlDbType.BigInt);
MySqlParameter pName = new MySqlParameter("@name", SqlDbType.NVarchar);
cmd.Parameters.Clear();
cmd.CommandText = query;
cmd.Parameters.Add(pName);
cmd.Parameters.Add(pId);    
foreach(DatagridViewRow dr in datagridview)
{
    pId.Value = dr.Rows["iDColumn"].ToString();        
    pName.Value = dr.Rows["nameColumn"].ToString();
    cmd.ExecuteNonQuery();
}

Hope this helps.

affanBajwa
  • 158
  • 3
  • 13
  • 1
    Additional context on top of this answer - check out this link: https://stackoverflow.com/questions/3432/multiple-updates-in-mysql. You can see a SQL query for MySQL, which updates multiple rows with one SQL command. I use this strategy often. I do wish MySQL would support UPSERT syntax... – Michael LoCicero Dec 15 '18 at 09:15
  • thank you for the answer. i am aware of this . I was in search for creating multiple update query (especially for datagridview in c#) and when we excute it all the updates are done together. – vivek agarwal Dec 16 '18 at 03:24