4

I have a question about whether we should use different Data access layers for different database providers like SQL Server, MySQL Server, Oracle etc..

Is it not better to use a single data access layer with the ORM or like mappings so that the database specific activities are taken care of with less coding than complete duplication of code across different data access layers.

Give me your suggestions along with references to use in case of C# programming language in .NET platform.

Saravanan
  • 133
  • 7

2 Answers2

3

Well saravanan, it seems that you already have the answer! YES, it would be wrong to use different DAL implementations if they all execute the same logic (and this only just for a broader DB engine support)! As you said, the "Don't repeat yourself" Design Principle speaks against it. And according to the "Separation of Concerns" Design Principle, it would also be wrong to mix your concerns. Your query logic is one concern and your DB engine access method/SQL dialect another. So YES, it is actually the job of ORMs to speak to the underlying DB engine and it's specific SQL dialect. In case of NHibernate as your ORM the change of the underlying DB engine would be just a few lines of configuration (for your SessionFactory object). You could simply specify your DB engine configuration during each deployment as you need it. Visit http://knol.google.com/k/nhibernate-chapter-3-isessionfactory-configuration for more details on NHibernate. It also shows the different dialects NHibernate supports. I hope this helps.

amiir
  • 31
  • 1
  • how about the separation of the query logic concern when MySQL has a different way for generating unique ids and the MS SQL version has a different ones. Also MS SQL Server supports Hashed Joins, where as MySQL does not. do we have to stick to the traditional SQL alone. Will there be any thing like NHibernate that internally takes care of these. – Saravanan Jul 14 '11 at 08:19
  • you have given points related to `DRY` and others, we do have real practical implementation and sql compatibility issues... – Saravanan Jul 14 '11 at 08:23
1

ok, one example is Entity Framework with Oracle - EF generates invalid SQL as far as any non-SQLServer DB is concerned (Microsoft extended sql with a new statement - Cross Apply). So in this case, using the EF ORM with Oracle or MySQL can be a disastrous choice.

Usually people like DB-independant client libraries, the cost is some performance and occasionaly data type related issues (eg a date can be represented differently for different DBs), occasionally you get some limitations in different DBs features (eg "select top 10 x from y" doesn't work with Oracle but does with SQLServer, you have to "select x from y where rowid < 10" instead. Usually this is allowed for in the client library but sometimes they won't offer that feature at all for either DB).

I think the only times you really want a DB-specific provider now is when performance is very important, especially so you don't have to worry about what the client library does with memory. Most DB accesses are slow, but re-allocating huge chunks of memory is even slower. The other aspect is if you need specialist features that are provided by a single DB, in this case you're more likely to need the providers library.

Community
  • 1
gbjbaanb
  • 48,354
  • 6
  • 102
  • 172
  • i also have confronted the situations that you have mentioned and have separate access layers for different database engines. however when i think about my application's scalability, i am skeptical on data access. Can't we get a workaround for this? – Saravanan Jul 14 '11 at 08:22