DataView RowFilter and Sort properties

[Mark]

These are definitely cool, but I was dismayed to learn that RowFilter does
not fully support the LIKE operator; only these forms are supported:
'%chars'
'chars%'
'%chars%'
(pattern starting and/or ending with the multi-character wildcard)

It does not support a mask with wildcard characters on both sides, like
'chars%morechars'
And it does not support the single character wildcard, '_'

Anybody know of any clever workarounds to support matching masks like
'a_bc%def%ghik' ?

Also, there any way to make case insensitive comparisons? For example,
Oracle allows filters like:
UPPER(CompanyName) LIKE 'ABC%'
lower(CompanyName) = 'mycompany'

Any way to achieve this?

Thanks

 

[Cor Ligthert [MVP]]

Mark.

These are definitely cool, but I was dismayed to learn that RowFilter does
not fully support the LIKE operator; only these forms are supported:

The Rowfilter and his expression has *nothing* to do with SQL or any other
expression language. The current Net expression itself is a very small
expression language. In future we hope that it will be replaced by LINQ.
That will be more extended, by instance more than SQL.

So there are a lot of workarounds, by instance to fetch a table from your
database server using SQL Like.

Cor

 

[Mark]

Hi Cor,

Thanks for the response.

The system I'm working on allows many levels of applying filter, sorting and
grouping. Only the very first level actually issues a query to the database,
and, as you said, LIKE is fully supported by the db manager. After that,
data is in DataTables, and the app will allow these to be filtered, sorted
and grouped, in a series of steps.

So once the query is issued, I am trying to use the DataView for subsequent
filtering and sorting levels. The app can always apply these other levels in
code (that is, make field comparisons for each row, and manipulate the
DataTable directly), but am trying to get as much mileage out of the
DataView capabilities as I can. Note that the app is a reporting only
beast, so updates are never done.

So please let me know if there is any way to get the DataView FilterRow and
Sort properties to more fully support LIKE, or to execute case insensitive
comparisons ... or, for that matter, comparisons with rounded values, like:
Round(QuarterlySales, -3) > = 5000
(select only those sales people whose quarterly sales, rounded to the
nearest thousand dollars, is >= 5000

Cheers,

 

[W.G. Ryan - MVP]

Hi Cor,

Thanks for the response.

The system I'm working on allows many levels of applying filter, sorting
and grouping. Only the very first level actually issues a query to the
database, and, as you said, LIKE is fully supported by the db manager.
After that, data is in DataTables, and the app will allow these to be
filtered, sorted and grouped, in a series of steps.

So once the query is issued, I am trying to use the DataView for
subsequent filtering and sorting levels. The app can always apply these
other levels in code (that is, make field comparisons for each row, and
manipulate the DataTable directly), but am trying to get as much mileage
out of the DataView capabilities as I can. Note that the app is a
reporting only beast, so updates are never done.

So please let me know if there is any way to get the DataView FilterRow
and Sort properties to more fully support LIKE, or to execute case
insensitive comparisons ... or, for that matter, comparisons with rounded
values, like:
Round(QuarterlySales, -3) > = 5000
(select only those sales people whose quarterly sales, rounded to the
nearest thousand dollars, is >= 5000

--Mark, with the DataView, you're going to have some problems b/c you've hit
the limitations in most of these cases. On a DataTable, there's a
CaseSensitive property that will allow for case sensitive/insensitive
comparisons. The closest I know of with Rounding is using Typecasting to
take of precision but that's probably not what you want. You can use a
dataSets extended properties to specify data much like the Tag property in
VB, so you could store what to do in there and reference it, but as far as
filtering on it or sorting it, I don't think that's going to happen.

However, one thing you can do is to create your own class that inherits from
DataTable that has a Round function in it. You'd just create your own
method and roll it out like that. I did this before for some advanced
Statistical functions that weren't available out of the box. Since these
values are 'static' in the sense that your Round method will return the
rounded value, you should be able to use the Expression to filter on it. I
did this on aggregates for the dataTable and not the individual row, but I'm
99.9% sure you can do the same with a DataColumn. In short, what I'm saying
is that you can get where you want, but not directly. If this is stuff you
do a lot though, it might be worth creating a library for it, that's what I
did for the Stats stuff (for instance, I had a UpperControlLimit and
LowerControlLimit property, a RSquare and a few others that weren't
supported). It was a little bit of a pain but since I use them a lot, well
worth it.

 

[Cor Ligthert [MVP]]

Mark,

There is a simple workaround with the dataview and that is as you almost
wrote in your previous message adding and removing extra columns.

In that way you can set the calculation as you want and filter those.

Have a look at this sample by instance

http://www.vb-tips.com/default.aspx?ID=76a81eb8-ea2d-48f4-99c3-a3539697edbd

I don't think that I can make a smaller sample,
Here the rowfilter could have been
US = 'Yes'

I hope this helps,

Cor

 

[Mark]

Interesting idea ... I forgot about column mapping! Thanks Cor

 

[Mark]

Hi William (or do you prefer Bill? ... or Mr. Ryan ;-)

Yes, deriving from DataTable is a good possibility. One reason I shied away
from it is that it is not easy to duplicate the derived class (which the app
needs to be able to do).

Public Class MyDataTable
Inherits DataTable
...

DataTable has Clone and Copy functions. My derived data table class will
have them from its base class, but they will always return instances of
DataTable, not MyDataTable. Someone else suggested I could use
serialize/deserialize as a "poor man's" copy, but I was thinking that might
be slow ... then again, to serialize to a memory stream should be pretty
snappy.

I'll mull it over a bit ... thanks for the suggestion.

Cheers ...

 

[W.G. Ryan - MVP]

Hi Mark:

Bill works ;-)

As far as Copy/Clone, unfortunately I can't be of help there. Ran into the
same problem and I ended up using serialization, depending on the size of
the dt, it's not a big deal. However the stuff I was doing was aggregating
a ton of data so it did have a performance issue, but I was dealing with
datatables larger than I am comfortable using. This has my interest though,
I was into it for a while and then got a solution that was 'good enough' so
forgot about it. I think I have a project for the night ;-) I'll let you
konw if I find anything.