Advice on Data Layer

[Natan]

I want to create an n-tier app and I would like an advice of you that
know more than me about this.

I want my app to support multiple databases in the way that when a
client wants to use Oracle and not SQL server, I can easily port the
data layer without touching the rest of the code.

So, I have 2 ideas.

1: Take the way MS Pet Shop did (I read a LOT in many sites how this
architecture is flawed) in creating an IDAL where i declare DAL
interfaces to all my objects and implement them in the real DAL (SqlDAL,
OracleDAL, etc).

2: Create abstract classes that implement common code to all the DALs
(like some selects are really equal in any database) and override them
in the real implementation providing the code that is different.

Both of them include am Data Access Interface to provide common way to
access all the data from a single point, and a Factory to choose which
DAL I should use.

I would like to know from you what is best in your opinion, even other
idea that i didn't write. I don't know if my references are the best ones.

Other thing that I am in doubt is that way MS pet shop uses to
instantiate classes. Is there any performance issues using
Assembly.CreateInstance(string)? I'm afraid that when using lots of
requests per second, this could affect the application.

Thanks in advance.

[Michel van den Berg]

I am using LLBLGen Pro (http://www.llblgen.com) myself: you can generate
your DAL for multiple databases. By using e.g. the facade pattern or
another layer on top of the two generated DALs you can create the wanted
effect.

Natan wrote:

I want to create an n-tier app and I would like an advice of you that
know more than me about this.

I want my app to support multiple databases in the way that when a
client wants to use Oracle and not SQL server, I can easily port the
data layer without touching the rest of the code.

So, I have 2 ideas.

1: Take the way MS Pet Shop did (I read a LOT in many sites how this
architecture is flawed) in creating an IDAL where i declare DAL
interfaces to all my objects and implement them in the real DAL (SqlDAL,
OracleDAL, etc).

2: Create abstract classes that implement common code to all the DALs
(like some selects are really equal in any database) and override them
in the real implementation providing the code that is different.

Both of them include am Data Access Interface to provide common way to
access all the data from a single point, and a Factory to choose which
DAL I should use.

I would like to know from you what is best in your opinion, even other
idea that i didn't write. I don't know if my references are the best ones.

Other thing that I am in doubt is that way MS pet shop uses to
instantiate classes. Is there any performance issues using
Assembly.CreateInstance(string)? I'm afraid that when using lots of
requests per second, this could affect the application.

Thanks in advance.