How to remove rows from huge data table without iterating it?

Question

I have a DataTable available with me which contains thousands of rows. There is a column called EmpID which is containing '0' for some of the rows. I want to remove them from my current DataTable and want to create a new correct DataTable. I cannot go row by row checking it since it contains huge amount of data. Give me a suggestion to overcome this problem.

Answer 1

the best way would be to filter it at source (if possible) - so if you are creating it from a db, exclude all 0 values in your sql query itself using a where

starting .net 2.0, ms enhanced the filtering logic on the datatable to a great extent. so if you used the dataview (on top of your datatable) and added the where clause in there and added some sort of runtime indexes on this field, it would give you the desired results without looping over all records

Answer 2

You can use DataTable.Select("EmpID <> 0"). This will return an array of DataRows which you can create your new DataTable from if required.

Answer 3

Isn't it possible to first select the rows with EmpID = 0 and then iterate over these only ?

        DataTable newTable = new DataTable();
        foreach (DataRow dr in oldTable.Select("EmpID = '0'")) {
            newTable.Rows.Add(dr);
            oldTable.Rows.Remove(dr);
        }

Answer 4

You can try

DataRow[] temp= table.Select("EmpID ='0'");

  foreach(DataRow dr in temp)
    {
     table.Rows.Remove(dr);
    }

table.acceptchanges();