Limiting T-SQL code as a .NET development standard

Discussion in 'ASP .Net' started by wildman@noclient.net, Jan 30, 2008.

  1. Guest

    I just switched jobs joining my third .NET shop.

    My last shop was full of Database developers who built almost all
    business logic into the databases. Most projects ended up with 100s of
    Stored Procedures, Views, function. In some cases cursors were used,
    etc.

    My new shop is looking to implement a Development standard that says,
    if and when possible, do not build code in the database. Instead
    code .NET Business/Data classes that spit out collections and have SQL
    code in the class.

    Any opinions on the difference, where to draw the line and what might
    advantages/disadvantages?

    Thanks in advance for any help or information.
    , Jan 30, 2008
    #1
    1. Advertising

  2. rote Guest

    I'll go for:-
    code .NET Business/Data classes that spit out collections and have SQL
    code in the class.
    Thats just my thoughts..
    why have 100s store procs(the problem is maintainence) etc..its for DBA's
    who don't want to think in the OOP direction.


    <> wrote in message
    news:...
    >I just switched jobs joining my third .NET shop.
    >
    > My last shop was full of Database developers who built almost all
    > business logic into the databases. Most projects ended up with 100s of
    > Stored Procedures, Views, function. In some cases cursors were used,
    > etc.
    >
    > My new shop is looking to implement a Development standard that says,
    > if and when possible, do not build code in the database. Instead
    > code .NET Business/Data classes that spit out collections and have SQL
    > code in the class.
    >
    > Any opinions on the difference, where to draw the line and what might
    > advantages/disadvantages?
    >
    > Thanks in advance for any help or information.
    >
    >
    >
    rote, Jan 30, 2008
    #2
    1. Advertising

  3. Don't neglect stored procedures. The advantages are many and they are
    significant. Just some of them:

    They are re-usable from different application platforms.
    They are easier to deploy if you need to make a change.
    In most cases (not in all though) they provide better performance for data
    access operations.

    --
    Eliyahu Goldin,
    Software Developer
    Microsoft MVP [ASP.NET]
    http://msmvps.com/blogs/egoldin
    http://usableasp.net


    <> wrote in message
    news:...
    >I just switched jobs joining my third .NET shop.
    >
    > My last shop was full of Database developers who built almost all
    > business logic into the databases. Most projects ended up with 100s of
    > Stored Procedures, Views, function. In some cases cursors were used,
    > etc.
    >
    > My new shop is looking to implement a Development standard that says,
    > if and when possible, do not build code in the database. Instead
    > code .NET Business/Data classes that spit out collections and have SQL
    > code in the class.
    >
    > Any opinions on the difference, where to draw the line and what might
    > advantages/disadvantages?
    >
    > Thanks in advance for any help or information.
    >
    >
    >
    Eliyahu Goldin, Jan 30, 2008
    #3
  4. Damien Guest

    On Jan 30, 3:23 am, wrote:
    > I just switched jobs joining my third .NET shop.
    >
    > My last shop was full of Database developers who built almost all
    > business logic into the databases. Most projects ended up with 100s of
    > Stored Procedures, Views, function. In some cases cursors were used,
    > etc.
    >
    > My new shop is looking to implement a Development standard that says,
    > if and when possible, do not build code in the database. Instead
    > code .NET Business/Data classes that spit out collections and have SQL
    > code in the class.
    >
    > Any opinions on the difference, where to draw the line and what might
    > advantages/disadvantages?
    >
    > Thanks in advance for any help or information.


    Take it to the logical extreme - to have the minimum amount of SQL
    anywhere, just create one routine that does a "select *" against any
    table in the database. Then do all of the joins, filtering,
    aggregation, etc in the .Net code. That'll really fly when your
    application needs 1 row from 50 million.

    If you have a database that needs 100s of stored procedures, write
    100s of stored procedures. If you have a database that only really
    needs 10, or 50, then write just those. There's no right number.

    What you should be doing in any circumstance is using the right tool
    for the job. Cursors are rarely the right tool, but that doesn't mean
    never. And although T-SQL has some limitations, it also has
    considerable strengths. You tell SQL Server what data you want, and it
    figures out the optimal way to get that data.

    Sometimes the optimal way still isn't fast enough, and that, IMO, is
    the point when you get the DBAs involved - "can you change anything to
    make this work faster?" - which may lead to them re-writing your
    procedure, or to them just changing some indexes on the server, or
    introducing partitioning, etc. All of that depends on how big your
    shop is and how it's split between devs/DBAs (and how much access devs
    have to boxes where they can do the SQL work - i.e. Dev servers)

    The advantage of putting code in the database becomes apparent as soon
    as another application starts dealing with the database. If the code's
    in the database, then you don't have to worry about it doing things
    differently when it's making changes. And just because you don't write
    a second application, it doesn't mean your customers won't want to.
    For instance, where I work, we write a large amount of our software
    ourselves. But one of our central systems is from a third party
    supplier. Our website can process many types of updates and write them
    into this 3rd party database. How I wish there were even foreign keys,
    let alone well written stored procs with good validation, to stop *me*
    from doing something dumb.

    Damien
    Damien, Jan 30, 2008
    #4
  5. sloan Guest

    http://www.codeproject.com/KB/architecture/DudeWheresMyBusinessLogic.aspx


    You should always ask the people who are doing the suggesting...

    "Have you (actually) done it BOTH WAYS?"

    I find (my personal experience) that people who want all the logic inside
    tsql have never really done a middle tier project, or a large ..must scale
    ...application. This is my personal and limited experience.....

    Having grown up in a TSQL IS the business logic system ...and now in a "Get
    it out of the Database" system...

    I believe in the middle tiered approach. You break the rules
    ~sometimes~...but very seldom.

    Let the db do what a db does well.
    CRUD operations.

    Once you learn how to do set-based CRUD operations in tsql..you don't look
    back.

    Row by Row, Cursors...they're so 1990's.

    I sometimes call (in front of other developers) the DataSet.GetXml that I
    ship off to TSQL land
    _perfectXml
    because I want to get across the point... that at that moment in time..all
    the data is already processed, and its just CRUD after that.

    Good luck.
    My opinion is 1 among many. But I'll remind you again...make sure the
    people giving you the advice have done it both ways.


    What do I mean by bulk crud operations? And using .GetXml()?

    See
    http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/268be0e152d8b876

    and even
    http://www.sqlservercentral.com/articles/Stored Procedures/thezerotonparameterproblem/2283/



    <> wrote in message
    news:...
    >I just switched jobs joining my third .NET shop.
    >
    > My last shop was full of Database developers who built almost all
    > business logic into the databases. Most projects ended up with 100s of
    > Stored Procedures, Views, function. In some cases cursors were used,
    > etc.
    >
    > My new shop is looking to implement a Development standard that says,
    > if and when possible, do not build code in the database. Instead
    > code .NET Business/Data classes that spit out collections and have SQL
    > code in the class.
    >
    > Any opinions on the difference, where to draw the line and what might
    > advantages/disadvantages?
    >
    > Thanks in advance for any help or information.
    >
    >
    >
    sloan, Jan 30, 2008
    #5
  6. gnewsgroup Guest

    On Jan 29, 10:23 pm, wrote:
    > I just switched jobs joining my third .NET shop.
    >
    > My last shop was full of Database developers who built almost all
    > business logic into the databases. Most projects ended up with 100s of
    > Stored Procedures, Views, function. In some cases cursors were used,
    > etc.
    >
    > My new shop is looking to implement a Development standard that says,
    > if and when possible, do not build code in the database. Instead
    > code .NET Business/Data classes that spit out collections and have SQL
    > code in the class.
    >
    > Any opinions on the difference, where to draw the line and what might
    > advantages/disadvantages?
    >
    > Thanks in advance for any help or information.


    Isn't it preferred to *not* to have extensive amount of raw sql
    queries in the classes? On one hand, it is hard to construct (at
    least error-prone), and on the other hand, it isn't as secure/
    efficient as stored procedures. I am working on a small web
    application, and I already have 100 stored procedures in the database,
    because I don't use raw sql's in my code.
    gnewsgroup, Jan 30, 2008
    #6

  7. >> I just switched jobs joining my third .NET shop.
    >>
    >> My last shop was full of Database developers who built almost all
    >> business logic into the databases. Most projects ended up with 100s of
    >> Stored Procedures, Views, function. In some cases cursors were used,
    >> etc.
    >>
    >> My new shop is looking to implement a Development standard that says,
    >> if and when possible, do not build code in the database. Instead
    >> code .NET Business/Data classes that spit out collections and have SQL
    >> code in the class.
    >>
    >> Any opinions on the difference, where to draw the line and what might
    >> advantages/disadvantages?
    >>
    >> Thanks in advance for any help or information.

    >
    >Isn't it preferred to *not* to have extensive amount of raw sql
    >queries in the classes? On one hand, it is hard to construct (at
    >least error-prone), and on the other hand, it isn't as secure/
    >efficient as stored procedures. I am working on a small web
    >application, and I already have 100 stored procedures in the database,
    >because I don't use raw sql's in my code.


    It's preferred to not have *any* SQL in your *business classes*. That's what
    the DAL is for. Choosing to implement your DAL as stored procs defeats much
    of the purpose of having a DAL, IMO.

    It's false to say that using parameterized queries is not as
    secure/efficient as stored procedures.
    Scott Roberts, Jan 30, 2008
    #7
  8. gnewsgroup Guest

    On Jan 30, 12:26 pm, "Scott Roberts" <-webworks-
    software.com> wrote:
    > >> I just switched jobs joining my third .NET shop.

    >
    > >> My last shop was full of Database developers who built almost all
    > >> business logic into the databases. Most projects ended up with 100s of
    > >> Stored Procedures, Views, function. In some cases cursors were used,
    > >> etc.

    >
    > >> My new shop is looking to implement a Development standard that says,
    > >> if and when possible, do not build code in the database. Instead
    > >> code .NET Business/Data classes that spit out collections and have SQL
    > >> code in the class.

    >
    > >> Any opinions on the difference, where to draw the line and what might
    > >> advantages/disadvantages?

    >
    > >> Thanks in advance for any help or information.

    >
    > >Isn't it preferred to *not* to have extensive amount of raw sql
    > >queries in the classes?  On one hand, it is hard to construct (at
    > >least error-prone), and on the other hand, it isn't as secure/
    > >efficient as stored procedures.  I am working on a small web
    > >application, and I already have 100 stored procedures in the database,
    > >because I don't use raw sql's in my code.

    >
    > It's preferred to not have *any* SQL in your *business classes*. That's what
    > the DAL is for. Choosing to implement your DAL as stored procs defeats much
    > of the purpose of having a DAL, IMO.
    >
    > It's false to say that using parameterized queries is not as
    > secure/efficient as stored procedures.- Hide quoted text -
    >
    > - Show quoted text -


    What is it "to implement DAL as stored procs"? Could you please
    explain? Thanks.
    gnewsgroup, Jan 30, 2008
    #8
  9. sloan Guest

    Design goals come into play here sometimes.

    ..

    If you want to support N number of databases (vendors), and your stuff can
    be written in basic sql...then inline sql can be a good thing.

    If you're 99% a Sql Server shop, then stored procedures make sense. (to me
    anyways).

    There are some many things you can do with derived tables....temp
    tables..and such "tricks of the trade", that I do everything usp's and
    sometimes views. I love derived tables.

    It also encapsulates things well, and you can fine tune certain stored
    procedures if they are bottlenecks.

    ...

    But again, if you have a simple project, simple needs. then inline sql in
    the code isn't a bad idea.

    ...

    Then there is security. usp's can give access to most of the data, but
    still deny rights to the base tables.
    As in..the Emp.AnnualSalary column can be offlimits to the Sql Server Login
    for normal users.

    There is also just the maintenance. Its easier (to me) to open up an actual
    uspABC.sql and work with it there.
    Rather than opening it, getting it to work, and then recoding it back to
    inline sql in the code.


    There are pros and cons. Which (as usual) there is no one blanket
    statement.





    "gnewsgroup" <> wrote in message
    news:...
    On Jan 29, 10:23 pm, wrote:
    > I just switched jobs joining my third .NET shop.
    >
    > My last shop was full of Database developers who built almost all
    > business logic into the databases. Most projects ended up with 100s of
    > Stored Procedures, Views, function. In some cases cursors were used,
    > etc.
    >
    > My new shop is looking to implement a Development standard that says,
    > if and when possible, do not build code in the database. Instead
    > code .NET Business/Data classes that spit out collections and have SQL
    > code in the class.
    >
    > Any opinions on the difference, where to draw the line and what might
    > advantages/disadvantages?
    >
    > Thanks in advance for any help or information.


    Isn't it preferred to *not* to have extensive amount of raw sql
    queries in the classes? On one hand, it is hard to construct (at
    least error-prone), and on the other hand, it isn't as secure/
    efficient as stored procedures. I am working on a small web
    application, and I already have 100 stored procedures in the database,
    because I don't use raw sql's in my code.
    sloan, Jan 31, 2008
    #9
    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. Akhlaq Khan
    Replies:
    4
    Views:
    723
    =?Utf-8?B?Q293Ym95IChHcmVnb3J5IEEuIEJlYW1lcikgLSBN
    Sep 27, 2004
  2. Shaguf
    Replies:
    0
    Views:
    792
    Shaguf
    Nov 28, 2008
  3. Kingdom
    Replies:
    6
    Views:
    134
    Adrienne
    Sep 25, 2003
  4. bj
    Replies:
    1
    Views:
    120
    Scott McNair
    Apr 16, 2004
  5. Matt Harrison

    limiting download size with net::http

    Matt Harrison, Jun 22, 2010, in forum: Ruby
    Replies:
    0
    Views:
    67
    Matt Harrison
    Jun 22, 2010
Loading...

Share This Page