Re: Data Access Layer (DAL): Stored Proceedurs vs. Dynamic SQL

Discussion in 'ASP .Net' started by Gavin Joyce, Jul 5, 2003.

  1. Gavin Joyce

    Gavin Joyce Guest

    Hi,

    I think both methods have their pros and cons. I personally never use dynamic SQL as I like to maintain strict interfaces to the database. If you decide to go with stored procedures, have a look at my tool nTierGen.NET - http://www.nTierGen.NET/. It generates the stored procedures and data access code.

    Thanks.,
    Gavin Joyce

    --
    ___________________________________________________________
    nTierGen.NET Code Generator - http://www.nTierGen.NET/

    Stored Procedures (Get, GetPaged, Insert, Update, Delete)
    Data Access Layer - C#
    Business Rules Layer - C# & VB.NET
    Strongly-Typed DataSets - C#
    Web Services - C#
    ___________________________________________________________
    "Ashish Kanoongo" <> wrote in message news:...
    Helo Friends

    We would like to hear your opinion on what Data Access Layer (DAL) strategy you think is best: Stored Proceedurs vs. Dynamic SQL as Strong Typed Classes. Keep in mind we are more concerned about ease of management vs. performance because the target customer for the portal application is less than 250 total users, 100 or less concurrent users, and on average the common customer will be no more than 50 users total.

    So far, a Strong Typed Classes DAL that creates run-time SQL seems to be catching my fancy, because I can get a code-generation tool that will do the repetative coding of creating the CRUD classes, and it also creates an additional Business Logic Layer with classes that a developer can use without interfering with the DAL. Not to mention I don't have to recode the DAL classes everytime I have a data model change - ORM.NET will do that for me. That tool is www.orm.net. Check it out and let me know what you think.

    My though here is I can make any neccessary data model change, create the DAL objects, and then hand over the VS.NET project to my team to code the neccessary business logic based on a written functional spec. This reduces my development time every time we have a custom implementation for a client.

    I met with the lead developer of ORM.NET and here is what he had to say:

    We think generating the SQL code at run-time is better from a code maintenance perspective than Stored Procedures, and only in certain instances will run-time SQL be less performant than SP's (i.e.Reporting & Analytics). The biggest advantages with run-time SQL is we side step the following long-term maintenance issues:
    a.. Creating dozens and dozens of stored procedures that create a whole other layer of complexity that needs to manage. - source safe, etc
    b.. It is harder catching errors within Stored procedures until run-time which makes unit testing a lot harder.
    c.. To save on the number of stored procedures and round-trips, developers often intermingle important business logic within the stored procedures which compromises the whole point of n-teir design.
    d.. As changes to the database are made you also have to make sure you track down and test those changes within stored procedures as well as your code base.

    So please let me know your opinion and suggestion.

    Ashish Kanoongo
     
    Gavin Joyce, Jul 5, 2003
    #1
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. et
    Replies:
    2
    Views:
    1,956
  2. Wolfgang
    Replies:
    1
    Views:
    1,141
  3. java-john
    Replies:
    0
    Views:
    551
    java-john
    Mar 23, 2006
  4. Ily
    Replies:
    2
    Views:
    360
    Robert Haken [MVP]
    Oct 13, 2006
  5. Dhananjay
    Replies:
    1
    Views:
    1,203
    sloan
    Dec 18, 2006
Loading...

Share This Page