ORM and Data Abstraction Layer

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » ORM and Data Abstraction Layer

ORM and Data Abstraction Layer

Rate Topic

Display Mode

Topic Options

Author

Message

Joseph Smith

Posted Wednesday, August 05, 2009 2:08 PM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, October 16, 2012 9:26 AM
Points: 16, Visits: 77

Hi all,

I work for a company using MS SQL databases exclusively, but we integrate with varying technologies and applications. I am trying to design a data abstraction layer based on ORM to alleviate tightly coupled code development. I was hoping to get some feedback from other DB professionals about likes/dislikes/recommendations for what to use to develop this (i.e. Hibernate, ObJectRelationalBridge, self-developed, etc). Does anyone have any thoughts about it?

Thanks in advance!

Post #765821

Lynn Pettis

Posted Friday, October 30, 2009 10:22 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 12:11 AM
Points: 21,602, Visits: 27,431

There have been several discussions on SSC regarding this. You may want to try searching this site for ORM, NHibernate, Hibernate.

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #811964

Florian Reischl

Posted Saturday, October 31, 2009 3:49 AM

SSCrazy

Group: General Forum Members
Last Login: Sunday, November 04, 2012 12:23 PM
Points: 2,087, Visits: 3,932

Hi

Since I didn't participate the previous discussions, let me share my ideas here. Tongue

Disclaimer: I come from .NET side. However,if you are a Java developer, things should be quite alike.

XYZ.Data.Core Library (abstract DAL)
First I start with a XYZ.Data.Core library which represents an abstract data abstraction layer (DAL).
The core DAL contains a IDataContext interface which works like ISession in (N)Hibernate, DataContext in LINQ2SQL or ObjectContext in Entity Framework (EF).
Second part of the Data.Core library is a IDataContextFactory with one method "GetContext():IDataContext" and a generic implementation of this interface. The generic DataContextFactory works with a IoC (inversion of control) to resolve the specific implementation. This can be done by libraries like Microsoft Unity-Library in .NET or any other IoC library or even by an own reflection implementation.
Last but not least the core library contains a here as defined by Martin Fowler in his great book "Patterns for Enterprise Application Architecture". My idea of the Object-Query is keep it simple. Don't try to realize all possible queries. Simple things like "SELECT xyz FROM myTable" and basic "INNER JOIN" operations are enough. They cover more than 90% of required queries. The rest should be done in custom SQL.

This library can be reused in every project and is completely independent.

XYZ.Domain Library
After that create a XYZ.Domain library which contains your domain model and represents the BLL (business logic layer). The Domain library references the - completely abstract - XYZ.Data.Core library to handle the CRUD operations. It contains all your business logic, micro-services and other components you need to handle your project requirements.

XYZ.Data.Hibernate
Hibernate is just an example. This library represents the real DAL implementation. It references both previous libraries and implements the Data.Core.IDataContext and the Data.Core.IDataContextFactory (which can be resolved by the generic DataContextFactory). If you work with a completely stored procedures based INSERT/UPDATE/DELETE interface, you should use mapping objects for each domain object. If not, the DataContext just works like a proxy of the Hibernate ISession. A helper class transforms the Data.Core.Query to a DAL specific query (e.g. a ICriteria in (N)Hibernate).

Academical business specific DAL
I know, the academical solution says there shouldn't even be any coupling between the BLL and the specific DAL but I work in a Enterprise Application environment and writing hundreds of Data Transfer Objects (DTOs) and their interfaces for hundreds of domain objects appears to be quiet unpractical to me. The academical approach would change the sequence of the domain library and the domain specific DAL. In my opinion a specific DAL will never be reusable for other projects.

Loose coupling between BLL and DAL
The BLL never works with the specific DAL like Data.Hibernate. It uses the generic DataContextFactory of Data.Core library to get a IDataContext and do CRUD operations on the persistence layer. This makes it possible to completely replace the DAL (like Hibernate) with any other persistence ignorant (PI) O/R-Mapper (like EF 2.0 or LINQ2SQL) without any changes within your BLL.

Service Layer
If you want to create a service layer (SL) or not depends on the size of your project and some other requirements like SOA. If you need/want a SOA solution, you need a SL. Indeed, even if you don't want/need SOA, a SL might be useful. It can be used as a abstraction of complex BLLs to orchestrate all required operations for higher level business tasks. The SL should only with the BLL (in my opinion). It should not work with the DAL, if possible, but it must never work with the specific DAL. If you do SOA, you need to populate DTOs to the outside world at this point. In other case you might want to share interfaces which represent the data of your BLL objects or even the BLL objects. (Without SOA, I prefer the interfaces approach, which is an additional lose coupling).

Hope this helps.
Flo

The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems

Post #811992

Mike Bishop

Posted Monday, November 02, 2009 10:54 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 7:00 PM
Points: 26, Visits: 136

Hi. My two cents and the same disclaimer as above (I am SQL Server / .NET / C# guy)
Recently had a disappointing experience as a SQL DBA in a Hibernate shop (yes, the Java Hibernate, not the NHibernate). The problems stem from a lack of understanding of exactly what Hibernate can do for you. Even though the Bauer and King book tell people not too, Java folks still insist they can "design the database" just by using Hibernate (i.e., hbm2ddl).

If this is your situation, you should be aware of the problems with the standard SQL Server dialect that Red Hat ships with Hibernate. The dialect is really extended from a base used by both SQL Server and Sybase. It drove me crazy why Java booleans were being translated to tinyint. Well, if you look at the java class, AbstractTransactSQLDialect, you'll quickly see why. You can also see why "text" data types are still be translated in deprecated SQL Server syntax.

HTH

Post #812486

« Prev Topic | Next Topic »

Permissions