Can I filter rows or create aggregate rows with DataTable?

[MrNobody]

I've read that the expression property for DataColumns is used to "Sets or
retrieves the expresssion used to filter rows, calculate the values in a
column, or create an aggregate column.".

I have seen examples on how to filter a column, but how would I filter out
an entire row depending on the value of a column?

For example, if I wanted to filter out every row in a multi-column table
where the "Is Fubar" column equals "true", how would I do this using
expression?

The other question was about creating an aggregate row. I see how aggregate
columns are created, which perform calcs on values with a row, but is there a
nice and easy way to create an aggregate row?

One option I was thinking about (stop me if it sounds too crazy) is to have
a second DataTable, which will only have one row with matching columns as the
first DataTable. This row would be the aggregate row for all the rows from
the first table. But can I use expression on the total columns in the second
table to sum all the values of a particular column in the first table?

[Miha Markic [MVP C#]]

"MrNobody" <Mr******@discussions.microsoft.com> wrote in message
news:61**********************************@microsof t.com...

I've read that the expression property for DataColumns is used to "Sets or
retrieves the expresssion used to filter rows, calculate the values in a
column, or create an aggregate column.".

I have seen examples on how to filter a column, but how would I filter out
an entire row depending on the value of a column?

For example, if I wanted to filter out every row in a multi-column table
where the "Is Fubar" column equals "true", how would I do this using
expression?
Try with "[Is Fubar] = true" expression.

The other question was about creating an aggregate row. I see how
aggregate
columns are created, which perform calcs on values with a row, but is
there a
nice and easy way to create an aggregate row?
What does aggregate row mean to you in first place?

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

One option I was thinking about (stop me if it sounds too crazy) is to
have
a second DataTable, which will only have one row with matching columns as
the
first DataTable. This row would be the aggregate row for all the rows from
the first table. But can I use expression on the total columns in the
second
table to sum all the values of a particular column in the first table?

[MrNobody]

"Miha Markic [MVP C#]" wrote:

Try with "[Is Fubar] = true" expression.

Ok, but then where do I put this expression? For example, I have a DataTable
with 3 columns named "ID", "Country", "Continent". If I want to filter every
row in this table where Continent = 'Europe', how do I do that?

I tried:

datatable.Columns["Continent"].Expression = "[Continent] = 'Europe'" but I
get the error: " Cannot set Expression property due to circular reference in
the expression.". So I tried doing that on the "ID" Column and all that
happens is it converts my data in ID column to '1' if the row's continent is
Europe or '0' if it's not. What I want is for that row to become hidden.

As far as I can tell, there is not other place to enter Expressions for rows
on DataGrid/DataTable.

Is there something I am missing?


What does aggregate row mean to you in first place?

Well to me it means a row which performs aggragate functions on the data in
all the other rows of the table. So if my table contained rows which had a
prices column, I may want to have an aggregate row on the bottom which does a
Sum on all the prices of that table. Can I do this using expression, and if
so, how do I do it?

Thanks for your reply

[Miha Markic [MVP C#]]

"MrNobody" <Mr******@discussions.microsoft.com> wrote in message
news:63**********************************@microsof t.com...

"Miha Markic [MVP C#]" wrote:

Try with "[Is Fubar] = true" expression.

Ok, but then where do I put this expression? For example, I have a
DataTable
with 3 columns named "ID", "Country", "Continent". If I want to filter
every
row in this table where Continent = 'Europe', how do I do that?

I tried:

datatable.Columns["Continent"].Expression = "[Continent] = 'Europe'" but
I
get the error: " Cannot set Expression property due to circular reference
in
the expression.". So I tried doing that on the "ID" Column and all that
happens is it converts my data in ID column to '1' if the row's continent
is
Europe or '0' if it's not. What I want is for that row to become hidden.

As far as I can tell, there is not other place to enter Expressions for
rows
on DataGrid/DataTable.

Is there something I am missing?

Use DataView to filter the content or DataTable.Select method.
DataView dv = new DataView(table);
dv.RowFilter = "filter expression";

What does aggregate row mean to you in first place?

Well to me it means a row which performs aggragate functions on the data
in
all the other rows of the table. So if my table contained rows which had a
prices column, I may want to have an aggregate row on the bottom which
does a
Sum on all the prices of that table. Can I do this using expression, and
if
so, how do I do it?

No, you can't, since the expression applies to whole column and aggregates
are used in master-detail relations.
However, you might add a row to table and set its values manually (to
reflect the aggregate value).

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com