Suggestions or comments needed: Abstracting Data Access Layer

[Charles Merighi]

I am editing this post to remove the sample code as I no longer think it would be a good design. After more research, it appears that the Data Access Object Design Pattern is a good solution to separate a Data Access API from the business objects. Example: http://www.tutorialspoint.com/design\_pattern/data\_access\_object\_pattern.htm Comments are welcome. Thanks! Carlos. Original Question: What is the best Design Pattern to separate data access from the business layer?

[Jose Amilcar Casimiro]

Why is it necessary to separate the data layer from the business layer? Are you thinking of changing the data repository? Is there another reason?

[jschell]

Just to counter the other inference in the other response there are good reasons for having a data layer. It helps to start with a data model. A data model represents data in the application and although it will often be one to one with database tables/objects that isn't always the case. Once you have that then your data base layer is created to expose that data model. This is common enough that there are already generic frameworks for it. NHibernate is one and there are others. In my experience it is a bad idea to use inheritance as you have suggested. Data models seldom represent inheritance and implementing it like that even when it seems like such models are there is often a problem. This of course is compounded by a poor design where inheritance is used rather than a better choice of attributes and/or composition. You data layer should not expose anything about the database itself. Often it will be better to have helper classes that do the real work and your exposed classes use those to do the real work.

[Lost User]

Carlos Merighe wrote:

ANY COMMENTS OR SUGGESTIONS?

STOP SHOUTING!

Carlos Merighe wrote:

PLEASE BE SPECIFIC.

STOP POSTING TEXT IN ALL-CAPS! You weren't specific with wich type of comment you expected, so my apologies, I'm grumpy, ignore it if you're not in a good mood. What's the difference between Oracle and SQL? Does Oracle not use SQL? Did you mean "SQL Server" or "MySQL"? Second, you don't want two different implementations to load data from a DataProvider that shares an interface; one can handle both scenario's using a db-agnostic interface (the IDbCommands) - there may be variations in the queries, but the infrastructure would be the same. Any ORM worth it's money would provide the same functionality. ORM's are sweet, they keep code nicely readable. Alternatively, if you want to dive into some examples created from your dataset - take a look at some code-generators like MyGeneration[^]; there's usually multiple templates to create 3-tier applications (and more) - some templates generating complete websites. A benefit is that the code is generated from data that you're (often) familiar with, making it easier to recognize some of the parts (compared to those abstract examples in the books).

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]

[Charles Merighi]

I agree with the comment regarding a shared interface. But inserting large number of records into Oracle works very differently from SQL/Informix. SQL's bulk loader does not share a common interface with Oracle. It is not important to discuss their interfaces here. Besides, that is a design test at this point. Guys, but the business layer is not inheriting from the data implementation, such as the Oracle-specific implementation class. The business object Bonus does not know where the data is coming from. Removing the inheritance would mean that a client would be able to access the Name property for the business object, which makes no sense to me. Example: Bonus obj = new Bonus( new LevelBonus_SQL() ); obj.Name = "some name"; obj.Load(); //It will load from SQL, but the business Bonus object does not know PROBLEM: Remove the inheritance and I can no longer access obj.Name.

[jschell]

Carlos A Merighe wrote:

PROBLEM: Remove the inheritance and I can no longer access obj.Name.

I use the following model when I create my own layers. Following is pseudo code.

class CustomerPrimitive
{
    String name
    String address
    String telephone.
}

class CustomerDb
{
    create(CustomerPrimitive)
    update(CustomerPrimitive)
    delete(Id id)
    List query(CustomerPrimitiveQuery)
}

Keep in mind that the 'Primitive' is a Data Model Object and not necessarily a direct table representation. The 'Primitive' must NOT have any significant functionality and must NOT have and database specific code in it. The database specific code goes in the 'Db' classes. You can add abstractions on top of the above but I haven't found them useful (and I have done it.) The 'Primitive' types should be in their own space (package, namespace, whatever) which is entirely separate from the 'Db' types.

[Lost User]

Ditch the term "Load"; how about "LoadByName" or "LoadById"? Throw an InvalidOperation if a property is set in a non-loaded (but created) object. Personally, I'd have that argument in the constructor, removing the possibility to create an empty class.

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]

[V 0]

Have a look at my article[^]

Carlos Merighe wrote:

it appears that the Data Access Object Design Pattern is a good solution to separate a Data Access API from the business objects.

I would even say it is mandatory. It makes you think in smaller problems, which in turn makes you build simple and easy solutions to those problems. If you pile up everything into one problem/solution you'll end up with a huge mess. Also it enforces re-usability, which leads to robust and stable code.

Carlos Merighe wrote:

What is the best Design Pattern to separate data access from the business layer?

The one that fits you best. I'm not saying that so I can give you an easy answer. There is no "best" solution. Try out different solutions and see what best fits your needs and coding styles. Depending on the technology you can go old school as in my article or go the new way with LINQ or other technologies. Note that if you're in a company you probably want to use their way of working. hope this helps.

V.
(MQOTD rules and previous solutions)

OriginalGriff wrote:

V is absolutely right

[John D Sanders]

You could also use the Unit of Work / Repository pattern for your purposes.

Thanks JD http://www.seitmc.com/seitmcWP