CODEWITHSUNDEEP
c#architecturedata-access-layer
aron
aron

Reputation: 1904

Suggestions for pluggable database layer

I am currently working on a software project which uses SQL Server database to store data. Since there are some plans to move away from SQL Server to Oracle, one of the requirements is to build pluggable database layer.

So my question is what is the best way to do it?

Upvotes: 3

Views: 747

Answers (4)

alexn
alexn

Reputation: 58952

I would recommend using NHibernate for your data layer. With it, you can easily swap out the configuration to work with almost any database driver you want.

NHibernate has good support for both MsSQL and Oracle.

You can write all your queries in Hibernates query language (HQL) which will be dialect agnostic. Another option is to use the linq provider in NHibernate 3 to get strongly typed data access.

As some other have mentioned, i would also recommend using the repository pattern and inject a Unit of Work or a SessionFactory.

Edit: Oracle now has released a beta of their Entity Framework provider: http://thedatafarm.com/blog/data-access/oracle-entity-framework-beta-released-today/

1: http://nhforge.org/Default.aspx## Heading ##

Upvotes: 1

Ken Smith
Ken Smith

Reputation: 20445

You have lots of choices. One option is to go with one of the various Object Relational Mapper (ORM) frameworks out there. NHibernate is a popular one, but Microsoft's Entity Framework (in v4) is a reasonable possibility as well, and it integrates better with Linq, if that's the sort of thing you're interested in.

A second option (not necessarily exclusive of the above) is to implement something like the Repository pattern, and run all database access through a repository layer. Some folks see the ORM frameworks as a replacement for the Repository pattern; other see a repository layer adding value on top of the ORM framework. The real value that a repository gives you is the ability to swap out ORM layers. Only you know whether that's a reasonable likelihood, and even if it is, it may be more work to implement the additional repository level than to just re-bind everything to the new ORM.

Upvotes: 1

Davita
Davita

Reputation: 9114

If you want to go ORM way, you can use NHibernate as alexn suggested, or Telerik's OpenAccess ORM. There is also EF provider for oracle and other DBMSes, but they are not free.

If you want to build your whole DAL from scratch, than go with Repository pattern. Create an interface for repository and create each repository provider for each db. Here's a discussion about.

Upvotes: 0

Shawn Mclean
Shawn Mclean

Reputation: 57469

I suggest using Entity Framework, quite easier than NHibernate and being matured very fast. For oracle support in EF, you'll need the oracle provider.

Along with using Entity framework, I suggest using a pattern such as Repository Pattern. This way, you can use Dependency Injection to change the implementation of your choice. Your application becomes independent of database or the ORM itself. So you could also use NHibernate if you wish.

Upvotes: 1

Related Questions