C# MySQL Transaction commit

Question

I want to update ~50 rows. So i do it in a foreach The Code run without any errors but there are no changes in the database.

public void updateItems(List<product> prdList)
    {
        MySqlTransaction tr = null;

        try
        {
            tr = this.con.BeginTransaction();

            MySqlCommand cmd = new MySqlCommand();
            cmd.Connection = con;
            cmd.Transaction = tr;

            foreach (product prd in prdList)
            {
                cmd.CommandText = "UPDATE products SET title='@title', quantity='@quantity' WHERE itemId LIKE '@itemId'";
                cmd.Parameters.AddWithValue("@title", prd.title);
                cmd.Parameters.AddWithValue("@quantity", prd.quantity);
                cmd.Parameters.AddWithValue("@itemId", prd.itemId);

                cmd.ExecuteNonQuery();
            }

            tr.Commit();
        }
        catch (MySqlException ex)
        {
            try
            {
                tr.Rollback();
            }
            catch (MySqlException ex1)
            {
                MessageBox.Show(ex1.ToString());
            }

            MessageBox.Show(ex.ToString());
        }
    }

If i print the Query String and run it on SQL-Bash, it works fine.

Answer 1

You should not use single quotes with your parameters. With single quotes, your query see them as a string literal, not a parameter.

cmd.CommandText = "UPDATE products SET title=@title, quantity=@quantity WHERE itemId LIKE @itemId";

Also since you try to parameterize LIKE part, you need to use %..% part in your parameter part or like '%' + @itemId + '%'.

By the way, if you have more than one prd in your prdList, you need to clear your all parameters at the top of your foreach loop.

foreach (product prd in prdList)
{
    cmd.Parameters.Clear();
    ...
    ...
}

As a last point, don't use AddWithValue anymore. It may generate unexpected results sometimes. Use .Add() method overloads to specify your parameter type and it's size. And don't forget to use using statement to dispose your connections and commands automatically.

Answer 2

change your command statement like this

from

"UPDATE products SET title='@title', quantity='@quantity' WHERE itemId LIKE '@itemId'";

to

"UPDATE products SET title=@title, quantity=@quantity WHERE itemId LIKE @itemId";

and dont forget to add

cmd.Parameters.Clear();

you should clear the parameter every loop.