Data Access Layer for any Database

Data Access Layer for any Database

Old forum URL: forums.lhotka.net/forums/t/6684.aspx


kam_csla posted on Thursday, March 26, 2009

Hi,

i use asp.net 2.0, csla.net framework and oracle database.

just wondering, what will be the changes if the application should work irrespective of the type of database.

It's should be like aspx-->aspx.cs-->class1.cs-->DataAccessLayer--> any database (oracle, sql server, ms access etc)

Thanks,

Kam.

rfcdejong replied on Thursday, March 26, 2009

Use an ORM ? :)

There are differend ORM's on the market, just go and google for it.

- NHibernate seems to be populair and it's free.
- I personaly like Opf3, but then u've to pay a bit.

General rule:
Don't use oracle packages or any intellegent code in mssqlserver stored procs if u want to be able to switch database vendor.

kam_csla replied on Thursday, March 26, 2009

Thanks for the info. But  we can't just go for a different frame work.  Just wanted to know about the changes, that i need to make in the data access layer to handle different databases.

rsbaker0 replied on Thursday, March 26, 2009

There are many differences in the SQL dialects understood by various backend databases.

They differ in what operators they support, join syntax, procedure call syntax, delimiters used for field names, etc.

Heck, Microsoft SQL Server and SQL Server Compact Edition even use a different format for the TOP predicate -- one is TOP nnn and the other is TOP(nnn), so there can be variations even from the same vendor.

This is the motivation to use an ORM (which can be used as just a DAL under CSLA) -- someone else has gone to the trouble to provide a mechanism that offers some independence from the idiosyncrasies of the back-end database.

You can of course do all this in your own DAL, but you're duplicating something that has already been done.

 

 

RockfordLhotka replied on Thursday, March 26, 2009

I discuss some solutions to this (at a design level) in Chapter 18 of the new 2008 book.

If you use the ObjectFactory model, you can just create a different set of factory objects for each type of database.

If you have your DataPortal_XYZ methods invoke an external DAL assembly, you can make that pluggable (using dynamic type loading and interfaces or reflection) and have a different DAL for each type of database.

Or you can use something like an ORM, where the data framework has done the work of abstracting the different types of database.

JoeFallon1 replied on Friday, March 27, 2009

If you design your BOs to use the ADO.Net interfaces (like IDataReader, etc.) then the impact on your application is zero when you switch database vendors. Naturally your DAL must return the correct type of IDatareader depending on some configuration setting: SQL Server or Oracle for example.

I used to support both and finally got rid of Oracle support but my app still supports it because I still use the interfaces.

As of version 8, I think, Oracle SQL syntax was extremely close to SQL Server. For any differences in syntax I just branched my code to produce the correct SQL based on the same config setting.

This technique worked just fine for years. We had some major clients using Oracle databases with our product. We convinced them to move to SQL Server so we could lower our testing costs. They wanted to free up Oracle licenses for other projects so it was a win-win.

Joe

Joe

Copyright (c) Marimer LLC