C# - How To Loop Through A Table To Update Each Row MySQL

Question

I'm working on a project where I have to update a table in MySql by looping through it and update every row (one by one) in C#. NOTE: I have to update the value's of only one column in my table.

Now, I know this question has been asked (many times) before but after spending a lot of time searching around the internet I haven't found a solution for my problem.

More Explanation:

So I already know a few things that are necessary to make this work:

• I know how to establish a (working) SSH connection.
• I know how to establish a (working) MySql connection to my database.
• I know how to run/execute a SELECT and UPDATE Query/Command in C#.

So the only thing that I need to make this whole thing work, is the loop itself.

Now, during my research I came across some answers that suggested using a Reader. I don't know if this is what I need and (if yes) how to use it correctly.

Below is the code that I have so far (for establishing the connections and executing query's).

My Code:

class ReaderDemo1
{
    public static void Update()
    {
        Console.WriteLine("[Core] Opening Connection To Database...");
        Database.openStockConn(Settings.databaseName, Settings.databaseUsername, Settings.databasePassword, Settings.sshHost, Settings.sshUsername, Settings.sshPassword);

        if (Database.stockConn.State != ConnectionState.Open)
        {
            Database.openStockConn(Settings.databaseName, Settings.databaseUsername, Settings.databasePassword, Settings.sshHost, Settings.sshUsername, Settings.sshPassword);
        }
        Console.WriteLine("[Core] database connection is now open!\n");

        MySqlCommand cmd = new MySqlCommand("SELECT value FROM catalog_product_entity_decimal", Database.stockConn);
        try
        {
            Console.WriteLine("[Price] Updating Prices...");

            MySqlCommand command = new MySqlCommand("UPDATE catalog_product_entity_decimal SET value= 1112 WHERE value_id= 4063", Database.stockConn);
            command.ExecuteNonQuery();

            Console.WriteLine("[Price] Prices Have Been Updated!");                
        }
        catch
        {
            Console.WriteLine("Updating Failed!");
        }
        finally
        {
            if (Database.stockConn != null)
            {
                Database.stockConn.Close();
            }
        }
    } 
}

Just to give some extra context:

• Database.cs is where I create the connections (SSH and MySql)
• Settings.cs is where I have all the Login data for the SSH and MySql connections.

If you guys would want to know, I have already attempted a few things myself regarding the loop but (as I already mentioned) these attempts weren't successful.

My Attempt With Reader:

using (var reader = command.ExecuteReader())
{
     var indexOfValue = reader.GetOrdinal("value");

     while (reader.Read())
     {
          var price1 = reader.GetValue(indexOfValue);

           Console.WriteLine("Executing Update Command...");

           MySqlCommand cmd = new MySqlCommand("UPDATE catalog_product_entity_decimal SET value= 1222 WHERE entity_id= 759 AND entity_id= 839 AND entity_id= 881", con);
           cmd.ExecuteNonQuery();

           Console.WriteLine("Update Command Executed!"); 
    }
} 

As said, the code above didn't work how I wanted it to work (probably because it isn't actually doing anything now). Just to let you guys know, I am not stuck at an error. I'm just stuck on how to do this.

I hope one of you guys can help me with this or point me in the rigth direction and if you think that my question is a duplicate of another one, just tell me and I will look into it! :) Thanks in advance.

Kind regards,

LKS

EDIT:

In case you guys wanted to know, this is what my table looks like. The table contains about 5600 rows, so these are just the top rows.

Answer 1

Like Frederiks answer, the thing that you are trying to do now is Looping over an table and execute the same Query over and over again.

Even if you manage to get your code working it would be very slow (because you have about 5600 rows to update).

So my suggestion is that you create an new table with the new value's in it (so the one's you wanted to have after your loop). Then just run a single update command to update your old table with values from your new table.

This option probably takes a few seconds and it will be done, so its much faster! :)

The Query you need should look something like this:

    UPDATE old_table
    INNER JOIN  new_table 
    USING (column) --> // if you want to update a specific column

EDIT:

In addition/ update to my answer, this is how you can update your table more accurate:

UPDATE old_table
   INNER JOIN new_table ON old_table.value_id = new_table.value_id  // Use this to set your columns with unique values's
   SET old_table.value = new_table.value // For the column with the value's you want to update

So, in the above code you update your old_table with the value's from your new_table. In this example you only update the value's from only one column (which you wanted).

You can expand the query for a different result.