Limiting T-SQL code as a .NET development standard

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.

Re: Limiting T-SQL code as a .NET development standard

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:8d28306d-a46b-4889-8cd5-524c8448df8b@s13g2000prd.google groups.com...
>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.
>
>
>

Re: Limiting T-SQL code as a .NET development standard

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:8d28306d-a46b-4889-8cd5-524c8448df8b@s13g2000prd.google groups.com...
>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.
>
>
>

Re: Limiting T-SQL code as a .NET development standard

On Jan 30, 3:23=A0am, wild...@noclient.net 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

Re: Limiting T-SQL code as a .NET development standard

http://www.codeproject.com/KB/architecture/DudeWheresMyBusin essLogic.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.pr ogramming/msg/268be0e152d8b876

and even
http://www.sqlservercentral.com/articles/Stored+Procedures/t hezerotonparameterproblem/2283/



wrote in message
news:8d28306d-a46b-4889-8cd5-524c8448df8b@s13g2000prd.google groups.com...
>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.
>
>
>

Re: Limiting T-SQL code as a .NET development standard

On Jan 29, 10:23=A0pm, wild...@noclient.net 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.

Re: Limiting T-SQL code as a .NET development standard

>> 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.

Re: Limiting T-SQL code as a .NET development standard

On Jan 30, 12:26=A0pm, "Scott Roberts" 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? =A0On 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. =A0I 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 wh=
at
> the DAL is for. Choosing to implement your DAL as stored procs defeats muc=
h
> 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.

Re: Limiting T-SQL code as a .NET development standard

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:66023013-f658-4328-817e-9a35ad422687@v17g2000hsa.google groups.com...
On Jan 29, 10:23 pm, wild...@noclient.net 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.