Query Parser for Dataset??

[Crash]

Is there any sort of query engine for use with .NET datasets? I have
an application that has a very small disconnected client database - 2
dozen tables, largest table @100 rows. If loaded into a dataset and
then saved to disk the absolute worst case file wouldn't even be 10
meg...

However, to present data to the user, the application would need to
issue a very complicated select statement; inner & outer joining most
of the tables, nested selects on most of the other tables, several
levels of grouping and aggregation.

Is there any way to do this without using an underlying database?

[Steven Nagy]

Well usually I would do this with stored procedures and views, but as
you mentioned you need to do it in the dataset.
Datatables have a few filtering mechanisms, but I don't think you will
be getting the kind of power you need out of them.
You really need to be specifying what data you want to select before it
gets to the dataset.
You can of course construct your sql statement in your C#, and then
send it via a data-adapter, and fill your dataset that way.
Just change the SelectCommand's command text to your sql statement. You
won't be able to do updates though.

If you have already considered all of these, I am not sure what else
there is to help.

[Crash]

We can use SQL Server express if we must... But I'd rather not set up
merge replication and all of that stuff if there is an easier way.

The application would be mind boggling simple if I could fetch a
dataset from the server - say through a web service - modify it
disconnected, and then pass the dataset back to the server via the web
service. Then the server could issue the Dataset.Update call to put
the data into SQL Server...

The only catch is that I need to present the data to the user in a grid
that is filled by a very complicated query...

[Steven Nagy]

So why can't you have your webservice simply accept an SQL string as a
parameter, and still return a dataset?
Then you can bind the dataset to the grid. You can create your complex
call in the SQL that gets passed.

This way you can have dynamic content in your grid at run time.

Naturally you'd want some sort of authentication on that kind of a web
method since it could potentially be used maliciously.

[Crash]

When I said disconnected I wasn't kidding... :)

Use case: A nurse connects to a network via cellular modem & vpn login
{painfully slow to establish connection}, downloads his/her caseload to
a tablet PC, disconnects. Then nurse drives all over town visiting
patients in their households and updating data. Nurse can add new
households and new patients while disconnected. Supervisors can edit
caseload and patient data on server side while nurse is disconnected.
Tablet can be turned on/off and application may be entered/exited at
will. Every other day or so nurse reconnects to network and
synchronizes with server...

The dataset is very small; one nurse's caseload - largest conceivable
real world caseload is @50 households containig @70 patients. The
dataset consists primarily of hierarchical household to patient and
mother to child relationships; most of the rest of the dataset is
enumeration lookup tables and some 1-many relationships; aka
inner/outer joins and nested selects when we do the complicated query.
In order to be HIPPA compliant we would naturally encrypt the dataset
if we saved it to the tablet or while it was being sent to/from server,
and of course the web service would be secure too...

The gotcha is a couple of things that need to have a realtime query
parser available while disconnected:

- The opening screen issues the complicated query to present the nurse
with his/her caseload. If nurse adds a new household and/or patient
they need to appear in the correct spot.
- Nurses can dynamically configure main grid to be "household centric"
or "patient centric" etc..
- Nurses need a rudimentary form of adhoc query capability to find
patients by bizarre combinations of demographics or medical conditions

The dataset approach might not work - I'm just pondering options
aloud... As a nurse can add a new patient while disconnected and a
supervisor can also change the caseload on the server while
disconnected this application might really need merge replication...
It just seems like I'm squirrel hunting with an elephant gun to
configure and administer 100+ tablets with SQL Server and merge
replication for a 70 row grid...

[Steven Nagy]

Sure.

I don't think you want to be putting MSDE on every tablet.
You can cache the dataset serialised and encoded to the local disk.
Essentially have this serialised dataset as a pretend local database,
peforming all your queries in your code;
keep copies fo the enum tables in the cache as well. I guess you still
have to figure out how to create complex queries against your dataset.
And that's the part I'm not sure on beyond the basic filters for the
dataview.

Otherwise your logic sounds good to me. Perhaps you need to just to
coded lookups for all the nested relationships against each table as
they are requested.