Data abstraction layer, is there something around?

[S. Lorétan]

Hi guys,

I'm coding an application connected to a database. I have some clients that
use this program with 2 or 3 computers, and I use MsSQL Express for them.
But if the client needs more computers to be connected to the database, I
have to use a standard MsSQL. No problem with that, but I want to be able to
switch from a database provider to another in an easy way.

I know that this way to do is called "Data(base) abstraction layer", but I
don't know how to use it. I've read some articles around the Internet, like
this one http://www.codeproject.com/cs/database/dalgen.asp, but I don't want
to have to regenerate source code each time I change the database. If it is
possible, I would like to use SQL to do my queries, and I have to be able to
do "complex" queries (joining, subselecting, ...).

The databases I would like to be able to use are MsSQL (Express or not),
MySQL, and if possible Oracle.

Is there something to do this in the Framework? Or maybe some open source
library?

Thank you for your help.

 

[Chris Fulstow]

Check out the Data Access Application Block from the Microsoft
Enterprise Library:
http://msdn.microsoft.com/library/en-us/dnpag2/html/entlibjan2006_dataaccessappblock.asp

 

[sloan]

First, its important to understand tiered development.
This will make more simple for you.

Check this:
http://sholliday.spaces.live.com/?_...ogview&_c=blogpart&partqs=amonth=5&ayear=2006

As the previous poster pointed out...............Check into the Enterprise
Library data access application block. (I'll abbreviate this as ELDAB for
this post)

One aspect of the ELDAB is that it allows a "common syntax" to whichever
database you're talking.
Access
Excel
Sql Server
Oracle

And you're able to switch the database.........in a dataConfiguration.config
file (1.1, 2.0 is the same concept but a little different)

............
Back to the tiers:
If you're datalayer object only returns
DataSet's
IDataReader's
Scalars
Void's/Nothing's

The the backend db doesn't become as big an issue.


.........

Now , sometimes the ELDAB is not sufficient.

The next thing you could do is create Interface(s) for the DataAccess Layer.

Lets call one
IEmployeeDataLayerObject
pubic DataSet GetInfo(int empid)
public void UpdateInfo(int empid, string fname, string lname)


You create 2 concrete versions of IEmployeeData
EmployeeDataForAccess : IEmployeeDataLayerObject
and
EmployeeDataForOracle : IEmployeeDataLayerObject

You create a simple Factory class, which returns 1 of the 2 concrete
objects.


See
12/1/2005
Understanding the Simple Factory Pattern
http://sholliday.spaces.live.com/?_...gview&_c=blogpart&partqs=amonth=12&ayear=2005

and look at the "reflection" example.


Normally, you don't have to go to the extreme of doing this last step.
However, I send alot of xml into my datalayers.

IEmployeeDataLayerObject
public void UpdateInfo(XmlDocument xmlDoc)

In this case, ........... I can actually send the raw xml into Sql
Server.....because it has an OPENXML command.
For Access, there is no internal Xml functionality, so I have .. create a
transaction in code, loop over the xml, and run several statements against
the Access database.
For Oracle, I've played with their xml functionality, and as far as 9.xxxx,
I was disappointed in it.

In this case, the ELDAB is deficient. But I can get around it with the
interfaces for the IEmployeeDataLayerObject, several concrete classes, and
using the "Simple Factory" design pattern with reflection (or "key" method)

This should help.

But first off, you need to get the Tiers correct, or you're just
complicating your life too much.

 

[Frans Bouma [C# MVP]]

Hi guys,

I'm coding an application connected to a database. I have some
clients that use this program with 2 or 3 computers, and I use MsSQL
Express for them. But if the client needs more computers to be
connected to the database, I have to use a standard MsSQL. No problem
with that, but I want to be able to switch from a database provider
to another in an easy way.

I know that this way to do is called "Data(base) abstraction layer",
but I don't know how to use it. I've read some articles around the
Internet, like this one
http://www.codeproject.com/cs/database/dalgen.asp, but I don't want
to have to regenerate source code each time I change the database. If
it is possible, I would like to use SQL to do my queries, and I have
to be able to do "complex" queries (joining, subselecting, ...).

The databases I would like to be able to use are MsSQL (Express or
not), MySQL, and if possible Oracle.

Is there something to do this in the Framework? Or maybe some open
source library?

If you're looking for a library which allows you to write SQL AND use
all kinds of different databases, you're out of luck: there's no SQL
standard which is implemented by all databases, or you've to fall back
on SQL-92, and even then it's not always implemented in all details in
all db's.

So, you have to make a choice:
- use stored procedures in all the databases you're accessing and call
them using a somewhat unified api. This is maintenance intensive but
could work
- use an O/R mapper. An O/R mapper offers you the ability to work with
multiple databases and not to worry about the different SQL dialects
and db specifics, you just write code against the o/r mapper api and
leave the sql generation over to the o/r mapper.

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------