An N-tier database application without using ORM, how does the user interface determine what it needs to display the data?

Question

I am looking for pointers and information here, I will do this CW, as I suspect that it does not have a single correct answer. This is for C #, so I will make some links to Linq below. I also apologize for the long post. Let me summarize this question here, and then the full question follows.

Summary: In the four-layer UI / BLL / DAL / DB application, how can I change the user interface, show more columns (say, in the grid), avoid leakage through the business logic level and data access layer to get the data to be displayed (suppose that it’s already in the database).

---

Suppose a layered application with 3 (4) layers:

• User Interface (UI)
• Business Logic Level (BLL)
• Data Access Level (DAL)
• Database (DB, Level 4)

In this case, the DAL is responsible for building the SQL statements and executing them against the database, returning the data.

Is the only way to "build" such a layer to always do "select *"? For me, this is a big no-no, but let me explain why I'm interested.

Let's say that I want my user interface to display all employees who have an active employment record. By “active,” I mean that busy-date entries contain today (or perhaps even a date that I can set in the user interface).

In this case, let's say I want to send an email to all of these people, so I have a code in BLL that ensures that I have not sent an email to the same people, etc.

BLL requires a minimum amount of data. Perhaps he calls the data access level to get a list of active employees, and then call to get a list of emails sent to them. Then he joins them and builds a new list. Perhaps this can be done using the data access layer, this is not important.

What is important for the business level, there really is little data that it needs. Perhaps he just needs a unique identifier for each employee, for both lists, to match, and then say, "These are unique identifiers of active ones that you have not yet emailed." Then I create DAL code that builds SQL statements that retrieve only what the business layer requires? I.e. just "SELECT id FROM employees WHERE ..."?

What should I do then for the user interface? It would be better for the user to include much more information, depending on why I want to send emails. For example, I could include some rudimentary contact information or the department in which they work, or the name of their managers, etc., Not to mention the fact that I, at least, must specify the name and email address.

How does the user interface get this data? Am I changing the DAL to make sure I get some data back to the user interface? Can I change the BLL to make sure it returns enough data for the user interface? If the object or data structures returned from the DAL back to the BLL can also be sent to the user interface, the BLL may not need to be significantly changed, but then the user interface requirements affect a level that exceeds the level with which it should interact, AND if two worlds work on different data structures, the changes are likely to be made for both.

And what then, when the user interface will be changed to help the user even more by adding more columns, how much would I need / need to change the user interface? (assuming that the data is already present in the database, so no changes are required there).

One of the suggestions that has appeared is to use Linq-To-SQL and IQueryable, so if DAL, which deals with what (as in which data types) and why (as in WHERE sentences) returned IQueryables, BLL can potentially return them to the user interface, which can then build a Linq query that will retrieve the data it needs. The user interface code can then insert the desired columns. This will work because with IQuerables the user interface will eventually execute the query, and then it will be able to use "select new {X, Y, Z}" to indicate what it needs, and even join other tables if necessary.

It looks messy for me. That the user interface executes the SQL code itself, although it is hidden behind the Linq interface.

But for this, the BLL or DAL should not close the database connections, and in a world like IoC, the DAL service can be deleted a little earlier than the user interface code, so the Linq request may simply end with the exception “Unable to access the remote object”.

So I'm looking for pointers. How far are we? How do you deal with this? I believe that changes in the user interface will flow through BLL and in DAL is a very bad decision, but now it does not look like we can do better.

Please tell me how stupid we are and how wrong I am?

And note that this is an outdated system. Changing the database schema has not existed for many years, so the decision to use ORM objects that essentially fulfill the "select *" equivalent is not really an option. We have some large tables that we would like to avoid by dragging the entire list of layers.

Answer 1

Use the concept of a presentation model (or data transfer objects), which are examples of user interface consumption. The BLL's task is to execute these objects, and if the data is incomplete, request additional data (which we call the model). Then BLL can make the right decisions about which model models to return. Do not let your model specification (data) penetrate the user interface.

UI <-- (viewmodel) ---> BLL <-- (model) --> Peristence/Data layers 

This decoupling allows you to better scale the application. Independence of persistence, which, it seems to me, naturally falls outside this approach, since the design and specification of models of views can be performed flexibly in BLL using linq2ql or other orm technology.

Answer 2

This is not a problem at all. I have seen many attempts (including the IQueryable approach I described), but none of them are perfect. Unfortunately, we are still waiting for the perfect solution. Until then, we will need to deal with imperfection.

I completely agree that DAL problems should not be passed to the upper layers, therefore, an isolating BLL is needed.

Even if you don’t have the luxury to redefine data access technology in your current project, it still helps to think about the domain model in terms of maintaining non-existence. Persistence Notorance Corporation is that every domain object is a standalone unit that has no idea about database columns. It is best to integrate the data as invariants in such objects, but this also means that the instance of the domain object will have all its composite data. This is either an offer, so the key is to find a good domain model, which ensures that each domain object will contain (and must be loaded) the "corresponding" amount of data.

Too granular objects can lead to frequent DAL interfaces, but too coarse objects can load too much non-local data.

A very important exercise is the analysis and the correct model of aggregates of domain models so that they correspond to the correct balance. The Domain-Driven Design book provides a very vivid analysis of aggregate modeling.

Another strategy that may be useful in this regard is to strive to make the most of the Hollywood principle. The main problem that you describe is related to queries, but if you can switch focus to a more team-oriented one, you can define some coarser interfaces that don't require you to always load too much data.

I do not know a simple solution to this problem. There are methods such as those described above that can help you solve some problems, but ultimately it is an art that uses experience, skill and discipline.