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 8 1164
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.
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
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
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
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
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 This discussion thread is closed Replies have been disabled for this discussion. Similar topics
77 posts
views
Thread by nospam |
last post: by
|
25 posts
views
Thread by David Noble |
last post: by
|
51 posts
views
Thread by nospam |
last post: by
|
62 posts
views
Thread by SAN3141 |
last post: by
| | | | | | | | | | |