Efficiently check if any cell in a DataTable contains a substring

Question

I am using the code below to allow a user to filter a DataTable by searching a particular string that could be in any column or any row. The code needs to delete rows where the value doesn't exist as the DataTable is exported after the operation.

The problem with this code is two-fold: 1) it is extremely slow for larger tables, and 2) it can only find the complete contents of a cell (i.e. if the column "Name" has a row where the value is "Andrew" the user should be able to search "drew" or "and" and get that result; right now it will return that row if they search "Andrew").

if(!String.IsNullOrEmpty(combo1Text) || !String.IsNullOrEmpty(combo2Text)
                && !String.IsNullOrEmpty(search1Text) && !search1Text.Contains("Type your search for" + comboText + "here"))
            {
                for (int i = tab1table.Rows.Count - 1; i >= 0; i--)
                {
                    DataRow dr = tab1table.Rows[i];
                    if (!dr.ItemArray.Contains(search1Text))
                    {
                        dr.Delete();
                        tab1table.AcceptChanges();
                    }
                    percentprogress++;
                    worker.ReportProgress(percentprogress);
                }
            }

What is the best way to do the filtering I want (and do so efficiently, so that it's not just looping through everything)?

https://stackoverflow.com/questions/21845016/how-to-filter-datagridview-in-c-sharp-win-forms — Magnetron, Apr 01 '19 at 17:29
It is much more efficient that way you have code now. Searching through DGV will force a refresh of DGV every time you delete which will sloow the code down. Search through the DataTable will be much more efficient. — jdweng, Apr 01 '19 at 17:33
@Magnetron I don't think that method will work in my case. Moreover, I need to delete the rows that don't pass the filter and I need to search every column, not just one. — DrakeMurdoch, Apr 01 '19 at 17:39
Call `tab1table.AcceptChanges` only once after processing all rows. It is an expensive operation. — TnTinMn, Apr 01 '19 at 18:07

score 3 · Accepted Answer · answered Apr 01 '19 at 18:09

To search if a cell content contains the searched text, try the following code:

for (int i = tab1table.Rows.Count - 1; i >= 0; i--)
{
    DataRow dr = tab1table.Rows[i];
    if (!dr.ItemArray.Any(x=>(x as string).Contains(search1Text)))
    {
        dr.Delete();
    }
    percentprogress++;
    worker.ReportProgress(percentprogress);
}
tab1table.AcceptChanges();

If you have any column that isn't of string type, you should replace (x as string) to x.ToString()

Efficiently check if any cell in a DataTable contains a substring

1 Answers1