3 Tier development question and MSSQL

[Mark]

Hi, I am building an application and I have always built applications like
so;

Presentation layer (Html, Webforms etc).
Logic layer (Database access routines etc).
Data layer (SQL database tables, stored procedures etc).

My question is about the Data layer. I have always written MSSQL stored
procedures to do everything (Even deleting a single record from a table) and
I call these stored from the Logic layer.

The place where I am working now wants me to stop writing stored procedures
and just call the Insert/ Update/ Delete routines from within the logic
layer.
e.g.

SqlCommand oCommand = ("DELETE FROM Person WHERE PersonID = 25",
MyDatabaseConnection);

instead of calling the stored procedure like so..

SqlCommand oCommand = ("spDeletePerson 25", MyDatabaseConnection);

My question is, which is the better way? The new companies reasoning is that
we do not have to create the stored procedures on new servers (Easier
replication) if we need to change servers. I think that as long as they are
creating the database tables for the application then the stored procedures
will be created as well. Not to mention that from a security point of view
we only have to give rights to the stored procedures and not to all the
tables and that stored procedures run better etc.

Any recommendations, help appreciated
Thanks in advance
Mark

[The Cleaning Wonder Boy]

Mark wrote:

Hi, I am building an application and I have always built
applications like so;

Presentation layer (Html, Webforms etc).
Logic layer (Database access routines etc).
Data layer (SQL database tables, stored procedures etc).

My question is about the Data layer. I have always written MSSQL
stored procedures to do everything (Even deleting a single record
from a table) and I call these stored from the Logic layer.

The place where I am working now wants me to stop writing stored
procedures and just call the Insert/ Update/ Delete routines from
within the logic layer.
e.g.

SqlCommand oCommand = ("DELETE FROM Person WHERE PersonID = 25",
MyDatabaseConnection);

instead of calling the stored procedure like so..

SqlCommand oCommand = ("spDeletePerson 25", MyDatabaseConnection);

My question is, which is the better way? The new companies reasoning
is that we do not have to create the stored procedures on new servers
(Easier replication) if we need to change servers. I think that as
long as they are creating the database tables for the application
then the stored procedures will be created as well. Not to mention
that from a security point of view we only have to give rights to the
stored procedures and not to all the tables and that stored
procedures run better etc.

Any recommendations, help appreciated
Thanks in advance
Mark

Look at it this way...

Moving to another DMBS would be easier if you didn't use SPs. You
should only use ANSI-SQL for your code as well.

