Select a DataRow from a DataRow [] collection

Question

My code :

DataRow[] row = ByTotalTemplate.Select("TEMPLATE_ID=" + DisTemplateID);

A row contains TEMPLATE_ID,MIN_AMOUNT,MAX_AMOUNT and DISCOUNT

Now I want to select a row where a given amount is between MIN_AMOUNT and MAX_AMOUNT

I tried to do this :

DataRow amountRow = row.Select("MIN_AMOUNT<" + quantity + " AND MAX_AMOUNT>" + quantity);

but this didn't work.

Answer 1

try this

        DataRow[] row = ByTotalTemplate.Select("TEMPLATE_ID=" + DisTemplateID);
        //
        //some use of **row** here 
        //than after select record from **row**
        DataRow[] amountRow = row.CopyToDataTable().Select("MIN_AMOUNT < " + qunatity + " AND MAX_AMOUNT > " + qunatity);

Answer 2

Instead of fiddling around with the expression syntax i would use Linq:

IEnumerable<DataRow> rows = ByTotalTemplate.AsEnumerable()
           .Where(r => r.Field<int>("TEMPLATE_ID") == DisTemplateID
                    && r.Field<int>("MIN_AMOUNT") < quantity
                    && r.Field<int>("MAX_AMOUNT") > quantity);

If you want a new DataTable with the filtered result:

DataTable table = rows.CopyToDataTable();

Note that CopyToDataTable throws an exception if there are no rows since it must derive the columns from the rows. So you have to check it before. You could use:

DataTable table = ByTotalTemplate.Clone();
if(rows.Any())
    table = rows.CopyToDataTable();

If you want an array instead:

DataRow[] rowArray = rows.ToArray();

If you just want the first row:

DataRow row = rows.FirstOrDefault(); // can be null if there is no matching row

Btw, your problem was that you used DataTable.Select on a DataRow[]