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 1212
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 thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: nospam |
last post by:
Reasons for a 3-tier achitecture for the WEB?
(NOTE: I said, WEB, NOT WINDOWS.
DON'T shoot your mouth off if you don't understand the difference.)
I hear only one reason and that's to switch a...
|
by: David Noble |
last post by:
We've been developing a web site using 3-tier architecture for 18
months now.
There is a common layer that defines the classes - using XML schemas.
The data layer acts as a wrapper to 3 databases...
|
by: nospam |
last post by:
THIS IS the DOTNETJUNKIES MESSAGE
-------------------------
We're Sorry
As many of you know we have recently launched SqlJunkies.com. We have
overhauled our runtime and will be using it on...
|
by: SAN3141 |
last post by:
There doesn't seem to be consensus about when to put code in the
database or in the middle tier. There was a long discussion about this
in an Oracle newsgroup (message ID:...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |