Populating nested collections from a database
I have the following 3 classes
Book
Product
SpecialOptions
There are many books and there are many books in the book. Likewise, there are many SpecialOptions in the Product. There are other properties of each of these three classes, so each class has the following interface
Public Interface IBook
Private ProductList() as collection (of Products)
Private Somethingelse() as String
End Interface
Public Interface IProduct
Private SpecialOptionsList() as collection (of SpecialOptions)
Private Somethingelse() as String
End Interface
Public Interface ISpecialOptions
Private SpecialOptionsProperty() as String
End Interface
I want to create a collection of books that has each of the products underneath it, and under each of those Products that I want to link to SpecialOptions when I pull data from the database. I cannot decide what would be the best way to do this.
I have two methods. Either I go from top to bottom or from bottom to top. Meaning, I start with a book and then fill in the product information and then fill in the details for each of those products. OR I can get the information first and then add them to the corresponding product and then do it again for the products in the books. None of them are very attractive.
Also, and because I suggested this to myself in proofreading, this is a structure in which I need to capture the actual relationship, so revisiting the problem with a different structure won't work.
source to share
There are several ways to approach the problem:
Load all books, then when creating each book object, load all Products, and then create each product object, load custom parameters. This will cause a lot of database queries, but is simple and simplifies DB Sprocs. Psudo code:
foreach bookR in GetBooks
Add a new book to the book collection
foreach product in GetProductByBook
Add a new product to the book product collection
foreach option in GetOptionByProduct
Add a new option to the product option collection
Load all books, all book products and all product parameters into one Sproc, returning 3 result sets. Then create all your books first, then create your products by finding the correct book in your book collection and adding it there. The same goes for your products. Psudo code:
foreach bookR in GetResultSet1
Add a new book to the book collection
foreach productR in GetResultSet2
Find the correct book in book collection
Add a new product to the book product collection
foreach option in GetResultSet3
Find the correct book in the book collection
Find the correct product in the book product collection
Add a new option to the product option collection
Return all data into one result set (LINQ-to-Sql does it this way). Concatenate all three tables together and return one result set. Iterate through each line, check if a workbook exists that matches that line (if not created), and then check if a product exists in that book (if not created). Psudo code:
results = GetResultSet
foreach result in results
get the book for matching result book id
if the book does not exist, create it from result
get the product on the book for the matching result product id
if the product does not exist, create it from result
etc
source to share