most urgent help needed making a business layer

Posted on 2006-04-26
Last Modified: 2010-04-23

I'm totally stuck. Please help :)

I am creating a query that returns a set of populated business objects. Lets take the scenario that customers can make orders, orders can contain products and there is a link table between orders and products called orderdetails.

In my business objects, a customer object will have an array of order objects, the orderdetails table will have an array of orders objects and products objects.

In essence, I need to return a set of finite set of customer objects in which all of the related objects are populated.

I have a stored procedure that returns me the relevant data for for each table/set of objects. I can populate the objects with their basic atomic attributes. The problem is setting up the relationships and setting up the arrays of objects. I need to do it as quick as possible :)

This is the only way i could think of (and it's not very good!)

Create the set of customer objects and store in a hashtable in which the key is the primary key of the record
create order objects. as i create the order object, retrieve the relevant customer from the hashtable by its primary key
create products and store in a hashtable in which the key is the primary key of the record, then and store in a hashtable in which the key is the primary key of the record
create order details, as i create order detail object, get the relevant orders and products from hashtable

That seems a total nightmare

Please tell me there is a better way. Next time I will invest in an OR mapper but for now I;m stuck!

Thanks a lot
Question by:andieje
    LVL 34

    Expert Comment

    Assuming (in outline) a setup like this at the database end

    Table Customers
       CustomerID - Primary Key

    Table Orders
       OrderID - Primary Key
       CustomerID - Foreign Key

    Table Products
       ProductID - Primary Key

    Table OrderDetails
       OrderDetailID - PrimaryKey
       OrderID - Foreign Key
       ProductID - Foreign Key

    a query on these lines

    SELECT Customer.CustomerDetails, Order.OrderID, OrderDetail.Quantity, Product.ProductDescription
    FROM Product INNER JOIN ((Customer INNER JOIN [Order] ON Customer.CustomerID = Order.CustomerID) INNER JOIN OrderDetail ON Order.OrderID = OrderDetail.OrderID) ON Product.ProductID = OrderDetail.ProductID
    ORDER BY Customer.CustomerID, Order.OrderID;

    would return the information for your customers in an order in which you could extract the data for your business objects without having to use hashtables.  If the CustomerDetails change we've finished with the last customer object instance and need to create a new instance.  If the OrderID changes within the same customer, we've finished with the last order object instance and need to create a new one.

    As I say, it's in outline.  But it's how I'd go.


    Author Comment


    I have already considered that option and dismissed it (though perhaps wrongly)

    For example, I don't want the order class to simply have a field customerID, i want the order class to have a customer object.

    This is ok for the orders table because there is only one customer. However, what do you do in the situation for orderdetails where an order or a product can feature in more than one orderdetails record. How do you know if you have already created the relevant order object, and if you have, where have you stored it and how do you retrieve it?

    the other alternative is for the business objects not to store objects and simply store an integer to represent the primary key of the object they reference.

    If i do that however, how do i do things like, get all the products in a order. I would have to store the products in a table and then look through them one by one everytime i need to access all the products in an order (which will be a lot of times)

    Any ideas?
    LVL 34

    Accepted Solution

    I'm obviously a bit confused between the order object and the orderdetails object.  On my scenario - where orderdetails would be one line of a multi-line order - there wouldn't be a situation "where an order [could] feature in more than one orderdetails record".  It would be the orderdetail that featured in an order and it could only feature in one order.

    Reading your latest post (and re-reading your first post) I see that that is not your scenario.  Indeed I still don't think I've fully understood your class structure and relationships.  I can't get my head round how an order can feature in more than one orderdetails record.  But I'm not sure that it matters.  I can understand how a product can feature in a number of other objects (whether that is properly order or orderdetails).  And I think we can talk concepts rather than specific object models.

    Over-simplified, it seems to me that the essence of your approach is that you get all the data and create objects and then search through those objects (using, I agree, an efficient hash-table method of finding them) to complete those objects that contain arrays of other objects.  The essence of my approach is that the data is obtained in an order specifically designed to cut down the searching.

    It's clear that my confusion about what are one to many relationships and what are many to many relationships means that my approach wouldn't work right down the line.  But does it have to be all or nothing?  Where there is a one to many relationship if we retrieve the data for the many side and the one side in a joined query and ordered on the one side we can create the one side instance and use it in building many instances of the many side.  So far as I can see that would work where the customer is the one side (to many orders, I think) and also where the product is the one side (although I'm not sure what other object is on the many side).  This will still leave a need to fill in the many-to-many links - for which a hash-table, or other searching, mechanism may be necessary.

    But - in so far as your present approach is, indeed, a "nightmare" - I think this more ordered approach might reduce the "nightmare" elements.  A downside is, in retrieving all the data you need to build the one side in a joined query with the data for every instance of the many side, you would be bring over a lot of data that you don't need.  Until (e.g.) the customer changes, all the customer data repeated with each order is superfluous.


    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
    Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    26 Experts available now in Live!

    Get 1:1 Help Now