473,593 Members | 2,884 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

3 Tier development question and MSSQL

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",
MyDatabaseConne ction);

instead of calling the stored procedure like so..

SqlCommand oCommand = ("spDeletePerso n 25", MyDatabaseConne ction);

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
Nov 16 '05 #1
8 1222
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",
MyDatabaseConne ction);

instead of calling the stored procedure like so..

SqlCommand oCommand = ("spDeletePerso n 25", MyDatabaseConne ction);

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.
Nov 16 '05 #2
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*******@n0Sp 8mTAIRAWHITIdot AC.NZ> wrote in message
news:fU******** ********@news.x tra.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",
MyDatabaseConne ction);

instead of calling the stored procedure like so..

SqlCommand oCommand = ("spDeletePerso n 25", MyDatabaseConne ction);

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

Nov 16 '05 #3
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******** ******@TK2MSFTN GP12.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*******@n0Sp 8mTAIRAWHITIdot AC.NZ> wrote in message
news:fU******** ********@news.x tra.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",
MyDatabaseConne ction);

instead of calling the stored procedure like so..

SqlCommand oCommand = ("spDeletePerso n 25", MyDatabaseConne ction);

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


Nov 16 '05 #4
"Sam Gentile [MVP - C#/.NET]" <no****@nomail. com> wrote in
news:un******** ******@TK2MSFTN GP12.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
Nov 16 '05 #5
Thanks guys :)
Have a great day, Cheers
Mark
"Frans Bouma [C# MVP]" <pe************ ******@xs4all.n l> wrote in message
news:Xn******** *************** **********@207. 46.248.16...
"Sam Gentile [MVP - C#/.NET]" <no****@nomail. com> wrote in
news:un******** ******@TK2MSFTN GP12.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

Nov 16 '05 #6
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.n l> wrote in message
news:Xn******** *************** **********@207. 46.248.16...
"Sam Gentile [MVP - C#/.NET]" <no****@nomail. com> wrote in
news:un******** ******@TK2MSFTN GP12.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

Nov 16 '05 #7
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",
MyDatabaseConne ction);

instead of calling the stored procedure like so..

SqlCommand oCommand = ("spDeletePerso n 25", MyDatabaseConne ction);

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
Nov 16 '05 #8

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
Nov 16 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

77
5663
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 database from SQL Server to Oracle or DB2 or vice versa... and that's it.... And a lot of these enterprises don't need it as they already know what database they are going to use and they don't plan on switching in and out database in the first...
25
5571
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 - SQL Server, Oracle and AS400. The business layer exposes web services which communicate with the front end, ASP.Net. All 3 tiers are on different boxes. This works well. Now I am leading a team to build a winforms app. I need some advice as
51
4026
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 DotNetJunkies.com also. -------------------------------------------------------------------------- YEP, DOTNET JUNKIES REDESIGN....
62
4086
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: ULcQb.466$KU5.37@nwrddc02.gnilink.net). Elsewhere there's been discussion about Microsoft SQL Server 2005 adding the CLR to support stored procedures in languages such as C#. A scan of the Web and discussion forums finds differing opinions about this.
0
8236
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8227
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
6642
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
5735
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5400
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
3893
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2379
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1467
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1202
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.