Thursday, August 21, 2008

C# Delete a Lot of Rows from a DataTable

The Problem

I'm working on an app for my company, and the users have the ability to load a lot of rows into a DataGridView. That hasn't been a problem, but when the user selects more than ~20 rows to delete, the app hangs and becomes unresponsive. While there are methods to just take the specific form out and put the operation on a background worker thread, I couldn't imagine what the problem could possibly be. What I was doing is getting an array of rows from the DataTable.Select() method, and then I would loop through the results and do the Remove() method of the DataTable. I had a key on this DataTable, so I tried dropping that, but it wasn't working. I did a few Google searches, but couldn't find the answer. I'm usually pretty good at searching Google, but this time, it was a desert out there.

The Solution

It occured to me that the DataTable.Clear() method is pretty fast, and adding rows is pretty quick, so the solution is to create a new DataTable, add the rows to keep in that DataTable, clear out the original table, and then call Merge() on it to bring in the kept rows.

        DataTable myDataTable;  //this would actually be a strongly typed DataSet in my case.
        private void Example()
        {
            
            DataRow[] dataRows = myDataTable.Select("Selected=true");
            //turn off the event for a bit
            myDataTable.RowDeleted -= myDataTable_RowDeleted;

            if (dataRows.Length == myDataTable.Rows.Count)
            {
                myDataTable.Clear();
            }
            else if (dataRows.Length > 20) 
            {
                DataTable myNewDataTable = new DataTable();
                dataRows = myDataTable.Select("Selected=false");
                DataRow newRow;
                foreach (DataRow row in dataRows)
                {
                    newRow = myNewDataTable.NewRow();
                    newRow.Col1 = row.Col1;
                    newRow.Col2 = row.Col2;
                    myNewDataTable.Rows.Add(newRow);
                }
                myDataTable.Clear();
                myDataTable.Merge(myNewDataTable);
            }
            else
            {
                //it's ok to loop through 20 or fewer
                foreach (DataRow selectedRow in dataRows)
                    myDataTable.Rows.Remove(selectedRow);

            }
            //turn the event back on
            myDataTable.RowDeleted += myDataTable_RowDeleted;
        
        }

3 comments:

Anonymous said...

I'll delete ur data table.

Anonymous said...

How well will something like this scale? For example, if the original dataTable had a few thousand rows, I would image there would be quite a bit of latency as the unselected rows are copied to a new table.

Ld00d said...

The problem came up because the user was deleting a bit more than 9k rows. The timing from my solution was a lot better than looping through and deleting rows from the existing DataTable. For some reason, even on a non-indexed DataTable, it's faster to add rows than to delete (even adding them 2x which is basically what I'm doing).