How complex should a DAL be?

Question

In principle, the DAL (data access level) should provide simple CRUD methods (Create / Read / Update / Delete), but I am always tempted to create more complex methods to minimize the ability to access databases from business logic.

What do you think of the subsequent CRUD extensions (most of them are fine, I suppose):

• Read: GetById, GetByName, GetPaged, GetByFilter ... etc Methods
• Create: GetOrCreate (the model object is returned from the database or created if it is not found and not returned), Create (lots-of-relations) instead of Create and several AssignTo methods call
• Update: Combine methods (the list of entities is updated, created and deleted in one call)
• Delete: Delete (bool children) - delete child elements, Clear methods
• Installation Methods: DAL is responsible for populating an empty database with predefined dictionary entities

Where do you usually use object caching capabilities? DAL or BLL? (My choice is BLL, but I also saw DAL implementations)

Where is the border when you decide: this operation is too specific, so I have to implement it in Business Logic Layer as multiple DAL calls? I often found insufficient BLL operations that were implemented in dozens of database calls because the developer was afraid to create a slightly more complex DAL.

Thank you in advance!

Answer 1

I think it should be as difficult as you need.

Probably the easiest way to manage this is to create some kind of base class (depending on your structure, it may be an abstract class with methods requiring overriding) that has your most basic CRUD methods, like

• Find All - Return List
• Primary Key Search
• Save (my preference is to create or update. If the object is marked as new, then it creates, and therefore update)
• Delete object or primary key

In addition, a subclass for each specific object for things like

• Find by name
• Delete by date
• etc .. and others.

... but they should all remain pure data access. Any rule of business rules (you, as a rule, know when you start to put, if statements and cases of switching) should remain in a business layer.

Answer 2

I'm not sure that “complex” is the right word; it really depends on what you define as “business logic,” and ensuring that you keep the separation of problems clean.

Read: GetById, GetByName, GetPaged, GetByFilter ... etc Methods

Like you, I would call it all read, and I think they are fine (GetById in particular). Some of the more obscure that you have are probably fine too if they are data oriented.

RE Caching: I did this mainly in BL, although I see no reason why you could not do this in DAL if that was appropriate . If you have a bunch of clients beating up the data repository, and the data doesn't change too often - then of course, why not.

RE Border: A few things come to mind.

• I always abstract my actual data access provider behind the interface (); if you make sure that this interface is clean of dependencies (that is: it will work equally well for MS SQL, Oracle, MySQL and NoSQL data sources), then you are on the right track - as soon as you imagine something that DAL technology is specific for an interface that, as you know, you are doing something wrong (if you are in the .net generic-ish types, such as DataTables, are great IMHO).
• When you develop BL, you should have some idea of ​​the size of the application and probably the envelope of the bottle; therefore there is no reason why you cannot be smart about how BL (and DAL) is designed. Thus, when you are faced with a business task that, as you know, will be clogged, return a lot of data, etc. I think it’s good to consider developing methods that will solve it in an effective way. You can design an interface with both bulk and a few smaller queries that do the same - so you cover various use cases. Yes, there will be luck related to maintainability, but this is partially mitigated if you take into account some thoughts / design upward (a bit like how you need to fix security in the application - and not try to add it later, as the next one).

Finally ( imporant ) - the thing about BL is that although it is often complex, it is also specific to the application / service you are working on; the data itself can have its own rules, and sometimes they are best applied at the data level - not in individual applications.