Willy
Willy

Reputation: 10650

How to get row which has max date from a dataset using rowfilter in NET 1.1 C#

I have a dataset, let's say "ds". This dataset contains only one table, and this table may have 1 or more rows.

I need to filter rows and get the row with min datetime and at the same time with a specific value for another field, for example, condition for rowfilter would be:

Min(DateTimeField) and anotherField=12

I am using RowFilter feature as below (for one field):

ds.Tables[0].DefaultView.RowFilter="anotherField=12"

but taken into account the datetime field I am trying to do below:

ds.Tables[0].DefaultView.RowFilter="anotherField=12 and Min(DateTimeField)"

but how to pass to rowfilter datetime field from this:

ds.Tables[0].Rows[0]["DateTimeField"]

I am not sure if Min(DateTimeField) within rowfilter is working.

So how can I filter dataset rows and get only one with min datetime and anotherField=12?

UPDATE:

Using filter below:

ds.Tables[0].DefaultView.RowFilter="anotherField=12 and Min(DateTimeField)"

I am getting below exception:

An unhandled exception of type 'System.Data.EvaluateException' occurred in system.data.dll

Additional information: Cannot perform 'And' operation on System.Boolean and System.DateTime.

Any ideas?

Upvotes: 0

Views: 1436

Answers (3)

Azar Shaikh
Azar Shaikh

Reputation: 449

Not tested. But suggest you to first compute Min(DateTimeField) using DataTable Compute

  DateTime minDateVal = (DateTime)ds.Tables[0].Compute("Min(DateTimeField)", "anotherField = 12");

Then apply you row Filter using minDateVal

DataView dv = ds.Tables[0].DefaultView;

dv.RowFilter = "anotherField=12 and DateTimeField='" + minDateVal + "'";

ds.Tables[0] = dv.ToTable();

Upvotes: 1

Esko
Esko

Reputation: 4207

Lot of ways to do it, if you want to use Linq you can do:

DataRow dr = (from r in ds.Tables[0].AsEnumerable()
         where (int)r["anotherField"] == 12
         orderby r["dateTimeField"] ascending
         select r).FirstOrDefault();

For this to work, you need to add reference to "System.Data.DatasetExtension" to your project if you don't have one.

Also this needs .Net Framework 4.0, so it won't help op. But I'll leave it here in case somebody finds it helpfull.

Upvotes: 0

Loonquawl
Loonquawl

Reputation: 1076

if I understand your question correctly, you have a syntax mistake in your filter expression, it should be

"anotherField=12 and DateTimeField=Min(DateTimeField)"

instead of

"anotherField=12 and Min(DateTimeField)"

Upvotes: 1

Related Questions