3 Tier development question and MSSQL

  • Thread starter Thread starter Mark
  • Start date Start date
M

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
 
Mark said:
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/archive/2003/11/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/archive/2003/11/18/38178.aspx

FB
 
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,
 
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,
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top