Tech Off Thread

10 posts

Creating DB independant DAL

Back to Forum: Tech Off
  • MasterPi

    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

  • rhm

    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.

  • matrixr

    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

  • MasterPi

    Abstract class was what I was thinking of before.

    Are there any other good design patterns I can use?




    mVPstar

  • mertsakarya

    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

  • MasterPi

    Wow. Thanks so much!

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


    mVPstar

  • figuerres

    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.

  • MasterPi

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



    mVPstar

  • figuerres

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



    mVPstar


    do you have beta 2 of Visual STudio 2005 ?




  • MasterPi

    I now do.



    mVPstar

Comments closed

Comments have been closed since this content was published more than 30 days ago, but if you'd like to continue the conversation, please create a new thread in our Forums, or Contact Us and let us know.