I have a Collection on Menu Sections which each contain a collection of Menu Items (it's a two-tier menu, simple stuff). Ideally I could deepload the MenuSection collection to grab the MenuItems atthe same time, but failing that, is there a way that I can return a separate Collection of each using only one database call via subsonic? Am I stuck using a DataSet with multiple tables?
A:
Yes - there are ways to do this - many different approaches. You can use a View and load everything, then spin that out to a structured object set. Or you could run a joined query into a datareader and load the objects with some looping logic.
Rob Conery
2009-05-30 19:11:44
Rob, thanks for the reply. I'm not really clear on what you mean by "spin that out" -- do you mean I should load a collection of rows that have the data from both menu sections and items?With the reader and looping, wouldn't that mean getting a collection of sections, then a separate call for each group of items?
Pete Michaud
2009-05-30 20:29:58
By "spin that out" i mean loop over the the reader and assign the values to a collection. If you use a view then you can get a flat representation of the data. If you use a joined query - same thing - a flat representation of the data that you fill the two collections with.
Rob Conery
2009-05-31 07:57:18
A:
What Rob said is correct. Another angle would be like so:
Partial Public Class MenuSection
Private _ChildMenus As Generic.List(Of MenuSection)
Public Property ChildMenus() As Generic.List(Of MenuSection)
Get
If _ChildMenus Is Nothing Then
' Load the data in the list
_ChildMenus = New SubSonic.Select().From(Data.MenuSelection). _
Where("ParentMenuItemId").IsEqualTo(1). _
ExecuteTypedList(Of Data.MenuSelection)()
End If
Return _ChildMenus
End Get
Set(ByVal value As Generic.List(Of MenuSection))
_ChildMenus = value
End Set
End Property
End Class
Rick Ratayczak
2009-05-31 04:56:38