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.

 

[Scott Allen]

Hi Natan:

I haven't looked at MS Pet Shop so I really can't comment there, but
since it was written primarly to win benchmark tests I'd look at it
with a grain of salt, as you have.

Factory and Provider patterns are a solid approach to achieving
support for multiple backends. These are the fancy terms for your
option #2. There are a few working examples to take a look at:

DAAB v3:
http://www.gotdotnet.com/workspaces/workspace.aspx?id=c20d12b0-af52-402b-9b7c-aaeb21d1f431

DNN uses a provider pattern:
http://dotnetnuke.com/

And some theory:

Provider Design Pattern, Part 1 & 2
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnaspnet/html/asp04212004.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnaspnet/html/asp04212004.asp

 

[No One]

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.

I created a data access framework for my current ASP.Net project. It uses XML
files to associate tables with a data object. I also made interfaces for the
web front end. The impl objects tie the two together and each configures its
own database accessor (per the XML files). It when builds the general SQL
code and caches it. The database accessor object only uses the IData...
interfaces. The real trick is getting parameter objects that must be tied to
the actual database engine being used. I created a database gateway interface
that the accessor uses and the impl passes on initialization. From the
gateway object, the accessor gets the connection string, maps DbTypes to the
correct types (in my case SqlTypes) and creates concrete IParameter objects.
So far, the only real drawback is in trying to preserve nulls in the data rows
as many of the .Net object types (IntXX, Datetime, Decimal, etc) do not
support nulls.

 

[Natan]

I created a data access framework for my current ASP.Net project. It uses XML
files to associate tables with a data object. I also made interfaces for the
web front end. The impl objects tie the two together and each configures its
own database accessor (per the XML files). It when builds the general SQL
code and caches it. The database accessor object only uses the IData...
interfaces. The real trick is getting parameter objects that must be tied to
the actual database engine being used. I created a database gateway interface
that the accessor uses and the impl passes on initialization. From the
gateway object, the accessor gets the connection string, maps DbTypes to the
correct types (in my case SqlTypes) and creates concrete IParameter objects.
So far, the only real drawback is in trying to preserve nulls in the data rows
as many of the .Net object types (IntXX, Datetime, Decimal, etc) do not
support nulls.

Thanks for your answer. About the nulls, while we don't have .net 2.0, I
found a nice project in sourceforge that may help solve the problems for
a while:

http://nullabletypes.sourceforge.net/

 

[Natan]

Factory and Provider patterns are a solid approach to achieving
support for multiple backends. These are the fancy terms for your
option #2. There are a few working examples to take a look at:

Thanks. I'll take a look.

 

[Joe Fallon]

I built a DAL for SQL Server and Oracle.
When I was done I looked at the MS DAAB and found them to be very similar.
So you should look at that first.

I just "translated" things like SqlConnection and OracleConnection to
IConnection.
My DAL reads a config file which defines which DB to use - SQL Server or
Oracle.
Then each method branches accordingly and returns the correct concrete
instance:
e.g. SqlConnection when running SQL Server.