Hello everyone,
Congratulations on this great forum. Great book, too.
I have a general question about TBH architecture. I'd like to know which way is best to efficiently handle one-to-many relationships in such a system. More specifically:
You are to develop a web service (ws) to allow authorized accounts to retrieve a listing of confirmed orders placed between two given dates. Such listing needs to retrieve all order items for each order. From the ws perspective, all it takes is a Order.GetOrders() call to be serialized. Let's take a look at how it would work:
As it is, TBH would first retrieve a list of DAL.OrderDetails from the DAL, pass it to the BLL, and parse them into a list of BLL.Store.Order objects. For each Bll.Order object, TBH loads a list of dependent Bll.OrderItem objects, which requires a new connection to the DB server to retrieve a listing of order-dependent Dal.OrdenItemDetails objects to be parsed into a list of Bll.OrderItem objects. (Let's ignore Bll.BizObject.Cache for now.)
We are talking a number of DB connections equal to 1 + the total no. of orders retrieved. While this may not be a big problem for a small-sized website, I don't think this is a scalable solution. From an architectural point of view, what would be a standard approach to such a requirement?
Some ideas would be to add a OrderItemDetails[] member to Dal.OrderDetails and create some methods (or add some method overloads) that retrieve two recordsets on a single DB server call. The first recordset would list all orders, and the second one would include all order items. The second recordset entries need to be sorted just as the first recordset was, to allow for efficient data parsing:
Code:
public override List<OrderDetails> GetOrders
(int statusID, DateTime fromDate, DateTime toDate bool includeOrderItems)
{
if(!includeOrderItems) return GetOrders(statusID, fromDate, toDate);
using (SqlConnection cn = new SqlConnection(this.ConnectionString))
{
/* Place SqlCommand logic here */
List<OrderDetails> orders = [we parse the first recordset];
if(orders.Count>0)
{
int orderIndex = 0;
OrderDetails currentOrder = orders[0];
while(secondRecordset.Read())
{
OrderItemDetails itemDetails=ParseOrderDetails(secondRecordset);
while(itemDetails.OrderID!=currentOrder.ID) currentOrder=orders[++orderIndex];
// hence the need for analog sorting
currentOrder.OrderItems.Add(itemDetails);
}
}
return orders;
}
}
When calling BLL.Store.Order.GetOrderFromOrderDetails(OrderDeta ils record), TBH should check whether the record contains subitems. When true, the usual call to SiteProvider.Store.GetOrderItems(record.ID) should be bypassed.
Alternatively, you could simply use an out List<OrderItemDetails> parameter and do the subitems population inside the Bll. This approach prevents having to add new members to the Dal.OrderDetails class.
What I'd like to know is what would be a standard approach according to TBH architecture (I am a self-taught programmer), be it this proposal, or an entirely different one. Also, while the example is very specific, I'd like to know the usual approach to this kind of one-to-many relationships in similar n-tier systems. Thanks.
Orders listing web service
