Independent DAL Design - Specific and Generic

Question

Scenario: Creating an app for learning purposes and am trying to make it database independent.

I have looked at the beerhouse architecture quite a bit where each database has its own dal an a mixture of SqlCommands and DataReaders etc. I no theres not a definite right or wrong, but generally in terms of maintenance, speed etc baring in mind the architecture side of .net apps is quite new to me, would you lean towards rolling your own specifc dal classes like the beerhouse or use something like the dbProviderFactory where quite a lot of the functionality is already there?

Are there any pros/cons with using the generic classes in System.Data.Common such as DbCommand, DbDataReader, opposed to the specific SqlCommand, SqlDataReader classes etc.

Thanks in advance.

Answer 1

As far as I know, using DbDataReader directly, is faster. But it's not extensible when you want to modify your UI. (e.g. have your Winforms and Webforms projects using the same DAL).

In my point of view if you want more flexibility, you will probably loose performance. The point is you (as the developer) are responsible to balance performance vs. complexity/extensibility. For example suppose you're designing a winforms application for a company. You're sure that the company will not change its data provider (e.g. from SQL Server to Oracle). Then there's no need to design your program to be able to connect to different data providers. As they say: KISS. ( Keep It Simple Stupid! ;p )