Populate EditableRootListBasePopulate EditableRootListBase
Old forum URL: forums.lhotka.net/forums/t/6508.aspx
Slayer posted on Wednesday, February 25, 2009
Hi
I use C# and SQL Server 2005 and I need a recommendation on how to populate my objects.
I have a Customers collection containing a collection of customer objects. Each customer object contains a Orders collection, containing a collection of orders.
I use a public Fetch() method on my Customers collection to populate the customers and their orders.
You can only have one DataReader open per connection, right. So that would mean that I need one connection for the 'SELECT * Customers WHERE Id = @Id' reader, and while I iterate through the customers reader I would need another connection for each 'SELECT * Orders WHERE CustomerId_fk = @Id'.
My question : Would you recommend I use the above way or just plain DataSets ?PeterRigbye replied on Thursday, February 26, 2009
I think what you need to do is return multiple result sets from the one stored procedure, as in
SELECT F1, F2 from CUSTOMERS where CustomerID= @ID
SELECT F1, F2 from ORDERS where CustomerID = @ID
in the Root collection B.O. use the dr.nextresult and pass this to the Orders collection to populate.
Or you could have each Customer BO fetch it's own orders directly from the database.
However I would caution that you woudl end up with a potentially large object and data traffic doing this - all customer with all orders!. Instead you might consider a light-weight read-only colection of customers, and when selected, retrieve just the orders for that customer.
If you've gone to the trouble to implement CSLA/object-based approach, don;t go back to using datasets IMHO.
JoeFallon1 replied on Thursday, February 26, 2009
Hi,
I have each BO fetch its own children. To do that you need a 2nd datareader.
I use code like this:
Protected
Overridable Sub Fetch(ByVal dr As SafeDataReader)
SetDefaults()
FetchData(dr)
PostFetchData()
FetchChildren()
MarkOld()
ValidationRules.CheckRules()
End Sub
Protected Overridable Sub FetchData(ByVal dr As SafeDataReader)
With dr
mKey = CLng(.GetValue("key"))
'etc.
End With
End Sub
Protected Overridable Sub PostFetchData()
'marker method that can be overridden in child class
End Sub
Protected Overridable Sub FetchChildren()
'load child objects here if there are any using a second datareader
Dim dr2 As SafeDataReader = Nothing
Try
dr2 = New SafeDataReader(DAL.ExecuteReader(xyzDAO.SelectByKey(mKey)))
mColl2 = Coll2.GetColl2(dr2)
Finally
dr2.Close()
End Try
Try
dr2 = New SafeDataReader(DAL.ExecuteReader(abcDAO.SelectByKey(mKey)))
mColl3 = Coll3.GetColl3(dr2)
Finally
dr2.Close()
End Try
End Sub
Joe
Slayer replied on Friday, April 03, 2009
Hey Joe, this sounds good. But, the dr passed to the fetch method is still open, so this will throw an exception if you attempt to open another reader in the FetchChildren method. You will first need to close dr, in order to query the children.
Or are you creating a new db connection for each reader ?JoeFallon1 replied on Friday, April 03, 2009
Slayer:Hey Joe, this sounds good. But, the dr passed to the fetch method is still open, so this will throw an exception if you attempt to open another reader in the FetchChildren method. You will first need to close dr, in order to query the children.
Or are you creating a new db connection for each reader ?
If you read the code closely you will see that the first datareader is a variable named dr.
I am creating a 2nd datareader named dr2 which uses a different connection to fetch the child objects.
Then I close it and re-use it for the next child, etc.
Joe
Copyright (c) Marimer LLC
