Creating DB independant DAL

Hello Niners,

What's the best way to create a Database independent data abstraction layer?  I don't want to rely on stored procedures as I would want to be able to use MS Access.

Help is much appreciated. Thanks!


mVPstar

Using stored procedures is the one thing you need to avoid when trying to be DB indepedent because they're the only thing that differs appreciably between DBs.

There are other differences like cursor models, but since ADO.NET massively dumbed-down the whole concept of cursors to a lowest common denominator forward only model that difference went away.

The only other thing to worry about is obscure data-types that may not be present in all DBs.

you can use an abstract class.

public abstract class DALBase
{
public abstract int CreateSomething(Something s);
public abstract int CreateAnother(Thing t);
...etc
}

then you inherit from DALBase, so for Sql
public class Sql : DALBase
{
//define your stored procs here maybe?
public override int CreateSomething(Something s) { ...use stored procs... }
public override int CreateAnother(Thing t) { ...do your sql stuff here... }
}

public class Access : DALBase
{
//use t-sql
...
}

hope that makes sense

Abstract class was what I was thinking of before.

Are there any other good design patterns I can use?




mVPstar

I recommend checking out Enterprise Library 1.0 from Microsoft. Just search for "Enterprise Library" in MSDN, you'll find the download link.

It contains a Data Acccess Application Block, which is DB independent. And I think it contains a class named "CommandWrapper" for creating DB independent SP structure.

Regards,
Mert Sakarya
mertsakarya@hotmail.com

Wow. Thanks so much!

The other 6 application blocks will be very useful as well.


mVPstar

If your starting to look at .net 2.0
-- I am starting to switch to it now

then look at how 2.0 lets you bind to objects.


so you have a client useing objects with methods that do the CRUD at the object level.

then you can attach that to a set of data classes that deal with the back end store.

I have done a few tests and it seems that this will work very well ....

the app only sees the objects.

Can you please elaborate more on what you mean? I'm still much of a beginner.



mVPstar

mVPstar wrote:

Can you please elaborate more on what you mean? I'm still much of a beginner.



mVPstar

I now do.



mVPstar