Selecting data from multiple DataTables in a DataSet

[Marc A. Scirri]

Using VB.NET:

I have a DataSet object(dsTempTables) with 3 DataTable
objects(dtHeader, dtPole, dtRemarks). I need to be able to select
data from multiple tables in a way like with old ADO (Select
table1.name, table2.ID from table1 Inner Join....) where you could
return a specific set of data from multiple tables based on a single
query.

It sounds like it is not as simple in ADO.NET but I would think that
it is still possible. I am jsut struggling to find a way to do it
using DataSets and DataTables.

I know I could just keep querying the data on the server when I need
it using the SQL dataReader and SQLCommand but I would like to be able
to store the data locally instead of creating temp tables on the
server.

Any help would be greatly appreciated.

 

[Trip Kirkpatrick]

I have a DataSet object(dsTempTables) with 3 DataTable
objects(dtHeader, dtPole, dtRemarks). I need to be able to select
data from multiple tables in a way like with old ADO (Select
table1.name, table2.ID from table1 Inner Join....) where you could
return a specific set of data from multiple tables based on a single
query.

Are there DataRelations joining these DataTables? My inference, from
your SQL pseudo-snippet, is that there are and so you should be able
to get a row from one table and use GetChildRows() or GetParentRow()
as applicable to get data from other tables.

Tk

Trip Kirkpatrick
Code, Databases, Markup, Accessibility spoken here

e: remove underscore + Muppet
w: http://triptronix.net/

 

[Marc Scirri]

I have data relations on the tables in teh dataset. What I forgot to
include is that I need to have certain select criteria. My SQL
pseudo-snippet should probably have been:

select Table1.fld1, Table2.fld2, Table2.fld3 from (...) inner join (...)
where Table1.fld1 = 'x' and Table2.fld3 = 'y'

I need a way to GetChildRows() with a filter on what is returned.

 

[Kevin Yu [MSFT]]

Hi Marc,

Thank you for posting in the community!

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to use a SQL join statement to
create a table from two DataTables in a DataSet. If there is any
misunderstanding, please feel free to let me know.

As far as I know, we cannot use a SQL join statement to do that, as T-SQL
statements are not supported in DataSets. However, for workaround, we can
try the following step:
1. Add a column in the child table using DataTable.Columns.Add().
2. Set the DataColumn.Expression property to
"Parent.ColumnNameOfParentTable"
3. After joining the parent table column to child table, we can use
DataTable.Select method get the rows which meet the criteria.

Hope this helps. Does this answer your question? If anything is unclear,
please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Marc Scirri]

That's kind of what I need to do. I didn't mean that I wanted to do a SQL
join on the data tables, but the results I need are similar to that. Let's
try this:

Header Table:

Detail Table:

In SQL to get the result set I wanted I would use the following query:

SELECT Count([Detail].[guid]) as Total, [Header].[state], [Detail].[itemID]
FROM [Header]
INNER JOIN [Header] on [Header].[guid] = [Detail].[parentguid]
WHERE [Header].[state] = 'NY' AND [Detail].[miles] > 40
GROUP BY [Detail].[itemID],[Header].[state]


I have the Header and Detail tables as DataTable objects but I have no idea
how to get the result set like I would from using a SQL query like above.

The tables are in a DataSet object with a relationship setup between
[Header].[guid] and [Detail].[parentguid].

 

[Kevin Yu [MSFT]]

Hi Marc,

I think in DataSet, we cannot do an INNER JOIN. In this case, we might do a
LEFT JOIN. Please try to use the way I mentioned in my last post to add a
column with the expression "Parent.ColumnNameOfParentTable" to the child
table. Thus we can manipulate the child table just like a joint table.

Does this answer your question? If anything is unclear, please feel free to
reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[new.microsoft.com]

Can you do that SQL in a VIEW and treat it like a real table?