advice about best way to use dataAdaptor/datasets

[tom_hotspur]

Hiya.

As a newbie to .net i need some advice about using the dataAdaptor / datasets.

Scenario: Basically i have 3 related tables (in SQL server) representing a
Master location, location and department. I want to simply display these in a treeview (where a master location can be expanded to show all location etc...).

In VB6 i achieved this by first returning all master locations (in a recordset) and for each master location i returned its contained locations with another recordset and each locations departments with yet another.

'loop through master locations
While Not objRec.EOF
        
        'add the cma as a node
        Set objNode = Me.tvw_company.Nodes.Add(, , objRec![cmano]    & "cma", objRec![cma], "closed")
        objNode.ExpandedImage = "open"
        
        'load locations (using same looping method)
        Call LoadDealers(objNode)
        
        'next record
        .MoveNext
    Wend

As this is a READ ONLY operation i first looked to DataReaders but i could not use this method as only 1 datareader is permitted per connection.

Therefore i turned to datasets and dataAdaptors. I have had no experience using them before so i could do with some advice in the above scenario.

What would be the best implementation of returning the locations for a master location and following on a locations department?

A dataset and dataAdaptor per routine?

OR

populate the dataset with all 3 related tables and then naviagate through them? But if so does this have any performance issues? The tables are not particularly large. AND most importantly how would i achieve this?


This isn't a particularly complex problem, its just that at the moment i haven't had any experience with the forementioned. Any advice would be mucho appreciated.

[jwebster03]

I would put all tables into a dataset and then navigate through. Remember, datasets work in a disconnected environment so you should not be faced with any performance issues. (Also consider creating a view on the sql server end)

Here is some code to create a dataadapter and then fill a dataset. SqlConn refering to a connection string.

Dim da As sqlClient.SqlDataAdapter
Dim ds As New Dataset

da = New sqlClient.SqlDataAdapter("SELECT * FROM WHEREVER", sqlConn)

da.Fill(ds, "ANYNAME") 'This fills the dataset

'You can now navigate through, manipulate the data, and bind to controls on your form.

[tom_hotspur]

Thanks for the advice Jim.

Originally posted by jwebster03
(Also consider creating a view on the sql server end)

A view wouldn't really give me what i'm after as i only want to present a 'Hierarchical' representation of the data in a treeview rather than a JOIN of the tables. But i'll have a go at returning multiple result sets from a stored proc.

Cheers for the help.