How to use Not In datatable.select

Question

I have a DataTable (Ado.Net) with a Status column. This column contains values ​​(in each record)

['Red', 'Green', 'Blue', 'Yellow', 'White', 'OtherColors']

I want to select all rows whose status value is not red, green, blue

What type of filter expression to use to select data with my proposed criteria. So I want to achieve some kind of thing, as we used in the sql query (WHERE Status NOT IN ("Red", "Green", "Blue")

NB: .NET 2.0 works in this project, I cannot use linq

Answer 1

I tested it, it works as desired:

DataRow[] filtered = tblStatus.Select("Status NOT IN ('Red','Green','Blue')");

The resulting DataRow[]contains only DataRows with OtherColors, Yellowand White.

If you can use LINQ, I would prefer that:

string[] excludeStatus = {"Red","Green","Blue"};
var filteredRows = tblStatus.AsEnumerable()
    .Where(row => !excludeStatus.Contains(row.Field<string>("Status")));

Answer 2

Without Linq, you can use a DataView string filter like this

public DataTable GetFilteredData(DataTable table, string[] filterValues)
{
    var dv = new DataView(table);
    var filter = string.join("','", filterValues);
    dv.RowFilter = "Status NOT IN ('" + filter + "')";
    return dv.ToTable();
}

Answer 3

, datatable , Linq , - :

var records = 
      from record in datatable
      where !record.Status.Contains('Red','Green','Blue')
      select record;

Linq , . , .