Question about 3-tier design. Help please.

HI friends,, please help me to clear me doubt..

Scenario

http://img258.imageshack.us/img258/5...eenshotrqv.jpg

As It is presented in the above picture... all logic related to customer is processed throught cutomerBAL class and logics related to Orders are processed through OrdersBLL. Same like.. all process with Customer table is procecesd by CustomerDAL and Order table with OrdersDAL.

so. each table has its own entity. Meantime..If I want o execute a joint query between Customers & Orders, which class will process it? Do I need a seperate entity for that?

Please help me to clear my doubt..

It depends on the circumstances. If appropriate you might create another entity but, in this case, probably not. You'd either perform a single query and extract out the data from the result set for the two entities or else you'd perform two separate queries.

Quote:

---

Originally Posted by jmcilhinney

It depends on the circumstances. If appropriate you might create another entity but, in this case, probably not. You'd either perform a single query and extract out the data from the result set for the two entities or else you'd perform two separate queries.

---

thnx jm,

the above scenario is just an example to explain my question. My environment is large. it has 50+ tables. 50+ entities. The reason I wanted to clear this doubt is to go on a standard way. I would have many joint query to execute if I want to proceed.

What I want to konw is the correct standard. I should create seperate DAL class and BLL class for where joint query needed or has to perform operation by normal query multiple times?

It's all about what makes sense. If you want to do something like GetOrdersBelongingToCustomer, then you're actually working with Orders here, the method will return Orders and you'll mostly be looking at Orders.

Similarly, GetCustomerNameFromOrderId has to do mostly with the Customer.

Quote:

---

Originally Posted by mendhak

It's all about what makes sense. If you want to do something like GetOrdersBelongingToCustomer, then you're actually working with Orders here, the method will return Orders and you'll mostly be looking at Orders.

Similarly, GetCustomerNameFromOrderId has to do mostly with the Customer.

---

thnx mendhak, you right! so, if want to get (GetOrdersBelongingToCustomer) then should i implement the function with join query inside OrdersBLL and Orders DAL ? Is it what you mean?

If you are getting all the Orders belonging to a Customer then you're only getting Orders, so there's obviously no need for a new entity. The fact that your stored procedure contains a join is irrelevant, but in this case it wouldn't anyway. The fact remains that the result set will be Order data only. For instance, you might have a Customer object in your application. The user selects that Customer and you want to show all the Orders for that Customer. You'd execute a stored procedure that took the CustomerID as a parameter and returned all the Orders with that CustomerID.

You need to keep in mind that there are often competing factors at work in software development. In this case you might want to perform a lot in a single stored procedure to reduce the number of trips to the database. If the sproc joins a lot of tables then you may end with a result set with data from many different sources. The problem there is that you then either need to complicate your app with new entities that are really just a whole or partial combination of other entities, which is bad from a maintenance perspective, or you need to provide complex logic to separate each record in the result set into multiple entities. That may be the best option but, then again, it may not.

Another thing to consider is that it's quite OK for a single sproc to return multiple result sets. Assuming ADO.NET, that means that you might be able to make a single trip to the database but populate multiple DataTables in a DataSet or return a DataReader with multiple result sets. That makes your application logic cleaner while still keeping the number of trips to the database to a minimum. Of course, it may make your stored procedures more complex, or it may not.

There really is no hard and fast rule. There are certain things that you shouldn't do but the best option always depends on the circumstances, and personal preference to a degree.

Thank again for the good reply jm.
Specially i wanted to notice the Database routine by the application. From your words I got we can use the join query in the main entity class as well.