C# - How to Loop Through a Table to Update Each Row MySQL

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

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.

How to update rows from database using foreach loop?

This line

DateTime deliveryDate = new DateTime();

creates a deliveryDate equals to "01/01/0001 00:00:00", of course adding 7,5 or 1 day doesn't give you a meaningful date to compare against DateTime.Now.

The logic to initialize the deliveryDate variable is not clear but probably you need to initialize it with the orderDate

DateTime deliverydate = orderDate; 

As others say in their comments, an UPDATE command without a WHERE condition updates all records in the table. Again you need some kind of initialization here. Probably, because you use the SELECT * FROM syntax, the record retrieved contains also the primary key of your records, so you can adjust the questy with:

string cmdText = "UPDATE Orders SET D_Status = @D_Status WHERE OrderID = @id"
SqlCommand update = new SqlCommand(cmdText, sqlcon);
update.Parameters.AddWithValue("@id", Convert.ToInt32(row["OrderId"]));
.....

Iterate trougth database table rows C#

You should not be calling the database each time you want to view the next record. Try reading all the data into a List.

I am not sure what you are using.. WinForms? WPF?

If WinForms you will need to do something like this.

    public class Student
    {//First create a class to hold your data in
      public string Name { get; set; }
      public string Num { get; set; }
    }

public class MyForm : Form
{
    int Index = 0;
    List<Student> FormData { get; set; }

    void GetData()
    {
    //This will hold all your data in memory so you do not have to make a database call each and every "iteration"
    List<Student> dbData = new List<Student>();

    string config = "server=localhost; userid = root; database = databaseName";
        MySqlConnection con = new MySqlConnection(config);

        MySqlDataReader reader = null;
        string query = "SELECT * FROM students";

        MySqlCommand command = new MySqlCommand(query, con);
        con.Open();
        reader = command.ExecuteReader();

        while (reader.Read())
        {
            Student newStudent = new Student();

            newStudent.Name = (string)reader["studentName"];

            newStudent.Num = (string)reader["studentNum"];
            //Add data to the list you created
            dbData.Add(newStudent);          

            .....
        }
        con.Close();

        //set the Form's list equal to the one you just populated
        this.FormData = dbData;
    }

    private void BindData()
    {
        //If winforms
        tbstudentName.Text = FormData[Index].Name;
        tbstudentNum.Text = FormData[Index].Num;

        //If wpf you will have to use view models and bind your data in your XAML but I am assuming you are using
        //winforms here.
    }

    private void NextRecord()
    {    //If you reached the end of the records then this will prevent IndexOutOfRange Exception
        if (Index < FormData.Count - 1)
        {
            Index++;
            BindData();
        }
    }

    private void PreviousRecord()
    {
        if (Index != 0)
        {
            Index--;
            BindData();
        }
    }
}

Now the above scenario will get it working quickly; however, there are better ways in doing this that would help you when you need to alter that data. I would recommend WinForms Binding. You can check it out here http://msdn.microsoft.com/en-us/library/c8aebh9k(v=vs.110).aspx

How to loop through database table to add data in datagrid?

You should only have to execute a single command once. You could then for example use a SqlDataReader to create a DataObject for each row that was returned from the query:

public static void AddData(DataGrid datagrid)
{
    collection.Clear();
    using (SqlConnection connection = GetConnection())
    {
        using (SqlCommand command = new SqlCommand("Select req_status, req_date_time, resp_user_name From TH_request where req_status = 'N'", connection))
        {
            connection.Open();
            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    collection.Add(new DataObject()
                    {
                        A = Convert.ToDateTime(reader["req_date_time"]),
                        B = Convert.ToString(reader["resp_user_name"]),
                        C = Convert.ToString(reader["req_status"]),
                        D = respUserName
                    });
                }

            }
        }
    }
    datagrid.ItemsSource = collection;
}

Split a foreach/for loop to make inserts by parts into MySQL database

I've finally managed to accomplish what I wanted thanks to this answer in another post:

How to split an array into chunks of specific size?

My final code goes like this:

String[][] chunks = arrFinal
                    .Select((s, i) => new { Value = s, Index = i })
                    .GroupBy(x => x.Index / 500)
                    .Select(grp => grp.Select(x => x.Value).ToArray())
                    .ToArray();

ConnectDB cdb = new ConnectDB();

for (int i = 0; i < chunks.Length; i++)
{
    foreach (string s in arrFinal)
    {
        var data = s.Split(';'); 
        if (data[1] == "REQUEST")
        {
            cdb.Insert(data[0], data[2], data[3], data[4], data[5], dateLog);
        }
        else if (data[1] == "RESPONSE")
        {
            cdb.Update(data[0], data[5]);
        }
    }
}

---

Related Topics