How sophisticated should be DAL?

Basically, DAL (Data Access Layer) should provide simple CRUD (Create/Read/Update/Delete) methods but I always have a temptation to create more sophisticated methods in order to minimize database access roundtrips from Business Logic Layer.

What do you think about following extensions to CRUD (most of them are OK I suppose):

1. Read: GetById, GetByName, GetPaged, GetByFilter... e.t.c. methods
2. Create: GetOrCreate methods (model entity is returned from DB or created if not found and returned), Create(lots-of-relations) instead of Create and multiple AssignTo methods calls
3. Update: Merge methods (entities list are updated, created and deleted in one call)
4. Delete: Delete(bool children) - optional children delete, Cleanup methods
5. Install met开发者_运维技巧hods: DAL is responsible for populating empty database with predefined dictionary entities

Where do you usually implement Entity Cache capabilities? DAL or BLL? (My choice is BLL, but I have seen DAL implementations also)

Where is the boundary when you decide: this operation is too specific so I should implement it in Business Logic Layer as DAL multiple calls? I often found insufficient BLL operations that were implemented in dozen database roundtrips because developer was afraid to create a bit more sophisticated DAL.

Thank you in advance!

I think it should be as sophisticated as you need it to be.

Probably the easiest way to manage this is by creating some sort of base class (depending on your framework, can be an abstract class with methods that require an override) that has your most basic CRUD methods, e.g.,

• Find all - return a list
• Find by primary key
• Save (my preference -- create or update. If object is flagged as new then it's a create, otherwise update)
• Delete by object or primary key

Beyond that, subclass for each specific object for stuff such as

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

...but all of these should remain to be purely data access. Any kind of business rule enforcement (you would usually know when you start putting if statements and switch cases) should remain in the Business Layer.

I'm not sure that 'sophisticated' is the right word, it really just depends what you define as "business logic" and ensuring that you keep clean Separation of Concerns.

Read: GetById, GetByName, GetPaged, GetByFilter... e.t.c. methods

Like you, I would term these all a Read, and I think they're fine (GetById in particular). Some of the more obscure ones you have there are probably fine too, as long as they are data centric.

RE Caching: I've done it mainly in the BL, although I don't see any reason why you couldn't do it in the DAL if that was appropriate. If you have a bunch of clients pounding the data repository and the data doesn't change too often - then sure, why not.

RE Boundary: A couple of things come to mind.

• I always abstract out my actual data access provider behind an interface (); if you ensure this interface is clean of dependencies (i.e.: it will work equally well for MS SQL, Oracle, MySQL and NoSQL data sources) then you're on the right track - as soon as you introduce anything that's DAL technology specific into the interface you know you're doing something wrong (if you're in .net generic-ish types like DataTables are fine IMHO).
• When you're developing the BL you should have some idea of the size of the app and likely bottle-necks; therefore, there's no reason you can't be smart about how the BL (and it's DAL) is designed. So, when you come-up against a business task that you know is going to be hammered, return a lot of data, etc I think it's fine to consider designing methods that tackle it in an efficient way. You can design an interface with both 'bulk' and many smaller querys that do the same thing - that way you cover different cases of use. Yes there will be a maintainability hit but this is partially mitigated by giving it some thought / design up-front (a bit like how you need to bake security into an app - and not try and add it in later as an after-thought).

Finally (imporant) - the thing about BL is that although it's often complicated, it's also specific to the app / service you're working on; the data itself can have it's own rules and sometimes those are best enforced at the data level - not in individual apps.