How to Perform SQL Select on a DataSet

  • Thread starter Thread starter webbertsolutions
  • Start date Start date
W

webbertsolutions

I am in the process of making a desktop version of an enterprise product.
In doing so, there a many options that are stored in different tables in
a database. These values are different depending on the installation.
I have re-created the database tables necessary into XML files and store
them on disk.

I would like to take advantage of the current code that I have. Currently,
I perform select statements on the database to return a dataset.

I would like to perform the same select statement on the DataSet in order
to return the "same data" to the upper layers.

For instance, if I had 3 database tables (now DataTables in a DataSet)

+------+ +------+ +------+
| Cust | | Addr | | Ordr |
+------+ +------+ +------+
| cid | | aid | | oid |
| name | | cid | | cid |
| | | city | | qty |
+------+ +------+ +------+

I would like to perform the following select

select cust.name, addr.city, ordr.oid, ordr.qty
from
cust, addr, ordr
where
cust.cid = addr.cid,
cust.cid = order.cid
order by
cust.name,
ordr.oid


The amount of data in the dataset will be reasonably small since it is
configuration data.

Is there an easy way to query the DataSet Tables to produce a DataSet that
is equivalent to the DataSet when querying a database.


Thanks,
Dave
 
Hi Dave,

Sql is not supported for querying datasets.
If you want to do complicated query like that you will have to do it
manually.
(There is support for selects on single table - see DataTable.Select method
or DataView class)
Why don't you query the database directly - and perhaps cache the result?
 
Can't query the database as there will not be on installed for a desktop version.
 
Hi Dave,

AFAIK, the DataSet is not database. You can only perform
some simple query to single datatable from either
datatable's Select method or dataview's RowFilter
property. You can't perform join query to multi-
datatables.

HTH

Elton Wang
(e-mail address removed)
 
Miha,

I saw some misunderstanding this weekend at a regular poster in another
newsgroup.

Can it by because of your messages where you write.
(I did not check if you do this always or that it is just this time).
(There is support for selects on single table - see DataTable.Select
method or DataView class)
Click to expand...

He was talking about the DataView Select method. You did not write it,
however maybe this created his misunderstanding.

(There is support for selects on single table - see DataTable.Select method
or DataView.rowfilter property)

Just that my eye catched it.

Cor
 
Dave,

A few things here....

1) You can persist the data from a dataset as XML (myDataset.ReadXML(),
myDataset.WriteXML() ) this will allow you to load up data from disk so
that you have the ability to persist data from the dataset.

2) You can't really do a join in a dataset. However, if you have a
scheam (i.e. relations) setup in your dataset. Something like :

custRows = myDS.Tables("Customer").Select( "Customer_ID = 4")
addrRows = custRows(0).getChildRows("FK_Customer_has_Address")
orderRows = custRows(0).getChildRows("FK_Customer_has_Orders")
Will allow you to accomplish what you want. Its not as nice as a single
join statment, but then again ...a dataset is not a database and we take
the tradoffs inherent in our design desicisions.

3) You can run single table selects using:any of these methods as
appropriate:
myDS.Tables("xxx").Select( <filter criteria here> )
myDS.Tables("xxx").Find( <pk to find> )
myDV = New DataView()

4) Have you thought about using MSDE as a datastore? Since you are
porting an application that seems to already run SQL Server (i assume),
MSDE is designed for just this type of scenario. It might reduce the
complexity and scope of changes necessary to port the app.

Hope that helps,
-eric barr
 
The closest similie to a Select on a dataset is to use a combination of
DataTable.Select/ DataRowsCollection.Find/
DataView.Find/DataView.FindRows/DataRelation.GetParentRow/
DataRelation.GetChildRows.

There is no way to do a direct select. You can however do xpath if you
really wanna go nuts.

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
 
Back
Top