Positioning of Stored Procedures

Hi all,

I have been facing this dilemma since when I started coding in asp.net 2.0. I can have Data Access Layer wherein I can write stored procedures to access the data from database. I can create data access object, data table and all other stuff. Also I can create stored procedure in SQL 2000 server, and then access them from the Data Access layer.

Which of the two method is preferable, and why. i have been searching net for answers to this question since long, but could not find anything.

All answers can contribute may be little but invaluable knowledge.

Thanks.

If security is critical, it's best to use stored procedures always because it lowers the attackable area of your database. I think this is what you are asking.

|||

I wanted to know, which is better:

1) Creating stored procedures in SQL Server 2000 and calling them in the Data access Layer, or may be in the code behind straight away.

2) Creating Table Adapters in Data Access Layer, and creating Table Adapter queries and accessing database or may be stored procedures within the Data Access Layer.

I have been informed that if you create the Table Adapter queries, they are equally secure as stored procedures; though i am not pretty confident about it.

Thanks again.

|||

Tell the truth, the issue is depending on your situation.

If you can connect database and your database permittion contol well, you need to do that on db.

if not, don't do that.

|||

Read this an argument against using SP. This will clarify your question as well

http://www.tonymarston.net/php-mysql/stored-procedures-are-evil.html

Hope that helps

|||

hello.

well, to be honest, sps aren't really something i'd advocate for crud behavior. in my opinion, using parametrized sql is the way to go. the performance/security bla bla that's has been used for several years is a myth and there are some posts out there that just show it. for instance, there's an old discussion between frans bouma and rob howard that started with a post from rob and a very well answer by frans. i'm putting only frans' post here since it is linked to rob's post.

http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx

having said that, i'm not saying that there really isn't a place for sps; just saying that kmost of the time the argument for using them are pure myths!

|||

Hi,

I feel creating stored procedures in SQL Server 2000 and calling them in the Data access Layer is better choice. The book Titled :

"Database programming using C#, VB 2005 and SQL Server 2005", Chapter 10:Developing Components for three-tier applications explains this concept.

Let us say we have developed an three-tier application using SQL server 2000. In future, the same application should able to access/insert to Oracle database. In this situation, writing stored procedures at the server level is better.

I will find out further info on this matter.

|||

With the technologies ASP.NET 2 provide, you r always free to choose the way you like (depending upon your handy side). With the nearly the same amount of effort or even less you can still handle the shift to Oracle database.

But that said, your approach of choosing store procedure suppose to be a little faster in most cases.

|||

hello.

ask4jm:

But that said, your approach of choosing store procedure suppose to be a little faster in most cases

again, this is a known myth. read frans' post to see what i'm speaking about.

|||

You r absolutely rite Luis. Unless the database developers wants to give the data through specific routines hiding rest of the infra, store procedures can be completely avoided.

|||

db2Command cmd= db2Commant();

cmd.Connection=con;
param = new DB2Parameter("@.ClientId", DB2Type.Decimal, 8);
((DbCommand)base.dbSelectCommand[0]).Parameters.Add(param);

//Insert Command and parameters
string sqlInserCommand = "ProcCon";

base.dbInsertCommand = new DbCommand[1];
base.dbInsertCommand[0] = new DB2Command(sqlInserCommand);

param = new DB2Parameter("@.Name", DB2Type.VarChar, 100, "ClientName");
((DbCommand)base.dbInsertCommand[0]).Parameters.Add(param);

param = new DB2Parameter("@.Cid", DB2Type.Int);
((DbCommand)base.dbInsertCommand[0]).Parameters.Add(param);