Datatable Select with Multiple condition

Question

Hi all,

DataTable table = new DataTable("EmployeeDetails");
table.Columns.Add("Id", typeof(int));
table.Columns.Add("Name", typeof(string));
table.Columns.Add("Status", typeof(int));

datatable contains following data

Id    Name    Status

1     A             0

2     B             0

2     C             1

I need to get all the employee details and also for employee id 2 i should get the row which has status 0.

result should be

Id    Name    Status

1     A             0

2     B             0

am not able to do with multiple condition on single column. please help.

---

vinaya kumar

Answer 1

Your example looks like a single-column single-condition selection:

table.Select("Status=0")

If you want only the line with Id=2 and Status=0, pass the two condition to the Select:

table.Select("Id=2 and Status=0")

Answer 2

Many ways to query a DataTable.

1.) You can use the DataTable.Select method. However, the results are an array of DataRow which is not bindable.
2.) You can use a DataView which is efficient for sorting, filtering. All DataTables have a default DataView -- DataTable.DefaultView
3.) Or you can use LINQ.

#2 and #3 are the preferred ways for binding the results.

//DataView
DataView dv = new DataView(tableOne, "OrderID <> 'XXXX'", "OrderTime desc", DataViewRowState.CurrentRows);

Or transform the DataView back to a DataTable
DataTable newTable = new DataView(tableOne, "OrderID <> 'XXXX'",
                "OrderTime desc", DataViewRowState.CurrentRows).ToTable();
                 
Or you can use LINQ
IEnumerable<DataRow> ordered = tableOne.AsEnumerable()
                .Where(i => i.Field<String>("OrderID ") != 'XXXX')               
                .OrderByDescending(i => i.Field<DateTime>("OrderTime "));

Or use LINQ to transform it to a DataTable
DataTable newTable = tableOne.AsEnumerable()
                 .Where(i => i.Field<String>("OrderID ") != 'XXXX')                 
                .OrderByDescending(i => i.Field<DateTime>("OrderTime "))
                .CopyToDataTable();

---

John Grove, Senior Software Engineer http://www.digitizedschematic.com/

Answer 3

Hi Louis.

Thanks for reply, along with Id=2 and Status=0 i need rest all the rows which are having id =1

how can i do this in select expression  

if ID!=2 return currentrow
else  if(id=2 & status =0)
return currentrow
else ignore row

---

vinaya kumar

Answer 4

Try using Linq:

            DataTable table = new DataTable();
            table.Columns.Add("Id", typeof(int));
            table.Columns.Add("Name", typeof(string));
            table.Columns.Add("Status", typeof(int));
            table.Rows.Add(1, "A", 0);
            table.Rows.Add(2, "B", 0);
            table.Rows.Add(2, "C", 1);

            DataTable Id2_Status0 = table.AsEnumerable().Where(w => (int)w["Id"] == 2 && (int)w["Status"] == 0)
                                                        .Select(s => s).CopyToDataTable(); //1 result

            DataTable Id1 = table.AsEnumerable().Where(w => (int)w["Id"] == 1).Select(s => s).CopyToDataTable(); //1 result

---

Mitja

Answer 5

table.Select("Id=1 or Id=2 and Status=0")