Datatable select with multiple conditions

Question

Hi

I have a datatable with 4 columns A, B, C and D such that a particular combination of values for column A, B and C is unique in the datatable.

Objective: To find the value of column D, for a given combination of values for column A, B and C.

I guess looping over the set of data rows should do it. Is there a way to use Datatable.Select() to accomplish this? To be more specific - can I have multiple conditions in the select filter i.e. a logical AND operator connecting conditions for each of the columns A, B and C.

cheers

Answer 1

Sounds like you should do that in an SQL query:

SELECT d
FROM your_table
WHERE a = <your_a>
AND b = <your_b>
AND c = <your_c>

Answer 2

Do you have to use DataTable.Select()? I prefer to write a linq query for this kind of thing.

var dValue=  from row in myDataTable.AsEnumerable()
             where row.Field("A") == 1 
                   && row.Field("B") == 2 
                   && row.Field("C") == 3
             select row.Field("D");

Answer 3

Yes, the DataTable.Select method supports boolean operators in the same way that you would use them in a "real" SQL statement:

DataRow[] results = table.Select("A = 'foo' AND B = 'bar' AND C = 'baz'");

See DataColumn.Expression in MSDN for the syntax supported by DataTable's Select method.

Answer 4

I found that having too many and's would return incorrect results (for .NET 1.1 anyway)

DataRow[] results = table.Select("A = 'foo' AND B = 'bar' AND C = 'baz' and D ='fred' and E = 'marg'"); 

In my case A was the 12th field in a table and the select was effectively ignoring it.

However if I did

DataRow[] results = table.Select("A = 'foo' AND (B = 'bar' AND C = 'baz' and D ='fred' and E = 'marg')"); 

The filter worked correctly!