How can I delete a huge number of rows without freeze the UI?

Question

I'm trying to figure out how delete a huge number of records from a DataGridView, by delete I mean run a MySql delete query and remove the row itself from the DataGridView. The issue is do all that without freeze the UI. I'm trying make it work with a BackgroundWorker. I know it still need do a Invoke() once a while and it still freeze the UI but those calls happen relatively fast and all the other other stuff keep going on background later, like the database deletion which is the most consuming-task. If there's a better to do that is very welcome.

My current implementation which obviously doesn't work, row.Index is always -1, is the following:

on click of button:

var rows = dataGridView1.SelectedRows;
backgroundWorker.RunWorkerAsync(rows);

then the backgroundWork's doWork event:

private void BackgroundWorker_DoWork(object sender, DoWorkEventArgs e)
        {
            using (MySqlConnection con = Database.GetConnection())
            {
                using (MySqlCommand cmd = con.CreateCommand())
                {
                    cmd.Connection = con;
                    con.Open();

                    var SelectedRows =  e.Argument as DataGridViewSelectedRowCollection;
                    int rowIndex = 0;
                    int total = SelectedRows.Count;
                    foreach (DataGridViewRow row in SelectedRows)
                    {
                        int id = getIdByRow_safe(row.Index); //row.Index is -1
        cmd.CommandText = string.Format("delete from xxx where id = {1}", id);

                        bool deleted = cmd.ExecuteNonQuery() == 1;

                        if (!deleted)
                        {
                            /* error handling ... */
                        }

                        int progress = (int)(100.0 / total * ++rowIndex);
                        BackgroundWorker.ReportProgress(progress, row);
                    }
                }
            }
        }

and getId() function and family:

  delegate int getIDEventHandler(int i);

    int getIdByRow(int index)
    {
        return (int)dataGridView1.Rows[index].Cells["id"].Value;
    }

    int getIdByRow_safe(int index)
    {
        if (dataGridView1.InvokeRequired)
            return (int) dataGridView1.Invoke(new getIDEventHandler(getIdByRow), new object[] { index });

        return getIdByRow(index);
    }

How do I fix this?

Look into [async and await](https://msdn.microsoft.com/en-us/library/mt674882.aspx). — Lews Therin, May 18 '16 at 19:00
what do you want the second line of code? `DeleteArguments arg = e.Argument as DeleteArguments;` — RomCoo, May 18 '16 at 19:07
@RomCoo: This was a mistake as I edited the code here on OS. Removed line — Jack, May 18 '16 at 19:09
You say `row.Index` is always -1. This looks like there is no row selected. Be aware, that you have to select the whole row for this purpose. — RomCoo, May 18 '16 at 19:27
@RomCoo: It stoped working when I try do delete from DoWork event. Before it, I when i was deleting in the main theread, it was fine. Also, I'm using SelectionMode = FullRowSelect. I'm reading about async programming, it seems a good approach — Jack, May 18 '16 at 19:33
Btw: deleting the rows one by one is slow. Try to delete with: `delete from xxx where id in (2, 77, 78, 101, 125)`. — Olivier Jacot-Descombes, May 18 '16 at 20:23
@OlivierJacot-Descombes: I'll try that! Thanks! I don't much MySql but from a research I did it wasn't possible if the condition were different. I don't throught in use `in` keyword. Seems pretty nice — Jack, May 18 '16 at 20:25
@Sylverac: I'm using async and await now, it's much better than a background worker for this case. post it as answer, thanks! — Jack, May 18 '16 at 20:52
@i486 That's bad advice. Unless you fully understand the ramifications of using `DoEvents()`...don't: http://stackoverflow.com/questions/11352301/how-to-use-doevents-without-being-evil — DonBoitnott, May 19 '16 at 13:06
@DonBoitnott I know this is not a good solution. But will make UI seem not freezing :) — i486, May 19 '16 at 14:16

score 1 · Accepted Answer · answered May 19 '16 at 12:26

I recommend transitioning to async/await instead of a Background/Worker. Here's the MSDN about async/await. And here's the example Microsoft has for it's usage:

// Three things to note in the signature:
//  - The method has an async modifier. 
//  - The return type is Task or Task<T>. (See "Return Types" section.)
//    Here, it is Task<int> because the return statement returns an integer.
//  - The method name ends in "Async."
async Task<int> AccessTheWebAsync()
{ 
    // You need to add a reference to System.Net.Http to declare client.
    HttpClient client = new HttpClient();

    // GetStringAsync returns a Task<string>. That means that when you await the
    // task you'll get a string (urlContents).
    Task<string> getStringTask = client.GetStringAsync("http://msdn.microsoft.com");

    // You can do work here that doesn't rely on the string from GetStringAsync.
    DoIndependentWork();

    // The await operator suspends AccessTheWebAsync.
    //  - AccessTheWebAsync can't continue until getStringTask is complete.
    //  - Meanwhile, control returns to the caller of AccessTheWebAsync.
    //  - Control resumes here when getStringTask is complete. 
    //  - The await operator then retrieves the string result from getStringTask.
    string urlContents = await getStringTask;

    // The return statement specifies an integer result.
    // Any methods that are awaiting AccessTheWebAsync retrieve the length value.
    return urlContents.Length;
}

How can I delete a huge number of rows without freeze the UI?

1 Answers1