[Sam Gentile [MVP - C#/.NET]]

I am not an expert in this area (my preference is SPs for many reasons not
the least of which is Security) but Rob Howard Microsoft PM for ASP.NET
covered a lot of this here:
http://weblogs.asp.net/rhoward/archi.../17/38095.aspx

There was also a great MSDN article giving a lot of the benefits vs.
tradeoffs that I am searching for.

--
----------------------------------
Sam Gentile
MVP - C#/.NET
INETA Speaker http://www.ineta.org/DesktopDefault.aspx
Read my blog at http://samgentile.com/blog/
------------------------------------------------------
"Mark" <ma*******@n0Sp8mTAIRAWHITIdotAC.NZ> wrote in message
news:fU****************@news.xtra.co.nz...

Hi, I am building an application and I have always built applications like so;

Presentation layer (Html, Webforms etc).
Logic layer (Database access routines etc).
Data layer (SQL database tables, stored procedures etc).

My question is about the Data layer. I have always written MSSQL stored
procedures to do everything (Even deleting a single record from a table) and I call these stored from the Logic layer.

The place where I am working now wants me to stop writing stored procedures and just call the Insert/ Update/ Delete routines from within the logic
layer.
e.g.

SqlCommand oCommand = ("DELETE FROM Person WHERE PersonID = 25",
MyDatabaseConnection);

instead of calling the stored procedure like so..

SqlCommand oCommand = ("spDeletePerson 25", MyDatabaseConnection);

My question is, which is the better way? The new companies reasoning is that we do not have to create the stored procedures on new servers (Easier
replication) if we need to change servers. I think that as long as they are creating the database tables for the application then the stored procedures will be created as well. Not to mention that from a security point of view
we only have to give rights to the stored procedures and not to all the
tables and that stored procedures run better etc.

Any recommendations, help appreciated
Thanks in advance
Mark

[Sam Gentile [MVP - C#/.NET]]

Ah, here it is:
http://msdn.microsoft.com/vstudio/de...ocsnetdev2.asp

An Evaluation of Stored Procedures for the .NET Developer

--
----------------------------------
Sam Gentile
MVP - C#/.NET
INETA Speaker http://www.ineta.org/DesktopDefault.aspx
Read my blog at http://samgentile.com/blog/
------------------------------------------------------
"Sam Gentile [MVP - C#/.NET]" <no****@nomail.com> wrote in message
news:un**************@TK2MSFTNGP12.phx.gbl...

I am not an expert in this area (my preference is SPs for many reasons not
the least of which is Security) but Rob Howard Microsoft PM for ASP.NET
covered a lot of this here:
http://weblogs.asp.net/rhoward/archi.../17/38095.aspx

There was also a great MSDN article giving a lot of the benefits vs.
tradeoffs that I am searching for.

--
----------------------------------
Sam Gentile
MVP - C#/.NET
INETA Speaker http://www.ineta.org/DesktopDefault.aspx
Read my blog at http://samgentile.com/blog/
------------------------------------------------------
"Mark" <ma*******@n0Sp8mTAIRAWHITIdotAC.NZ> wrote in message
news:fU****************@news.xtra.co.nz...

Hi, I am building an application and I have always built applications

like

so;

Presentation layer (Html, Webforms etc).
Logic layer (Database access routines etc).
Data layer (SQL database tables, stored procedures etc).

My question is about the Data layer. I have always written MSSQL stored
procedures to do everything (Even deleting a single record from a table)

and

I call these stored from the Logic layer.

The place where I am working now wants me to stop writing stored

procedures

and just call the Insert/ Update/ Delete routines from within the logic
layer.
e.g.

SqlCommand oCommand = ("DELETE FROM Person WHERE PersonID = 25",
MyDatabaseConnection);

instead of calling the stored procedure like so..

SqlCommand oCommand = ("spDeletePerson 25", MyDatabaseConnection);

My question is, which is the better way? The new companies reasoning is

that

we do not have to create the stored procedures on new servers (Easier
replication) if we need to change servers. I think that as long as they

are

creating the database tables for the application then the stored

procedures

will be created as well. Not to mention that from a security point of view we only have to give rights to the stored procedures and not to all the
tables and that stored procedures run better etc.

Any recommendations, help appreciated
Thanks in advance
Mark

[Frans Bouma [C# MVP]]

"Sam Gentile [MVP - C#/.NET]" <no****@nomail.com> wrote in
news:un**************@TK2MSFTNGP12.phx.gbl:

I am not an expert in this area (my preference is SPs for many reasons not
the least of which is Security) but Rob Howard Microsoft PM for ASP.NET
covered a lot of this here:
http://weblogs.asp.net/rhoward/archi.../17/38095.aspx

There was also a great MSDN article giving a lot of the benefits vs.
tradeoffs that I am searching for.

Hi Sam :) , I recognize that article. I remember I wrote an article
about that article :D

http://weblogs.asp.net/fbouma/archiv.../18/38178.aspx

FB

--
Get LLBLGen Pro, the new O/R mapper for .NET: http://www.llblgen.com
My .NET Blog: http://weblogs.asp.net/fbouma
Microsoft C# MVP

[Mark]

Thanks guys :)
Have a great day, Cheers
Mark
"Frans Bouma [C# MVP]" <pe******************@xs4all.nl> wrote in message
news:Xn*********************************@207.46.24 8.16...

"Sam Gentile [MVP - C#/.NET]" <no****@nomail.com> wrote in
news:un**************@TK2MSFTNGP12.phx.gbl:

I am not an expert in this area (my preference is SPs for many reasons not the least of which is Security) but Rob Howard Microsoft PM for ASP.NET
covered a lot of this here:
http://weblogs.asp.net/rhoward/archi.../17/38095.aspx

There was also a great MSDN article giving a lot of the benefits vs.
tradeoffs that I am searching for.

Hi Sam :) , I recognize that article. I remember I wrote an article
about that article :D

http://weblogs.asp.net/fbouma/archiv.../18/38178.aspx

FB

--
Get LLBLGen Pro, the new O/R mapper for .NET: http://www.llblgen.com
My .NET Blog: http://weblogs.asp.net/fbouma
Microsoft C# MVP

[Sam Gentile [MVP - C#/.NET]]

Yes I should have mentioned my pal Fran's rebuttal-)

--
----------------------------------
Sam Gentile
MVP - C#/.NET
INETA Speaker http://www.ineta.org/DesktopDefault.aspx
Read my blog at http://samgentile.com/blog/
------------------------------------------------------
"Frans Bouma [C# MVP]" <pe******************@xs4all.nl> wrote in message
news:Xn*********************************@207.46.24 8.16...

"Sam Gentile [MVP - C#/.NET]" <no****@nomail.com> wrote in
news:un**************@TK2MSFTNGP12.phx.gbl:

I am not an expert in this area (my preference is SPs for many reasons not the least of which is Security) but Rob Howard Microsoft PM for ASP.NET
covered a lot of this here:
http://weblogs.asp.net/rhoward/archi.../17/38095.aspx

There was also a great MSDN article giving a lot of the benefits vs.
tradeoffs that I am searching for.

Hi Sam :) , I recognize that article. I remember I wrote an article
about that article :D

http://weblogs.asp.net/fbouma/archiv.../18/38178.aspx

FB

--
Get LLBLGen Pro, the new O/R mapper for .NET: http://www.llblgen.com
My .NET Blog: http://weblogs.asp.net/fbouma
Microsoft C# MVP

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

See comments inline.

My question is about the Data layer. I have always written MSSQL stored
procedures to do everything (Even deleting a single record from a table) and I call these stored from the Logic layer.
I do the same thing, all the contacts with the DB is through SP.
SqlCommand oCommand = ("DELETE FROM Person WHERE PersonID = 25",
MyDatabaseConnection);

instead of calling the stored procedure like so..

SqlCommand oCommand = ("spDeletePerson 25", MyDatabaseConnection);

My question is, which is the better way?
IMHO you should alway use SP instead of quering the tables , I do not have
any especific links now though,
Using SP has several advantages, they works as logic layer to the physical
tables, this give you flexibility to redesign the DB struct and you have to
change NONE of the code that use them, you only change the SP, this is a
HUGE improvement., think about if you change the way the Person data is
stored; if y ou use simple queries from the code you would have to change
ALL the code that query the Person table, this can be dantesque, more if you
have more than one application using the same DB.

The SP are precompiled, the queries must be compiled each time ( I think
SQL2K has some optimization about this but they are connection's dependand )
this improve the performance of the application,

I can think of others motives, include the security but the above are more
than enough for using SP.

The new companies reasoning is that we do not have to create the stored procedures on new servers (Easier
replication) if we need to change servers. I think that as long as they are creating the database tables for the application then the stored procedures will be created as well.

That's correct, I do not see any logic in what they say. Replication is for
the data, The SP are as part of the DB as the tables, you create them both
in the same way.
Maybe they are refering to DTS packages where you have the option of create
the tables, but then you have the same problem with the FKs , I do not think
they are "transfered" with DTS. So in no way they are correct.
Cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

[Ignacio Machin \( .NET/ C# MVP \)]

Hi ,

Moving to another DMBS would be easier if you didn't use SPs. You
should only use ANSI-SQL for your code as well.

How many times you have the need to change of DBMS ?
I have never had to do it.

You design the DB based on the DBMS you are going to use, otherwise you lose
a LOT of functionality that the platform provide.

Cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation