Complex queries on Local data objects?

[divisortheory]

I'm using typed datasets and in my application I, for the most part,
keep local copies of everything in the database in DataTables. So at
any given time my local tables always have at least as much (usually
more) information than the actual database, and occasionally I use the
adapters to Update() the changes back to the database. However,
sometimes I want to access the data in a way which is beyond the scope
of what the generated typed dataset code allows me to do. I can't seem
to find any functions in the DataTable and/or DataSet objects either.
There's the Select() method, but that doesn't let me specify GROUP BY,
JOINs, or other types of more complicated Sql queries. Is there a way
to do this without making a round trip to my database?

If I absolutely must, I can write some code such that every time I need
to execute one of these more complicated queries, all my local
modifications get flushed to disk and then I run the query against the
database, but if possible I'd like to do it on the local
DataSet/DataTable objects.

Thanks

 

[Cor Ligthert [MVP]]

Dibishortheory,

Maybe not the best answer for people using strongly typed datasets, however
try the DataView especially in the 2005 version it has the possibilities you
where asking.

(Although for Joins you need DataRelations)

http://www.vb-tips.com/dbpages.aspx?ID=cb1408ff-030d-4ea8-80b1-af26354b1fa0

There is more stuff about this on our website

Cor

 

[Otis Mukinfus]

I'm using typed datasets and in my application I, for the most part,
keep local copies of everything in the database in DataTables. So at
any given time my local tables always have at least as much (usually
more) information than the actual database, and occasionally I use the
adapters to Update() the changes back to the database. However,
sometimes I want to access the data in a way which is beyond the scope
of what the generated typed dataset code allows me to do. I can't seem
to find any functions in the DataTable and/or DataSet objects either.
There's the Select() method, but that doesn't let me specify GROUP BY,
JOINs, or other types of more complicated Sql queries. Is there a way
to do this without making a round trip to my database?

If I absolutely must, I can write some code such that every time I need
to execute one of these more complicated queries, all my local
modifications get flushed to disk and then I run the query against the
database, but if possible I'd like to do it on the local
DataSet/DataTable objects.

Thanks

When using The IDE to create Data Sources you can write your own queries and
point the wizard at them. The easiest way to do that is to create stored
procedures and specify them when the wizard asks if you want to create or use
existing stored procedures.

Another way to get the queries you want is to add them to the TableAdapters in
the DataSet Designer.

This is all explained in the online documentation.
Good luck with your project,

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com

 

[Miha Markic [MVP C#]]

Is this a single user application?

 

[divisortheory]

Is this a single user application?

Yes

 

[Adrian Moore]

Hi,

You might be interested in the assembly I've been working on at
http://www.queryadataset.com. Besides INNER JOINS, it lets you perform
complex SQL SELECT statements including UNION, OUTER JOINS, GROUP BY,
HAVING, ORDER BY, sub-queries, etc against the tables in a dataset.

Adrian Moore
http://www.queryadataset.com