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.

Solution

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.

Comments

[Marc B]

you're quoting your parameters. that is wrong. parameter go in "naked", never quoted. set title=@title, not set title='@title'.

[DavidG]

And don't use AddWithValue, be specific about the types you are adding.

[Philipp Nies]

Example DB: itemID - 0001 itemID - 00001 Now if i search %itemId i get 2 results back or?

[Philipp Nies]

*If i use Parameters.Add() VS display .....Add() ist veraltet: Use AddWithValue()