Selecting from a Dataset? ASP.NET

[Tavish Muldoon]

Hello,

I have a dataset with 2 tables. I want to display only selected data
from one of these tables.

The table names are Albums and Tracks. That Dataset is called
AlbumsTracks

If I only wanted to select those tracks that had a common AlbumID as
those in Albums - how would I get this out of a dataset?

Pseudo code:
Select * from Tracks where AlbumID=Albums.AlbumID
from Dataset Albums tracks.

Syntax? Or is there a smarter method?

Thanks,

Tmuld.

 

[JJ]

Table objects have a Select method which enables filtering
and sorting of data. For your application try:

DataRow[] rows = Tracks.Select("AlbumID=" +
someAlbumID.ToString());

For readonly filtering, use a DataView Filter instead.

 

[Pat]

You could use a DataRelation object.
Pseudo Code:
- create dataset
- read table albums
- read table tracks
- create DataRelation object for tracks <--> albums

alternatively, (and quicker)
is to create a stored procedure and run it from the data
adapter instead of a query:

CREATE PROCEDURE [dbo].[GetAlbums]
AS
BEGIN
Select Tracks.* from Tracks T inner join Albums A ON
T.AlbumID = A.AlbumID
END

The second method should work in any standard T-SQL
enabled db such as MySQL, INTERBASE, MS-SQL or Access.
You would need to check specific formatting, especially
for Access and INTERBASE.

Hope this helps.

 

[Roy Osherove]

You would need to create A Data Relation between those Data Tables in the
dataset, and then you can navigate each album row's children using this
syntax:

For each DataRow track in MyAlbumRow.GetChildren(RelationName)
{
// loop through all the children.
}

If you want to show all the child rows in a datagrid, you can bind the grid
directly to the relation by setting its DataSource to the dataset, and its
datamember to "MasterTableName.RelationName".

Then just bind the Albums table to another grid and watch the magic...

Good Luck.